Display search result by joining tables in php

PHP
1

Display search result by joining tables in php

Good day everyone

I needed users to search for cars in my application by brand and location.

I created two drop down fields “Brand” and “Location” that is populated from database.

When users choose the prefered brand and location, and search button is clicked.

It will open a new page displaying the related search.

The challenges i am having right now is how to join the tables in order to display the
required result to the users.

See below code

Page1.php

<div class="form-group">
                                        <label for="exampleSelectGender">Select City/Location<span
                                                style="color:red">*</span></label>
                                        <select class="form-control" name="location" required id="exampleSelectGender">
                                            <option>---Select Location---</option>
                                            <?php $ret = "select LocationName,LocationName from tbllocations";
$query = $dbh->prepare($ret);
$query->execute();
$results = $query->fetchAll(PDO::FETCH_OBJ);
if ($query->rowCount() > 0) {
    foreach ($results as $result) {
        ?>
                                            <option value="<?php echo htmlentities($result->LocationName); ?>">
                                                <?php echo htmlentities($result->LocationName); ?></option>
                                            <?php }}?>
                                        </select>
                                    </div>


                                    <div class="form-group">
                                        <label for="exampleSelectGender">Select Brand<span
                                                style="color:red">*</span></label>
                                        <select class="form-control" name="brand" required id="exampleSelectGender">
                                            <option>---Select Brand---</option>
                                            <?php $sql = "SELECT * from  tblbrands ";
$query = $dbh->prepare($sql);
$query->execute();
$results = $query->fetchAll(PDO::FETCH_OBJ);
$cnt = 1;
if ($query->rowCount() > 0) {
    foreach ($results as $result) {?>
                                            <option value="<?php echo htmlentities($result->brandID); ?>">
                                                <?php echo htmlentities($result->BrandName); ?></option>
                                            <?php }}?>
                                        </select>
                                    </div>

                                    <div class="spacer-10"></div>
                                    <div class="form-group">
                                        <button type="submit" class="btn btn-main fa fa-search">Search Car</button>
                                    </div>

SearchResult.php

<?php $sql = "SELECT tblvehicles.*,tblbrands.BrandName,tblbrands.brandID as bid  from tblvehicles join tblbrands on tblbrands.brandID=tblvehicles.VehiclesBrand.*.
                                tbllocations.LocationName,tbllocations.locID as lid  from tblvehicles join tbllocations on tbllocations.locID=tblvehicles.Location";
