Filtering data from database

[Martin C++]

Hello I need to filter data from database. Use selects the data he need from selectboxes and inputs. Not all inputs must be filled. Those that arent have value of -1. I have put together a SQL statement that should work. Probably would work without the prepare but I need to have it there. I need to know what is wrong with this code. Why is the WHERE caluse not working there. PS. If i set $where = "1=1" then I can get all data but when I use the code I have which generates WHERE = autod.mark=7 then I dont get any data.

$response['filter'] = $filter->make;
                    $whereClauses = array();
                    if ( $filter->make > 0 ) $whereClauses[] = 'autod.mark='.mysql_real_escape_string($filter->make);
                    if ( $filter->color > 0 ) $whereClauses[] ='autod.varv='.mysql_real_escape_string($filter->color);
                    if ( $filter->model > 0 ) $whereClauses[] = 'autod.mudel='.mysql_real_escape_string($filter->model);
                    if ( !empty($filter->yearFrom) && is_numeric($filter->yearFrom) ) $whereClauses[] = 'aasta >= '.mysql_real_escape_string($filter->yearFrom);
                    if ( !empty($filter->yearTo) && is_numeric($filter->yearFrom) ) $whereClauses[] = 'aasta =< '.mysql_real_escape_string($filter->yearTo);
                    if ( !empty($filter->priceFrom) && is_numeric($filter->priceFrom) ) $whereClauses[] = 'hind >='.mysql_real_escape_string($filter->priceFrom);
                    if ( !empty($filter->priceTo) && is_numeric($filter->priceFrom) ) $whereClauses[] = 'hind =<'.mysql_real_escape_string($filter->priceTo);

                    $where = '';
                    if (count($whereClauses) > 0) {
                        $where .= implode(' AND ',$whereClauses);
                    }

                    $response['filter'] = $where;
                    $cmd = $connect->prepare("SELECT autod.id, mark.mark, mudel.mudel, varv.varv, aasta, hind, kirjeldus FROM autod 
                    INNER JOIN mark ON autod.mark = mark.id
                    INNER JOIN varv ON autod.varv = varv.id
                    INNER JOIN mudel ON autod.mudel = mudel.id
                    WHERE ?
                    ");
                    $cmd->bind_param("s", $where);

[cereal]

Not tested, but if I'm not wrong this $cmd->bind_param("s", $where); will insert the value of $where as a string, so:

'autod.mark=7'

instead of:

`autod`.`mark`=7

If you try to display the error of the query you should get something like:

 Warning | 1292 | Truncated incorrect INTEGER value: 'autod.mark=7' 

[Martin C++]

Cant test it now, but I will later. The worst part is thatI cant see any errors because I am using this with ajax. While we are on the subject maybe you can also tell me how can I display the errors generated by PHP file wtih javascript console, when using jquery ajax ? Thanks for answer. I will let you know if it worked later.

[andris.petrams.1]

Display error's configure in "php.ini" (/etc/php5/apache2/php.ini), read this - http://php.net/display-errors

[Martin C++]

Not tested, but if I'm not wrong this $cmd->bind_param("s", $where); will insert the value of $where as a string, so:

'autod.mark=7'
instead of:

autod.mark=7
If you try to display the error of the query you should get something like:

Warning | 1292 | Truncated incorrect INTEGER value: 'autod.mark=7'

Not tested, but if I'm not wrong this $cmd->bind_param("s", $where); will insert the value of $where as a string, so:

'autod.mark=7'
instead of:

autod.mark=7
If you try to display the error of the query you should get something like:

Warning | 1292 | Truncated incorrect INTEGER value: 'autod.mark=7'

Not tested, but if I'm not wrong this $cmd->bind_param("s", $where); will insert the value of $where as a string, so:

'autod.mark=7'
instead of:

autod.mark=7
If you try to display the error of the query you should get something like:

Warning | 1292 | Truncated incorrect INTEGER value: 'autod.mark=7'

display the e

This is not working. Its says call to "Fatal error: Call to a member function execute()"
I will put the entire code this time

$filter = json_decode(stripslashes($_REQUEST['filter']));
                //$response['filter'] = $filter;
                if(!$filter){
                    $sql = "SELECT autod.id, mark.mark, mudel.mudel, varv.varv, aasta, hind, kirjeldus FROM autod 
                    INNER JOIN mark ON autod.mark = mark.id
                    INNER JOIN varv ON autod.varv = varv.id
                    INNER JOIN mudel ON autod.mudel = mudel.id
                    ";
                }else{
                    $whereClauses = array();
                    if ( $filter->make > 0 ) $whereClauses[] = '`autod.mark`='.mysql_real_escape_string($filter->make);
                    if ( $filter->color > 0 ) $whereClauses[] ='`autod.varv`='.mysql_real_escape_string($filter->color);
                    if ( $filter->model > 0 ) $whereClauses[] = '`autod.mudel`='.mysql_real_escape_string($filter->model);
                    if ( !empty($filter->yearFrom) && is_numeric($filter->yearFrom) ) $whereClauses[] = '`autod.aasta` >= `'.mysql_real_escape_string($filter->yearFrom) .'`';
                    if ( !empty($filter->yearTo) && is_numeric($filter->yearFrom) ) $whereClauses[] = '`autod.aasta` =< `'.mysql_real_escape_string($filter->yearTo) .'`';
                    if ( !empty($filter->priceFrom) && is_numeric($filter->priceFrom) ) $whereClauses[] = '`hind` >='.mysql_real_escape_string($filter->priceFrom);
                    if ( !empty($filter->priceTo) && is_numeric($filter->priceFrom) ) $whereClauses[] = '`hind` =<'.mysql_real_escape_string($filter->priceTo);

                    $where = '';
                    if (count($whereClauses) > 0) {
                        $where = "WHERE" .implode(' AND ',$whereClauses);
                    }

                    $response['filter'] = $where;

                    $sql = "SELECT autod.id, mark.mark, mudel.mudel, varv.varv, aasta, hind, kirjeldus FROM autod 
                    INNER JOIN mark ON autod.mark = mark.id
                    INNER JOIN varv ON autod.varv = varv.id
                    INNER JOIN mudel ON autod.mudel = mudel.id" . $where;
                    $response['filter'] = $sql;
                }

                $cmd = $connect->prepare($sql);
                if(!$cmd){
                    $response['err'] = "Tekkis viga andmete küsimisel!";
                    $response['success'] = false;
                }
                $cmd->execute();
                $cmd->store_result();
                $cmd->bind_result($id, $make, $model, $color, $year, $price, $description);
                $cars = array();
                while($cmd->fetch()){
                    $car = array('id' => $id, 'make' => $make, 'model' => $model, 'color' => $color, 'year' => $year, 'price' => $price, 'description' => $description);
                    $car = json_encode($car);
                    array_push($cars, $car);
                }
                $cmd->close();
                $response['cars'] = $cars;

PS. I also modified the beginning so it form the SQL statement before its sent to prepare. Can you tell me why this error is generated. It works fine without the filter (with the first SQL statement) so the mistake must be in the $sql.

[cereal]

Not:

`autod.mark`

But:

`autod`.`mark`

Also at line 29 there is no space between mudel.id and $where values, so it will generate:

... INNER JOIN mudel ON autod.mudel = mudel.idWHERE`autod.mark`=7;

Which is wrong. To see the error in the query change this:

if(!$cmd){
    $response['err'] = "Tekkis viga andmete küsimisel!";
    $response['success'] = false;
}

to:

if ( ! $cmd) {
    printf("Error message: %s\n", $connect->error);
    die(); # stop this test execution here.

    $response['err'] = "Tekkis viga andmete küsimisel!";
    $response['success'] = false;
}

# optional, but useful to avoid wrong executions
else
{
    # execute the query
}

If you're still using ajax then use the response array, but the lack of the ELSE statement, in case of wrong input/setting, will continue to execute the script generating errors like previous.

Reference: http://www.php.net/manual/en/mysqli.error.php

[Martin C++]

It worked now. I had some other errors there too which I didnt notice before. Thank alot!