Error in mysql query in select

Question

I have a problem, I wanted to make a query to the search form data where people insert some filters will search for products appear less in this specific case are houses. had made a query with "or" does not work as well as this it makes the fields alone, and "and" not also works because I do not want users to fill out all the fields, so is the they want, so I did it

$texto = array();
$contador=0;

if($_SESSION["finalidade"]!=""){
    $contador++;
    $texto[1]="`finalidade` LIKE ".$finalidade;}

if($_SESSION["tipoimovel"]!=""){
    $contador++;
    $texto[2]="`tipoimovel` LIKE ".$tipoimovel;}

if($_SESSION["nquarto"]!=0){
    $contador++;
    $texto[3]="`nquartos` = ".$nquarto;}

if($_SESSION["pmin"]!=0){
    $contador++;
    $texto[4]="`preco` > ".$pmin;}

if($_SESSION["pmax"]!=0){
    $contador++;
    $texto[5]="`preco` < ".$pmax;}

if($_SESSION["elevador"]!=""){
    $contador++;
    $texto[6]="`elevador` LIKE ".$elevador;}

if($_SESSION["garagem"]!=""){
    $contador++;
    $texto[7]="`garagem` LIKE ".$garagem;}

if($_SESSION["vistapriv"]!=""){
    $contador++;
    $texto[8]="`vistapreveligiada` LIKE ".$vistapriv;}

if($_SESSION["piscina"]!=""){
    $contador++;
    $texto[9]="`piscina` LIKE ".$piscina;}

if($_SESSION["jardim"]!=""){
    $contador++;
    $texto[10]="`jardim` LIKE ".$jardim;}

if($_SESSION["condominiof"]!=""){
    $contador++;
    $texto[11]="`condominio` LIKE ".$condominiof;}

if($_SESSION["conselho"]!=""){
    $contador++;
    $texto[12]="`conselho` LIKE ".$conselho;}

if($_SESSION["frequesia"]!=""){
    $contador++;
    $texto[13]="`frequesia` LIKE ".$frequesia;}

if($_SESSION["refimovel"]!=""){
    $contador++;
    $texto[14]="`referencia` LIKE ".$refimovel;}

    $textocompleto="";


$contador2=1;

for($y = 0; $y < 16; $y++) {

    if($texto[$y]!=""){
        if($contador2==1 && $contador2!=$contador){
            $contador2++;
            ;
        $textocompleto=$texto[$y]." AND ";  }
        elseif($contador2==$contador){
            $contador2++;

        $textocompleto=$textocompleto.$texto[$y];}
        elseif($contador2==1 && $contador2==$contador){
            $contador2++;

        $textocompleto=$texto[$y];  }
        else {
            $contador2++;

        $textocompleto=$textocompleto.$texto[$y]." AND ";}



    }



}

$results = $mysqli->prepare("SELECT ID, imagem, frequesia ,conselho, preco FROM `imovel` WHERE ?  ORDER BY `imovel`.`preco` DESC LIMIT ?,? ");
$results->bind_param("sii",$textocompleto,  $page_position, $item_per_page);
$results->execute();
$results->bind_result($id ,$imagem, $frequesia ,$conselho, $preco); //bind variables to prepared statement
}

the problem is that it is not me to return anything, does not show me any value. already do not know what else to do because if you make a if for each of the possibilities will give more than 100 if and will take a long time. if anyone knows of some effective and quick way to do this please help me, because in fact not know what else to do to fix this problem, thank you

Firstly, did you start the session? Then you have 2x (valid) `?` but 3 in your binds. You're not checking for errors here. Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. — Funk Forty Niner, Dec 15 '15 at 14:02
Plus, you can't bind columns `WHERE ?` and checking for errors would have told you about that. Which is why it's failing. — Funk Forty Niner, Dec 15 '15 at 14:03
yeah i start session_start(); $muda=$_SESSION["muda"] ; i have 3 ? WHERE ? LIMIT ?,? "); — Igor Ponte, Dec 15 '15 at 14:04
do a "echo" on the prepare("SELECT...") with the bind variables and the put the output in your SQL Database. Thats how you get a right Error message. Try it. — MCSell, Dec 15 '15 at 14:05
ahh, my fear was even this ... and have any idea how phaco this? — Igor Ponte, Dec 15 '15 at 14:05
possible duplicate of http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement — Funk Forty Niner, Dec 15 '15 at 14:06
you can't bind tables/columns as per my link above. Plus, what is the value of `$finalidade`? is it a string or an integer? — Funk Forty Niner, Dec 15 '15 at 14:10
It would surprise me if you could pass a WHERE condition as a parameter. I also don't see why you'd use unsafe stringification first and then use a parameter; if you don't care about safety, just concat your complete text and prepare that. However, I would suggest adding both a string and an object in the ifs (also, I'd just put them in the next free slot instead of a fixed one), so `$texto[$contador] = "'column' like ?"; $parametro[$contador] = $column; $contador++;` — Zastai, Dec 15 '15 at 14:12
@Zastai you said like that ´if($_SESSION["finalidade"]!=""){ $contador++; $texto[1]="`finalidade` LIKE ?"; $parametro[1] = $column; $contador++; }´ — Igor Ponte, Dec 15 '15 at 14:28
@IgorPonte No, use contador to index the arrays. That makes them easier to process afterwards. — Zastai, Dec 15 '15 at 14:49

