Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP Mysql drop box default option if array empty

Posted on 2011-03-18
5
Medium Priority
?
443 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 2000 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 111

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

885 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