Dynamic Drop down lists - help needed

Orla

Hi Folks,

I have a category drop down list which I have populated from the Category table in the MySQL database.

I now want to populate the products list from the products table but based on the catgory selected above. i have it working at the moment retrieving all the results but as stated i want to limit this based on the category seelcted.

so my questions is:

should I put this restriction in the sql statement to restrict the query result?
should I put the restriction in the while loop when the complete recordset has been retrieved but only the required records will be populated(that's what's currently there but I dont know how to reference it correctly)?

and lastly how do i reference a previously selected option correctly? Is there a selectedIndex property i can use?

Any help would be gratefully appreciated,
ORla

my code is as follows:


<select name="mnucategory" id="mnucategory">
<option value="0" selected>Choose a Category</option>
<?PHP

  while($row = mysql_fetch_array($result) )
{
 echo '<option value="', $row[0], '">', $row[1], '</option>';
}
?>
 </select>

  </p>
 <p>Select a Product 
      <select name="mnuproduct" id="mnuproduct">
      <option value="0">Choose an Product</option>


<?PHP
// create the SQL statement
$sql2 = "SELECT Distinct prodid, proddesc, price FROM products ";
// execute the SQL statement
$result2 = mysql_query($sql2, $conn) or die(mysql_error());

while($row2 = mysql_fetch_array($result2) && $row2[0]== )
{
echo '<option value="', $row2[0], '" >', $row2[1], '</option>';
}
?>
</select>

paulnaj

Hi Orla,

Remember that PHP only works at the server so if you want PHP to do it, it has to go like this:

The first time the user comes to this page, no category has yet been selected so you can either not show the products at all or you show the lot.

On selecting a category, the form should submit (or the user could make a selection and click a submit button) to the same page, but now, a category has been selected, so the products dropdown can be filtered using the category value sent.

So, you want something like (I can't debug this easily and I've assumed the name of the category field in the products table) ...

<?php
// Check to see if category has been sent
$mnucategory = isset($_GET['mnucategory'])?	$_GET['mnucategory']: 0;

echo '<p>
	<select name="mnucategory" id="mnucategory" onchange=\'this.form.submit();\'> 
		<option value="0"'.($mnucategory == 0? ' SELECTED': '').'>Choose a Category</option>';

while($row = mysql_fetch_array($result)){
	echo '
		<option value="'.$row[0].'"'.(($mnucategory == $row[0])? ' SELECTED': '').'>'.$row[1].'</option>'; 
} 
	echo '
	</select> 
</p>';

// Now decide which SQL to run.
// I'm going to assume that you'll want to show
// all the products if no category is chosen.

// Not sure why you put DISTINCT in, so I've taken it out.
// create the SQL statement 
$sql2 = "SELECT prodid, proddesc, price FROM products"; 
if($mnucategory != 0){	// Filter products
	$sql2 .= " WHERE category='".$mnucategory."'"; 

}
// execute the SQL statement 
$result2 = mysql_query($sql2, $conn) or die(mysql_error()); 
echo '
<p>Select a Product 
	<select name="mnuproduct" id="mnuproduct"> 
		<option value="0">Choose an Product</option> 
';
while($row2 = mysql_fetch_array($result2)){ 
	echo '
		<option value="', $row2[0], '" >', $row2[1], '</option>'; 
} 
	echo '
	</select> 
</p>';
?>

Paul 🙂

Orla

Thanks Paul for that comprehensive reply.

I really am grateful for your guidance and am coming to understand things a lot better. I now understand the need for submitting the form and the server to refresh the list .

Regarding the suggestion of submitting the form when the first box is selected, I suppose you mean setting the action of the form to "$_SERVER[PHP_SELF]" I have set it to this and also to action=order5.php as is the filname but both are giving me error 403:

Forbidden
You don't have permission to access /"order5.php/" on this server.
Apache/2.0.52 (Win32) PHP/4.3.9 Server at localhost Port 80

