Solved

PHP Mysql drop box default option if array empty

Posted on 2011-03-18
5
434 Views
Last Modified: 2012-06-27
Hello,

I have the below code - Depending on the data posted to it some categories do not have sub categories so the mysql array returns empty.

How can I have a <option value="">n/a</option> for the displayed option when it is empty (i.e no sub category?

Thanks.
<select name="site" style="width:220px;" onchange="this.form.submit();">
				  <?php
				  $sqlecho3=mysql_query("select * from ir_subcat where SubCategoryCode='".$_POST['site']."'" );
				  $singlesite2=mysql_fetch_array($sqlecho3);
				  $subcat = $singlesite2['SubCategoryCode'];
				  $subcatd = $singlesite2['descipt'];
?>
<option value="<?php echo $singlesite2['CategoryCode'];?>"><?php echo $singlesite2['descipt']; 
?></option>

<?php
 $sqlsite2=mysql_query("select * from ir_subcat where CategoryCode='".$_POST['site1']."'" );
  while($linesite2=mysql_fetch_array($sqlsite2)) {
	?>

<option value="<?php echo $linesite2['SubCategoryCode'];?>"><?php echo $linesite2['descipt']; 
?> </option> </select>

Open in new window

0
Comment
Question by:AUCKLANDIT
5 Comments
 
LVL 13

Expert Comment

by:dsmile
ID: 35164797
Do you mean this?
<select name="site" style="width:220px;" onchange="this.form.submit();">
                                  <?php
                                  $sqlecho3=mysql_query("select * from ir_subcat where SubCategoryCode='".$_POST['site']."'" );
                                  $singlesite2=mysql_fetch_array($sqlecho3);
                                  $subcat = $singlesite2['SubCategoryCode'];
                                  $subcatd = $singlesite2['descipt'];
?>
<option value="<?php echo $singlesite2['CategoryCode'];?>"><?php echo $singlesite2['descipt'] ? $singlesite2['descipt'] : 'n/a'; 
?></option>

<?php
 $sqlsite2=mysql_query("select * from ir_subcat where CategoryCode='".$_POST['site1']."'" );
  while($linesite2=mysql_fetch_array($sqlsite2)) {
        ?>

<option value="<?php echo $linesite2['SubCategoryCode'];?>"><?php echo $linesite2['descipt'] ? $linesite2['descipt'] : 'n/a'; 
?> </option> </select>

Open in new window

0
 
LVL 3

Accepted Solution

by:
CombatGold1 earned 500 total points
ID: 35164843
You need a condition using the mysql_num_rows(result) function. This should work:
<select name="site" style="width:220px;" onchange="this.form.submit();">
<?php
$sqlecho3 = mysql_query("select * from ir_subcat where SubCategoryCode='".$_POST['site']."'" );
$singlesite2 = mysql_fetch_array($sqlecho3);
$subcat = $singlesite2['SubCategoryCode'];
$subcatd = $singlesite2['descipt'];
echo '<option value="' .$singlesite2['CategoryCode']. '">' .$singlesite2['descipt']. '</option>';

$sqlsite2 = mysql_query("select * from ir_subcat where CategoryCode='".$_POST['site1']."'" );
if (mysql_num_rows($sqlsite2) > 0)
{
	while($linesite2 = mysql_fetch_array($sqlsite2))
		echo '<option value="' .$linesite2['SubCategoryCode']. '">' .$linesite2['descipt']. '</option>';
}
else
	echo '<option value="">N/A</option>';
?>
</select>

Open in new window

0
 
LVL 3

Expert Comment

by:CombatGold1
ID: 35164860
dsmile beat me to it. The difference between our code examples is his will always make an N/A option (even if there are categories/sub-categories found), but mine will only make an N/A option if there are no sub-categories in a category. It depends what result you're looking for.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35165009
There are so many things wrong in the original code snippet that I hardly know where to start, but here are a couple of ideas.

Learn about how to write a safe query.  This is a function that can help.
http://php.net/manual/en/function.mysql-real-escape-string.php

Learn about how to control the number of rows returned from a query.  The MySQL LIMIT clause is your friend.  The mysql_num_rows() function is your friend.
http://dev.mysql.com/doc/refman/5.1/en/select.html
http://us.php.net/manual/en/function.mysql-num-rows.php

Learn about how to test a query for success and handle failures.  MySQL is not a black box - it can and will fail for reasons outside your control and your programming must account for this eventuality, just as it accounts for the risk that external data is polluted or malicious.
http://php.net/manual/en/function.mysql-query.php
http://php.net/manual/en/function.mysql-error.php

A general design pattern for SELECT controls in HTML forms often includes an empty option value at the top of a dropdown list with a text that says something like "Choose Color."  Your script would output this option first, then your iterator would take the color rows from the data base query and prepare option tags for each available color.  Once the rows were exhausted, the iterator would be complete, then the script might add an option that said, "No Preference."
0
 
LVL 1

Author Comment

by:AUCKLANDIT
ID: 35166116
Thanks CombatGold1.

Just one thing though - since it reloads on select I need to also have n/a be an option on echo for in here..

$sqlecho3 = mysql_query("select * from ir_subcat where SubCategoryCode='".$_POST['site']."'" );
$singlesite2 = mysql_fetch_array($sqlecho3);

Any idea how to do this - with out it then showing before any option is selected - if there are options available.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question