Re: PHP query using WHERE with AND

Shaun-
Thanks! You've gotten me onto the correct path now.
Harold

s wrote:


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 :)



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@xxxxxxxxxxxxxx> (to email, remove .ape)
--

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: PHP query using WHERE with AND
    ... AND (pictures.markername LIKE $surname) ... Just picture it in context of the full query. ... the LIKE operator in SQL is mostly useless without at least ... Worse, failure to escape criteria can lead to SQL injection, ...
    (alt.php)