Forbidden
You don't have permission to access /"$_SERVER[PHP_SELF]/" on this server.
Apache/2.0.52 (Win32) PHP/4.3.9 Server at localhost Port 80

My mistake is probably so simple but being new to the language it's a challenge! Regarding the code at the top of the page I will be removing the password and username from the connection string at a later dat but for now i just want to get the damn thing working. hence the frustration and the gratitude for your help,

Regards and thanks in advance,
Orla

My code is as follows:

<?PHP
	// open the connection
	$conn = mysql_connect("host", "user", "password")or die("Could not connect: " . mysql_error());

// pick the database to use
mysql_select_db("chemfast",$conn);

// create the SQL statement
$sql = "SELECT catid, catdesc FROM category";

// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());

//get the number of rows in the resultset
$number_of_rows=mysql_num_rows($result);
//echo "the number of rows is $number_of_rows";

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Placing an Order</title>

</head>

<body>
<h1 align="center">Placing an Order</h1>

<table width="584" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutDefaultTable-->
  <tr> 
    <td width="156" height="26">&nbsp;</td>
    <td width="114">&nbsp;</td>
    <td width="195">&nbsp;</td>
    <td width="119">&nbsp;</td>
  </tr>
  <tr> 
    <td height="36">&nbsp;</td>
    <td>&nbsp;</td>
    <td valign="top"><h1>Order Form</h1></td>
    <td>&nbsp;</td>
  </tr>
  <tr> 
    <td height="17"></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td height="329"></td>
    <td colspan="3" valign="top">

<?php 
echo '<form name=\"orderform\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">';
// Check to see if category has been sent 
$mnucategory = isset($_GET['mnucategory'])?    $_GET['mnucategory']: 0; 

echo '<p> 
    <select name=\"mnucategory\" id=\"mnucategory\" onchange=\'this.form.submit();\'> 
        <option value="0"'.($mnucategory == 0? ' SELECTED': '').'>Choose a Category</option>'; 

while($row = mysql_fetch_array($result)){ 
    echo ' 
        <option value="'.$row[0].'"'.(($mnucategory == $row[0])? ' SELECTED': '').'>'.$row[1].'</option>';  

}  

    echo ' 
    </select> 
</p>'; 

// create the SQL statement 
$sql2 = "SELECT prodid, proddesc, price FROM products";  

if($mnucategory != 0){    // Filter products 
    $sql2 .= " WHERE category='".$mnucategory."'";  

} 
// execute the SQL statement 
$result2 = mysql_query($sql2, $conn) or die(mysql_error());  

echo ' 
<p>Select a Product 
    <select name="mnuproduct" id="mnuproduct"> 
        <option value="0">Choose an Product</option> 
'; 
while($row2 = mysql_fetch_array($result2)){  

    echo ' 
        <option value="', $row2[0], '" >', $row2[1], '</option>';  

}  

    echo ' 
    </select> 
</p>'; 
?>  

    </p>
    <p>Product Title 
      <input name="txttitle" type="text" id="txttitle">
    </p>
    <p>Product Price 
      <input name="txtprice" type="text" id="txtprice">
    </p>
    <p>Enter the Quantity required 
      <input name="chkqty" type="text" id="chkqty" size="3" maxlength="3">
    </p>
    <p>Total Price 
      <input name="txttotalprice" type="text" id="txttotalprice">
    </p>
    <p> 
      <input type="submit" name="Submit" value="Add to Basket">
      <input type="reset" name="Submit2" value="Cancel Order">
    </p>
  </form></td>
  </tr>
  <tr>
    <td height="15"></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
</table>
</body>
</html>

paulnaj

Hi again,

The statement ...

<?php
echo '<form name=\"orderform\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">';

// ... is mixed up quotes-wise. Should be ...

echo '<form name="orderform" method="post" action="'.$_SERVER['PHP_SELF'].'">';
?>

