Hi,

Im having some issues with a search querey that is using pagination and a WHERE clause.

If I just use the querey without the WHERE clause it works fine:

PHP How do I post PHP blocks?

$sql = "SELECT * FROM admin ORDER BY $orderby ASC LIMIT $start_from, $display_no"; 
$rs_result = mysql_query ($sql, $conn); 


but if I add the WHERE clause in:

PHP How do I post PHP blocks?

$sql = "SELECT * FROM admin ORDER BY $orderby WHERE $table_name = '$search_term' ASC LIMIT $start_from, $display_no"; 
$rs_result = mysql_query ($sql, $conn); 


I get this error:

PHP How do I post PHP blocks?

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/fhlinux129/m/mysql.atumstudios.com/user/htdocs/backend/logged/admin_search_records.php on line 109 


Any ideas?

I've tried moving the WHERE clause around the statement but seem to get the same error.

I did try removing the pagination part of the code and keeping the WHERE clause and that seemed to work its just getting them both to work together.

Sorry if this should be in the SQL forum.

I see a few problems.


1. "ORDER BY" should go after "WHERE"
2. "ASC" should immediately follow "ORDER BY"
3. Your variable name is "$table_name", which concerns me.. you can't do a where clause on a table name... only a column name.
4. PHP variables inline in a string should always contain braces {} around them to prevent confusion in the compiler.

Try this:

PHP How do I post PHP blocks?

$sql = "SELECT * 
        FROM admin 
        WHERE {$column_name} = '{$column_value}' 
        ORDER BY {$orderby} ASC 
        LIMIT {$start_from}, {$display_no}"; 


By the way, you need some error checking in your code to ensure that your SQL isn't causing problems.

something like:

PHP How do I post PHP blocks?

$rs = mysql_query($sql);
if (mysql_error()) {
    throw new Exception(mysql_error());
} 


Of course, this should go in a function called by a larger scope that gracefully handles such errors...

Thanks,

That seems to be working now. Now I've just got to worry about the error checking

do you have any links to tutorials on error checking that youd recommend?

I've found this one: http://forums.mysql.com/read.php?98,54059,54140

is that the type of error checking you were refering to? Im learnig this as I go along and just trying to not make too many mistakes so any help is very appreciated.

No, that's handling the error at the SQL level... you only need to handle at the PHP level by degrading gracefully if an error is encountered.

Read up on PHP Exceptions and try... catch statements for details on how to implement error handling in your code.