PHP Code Question

Hi Everyone,

I am NOT a programmer, but I am attempting to put together a dropdown listing for our company’s website. It’s a very simple operation, but I’m struggling figuring out how to make it work.

I have it partially working.

The idea is the list all of the tires that we have in our inventory so that customers know what we have for them. We want to have 3 (possibly add more later) dropdown menus that allow a person to choose the width, sidewall, and rim size. That will then sort information from a MySQL database and display it to the customer. (This way they can see all of the R16 tires we have. Or all of the 70/R16, or all of the 175/90/R15, etc.)

While looking around online (and on this site) I’ve found quite a bit of useful information that I’ve been able to use to get things going.

Right now my database is called names and has the tables: Width, Sidewall, Rim.

I am able to get the dropdown menus to populate correctly.
I am able to get the Width dropdown menu to work correctly.
The Sidewall (Height) and the Rim do not work and I have NO clue where to go next to attempt to get them to work.

ANY help would be GREATLY appreciated!

Thank you in advance,

Here is the code…



<?php

/*
* Testing configuration
*/
define('ENVIR',0);
define('ENVIR_DEV',0);
define('ENVIR_LIVE',1);

/*
* Data base credentials (replace this with your db credentials)
*/
define('DB_USER','**********');
define('DB_PWD','**********');
define('DB_HOST','**********');
define('DB_NAME','**********');

/*
* Connect to database
*/
try {
    $db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PWD);
} catch(PDOExeption $e) {

    if(ENVIR == ENVIR_DEV) {
        echo '<p>',$e->getMessage(),'</p>';
    }

    echo '<p>Unable to connect to database<p>';
    exit;
}

/*
* Template variables
*/
$tpl = array(
    'filter'=>array(
         '#action'        => $_SERVER['SCRIPT_NAME']
        ,'#method'      => 'get'
        ,'Sidewall'    => array(
            '#values'=>array(
                 array('value'=>'','label'=>'All')
            )
        )
		,'Width'    =>array(
		    '#values'=>array(
			     array('value'=>'','label'=>'All')
            )
		)
		,'Rim'    =>array(
		    '#values'=>array(
			     array('value'=>'','label'=>'All')
            )
		)
	)
    ,'grid'=>array(
        'names'=>array()
    )
);

/*
* Width Dropdown menu info pulled from DB
*/
$stmt = $db->query('SELECT Width FROM names GROUP BY Width ORDER BY Width ASC');

if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
}

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['filter']['Width']['#values'][] = array(
         'label'        => $row['Width']
        ,'value'        => $row['Width']
        ,'selected'        => isset($_GET['filter'],$_GET['filter']['Width']) && $_GET['filter']['Width'] == $row['Width']
    );
}

/*
* Sidewall Dropdown menu info pulled from DB
*/
$stmt = $db->query('SELECT Sidewall FROM names GROUP BY Sidewall ORDER BY Sidewall ASC');

if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
}

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['filter']['Sidewall']['#values'][] = array(
         'label'        => $row['Sidewall']
        ,'value'        => $row['Sidewall']
        ,'selected'        => isset($_GET['filter'],$_GET['filter']['Sidewall']) && $_GET['filter']['Sidewall'] == $row['Sidewall']
    );
}

/*
* Rim Dropdown menu info pulled from DB
*/
$stmt = $db->query('SELECT Rim FROM names GROUP BY Rim ORDER BY Rim ASC');

if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
}

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['filter']['Rim']['#values'][] = array(
         'label'        => $row['Rim']
        ,'value'        => $row['Rim']
        ,'selected'        => isset($_GET['filter'],$_GET['filter']['Rim']) && $_GET['filter']['Rim'] == $row['Rim']
    );
}



/*
* ISSUES HERE!!!!!     Filter info from DB to show on list.
*/
$stmt = $db->prepare(sprintf(
    'SELECT Sidewall,Width,Rim FROM names %s'
    , isset($_GET['filter'],$_GET['filter']['Sidewall']) && !empty($_GET['filter']['Sidewall'])?'WHERE Sidewall = :Sidewall':''
	, isset($_GET['filter'],$_GET['filter']['Width']) && !empty($_GET['filter']['Width'])?'WHERE Width = :Width':''
	, isset($_GET['filter'],$_GET['filter']['Rim']) && !empty($_GET['filter']['Rim'])?'WHERE Rim = :Rim':''
));

if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
}

