Trouble with query declaring 2 different variables?

Question

I'm trying to pull results based on ONLY a state entry OR a state and city entry from POST and its driving me nuts...

Thanks in advance!

$totnum=mysql_query("SELECT item_id,active from items WHERE state='$st' OR city+'$_POST[city]' AND state='$st' AND active='1'");
$totalnumber=mysql_num_rows($totnum);
$totrow=mysql_fetch_array($totnum);

Expected Result:

State = Florida

OR

State = Florida City = Daytona Beach

Have you tried using parentheses in your SQL statement to control the precedence of your ANDs and ORs? They work. — O. Jones, Jan 25 '15 at 01:43
Yes the `+` was a mistake but still not the answer. Im using ajax to pull cities on form page and for some reason the `POST` data comes through okay if the city is a single word but if its 2 words it only gives first word @Sean — Marty Demichele, Jan 25 '15 at 04:28
If your ajax is only sending the first word, when the city is two words, my first recommendation is to look at your city html code. Do you have quotes around the value -> `value="Daytona Beach"`? Typically when only the 1st word is sent, you are missing the quotes -> `value=Daytona Beach` — Sean, Jan 25 '15 at 04:39
@Sean Yes, that was part of the problem. I had to get crafty for it to work. Thanks for your input, answer below — Marty Demichele, Jan 25 '15 at 07:38

score 0 · Accepted Answer · answered Jan 25 '15 at 01:43

0

First of all, can you display the SQL?

$sql = "SELECT item_id,active from items WHERE state='$st' OR city+'$_POST[city]' AND state='$st' AND active='1'";
echo $sql; //Let's see

$totnum=mysql_query($sql);
$totalnumber=mysql_num_rows($totnum);
$totrow=mysql_fetch_array($totnum);

Then... try run it on "phpMyAdmin" for testing...

2 - Your SQL is very very dangerous try read about "SQL Injection" and change your queries to Prepared Statements

answered Jan 25 '15 at 01:43

xkothe

633
1
5
18

gives this error `SELECT item_id from items WHERE state='Florida' OR city+'AUBURNDALE' AND state='Florida' AND active='1' Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /srv/disk11/1786977/www/.com/search_results.php on line 79 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /srv/disk11/1786977/www/.com/search_results.php on line 80` – Marty Demichele Jan 25 '15 at 01:52
@MartyDemichele: http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole – CBroe Jan 25 '15 at 04:14

score 0 · Answer 2 · answered Jan 25 '15 at 07:29

This is what I had to do, it took a few hours to figure it out but now I get correct results...Thanks for everyones input. Forgive me for being an amateur.

if($_POST['state'] AND $_POST['city']=="Select City"){

        $search_fields[]=" state='$st'";
        $showsearch[]=" $st ";
    } else {
if($_POST['state'] AND $_POST['city']){

        $search_fields[]=" state='$st' AND city1='$_POST[city]'";
        $showsearch[]=" $st - $_POST[city]";
    }
}

$search_fields = implode(' AND ',$search_fields);
$showsearch = implode(" AND ",$showsearch);

$sql = "SELECT item_id from items WHERE $search_fields AND active='1'";

$totnum=mysql_query($sql);
$totalnumber=mysql_num_rows($totnum);
$totrow=mysql_fetch_array($totnum);

Trouble with query declaring 2 different variables?

2 Answers2