$query = $dbh->prepare($sql);
$query->bindParam(':brand', $brand, PDO::PARAM_STR);
$query->bindParam(':location', $location, PDO::PARAM_STR);
$query->execute();
$results = $query->fetchAll(PDO::FETCH_OBJ);
$cnt = 1;
if ($query->rowCount() > 0) {
    foreach ($results as $result) {?>

I have tried joining the tables but every time I search, it does not display any results.

Kindly help

2

You’re binding parameters into a query that doesn’t have any placeholders, doesn’t that throw an error?

Does your query work in phpmyadmin, or whatever db admin tool you use?

1 Like
3

First of all: A * in a query is in 99.9% of the case not good.

Second: your query should throw an error. Did you ever checked the scripts response? Did you set error reporting to on?

Don’t try to develop a sql query in PHP, use a database management tool like phpMyadmin or better ones. Create the query in this tool and check the response.

If you want us to help you creating the query, we need you database structure. Easiest way Is to show us the output of

Describe tablename

Of all your datatables

1 Like
4

Depends on the PHP version. Before 8.0, PDO’s default error mode is PDO::ERRMODE_SILENT. After 8,0, it’s PDO::ERRMODE_EXCEPTION.

5

Thank you

6

Well noted

7

Okay

8

There are a number of reasons the code provided doesn’t work and some have already been pointed out. We asked for the DB table structure so we can better provide answers for you. As also pointed out you typically would not grab all fields with * but specify the fields needed from that table. I would imagine a vehicles table would have the fields make, modal, year, doors, engine, interior and exterior colors etc. You have used brand and location and from the looks of it you are storing the ID(s) from the tblbrands and tbllocations tables in the tblvehicles table. These are the tables and fields the “JOINS” will be based on.

First we need to get there. I do not see any form tags in the posted code. If you don’t have them, add them around your inputs.

<form action="SearchResult.php" method="post">

	<div class="form-group">
		<label for="exampleSelectGender">Select City/Location<span style="color:red">*</span></label>
		<select class="form-control" name="location" required id="exampleSelectGender">
			<option>---Select Location---</option>
			<?php $ret = "SELECT locID, LocationName FROM tbllocations";
			$query = $dbh->prepare($ret);
			$query->execute();
			$results = $query->fetchAll(PDO::FETCH_OBJ);
			if ($query->rowCount() > 0) {
				foreach ($results as $result) {
			?>
					<option value="<?php echo htmlentities($result->locID); ?>">
					<?php echo htmlentities($result->LocationName); ?></option>
			<?php }}?>
		</select>
	</div>
	
	
	<div class="form-group">
		<label for="exampleSelectGender">Select Brand<span style="color:red">*</span></label>
		<select class="form-control" name="brand" required id="exampleSelectGender">
			<option>---Select Brand---</option>
			<?php $sql = "SELECT brandID, BrandName FROM tblbrands";
			$query = $dbh->prepare($sql);
			$query->execute();
			$results = $query->fetchAll(PDO::FETCH_OBJ);
			$cnt = 1;
			if ($query->rowCount() > 0) {
				foreach ($results as $result) {?>
					<option value="<?php echo htmlentities($result->brandID); ?>">
					<?php echo htmlentities($result->BrandName); ?></option>
			<?php }}?>
		</select>
	</div>
	
	<div class="spacer-10"></div>
	<div class="form-group">
		<button type="submit" class="btn btn-main fa fa-search">Search Car</button>
	</div>
</form>

On the SearchResult page you should wrap your query with a Request Method condition and 1 or more named conditions. As you are requiring both the Brand and the Location for the query we should use these for our “named conditions”.

if($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['brand']) && !empty($_POST['location'])){

In the query you would specify the fields you want from tblvehicles table and for neatness I define v as the table alias. Note: I am using the fields id and year in this example as none were provided. Change or add fields as needed.

$sql = "SELECT 
	  v.id 
	, v.year

In this same SELECT fields section you also define the fields from the joined tables, again using table aliases. Based on your example I have this.

SELECT 
	  v.id 
	, v.year
	, b.brandID as bid	 
	, b.BrandName 
	, l.locID as lid
	, l.LocationName

Now we define the tables and joins. I tend to write the ON condition where the joining table = the primary table as ON is referring to the joining table like so.

FROM tblvehicles v 
	JOIN tblbrands b 
		ON b.brandID = v.VehiclesBrand 
	JOIN tbllocations l 
		ON l.locID = v.Location

Now it was already mentioned by droopsnoot that placeholders were missing. In fact the whole WHERE condition is missing so any “search” is not being passed to the query. Using your named placeholder against the tblvehicles fields would look like this.

WHERE v.VehiclesBrand = :brand AND v.Location = :location

You have also used the variables $brand and $location but I have not seen them defined anywhere. I will assume these are trimmed POST values.

$brand = trim($_POST['brand']);
$location = trim($_POST['location']);

Finally I would suggest adding an ELSE you your rowCount() condition so if no results are found the user is shown a message.

if ($query->rowCount() > 0) {
		
}else{
	echo "no results found";
}

This is my version based on what I know of your tables and the changes I talked about.

if($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['brand']) && !empty($_POST['location'])){

	$brand = trim($_POST['brand']);
	$location = trim($_POST['location']);
	
	$sql = "SELECT 
	  v.id 
	, v.year
	, b.brandID as bid	 
	, b.BrandName 
	, l.locID as lid
	, l.LocationName 
	FROM tblvehicles v 
		JOIN tblbrands b 
			ON b.brandID = v.VehiclesBrand 
		JOIN tbllocations l 
			ON l.locID = v.Location 
	WHERE v.VehiclesBrand = :brand AND v.Location = :location"; 
	$query = $dbh->prepare($sql);
	$query->bindParam(':brand', $brand, PDO::PARAM_STR);
	$query->bindParam(':location', $location, PDO::PARAM_STR);
	$query->execute();
	$results = $query->fetchAll(PDO::FETCH_OBJ);
	$cnt = 1;
	if ($query->rowCount() > 0) {
	    foreach ($results as $result) {
			//Add your display here
			echo "<pre>";
			print_r($result);	
			echo "</pre>";
			
			}
	}else{
		echo "no results found";
	}
}

Now personally I would make the form aspect part of the “results display” page so a person could modify their search based on the results without going back and forth between pages.