Solved

PHP Mysql drop box default option if array empty

Posted on 2011-03-18
5
438 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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

621 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