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