Solved

Posting values from multiple select boxes

Posted on 2013-01-24
4
289 Views
Last Modified: 2013-01-26
I have a form that contains two select boxes, in which a user can select multiple items from each box.

One box is for a zip code range, and the next time contains a period of time in which can be selected.

I've got the data posting in a nested foreach loop, but wondering how all those queries can somehow echo out one total dollar amount for all queries.

if(isset($runQuery))
	{
	echo '<pre>';
	var_dump($_POST);
	echo '</pre>';
	
	foreach ($_POST['zipCodes'] as $selZips) 
		{ 
		foreach($_POST['timePeriods'] as $selTime)
			{
			echo $selZips . ' - ' . $selTime . '<br />'; 
			$query = "SELECT hire_net FROM dw_invoice WHERE zip = '".$selZips."' and period = '".$selTime."'";
			
			echo $query . '<br />';
			}
		}
	}

//Program uses selected zip codes and time periods to generate a revenue figure for the given criteria.
?>

<span class="sectitleblk">Revenue Lookup by Zip & Time Period</span><br /><br />
<form action="" method="POST">
	<table width="100%" cellspacing="0" cellpadding="0" border="1">
		<tr>
			<td valign="top">Zip Codes:</td>
			<td><select name="zipCodes[]" multiple="multiple" style="height:350px; width:200px;">
				<?php
				$zipQuery = mysql_query("SELECT zip FROM dw_invoice GROUP BY zip") or die("zipQuery: " . mysql_error());
				
				if(mysql_num_rows($zipQuery) > 0)
					{
					while ($zipList = mysql_fetch_array($zipQuery))
						{
						echo '<option value="'.$zipList['zip'].'">'.$zipList['zip'].'</option>';
						}
					}
					?>
					</select>
				</td>
				<td valign="top">Time Period:</td>
				<td><select name="timePeriods[]" multiple="multiple" style="height:350px; width:200px;">
					<?php
					$timeQuery = mysql_query("SELECT period FROM dw_invoice GROUP BY period");
					
					if(mysql_num_rows($timeQuery) > 0)
						{
						while($timeList = mysql_fetch_array($timeQuery))
							{
							echo '<option value="'.$timeList['period'].'">'.$timeList['period'].'</option>';
							}
						}
						?>
					</select>
				</td>
		</tr>
	</table>
	<br /><br />
	<div align="center"><input type="submit" name="runQuery" value="Gather Data" class="btn" />
</form>

Open in new window

0
Comment
Question by:t3chguy
  • 2
4 Comments
 
LVL 1

Author Comment

by:t3chguy
ID: 38817242
Var dump produces exactly what is expected.

array(3) {
  ["zipCodes"]=>
  array(6) {
    [0]=>
    string(3) "186"
    [1]=>
    string(3) "187"
    [2]=>
    string(3) "188"
    [3]=>
    string(3) "189"
    [4]=>
    string(3) "190"
    [5]=>
    string(3) "191"
  }
  ["timePeriods"]=>
  array(4) {
    [0]=>
    string(6) "201207"
    [1]=>
    string(6) "201208"
    [2]=>
    string(6) "201209"
    [3]=>
    string(6) "201210"
  }
  ["runQuery"]=>
  string(11) "Gather Data"
}

Open in new window

0
 
LVL 12

Expert Comment

by:sivagnanam chandrakanth
ID: 38817491
Seems you have just printed the query, haven't executed

see your code below
$query = "SELECT hire_net FROM dw_invoice WHERE zip = '".$selZips."' and period = '".$selTime."'";
			
echo $query . '<br />';

Open in new window


change like this

$query = mysql_result(mysql_query("SELECT hire_net FROM dw_invoice WHERE zip = '".$selZips."' and period = '".$selTime."'"),0);
			
echo $query . '<br />';

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38822564
Before you use the mysql_result() function, please see the large red warning block on this page: http://php.net/manual/en/function.mysql-result.php

In addition, you should always construct the query string in a separate variable, not in the function call.  The reason for this is quite simple - you need to print out the query if it fails, and you can only do that if the query exists in its own variable.

That aside, it looks like what you would want to do is create a query string with a dynamic WHERE clause.  I'll try to show you how to do that with some simulated input.  While I am working on the sample script, please read this article to learn how to handle internal representations of DATETIME values in PHP and MySQL.  The "timePeriods" look almost like ISO-8601 representations, byt not quite.  Fixing that up will make your programming easier!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38822601
Probably something like this will work.  Obviously I cannot test it, but I think it's correct in principle.  Don't forget to run the query after you've created the query string ;-)

<?php // RAY_temp_t3chguy.php
error_reporting(E_ALL);
echo '<pre>';


// SIMULATE THE $_POST DATA
$_POST
= array
( "zipCodes"    => array( "186", "187", "188", "189", "190", "191" )
, "timePeriods" => array( "201207", "201208", "201209", "201210" )
, "runQuery"    => "Gather Data"
)
;

// DOES IT LOOK CORRECT? (YES)
var_dump($_POST);

// RUN SOME FAIRLY HEAVY-DUTY SANITY CHECKS ON THE EXTERNAL DATA!
$z = array_map('mysql_real_escape_string', $_POST['zipCodes']);
$t = array_map('mysql_real_escape_string', $_POST['timePeriods']);

// CREATE A WHERE CLAUSE
$zw = 'zip=' . implode(' OR zip=', $z);
$tw = 'tps=' . implode(' OR tps=', $t);

// BUILD A QUERY
$sql = "SELECT thing FROM thetable WHERE ($zw) AND ($tw)";

// SHOW THE QUERY
print_r($sql);

// RUN THE QUERY
$res = mysql_query($sql);
if (!$res)
{
    $msg = "FAIL: $sql WHY: " . mysql_error();
    error_log($msg);
    die($msg);
}

Open in new window

Best regards, ~Ray
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

803 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