$stmt->execute(isset($_GET['filter'],$_GET['filter']['Sidewall']) && !empty($_GET['filter']['Sidewall'])?array(':Sidewall'=>$_GET['filter']['Sidewall']):array());
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['grid']['names'][] = $row;
}
/*  Attempting to filter Rim in addition to sidewall = ERROR
$stmt->execute(isset($_GET['filter'],$_GET['filter']['Rim']) && !empty($_GET['filter']['Rim'])?array(':Rim'=>$_GET['filter']['Rim']):array());
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['grid']['names'][] = $row;
}
*/
/*  Attempting to filter Width in addition to sidewall = ERROR
$stmt->execute(isset($_GET['filter'],$_GET['filter']['Width']) && !empty($_GET['filter']['Width'])?array(':Width'=>$_GET['filter']['Width']):array());
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['grid']['names'][] = $row;
}
*/



/*
* Start template output
*/
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <title>Tire Inventory</title>
</head>
<body>

<!-- user selects from downdown menu section -->
<form action="<?php echo $tpl['filter']['#action']; ?>" method="<?php echo $tpl['filter']['#method']; ?>">
    <fieldset>
        <legend>Tire Inventory</legend>
        <ul>
            <li>
			    <label for="filter-last-name">Select Tire Height</label>
                <select name="filter[Sidewall]" id="filter-last-name">
                    <?php
                    foreach($tpl['filter']['Sidewall']['#values'] as &$option) {
                        printf(
                            '<option value="%s"%s>%s</option>'
                            ,htmlentities($option['value'])
                            ,$option['selected']?' selected':''
                            ,htmlentities($option['label'])
                        );
                    }
                    ?>
                </select>
				<label for="filter-last-name">Select Tire Width</label>
                <select name="filter[Width]" id="filter-last-name">
                    <?php
                    foreach($tpl['filter']['Width']['#values'] as &$option) {
                        printf(
                            '<option value="%s"%s>%s</option>'
                            ,htmlentities($option['value'])
                            ,$option['selected']?' selected':''
                            ,htmlentities($option['label'])
                        );
                    }
                    ?>
                </select>

			    <label for="filter-last-name">Select Tire Rim Size</label>
                <select name="filter[Rim]" id="filter-last-name">
                    <?php
                    foreach($tpl['filter']['Rim']['#values'] as &$option) {
                        printf(
                            '<option value="%s"%s>%s</option>'
                            ,htmlentities($option['value'])
                            ,$option['selected']?' selected':''
                            ,htmlentities($option['label'])
                        );
                    }
                    ?>
                </select>



				
            </li>
            <li>
                <input type="submit" name="filter[submit]" value="Filter Names">
            </li>
        </ul>
    </fieldset>
</form>

<!-- data grid template -->
<table>
    <caption>Tires</caption>
    <thead>
        <tr>
            <th>Tire Height</th>
            <th>Tire Width</th>
			<th>Tire Rim Size</th>
        </tr>
    </thead>
    <tbody>
        <?php
            if(!empty($tpl['grid']['names'])) {
                foreach($tpl['grid']['names'] as &$name) {
                    printf(
                        '<tr>
                            <td>%s</td>
                            <td>%s</td>
							<td>%s</td>
                         </tr>'
                         ,htmlentities($name['Sidewall'])
                         ,htmlentities($name['Width'])
						 ,htmlentities($name['Rim'])
                    );
                }
            } else {
                echo '<tr><td colspan="3">No names available</td></tr>';
            }
        ?>
    </tbody>
</table>

</body>
</html>


Lets step back a moment.

Right now my database is called names and has the tables: Width, Sidewall, Rim.

Then:


$stmt = $db->query('SELECT Width FROM names GROUP BY Width ORDER BY Width ASC');

This indicates to me that you have a table called names with the columns Width, Sidewall, Rim.

Now, you are trying to debug an SQL statement which is generated by PHP, then being output into template code – the chances of mucking this up have been increased exponentially.

So, have you established what the SQL statement on its own should look like?
Is there some matching data in the db for test purposes, and is that what you are using? (175/90/R15 for example).

Get that sorted out first.

So you should know your target SQL statement.

So what is PHP building up on your behalf, and how does that differ from your target SQL statement?

The answer is to temporarily output that SQL string and see exactly how it differs from what you expect.

I’d say make a copy this file, remove all the template stuff and make a really simple form to fuel your selects that you KNOW should work.


<form action ="" method = POST>
<select name=Width>
<option value =175>175</option>
</select>
<select name=Sidewall>
<option value =90>90</option>
</select>
<select name=Rim>
<option value =15>15</option>
</select>
<input type=submit>
</form>

Your problem is not that you do not know what you are doing, but that you have failed to develop incrementally.

Get a basic version working then slowly add on bells and whistles.

Having your select boxes generate the correct SQL statements is pretty fundamental and is the first thing you should have sorted out.

If the database design is wrong then everything else will be far more complicated than it needs to be. Are you sure that you have a fully normalized database? If not then making sure that the database design is fixed would be the first step.