Solved

PHP Mysql drop box default option if array empty

Posted on 2011-03-18
5
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 110

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 count occurrences of each item in an array.

710 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