Why my search wont refine!?!?!

Question

I have this bit of code:

//Restrict the SQL query with an AND clause if a member has been selected
if ($form_member_id != 0) {
 $query .= "AND photos.member_id = '$form_member_id' ";
}

It is meant to refine a search query down to only the selected user, so the whole query together reads:

SELECT
photos.photo_id, members.member_name, photos.photo_title, photos.photo_film, photos.photo_height, photos.photo_width
FROM members, photos
WHERE members.member_id = photos.member_id
    AND photos.member_id = '$form_member_id'

For some reason this does not work, ive tested the query and it works fine, but for some reason it wont work with the code ive written. I've checked for difference in the names of the variables but they are all the same....anyone know why its not working!!!

Answer 1

Obvious step is to print the full query before running it, then run it manually and see what happens. Also, you should be using prepared statements.

Answer 2

I think that you need a space before your AND otherwise it will be:

WHERE members.member_id = photos.member_idAND photos.member_id = '$form_member_id'

instead of

WHERE members.member_id = photos.member_id AND photos.member_id = '$form_member_id'

Ditto what @Matthew says about using parameterized queries, but I still think the above is the issue.

Answer 3

One other thing, and I'm not experienced with php, but your code looks prime-target for SQL-Injection attacks...

Someone could stuff the buffer for your "$form_member_id" and put in a value like

'; truncate members; '

where the leading and trailing quote are part of the submitted string... the first '; will terminate your string, ; to end a statement, and then truncate your table and ignore the rest...

Again, I'm not a PHP person, but so many other historical security postings talk about PARAMETERIZING your queries to prevent such injection attacks

Answer 4

debug the script using

if ($form_member_id != 0) {
        $query .= "AND photos.member_id = '$form_member_id' ";
        die($query);
}

copy and paste the query and run it in phymyadmin or etc to figure out the source of bug

for better security, you may want it to be like this

if ($form_member_id != 0) {
        $query .= "AND photos.member_id = '" . mysql_real_escape_string($form_member_id) . "' ";
}

Answer 5

You should echo the query if its now working for you to see what seems to be the problem :)