Posted by s on 03/31/06 06:07

On Fri, 31 Mar 2006 02:10:20 GMT, Harold Ralston
<lumina_car@yahoo.com> wrote:

>I have been trying to learn enough PHP and MySQL to use a query with
>one of my genealogy data pages, instead of viewers needing to browse
>through it all. What little I have learned is from the Library Books
>at our local library.

Great! Make sure to check out the PHP manual
<http://www.php.net/manual/en/> and the MySQL documentation
<http://dev.mysql.com/doc/> for answers that'll come even faster than
those from the hardcopy library :)

>This PHP code works fine until I insert the line beginning with AND,
>then it won't execute.
>Have I missed some punctuation?
>Thanks for any help.
>
>$query = "SELECT pictures.markername, pictures.image,
>pictures.field1, places.place
>FROM pictures,places
>WHERE (pictures.place_id=places.place_id)
>AND (pictures.markername LIKE $surname )
>ORDER BY pictures.markername ASC";
>$result = mysql_query($query)
> or die ("Couldn't execute query.");

You're missing a couple of things, and you already know what one of
them is. For the following code, instead of replicating your entire
SQL query, I'm only going to show the line you've had trouble with
(the AND clause)... Just picture it in context of the full query.

First off, when you're using strings in queries, you've got to put
them inside quotes, like so:

AND (pictures.markername LIKE '$surname')

Secondly, the LIKE operator in SQL is mostly useless without at least
one % character, which SQL interprets as a wildcard. So, suppose
$surname is "Smith" and you wanted to find records with a markername
column that started with "Smith," you'd use:

AND (pictures.markername LIKE '$surname%')

The following fields would match:

Smith
Smithton
Smithhardt

....but not Goldsmith.

Or maybe you wanted it the other way around:

AND (pictures.markername LIKE '%$surname')

This would match:

Smith
Goldsmith
Hammersmith

...but not Smithton.

You can even combine them,

AND (pictures.markername LIKE '%$surname%')

That would match all of the names mentioned so far, and any other name
that started with, ended with, or contained "Smith."

If you're not looking to do "fuzzy" matching like this, and you only
want an exact match, use the equals operator instead of LIKE:

AND (pictures.markername = '$surname')

One other thing you should be mindful of. If there's even the remotest
possibility that your variable could contain an apostrophe, quotation
mark, or other meaningful SQL character, you should escape the
variable first. For example, imagine the following query when $surname
= "O'Connor" ...

SELECT * FROM userData WHERE surname='$surname'

Suddenly it expands to:

SELECT * FROM userData WHERE surname='O'Connor'

....which will generate an error, because SQL sees you comparing
surname to 'O' and has no idea what the remaining Connor' is all
about. Worse, failure to escape criteria can lead to SQL injection,
which is mostly a fancy term for "random people can manipulate the
records in your database." So, it's best to escape out of habit:

$surname = mysql_real_escape_string($surname);

...prior to using $surname in your SQL query.

A parting tip, use the mysql_error() function instead of simply dying
with a static string:

$result = mysql_query($query) or die(mysql_error());

This will give you more detailed information about why your query
failed.

hth

Shaun

--
<s@guerril.la.ape> (to email, remove .ape)
--

[Back to original message]