Thorsten Kettner · Answer 1 · 2015-12-15T14:13:56.567

You cannot bind parts of an SQL statement, you can only bind values.

So this is valid:

$results = $mysqli->prepare("SELECT col1, col2 FROM tbl WHERE col3 = ?");
$results->bind_param("i", 1234);

and this is not:

$results = $mysqli->prepare("SELECT col1, col2 FROM tbl WHERE ?");
$results->bind_param("s", "col3 = 1234");

You can concatenate the strings, though:

prepare("SELECT ... WHERE ".$textocompleto." ORDER BY imovel.preco DESC LIMIT ?,? ");

Bruno de baets · Answer 2 · 2015-12-26T13:33:50.080

You can use a prefix on your $\_SESSION variable like sf_{variable_name} for all required fields. After that, you can loop throughout them to build your query (ps : code not tested):

$sql = "SELECT ";
$sql_cond = " WHERE ";
$texto = [];
foreach($_SESSION as $k => $v){
    if(substr($v, 0, 3) == "sf_"){
        $name = substr($k, 3, strlen($k))
        $texto[$name] = $v;
        $sql .= $name.", ";
        $sql_cond .= $name." LIKE :".$name;
    }
}
$final_sql = $sql.$sql_cond;
$results = $mysqli->prepare($final_sql);
foreach($texto as $k => $v){
    $results->bind_param(":".$k, "%".$v."%");
}
$results->execute();

Ok, after some tests, i did the same with PDO object in a test db for medias (PDO is nearly the same stuff than MYSQLI object), and it work fine for me, just adapt your db ids for your case

$pdo = new PDO('mysql:host=localhost;dbname=media_center', 'root', 'root');

$_SESSION["sf_title"] = "Interstellar";
$_SESSION["sf_description"] = "sci-fi";

$sql = "SELECT * ";
$sql_cond = " WHERE ";
$texto = [];

foreach($_SESSION as $k => $v){
    if(substr($k, 0, 3) == "sf_"){
        $name = substr($k, 3, strlen($k));
        $texto[$name] = $v;
        $sql_cond .= $name." LIKE :".$name." OR ";
    }
}

$sql_cond = substr($sql_cond, 0, strlen($sql_cond)-4);
$final_sql = $sql." FROM media ".$sql_cond;
$results = $pdo->prepare($final_sql);

foreach($texto as $k => $v){
    $key = ":".$k;
    $value = "%".$v."%";
    $results->bindParam($key, $value);
}

$results->execute();

this code give me one error "Fatal error: Call to a member function execute() on a non-object" can you help me pls? — Igor Ponte, Dec 18 '15 at 23:16
can you put this code inside my code pls for i see what i do wrong? — Igor Ponte, Dec 18 '15 at 23:26

score -1 · Answer 3 · answered Dec 15 '15 at 14:08

-1

I guess you will need to wrap the values in single qoute ''

For example:

$texto[1]="`finalidade` LIKE ".$finalidade;

should be

$texto[1]="`finalidade` LIKE '".$finalidade."'";

Try this.. I think it will solve it

answered Dec 15 '15 at 14:08

Sanchit

541
2
18

if `$finalidade` is an integer, then their code is valid/correct. – Funk Forty Niner Dec 15 '15 at 14:11
@IgorPonte you didn't respond to an important question earlier. What is the value of `$finalidade`? is it a string or an integer? string = "city" integer = `123` – Funk Forty Niner Dec 15 '15 at 14:16
@IgorPonte then their answer here is wrong and I had a feeling it was from the beginning. – Funk Forty Niner Dec 15 '15 at 14:23
Did you put the single qoutes in all the condition? – Sanchit Dec 15 '15 at 14:38
yes and go completing the measure that you go filling – Igor Ponte Dec 15 '15 at 15:13
Instead of the `foreach` , try `$textocompleto = implode(' AND ',$texto)` – Sanchit Dec 16 '15 at 04:45

Error in mysql query in select

3 Answers3