Solved

Posting values from multiple select boxes

Posted on 2013-01-24
4
290 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 look for a specific file type in a local or remote server directory using PHP.

856 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