Display search result by joining tables in php

[johnman]

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>

                                </form>

                            </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) {?>

 

Every time i tried the search it always display no items instead of displaying list of items related to the search keyword selected

Kindly help

[mac_gyver]

the following is the syntax definition for a SELECT query -

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

the relevant parts that you need for this query are -

SELECT select_expr , select_expr ...

FROM table_references

WHERE where_condition

ORDER BY {col_name | expr | position} [ASC | DESC], ...

you should list out the columns you are selecting, i.e. don't use *

there would only be one FROM keyword in this particular query.

the table_references section is where the JOIN part of the query goes.

the where_condition would contain the columns, comparison operators, and prepared query place-holders for the brand and location matching.

almost every query that can return a set of rows should have an ORDER BY ... term so that the result is in a desired order.

the existing sql statement should be producing a query error and probably php errors. you should use exceptions for database statement errors (which is the default now in php8+) and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) you would set the PDO error mode to exceptions when you make the database connection. you should also set emulated prepared queries to false, so that you use true prepared queries. do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your development system, so that php will report and display all the errors it detects (which will now include database statement errors)?

as to the rest of the code -

1. the form processing and form should be on the same page. this will result in the least amount of code and provide the best user experience.
2. the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document.
3. you need to validate the resulting web pages at validator.w3.org
4. don't prepare and execute a non-prepared query. just use the ->query() method.
5. if you set the default fetch mode when you make the database connection, you won't need to specify it in each fetch statement.
6. don't use the ->rowCount() method. it is not guaranteed to work with SELECT queries. since you have fetched all the rows of data from the query into a php variable, you can just test that variable to determine if the the query matched any rows.
7. for the required attribute to work for a select/option menu, the value attribute for the 1st option/prompt choice must be an empty string (the w3.org validator will point out this problem.)
8. the form should be 'sticky' and re-select the option choices that match any existing search data, so that if the search doesn't  match things the user is interested in, the user can just make a different selection and resubmit the form.
9. in the location query, listing the column name twice does nothing. you only need to specify the column name once in the query. you can reference it as many times are you want in the result set.
10. if you put the label tags around (before/after) the field they belong with, you can eliminate the for='...' and corresponding id='...' attributes, which will eliminate more of the markup errors (ids must be unique.)
11. if you use implicit binding, by suppling an array of values to the ->execute([...]) call, you can eliminate the bindParam() statements.
12. if you use simple positional prepared query place-holders ?, you can eliminate the repetitive typing of place-holder names in the query and php code.

 

[Phi11W]

Taking a wild guess at your database structure, your query should look more like this: 

SELECT 
  v.field_1
. . . 
, v.field_n      /* Don't use "select *" in Application code. */ 
, b.BrandName
, b.brandID as bid 
, l.LocationName 
, l.locID as lid
from tblvehicles v 
join tblbrands b on v.VehiclesBrand = b.brandID
join tbllocations l on v.Location = l.locID

Regards, 
   Phill  W.