mysql_error %s in WHERE OR clause

Question

$colname_rs_txtSearchFirstname = $_POST['txt_search'];    
$sql_rs_txtSearch = sprintf("SELECT * FROM staffstu WHERE lastname = %s OR 
                    firstname = %s ORDER BY lastname, firstname ASC", 
                    GetSQLValueString($colname_rs_txtSearchFirstname, "text", 
                    $colname_rs_txtSearchFirstname, "text"));

It always gives

mysql_error

If I do not use "sprintf" that's working fine. I can't find the solution.

You need to enclose string constants in single quotes. So, use `'%s'` for the pattern in the string. — Gordon Linoff, Nov 11 '15 at 01:08
thanks a lot Gordon, I will try it when back to office. May I ask that if only one %s in the statement, why no need to enclose? thanks! — faifai, Nov 11 '15 at 02:34
Hi Gordon, i just tried to enclose by single quotes but same error. — faifai, Nov 11 '15 at 06:43
@faifai Could you echo the string (query), it will be easy to point out error? — Shankar, Nov 11 '15 at 06:51
http://stackoverflow.com/questions/22156640/mysql-query-using-sprintf-in-php — Master Yoda, Nov 11 '15 at 06:54

score 0 · Accepted Answer · edited May 23 '17 at 12:15

You are missing a parameter for the second %s! You should get the following PHP warning

Warning: sprintf(): Too few arguments

$colname_rs_txtSearchFirstname = $_POST['txt_search'];    
$sql_rs_txtSearch = sprintf("SELECT * FROM staffstu WHERE lastname = %s OR 
                firstname = %s ORDER BY lastname, firstname ASC", 
                GetSQLValueString($colname_rs_txtSearchFirstname, "text", 
                $colname_rs_txtSearchFirstname, "text"), <missing_param>);

I think you want this (check if it is correct)

$colname_rs_txtSearchFirstname = $_POST['txt_search'];    
$sql_rs_txtSearch = sprintf("SELECT * FROM staffstu WHERE lastname = '%s' OR 
                firstname = '%s' ORDER BY lastname, firstname ASC", 
                GetSQLValueString($colname_rs_txtSearchFirstname, "text"), 
                GetSQLValueString($colname_rs_txtSearchLastname, "text"));

You should not use the mysql_* functions. More information here: Why shouldn't I use mysql_* functions in PHP?

Thanks sebastianbrosch, it work now after added your suggested arguments and remove the type of variable ($colname_rs_txtSearchFirstname) — faifai, Nov 11 '15 at 11:52

mysql_error %s in WHERE OR clause

1 Answers1