phpjqueryredbean

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


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?


Solution

  • 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)); 
        }