Sort publications by form data. Error querying database (RedBeanPHP)

Question

I need to sort publications by the data that is received from the form.

I get this error:

Fatal error: Uncaught [42000] - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sity = 'sity1' make = 'make1' model = 'model1' volume = '' mileage = '' numOfOwn' at line 1 trace: #0 D:\PHP\OSPanel\domains\test\libs\rb.php(1080): RedBeanPHP\Driver\RPDO->runQuery('SELECT * FROM p...', Array) #1 D:\PHP\OSPanel\domains\test\libs\rb.php(4245): RedBeanPHP\Driver\RPDO->GetAll('SELECT * FROM p...', Array) #2 D:\PHP\OSPanel\domains\test\libs\rb.php(12311): RedBeanPHP\Adapter\DBAdapter->get('SELECT * FROM p...', Array) #3 D:\PHP\OSPanel\domains\test\libs\rb.php(13324): RedBeanPHP\Facade::query('get', 'SELECT * FROM p...', Array) #4 D:\PHP\OSPanel\domains\test\index.php(31): RedBeanPHP\Facade::getAll('SELECT * FROM p...', Array) #5 {main} thrown in D:\PHP\OSPanel\domains\test\libs\rb.php on line 810

Why can't I pass a query string as an argument to a function?

I tried to come up with a different algorithm, so as not to pass the string $sql into the argument of the R::getAll() function, but it didn’t work.

Here is the form itself (in index.php):

            <form class = "search_form">
                <select name = "region">
                    <option value = "none" hidden = ""> Select a region </option>
                    <option value = "region1"> Region # 1 </option>
                    <option value = "region2"> Region # 2 </option>
                    <option value = "region3"> Region # 3 </option>
                    <option value = "region4"> Region # 4 </option>
                </select>
                <select name = "sity">
                    <option value = "none" hidden = ""> Select a city </option>
                    <option value = "sity1"> City # 1 </option>
                    <option value = "sity2"> City # 2 </option>
                    <option value = "sity3"> City # 3 </option>
                    <option value = "sity4"> City # 4 </option>
                </select>
                <select name = "make">
                    <option value = "none" hidden = ""> Select a make </option>
                    <option value = "make1"> Make # 1 </option>
                    <option value = "make2"> Make # 2 </option>
                    <option value = "make3"> Make # 3 </option>
                    <option value = "make4"> Make # 4 </option>
                </select>
                <select name = "model">
                    <option value = "none" hidden = ""> Select a model </option>
                    <option value = "model1"> Model # 1 </option>
                    <option value = "model2"> Model # 2 </option>
                    <option value = "model3"> Model # 3 </option>
                    <option value = "model4"> Model # 4 </option>
                </select>
                <input type = "number" name = "volume" min = "0" step = "any" placeholder = "Enter engine volume">
                <input type = "number" name = "mileage" min = "0" placeholder = "Enter vehicle mileage">
                <input type = "number" name = "numOfOwners" min = "0" placeholder = "Enter the number of hosts">
                <button type = "submit" class = "search-btn btn btn-warning"> Search </button>
            </form>

index.php:

$sql = "SELECT * FROM publication";
    if(!empty($data)){
        $sql .=" WHERE";
        $bindings = [];
        $i = 0;
        foreach ($data as $key => $value) {
            $bindings[$i] = $value;
            $i++;
            if($data->next){
                $sql .= " $key = ? AND";
            }else{
                $sql .=" $key = ?";
            }
        }


        $publications = R::getAll($sql, $bindings);
        exit(json_encode($publications));
    }else{
        $publications = R::findAll('publication', "ORDER BY `id` DESC LIMIT ?, ?", array($from, $publication_on_page)); 
    }

main.js:

$('.search-btn').click(function (e){
    e.preventDefault();

    let region = $('select[name="region"]').val(),
        sity = $('select[name="sity"]').val(),
        make = $('select[name="make"]').val(),
        model = $('select[name="model"]').val(),
        volume = $('input[name="volume"]').val(),
        mileage = $('input[name="mileage"]').val(),
        numOfOwners = $('input[name="mileage"]').val();

    let formData = new FormData();
    if(region != 'none'){
        formData.append('region', region);
    }
    if(sity != 'none'){
        formData.append('sity', sity);
    }
    if(make != 'none'){
        formData.append('make', make);
    }
    if(model != 'none'){
        formData.append('model', model);
    }
    if(volume != ''){
        formData.append('volume', volume);
    }
    if(mileage != ''){
        formData.append('mileage', mileage);
    }
    if(numOfOwners != ''){
        formData.append('numOfOwners', numOfOwners);
    }

    $.ajax({
        url: 'index.php',
        type: 'GET',
        dataType: 'json',
        processData: false,
        contentType: false,
        cache: false,
        data: formData,
        success (data) {
            $("#publ").html('');
            for(value in data){
                $("#publ").append(
                    '<div class="col-md-6" style="background-color: red;">' + data[value]['id'] + '</div>'
                );
            }
        }
    });
});

Tell me if I'm doing sorting right?

You should be seeing this: `Notice: Trying to get property 'next' of non-object`. Do you have [error reporting](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) turned on? — El_Vanja, Mar 26 '20 at 17:14
Your `if($data->next)` is never true (see my first comment) and thus your `WHERE` clause is built without any `AND` keywords. — El_Vanja, Mar 26 '20 at 17:15
@El_Vanja, unfortunately, error reporting is turned off. What can replace $data->next? — arxfatalis, Mar 26 '20 at 17:21
Based on the error on the query syntax, you are missing the `and` keyword in the query - `'sity = 'sity1' make = 'make1' model = 'model1' volume = '' mileage = '' numOfOwn'`. The query should be - `'sity = 'sity1' and make = 'make1' and model = 'model1' and volume = '' mileage = '' numOfOwn'` and so on. — Benny, Mar 26 '20 at 20:02

score -1 · Accepted Answer · answered Mar 26 '20 at 17:20

It seems that $data->next is not working here, maybe because $data is an array and not an object, you can try this:

    $sql = "SELECT * FROM publication";
    if(!empty($data)){
        // Create an array for columns to make the filter
        $filters = [];
        $bindings = [];
        foreach ($data as $key => $value) {
            // No need to index for $bindings
            $bindings[] = $value;
            // Add column and ? to array
            $filters[] = "$key = ?";
        }
        // Apply glue to get the query
        $sql .= ' WHERE ' . implode(' AND ', $filters);

        // Do you want to order these too?

        $publications = R::getAll($sql, $bindings);
        exit(json_encode($publications));
    }else{
        $publications = R::findAll('publication', "ORDER BY `id` DESC LIMIT ?, ?", array($from, $publication_on_page)); 
    }

Sort publications by form data. Error querying database (RedBeanPHP)

1 Answers1