PHP MYSQL (MariaDB - XAMPP) Problem with returning results with more than one parameters in a search engine web app

Question

I have created a dynamic website for car classifieds where anyone can insert the data of their car along with their images of the specific car. The problem that i cant solve and have spend over 15++ hours to solve is in the search area where in the page PHP-SearchInfo.php there are six fields (mark, model, edition, color, cc_from, and cc_to) where when the user enters then the results go to an other page the PHP-SearchResults. In my code if only one field is entered it works, also the cc_from and cc_to works, and finally if only the two fields (mark and model) are filled again it works. THE PROBLEM THAT I CANT SOLVE IS WHEN THE USER ENTERS THREE VALUES (mark,model,edition) THEN IT DOES NOT WORK AND IT TELLS ME THAT THERE IS AN ERROR IN MY SYNTAX (BUT INSIDE THE XAMPP IT WORKS PERFECTLY THE RESULT THAT I WANT). Can someone please help me with this search problem. Thank You A L

**PHP-SearchResults:**
if ($_GET['mark'] != "" || $_GET['model'] != "" || $_GET['edition'] != "" || 
$_GET['color'] != "" ||        $_GET['cc_from'] != "" || $_GET['cc_to'] != "")

$mark = $_GET['mark'];
$model = $_GET['model'];
$edition = $_GET['edition'];
$color = $_GET['color'];
$cc_from = $_GET['cc_from'];
$cc_to = $_GET['cc_to'];

$connection = new mysqli ($dbhost, $dbuser, $dbpass, $dbname);
$search_query = "SELECT * FROM car_classifieds NATURAL JOIN cars_classified_photos WHERE ";
// MySQL Query - Only with $mark
if($mark)
{
$search_query .= " mark LIKE '%".$mark."%' AND imagesheader = 0";
}
// MySQL Query - Only with $edition
if($edition)
{
$search_query .= " edition LIKE '%".$edition."%' AND imagesheader = 0";
}
// MySQL Query - Only with $color
if($color)
{
$search_query .= " color LIKE '%".$color."%' AND imagesheader = 0";
}
// MySQL Query - Only with $mark and $model
if(!empty($mark) && !empty($model))
{
$search_query .= " AND (mark LIKE '%".$mark."%' AND model LIKE '%".$model."%' AND imagesheader = 0) 
OR   (mark LIKE '%".$mark."%' AND model LIKE '%".$model."%' AND imagesheader = 0)";
}
**// MySQL Query - Only with $mark and $model and $edition (THATS THE PROBLEM THAT DOES NOT WORK)
if(!empty($mark) && !empty($model) && !empty($edition))
{
$search_query .= " AND (mark LIKE '%".$mark."%' AND model LIKE '%".$model."%' 
AND edition LIKE    '%".$edition."%' AND imagesheader = 0) OR 
(mark LIKE '%".$mark."%' AND model LIKE      '%".$model."%' AND   edition LIKE '%".$edition."%' 
AND      imagesheader = 0) 
OR (mark LIKE '%".$mark."%' AND model LIKE '%".$model."%' AND edition LIKE '%".$edition."%' 
AND   imagesheader = 0)";
}**
if(($cc_from && $cc_to) || ($cc_from && !empty($cc_from)) || ($cc_to && !empty($cc_to)))
{
$search_query .= " (cc <= '$cc_from' AND cc >= '$cc_from' AND imagesheader = 0) OR 
(cc >= '$cc_to' AND   cc <= '$cc_to' AND imagesheader = 0) OR (cc BETWEEN '$cc_from' AND '$cc_to' 
AND   imagesheader = 0)";
}
$result = $connection->query($search_query);
if(!$result) 
die();
$foundnum = $result->num_rows;
if ($foundnum == 0)
{
echo 
"<div class='text-center text-light bg-danger p-3'>NO RESULTS</div>";
}
else
{
echo
// Display the data of the classified info (data and images)

Please quote error messages verbatim, instead of giving us your own paraphrased version of them. Have you made a debug output of the final value of `$search_query`, and checked what that looked like? — CBroe, Jun 05 '23 at 11:58
This is because your SQL query doesn't have `AND`. So if 2 or more options are picked, you will have a syntax error. Your first `if` statements don't align with your stated intention in the annotation next to them — tola, Jun 05 '23 at 11:59
Where do i have to put the AND operator so i can return more than two values from my query. I also put the function mysqli_real_escape_string() in all my get variables to be protected from MySQL Injections tola???? — A L, Jun 05 '23 at 12:14
I've posted an answer, that to the best of my understanding, should cover the cases you presented. — tola, Jun 05 '23 at 12:20

tola · Answer 1 · 2023-06-05T12:22:14.137

Your code seems way too cluttered for your intended goal.

First of all, regardless of what you want or need to achieve - always make sure to fend off SQL injections - check this question. This is extremely important.

As for the code itself, I'm pretty sure this will get the results you are trying to achieve (I've only included:

if ($mark || $edition || $color){
   $search_query = "SELECT * FROM car_classifieds NATURAL JOIN cars_classified_photos WHERE ";
   // MySQL Query - $mark input
   if($mark)
   {
       $search_query .= " mark LIKE '%".$mark."%' AND ";
   }
   // MySQL Query - $edition input
   if($edition)
   {
      $search_query .= " edition LIKE '%".$edition."%' AND ";
   }
   // MySQL Query - $color input
   if($color)
   {
      $search_query .= " color LIKE '%".$color."%' AND ";
   }
   // MySQL Query - default - imagesheader
   $search_query .= " imagesheader = 0";

   // no need for all the other ifs you had here

   $result = $connection->query($search_query);
}

// display the data, or whatever you intend to do with it

the first if with all the input options is not necessary. If you want to give the option of searching without any input, don't add it.

PHP MYSQL (MariaDB - XAMPP) Problem with returning results with more than one parameters in a search engine web app

1 Answers1