Jcode Jcode - 6 months ago 59
SQL Question

PHP passing mysql query result to be displayed on another page

Apologies if this is an amateur question, my PHP skills are still in development. I've currently created a search box with the current code.

<?php
// Tyre search setup
$profileSql = "SELECT DISTINCT `profile` FROM `tyres` WHERE `profile` > 0 ORDER BY `profile`";
$profileResult = $db->query($profileSql);

$widthSql = "SELECT DISTINCT `width` FROM `tyres` WHERE `width` > 0 ORDER BY `width`";
$widthResult = $db->query($widthSql);

$diamSql = "SELECT DISTINCT `diam` FROM `tyres` WHERE `diam` > 0 ORDER BY `diam`";
$diamResult = $db->query($diamSql);

if(isset($_GET['profile']) && isset($_GET['width']) && isset($_GET['diam'])) {
$profile = $_GET['profile'];
$width = $_GET['width'];
$diam = $_GET['diam'];

$tyreSql = "SELECT * FROM `tyres` WHERE `profile` = " . $profile . " AND `width` = " . $width . " AND `diam` = " . $diam . " AND rrp > 0 ORDER BY `profile`";
$tyreResult = $db->query($tyreSql);
}
?>


Here is the form this logic is tied to.

<form id="formTyreSearch" name="tyreSearch" method="GET">
<h2>Tyre Search</h2>
<p>Use our search below to find the tyre for your car.</p>
<div class="form-group">Tyre Profile:
<select name = "diamSearch">
<?php while($row=mysqli_fetch_assoc($profileResult)) : ?>
<option value="<?php echo $row['profile']; ?>"><?php echo $row['profile']; ?></option>
<?php endwhile; ?>
</select>
</div>

<div class="form-group">Tyre Width:
<select name = "pcdSearch">
<?php while($row=mysqli_fetch_assoc($widthResult)) : ?>
<option value="<?php echo $row['width']; ?>"><?php echo $row['width']; ?></option>
<?php endwhile; ?>
</select>
</div>

<div class="form-group">Tyre Diam:
<select name = "pcdSearch">
<?php while($row=mysqli_fetch_assoc($diamResult)) : ?>
<option value="<?php echo $row['diam']; ?>"><?php echo $row['diam']; ?></option>
<?php endwhile; ?>
</select>
</div>

<button type="submit" name="filterOptions" value="displayManu" class="btn btn-default btn-sm btn-primary"><i class="fa fa-pencil"></i> Search Now</button>
</form>


Now from here I assume the values selected in the form are set in the $tyreResult variable.

I want to pass this result to a tyres.php page and display the search result there, what is best practice to do this? and how should I handle if the user hasn't selected all three values, as there is going to be other content on the site I don't really want to reload the page and display an error at the top...

Answer

Just add an action attribute to your form and move the result logic into tyres.php. If they haven't selected all values, redirect them back to the previous page. But that shouldn't happen unless the user mischievously alters your HTML. By default, the first options of your <select> tags will be selected.

search.php

<?php    
// Tyre search setup
$profileSql = "SELECT DISTINCT `profile` FROM `tyres` WHERE `profile` > 0 ORDER BY `profile`";
$profileResult = $db->query($profileSql);

$widthSql = "SELECT DISTINCT `width` FROM `tyres` WHERE `width` > 0 ORDER BY `width`";
$widthResult = $db->query($widthSql);

$diamSql = "SELECT DISTINCT `diam` FROM `tyres` WHERE `diam` > 0 ORDER BY `diam`";
$diamResult = $db->query($diamSql);
?>

<form id="formTyreSearch" method="get" action="tyres.php">
    <h2>Tyre Search</h2>
    <p>Use our search below to find the tyre for your car.</p>
    <div class="form-group">
        Tyre Profile:
        <select name="profile">
            <?php while ($row = mysqli_fetch_assoc($profileResult)) : ?>
                <option value="<?php echo $row['profile'] ?>"><?php echo $row['profile'] ?></option>
            <?php endwhile ?>
        </select>
    </div>
    <div class="form-group">
        Tyre Width:
        <select name="width">
        <?php while ($row = mysqli_fetch_assoc($widthResult)) : ?>
            <option value="<?php echo $row['width'] ?>"><?php echo $row['width'] ?></option>
        <?php endwhile ?>
        </select>
    </div>
    <div class="form-group">
        Tyre Diam:
        <select name="diam">
        <?php while ($row = mysqli_fetch_assoc($diamResult)) : ?>
            <option value="<?php echo $row['diam'] ?>"><?php echo $row['diam'] ?></option>
        <?php endwhile ?>
        </select>
    </div>
    <button type="submit" name="filterOptions" value="displayManu" class="btn btn-default btn-sm btn-primary">
      <i class="fa fa-pencil"></i> Search Now
    </button>
</form>

tyres.php

<?php
if(isset($_GET['profile'], $_GET['width'], $_GET['diam'])) {
    $profile = $_GET['profile'];
    $width = $_GET['width'];
    $diam = $_GET['diam'];

    $tyreSql = "SELECT * FROM `tyres` WHERE `profile` = " . $profile . " AND `width` = " . $width . " AND  `diam` = " . $diam . " AND rrp > 0 ORDER BY `profile`";
    $tyreResult = $db->query($tyreSql);
} else {
    header('Location: search.php');
    exit;
}
?>

<!-- show the results -->
Comments