getting results from db

[snehula]

Hi there folx!

So, my problem is the following: I have a form where the user can look for a person in the db either by filling in their name, phone no, or other details. On submit, the script builds the sql string depending on which fields were filled in. I obviously have some ugly mistake in the code, since the query result comes back as a boolean instead of data, at least that's what the error says.

$sql = "SELECT idCustomer, firstname, lastname, address1_street, address2_no, county, home_phone FROM persons WHERE ";
 
// expand sql string conditionally if given form field is filled out
 if(!empty($_POST['firstname']))
 { $sql .= " (UPPER(firstname) LIKE'". mysql_real_escape_string(trim(strtoupper($_POST['firstname'])))."' AND ";
 }
 if(!empty($_POST['surname'])) 
 { $sql .= " (UPPER(lastname) LIKE'". mysql_real_escape_string(trim(strtoupper($_POST['surname'])))."' AND ";
 }
 if(!empty($_POST['address'])) 
 { $sql .= " UPPER(address1_street) LIKE'".mysql_real_escape_string(trim(strtoupper($_POST['address'])))."' AND ";
 }
 if(!empty($_POST['phone'])) 
 {
 $phone = mysql_real_escape_string(trim($_POST['phone']));
 $sql .= "(home_phone LIKE'".$phone."' OR work_phone LIKE'".$phone."' OR mobile_phone1 LIKE'".$phone."' OR mobile_phone2 LIKE'".$phone."') AND ";
 }
$sql .= " idCustomer <> NULL"; // this is just to add something to the end of the sql string because of the ANDs
//query database
$result=mysql_query($sql);
$count=mysql_num_rows($result); //here i get an error saying mysql_num_rows() expects parameter 1 to be resource, boolean given...

any help appreciated :-)

[agent_x]

First thing I'd do is change the line

$result = mysql_query($sql);

to

$result = mysql_query($sql) or die(mysql_error() . ", query was $sql");

run that and see what it says.

[snehula]

thanx agent_x i had some spare brackets in my sql syntax and obviously the idCustomer <> NULL didn't work until i put quotes around the null or just put some other value in the quotes that it never has.
Another thing I havent a clue about, me being a total beginner, is how to execute the php script without redirecting to a new page. I'm using ajax to fetch the data and display them in a div as responsetext, and i thought it takes care about staying on the same page without explicitly forcing it to, but obviously it doesn't.
i tried returning false to the submit's onclick event that calls the ajax function, but in vain (is that only for links?). Setting a header location in the php script back to the same page i'd guess is a useless idea, coz why then am i using ajax at all?
I'm a confused..

[snehula]

ok sorry.. i had the form action set, which i shouldn't have had..
silly me, sorry..

[agent_x]

Instead of writing

$sql .= " idCustomer <> NULL";

you can just do

$sql .= " 1";

So effectively, without any other clauses, your SQL boils down to

"SELECT blah blah FROM persons WHERE 1"

which means select every row, since the condition "1" evaluates to true, and is more efficient as MySQL doesn't have to fetch data from a column for a redundant clause.

[snehula]

well i dont want to select ever row, i only added that &sql .="idCustomer <> NULL" because I cannot tell for sure which fields the user is going to be searching on (if it's firstname lastname or address or whatever) and I have a couple of ifs that expand the sql string's 'where' clause conditionally (depending on whether the field was filled out in the form). I ended each expanded sql string with AND to accomodate the possibility of adding another condition (if another form field is filled). So, if a user wants to search someone by surname and address, to narrow down the search, he can do that. But if i don't add that unconditional &sql .="idCustomer <> NULL" there will be a trailing AND at the end of the statement with nothing coming after it. I didnt know what to add there so as not to change the query, so i thought since no customerid is ever null, that might work. If there's a better safer nicer way to do it, i'll be happy to learn about it :-)

[agent_x]

Yes exactly. If you start with a line of SQL that reads

"SELECT blah FROM table WHERE 1"

then you can add more conditions as you go so you end up with

"SELECT blah FROM table WHERE 1 AND condition1 AND condition2"

and it will be valid and select the rows you want. Even if you don't add any more conditions, "WHERE 1" on its own is still valid and will select all the rows.

[rocknbil]

You have no spaces after "like" in the phone part of the clause, but I would take a completely different approach. You are doing a "patch"

// this is just to add something to the end of the sql string because of the ANDs

When in fact, you should just not add and's unless you need them. :-) Second, "like" is a wildcard and has overhead, you should only use it if you need to. Like won't work on a phone anyway without some serious restrictions on how you input the data to the database. If you have

123-456-7890

in the database, none of these would match, even on a like

1234567890
123.456.7890
123 456 7890
[phone]
1-123-456-7890

There are more complex ways around this, but for now let's go with exact match, =.

Last, it's a **really bad idea** to use the same field names in your database as in your forms. Below shows you how to alias those.

So you may or MAY NOT have a where clause. Build your where clause first, then append it to the select. We simplify this by setting it to null at the outset, which allows us to do a simple if ($where) { then append. }

$where = null; 
// FORM FIELDS as keys, DB FIELDS as values. 
$possible_vars = Array( 
'fname' => 'firstname', 
'lname' => 'surname', 
'addr' => 'address', 
'ph' => 'phone' 
); 
// 
foreach ($possible_vars as $key => $value) { 
 if (! empty($_POST[$key])) { 
 // Only add AND if $where has already begun 
 if ($where) { $where .= " and"; } 
 if ($key == 'ph') { 
 $phone = mysql_real_escape_string(trim($_POST[$key])); 
 $where .= " (home_phone = '".$phone."' OR work_phone = '".$phone. 
 "' OR mobile_phone1 = '".$phone."' OR mobile_phone2 = '".$phone."')"; 
 } 
 else { $where .= " upper($value) = '" . mysql_real_escape_string(trim(strtoupper($_POST[$value]))) . "'"; } 
 } 
} 
$sql = "SELECT idCustomer, firstname, lastname, address1_street, address2_no, county, home_phone FROM persons"; 
if ($where) { $sql .= " where $where"; } 
// echo $sql; exit; // use this to check it 
$result=mysql_query($sql) or die("Cannot execute query: " . mysql_error()); 
$count=mysql_num_rows($result); 

A note when building a string this way, be mindful of the spaces in the concatenation. I have them all at the beginnings.

$where .= " and";
$where .= " (home....
$where .= " upper($v ....
$sql .= " where ....

Pick one or the other, beginning or end of concatenation, always include the spaces and you won't get mySQL errors over it.

[snehula]

thanks a lot guys :-)