[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Posting values from multiple select boxes

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
t3chguy
Asked:
t3chguy
  • 2
1 Solution
 
t3chguyAuthor Commented:
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
 
sivagnanam chandrakanthCommented:
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
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now