I never use double-quotes unless I have to, which is very rarely.
With single-quotes, HTML is natural (don't use \", just ") and you can't embed variables, so are forced to break out of the string.

For me, this is much easier to read and debug.

Also, always quote array keys, so ...

$_SERVER['PHP_SELF']

... instead of ...

$_SERVER[PHP_SELF]

See if that works out. Can't see anything else.

Paul. 🙂

Edit:

Forgot to mention, go back and edit out your username andpassword ... normal thing to do is just use empty strings where sensitive info will go.

Orla

Thanks Paul for that suggestion

Firstly and probably most importantly i have edited the above script to remove the username and password as suggested. I now appreciate the potential problem created by its insertion.

secondly regarding quotes -As I may have mentioned before, I am new to PHP, escaping characters, and the nesting of one type of quotes within another. Just as you think you're getting a handle on it, the realisation sets in that you're a long way off!!

I have ammended my code with your suggestions and the page is refreshing to itself which is great -

however it completely refreshes and the intially selected category unfortunately does not persist when the page is submitted at that stage

neither does the filtering of the products based on the category choice. - a problem associated with the non-persistence of the category choice, I would assume.......

so I am back to the drawing board on this one.

If you do have any suggestions I would be very grateful and especially grateful given that today is Sunday - a universal day of supposed rest!!!

Thanks again,
Orla

paulnaj

Hi Orla,

We'll get there in the end!! I looked back in your full listing and noticed ...

1) I assumed you were using the GET method on your form, but you're actually using POST, so change to the line ...

$mnucategory = isset($GET['mnucategory'])? $GET['mnucategory']: 0;

... to ...

$mnucategory = isset($POST['mnucategory'])? $POST['mnucategory']: 0;

... and while you're at it, put this line near or at the top of the page. It's a global and affects the page in a major way ... should deal with asap.

2) The line ...

echo '
<select name=\"mnucategory\" id=\"mnucategory\" onchange='this.form.submit();'>
<option value="0"'.($mnucategory == 0? ' SELECTED': '').'>Choose a Category</option>';

... has gone a bit pear-shaped ... should be ...

echo '
<select name="mnucategory" id="mnucategory" onchange="this.form.submit();">
<option value="0"'.($mnucategory == 0? ' SELECTED': '').'>Choose a Category</option>';

The thing with single- and double-quotes is this:

Double-quoted strings allow you to embed variables, while single-quoted ones don't.

So ...
$name = 'paul';

// Embedding variable in string
// Double
echo "Hello, $name "; // Works - often used
echo "Hello, ".$name." ";// Works

// Single
echo 'Hello, $name '; // Doesn't embed
echo 'Hello, '.$name.' '; // My method

// Using quotes in string
// Double
echo "Hello "; // Works - often used
echo "Hello "; // Works
// echo "Hello "; // Doesn't work

// Single
echo 'Hello '; // My method
echo 'Hello '; // Works
// echo 'Hello '; // Doesn't work

It's up to you which you use, but for me single-quotes all the time ... never embed variables. It's easier to read and easier to write HTML.

Hope that does it!!

Paul.

Roger_Ramjet

Orla, I'd just like to add my agreement with what Paul says: 'single quotes all the time'.

Once it becomes a habit, you'll find you make fewer syntax mistakes in your code, and those you make will be easier to find. You will also find it much easier to read your own code when you come back to update a page in 18 months time.

Orla

Paul,

I dont know why i am feeling triumphant right now given the fact that my page is fully working, when most of it is down to your input.

i really appreciate your persistence in helping me and your detailed explanations of each aspect.

Thanks especially for the guidance on single and double quotes - a more thorough demonstration and explanation than found in most books.

It's just such a good feeling to have had a productive weekend and have something concrete on the Monday.

With my project due in January, time in closing in so it's a great feeling to be edging closer

Thanks again for everything,
Orla