Is it possible to use a MySQL query in a PHP variable?

Question

Edit: I've changed the query to this version but I'm still not getting any results even when I should be.

if (isset($_POST['schbttn'])) {
$breed1 = $_POST['schbreed1'];
$breed2 = $_POST['schbreed2'];
$sex = $_POST['schsex'];
$colour = $_POST['schcolour'];
$age = $_POST['schage'];
include ('inc/dbconn.php');
// If breed2 NULL, search with this query
if ($breed2 == "NULL") {
    $search = mysqli_query($dbconn, "SELECT * FROM `lstfnd` WHERE `doglf_stat` = 'Lost' AND `doglf_breed1` = '$breed1' AND `doglf_breed2` IS NULL AND `doglf_sex` = '$sex' AND `doglf_colour` = '$colour' AND `doglf_age` = '$age'");
// Else search with this query
} else {
    $search = mysqli_query($dbconn, "SELECT * FROM `lstfnd` WHERE `doglf_stat` = 'Lost' AND `doglf_breed1` = '$breed1' AND `doglf_breed2` = '$breed2' AND `doglf_sex` = '$sex' AND `doglf_colour` = '$colour' AND `doglf_age` = '$age'");
}
$schrow = mysqli_fetch_assoc($search);
}

I'm trying to create a simple search function where a user can search by multiple fields.

I've taken the entries of each field

$breed1 = $_POST['breed1'];
$breed2 = $_POST['breed2'];
$sex = $_POST['sex'];
$colour = $_POST['colour'];
$age = $_POST['age'];

and built the query through if loops

$query = "SELECT * FROM `table` WHERE `stat` = 'Lost'";
// If breed1 is not ALL, add to search
if ($breed1 != "ALL") {
    $query = $query." AND `breed1` = '$breed1'";
}
// If breed2 is not ALL, add to search
if ($breed2 != "ALL") {
    if ($breed2 == "NULL") {
        $query = $query." AND `breed2` IS NULL";
    } else {
        $query = $query." AND `breed2` = '$breed2'";
    }
}
// If sex is not ALL, add to search
if ($sex != "ALL") {
    $query = $query." AND `sex` = '$sex'";
}   
// If colour is not ALL, add to search
if ($colour != "ALL") {
    $query = $query." AND `colour` = '$colour'";
}
// If age is not ALL, add to search
if ($age != "ALL") {
    $query = $query." AND `age` = '$age'";
}
$query = $query.";";

and placed the query in a PHP variable to use when running the query.

include ('inc/dbconn.php');
$search = mysqli_query($dbconn, "'.$query.'");
$schrow = mysqli_fetch_assoc($search);

However, when I try to display the results of the search, I get an error code.

 mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given...

So is what I am attempting possible to accomplish using this method? And if not, any suggestions for alternative methods?

Its totally possible, and the error you're getting back [already has a solution](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole) — castis, May 28 '15 at 17:34
**USE PREPARED STATEMENTS!** Right now, you are wide open to SQL injection attacks. — Jack, May 28 '15 at 19:45
I've got validation, escaping special characters, etc. in the actual code. Right now I'm more interested in getting it to function, as I don't have the time to do anything else to it. — , May 28 '15 at 20:02

score 0 · Answer 1 · answered May 28 '15 at 17:49

0

change this line

$search = mysqli_query($dbconn, "'.$query.'");

to

$search = mysqli_query($dbconn, $query);

$query is variable, do not use that as string.

answered May 28 '15 at 17:49

Ethan.R

157
2
16

That's the first version I tried, still got the same error. PHP documentation says the query is meant to be a string, that's why I tried this. – May 28 '15 at 18:20
use var_dump() before execute the query and see what is the data type, is this string or not/ – Ethan.R May 28 '15 at 18:27
`string(75) "SELECT * FROM `lstfnd` WHERE `doglf_stat` = 'Lost' AND `doglf_breed2` = '';" ` – May 28 '15 at 18:39
problem is here, ( = '';" ) use single quote ' instead double quote – Ethan.R May 28 '15 at 18:53
Which line is this? `$query = $query.";";` ? – May 28 '15 at 18:59
no, i think one of those variables got this double quote, i have no idea witch one, i suggest var_dump all variables to find out. – Ethan.R May 28 '15 at 19:15

Is it possible to use a MySQL query in a PHP variable?

1 Answers1