MySQL query is not POST'ing successfully to another MySQL query

[Jonny125]

Hi there,

 

I've got a PHP page (salesperf.php) that consists of a number of HTML forms that POST (mostly html select values) to another PHP page (salesdayvsday.php) where the majority of the MySQL query's are performed, using these POST'd values in the query's. 

 

However,  on the first PHP page (salesperf.php), I do have just one MySQL query,which allows the user to choose in a html select dropdown, the values from a column in one of the database tables, this should then be POST'd along with the values chosen by the user in the HTML select dropdowns. This retrieves the records in the storename column. Which appears to work as expected, though I'm not entirely sure if it is successfully POST'ing the storename value chosen by the user. Here is that form:

 

salesperf.php

<table border="1" cellpadding="2" cellspacing="3" width="96%">
<form name="F_Salesdayvsday" method="post" action="salesdayvsday.php">
<th colspan="2">Sales Performance Compare two Days</th>
<tr>
<td width="48%">Day 1: </td><td width="48%"><select name="day1" type="int" id="day1">
<option value="1">Monday</option>
<option value="2">Tuesday</option>
<option value="3">Wednesday</option>
<option value="4">Thursday</option>
<option value="5">Friday</option>
<option value="6">Saturday</option>
<option value="7">Sunday</option>
</tr>
<tr>
<td width="48%">Day 2: </td><td width="48%"><select name="day2" type="int" id="day2">
<option value="1">Monday</option>
<option value="2">Tuesday</option>
<option value="3">Wednesday</option>
<option value="4">Thursday</option>
<option value="5">Friday</option>
<option value="6">Saturday</option>
<option value="7">Sunday</option>
</td></tr>
<tr><td colspan="2">
<?php
require_once 'MySQLcon.php'; //connect to db with creds from this file

$get=mysql_query("SELECT storeid, storename FROM stores WHERE accountref='1234'");

echo "<center>";
echo "<select name='storename' type='int' id='storename'>";
while($row = mysql_fetch_assoc($get))
	{
echo "<option value=";
$row['storename'];
echo ">";	
echo $row['storename'];
echo "</option>";
}
echo "</select></center>";
?>
</td></tr>
<td colspan="2"><center><input type="submit" name="submit" value="Submit"></center></td></tr>
</form>
</table>

What I can't get to work, is the $_POST['storename'] on the salesdayvsday.php page (towards the end of the MySQL query), that uses the value which is retrieved in the MySQL Query on the first (salesperf.php) page. The salesdayvsday.php page works fine for every part except where it tries to use $_POST['storename'] in the main MySQL query on that page. If the $_POST['storename'] isn't in the below query, it does work. 

 

salesdayvsday.php

 

// Day 1
$sql1 ="SELECT depthour.storeid, depthour.day, depthour.hour, SUM(depthour.qty) AS 'Total Quantity', SUM(depthour.value) AS 'Total Value', AVG(depthour.qty) AS 'Average Quantity', AVG(depthour.value) AS 'Average Value', SUM(depthour.value) / SUM(depthour.qty) AS 'Average Value Per Item', stores.storename, stores.storeID, stores.accountref
FROM depthour
INNER JOIN stores ON stores.storeID = depthour.storeID
WHERE accountref =1234 AND day=" . $_POST['day1'] ." AND storename=" . $_POST['storename'] ."";

//echo "SQL Query used: "; echo $sql;


$query1 = mysql_query($sql1); //give  resource the variables
while ($row1 = mysql_fetch_array($query1)) {  //display results for hour entered by user
if (!$query1) { // add this check.
    die('Invalid query: ' . mysql_error());
     
}

echo "<center><table border='1' cellpadding='2' cellspacing='3' width='96%'>";
echo "<tr><th colspan='2'>Store ID: " .$row1['storename']; 
echo "</th></tr>";
echo "<tr><th width='48%'>Day 1: </th><th width='48%'>" .$days[$row1['day']]; 
echo "</th></tr>";
echo "<tr><td>Total Quantity: </td><td>" .$row1['Total Quantity']; 
echo "</td></tr>";
echo "<tr><td>Total Value: </td><td>" .$row1['Total Value']; 
echo "</td></tr>";
echo "<tr><td>Average Quantity: </td><td>" .$row1['Average Quantity']; 
echo "</td></tr>";
echo "<tr><td>Average Value: </td><td>" .$row1['Average Value']; 
echo "</td></tr>";
echo "<tr><td>Average Value Per Item: </td><td>" .$row1['Average Value Per Item']; 
echo "</td></tr>";
echo "</table></center><br>";
};

The error I get is: 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /data02/c8540223/public_html/storemonitor/salesdayvsday.php on line 24

 

 

Line 24 being: while ($row1 = mysql_fetch_array($query1)) {  //display results for hour entered by user

 

And as I said, if I remove the AND storename=" . $_POST['storename'] ." from the MySQL query on salesdayvsday.php, it works without any problems. 

 

 

 

 

I hope this makes sense, if I haven't explained it enough please say so....any help is much appreciated, thanks in advance. 

[craygo]

i think you running into a problem with your quotes. You should also make sure your post values are being checked before submiting them to your query.

 

also passe the store id rather than the store name to the query

<?php
require_once 'MySQLcon.php'; //connect to db with creds from this file

$get=mysql_query("SELECT storeid, storename FROM stores WHERE accountref='1234'");

echo "<center>";
echo "<select name='storeid' type='int' id='storename'>";
while($row = mysql_fetch_assoc($get))
	{
echo '<option value="'.$row['storeid'].'" >'.$row['storename'].'</option>';
}
echo "</select></center>";
?>

Now run your query

$day1 = $_POST['day1'];
$storeid = $_POST['storeid'];
$sql1 ="SELECT depthour.storeid, depthour.day, depthour.hour, SUM(depthour.qty) AS 'Total Quantity', SUM(depthour.value) AS 'Total Value', AVG(depthour.qty) AS 'Average Quantity', AVG(depthour.value) AS 'Average Value', SUM(depthour.value) / SUM(depthour.qty) AS 'Average Value Per Item', stores.storename, stores.storeID, stores.accountref
FROM depthour
INNER JOIN stores ON stores.storeID = depthour.storeID
WHERE accountref =1234 AND day='$day1' AND stores.storeid = '$storeid'";

try that

[Jonny125]

This gets rid of the error, so that the HTML table displays - which would contain the returned MySQL query data, though does not display any of the data. 

 

Thanks for the help. 

[Jonny125]

So instead of displaying the error, it just doesn't display the data. When I remove the  AND stores.storeid = '$storeid' from the main query it works 

[craygo]

are you sure you have a row in stores that links to a row in depthour? you can try using the depthour table storeid to query on rather that the stores table

$sql1 ="SELECT depthour.storeid, depthour.day, depthour.hour, SUM(depthour.qty) AS 'Total Quantity', SUM(depthour.value) AS 'Total Value', AVG(depthour.qty) AS 'Average Quantity', AVG(depthour.value) AS 'Average Value', SUM(depthour.value) / SUM(depthour.qty) AS 'Average Value Per Item', stores.storename, stores.storeID, stores.accountref
FROM depthour
INNER JOIN stores ON stores.storeID = depthour.storeID
WHERE accountref = '1234' AND day='$day1' AND depthour.storeid = '$storeid'";

With an inner join you should at least get the data in the depthour table even if there is no associated row in the stores table.

 

If your main table is stores you may want to switch the query up to get at least the stores even if there is no depthour data associated with it.

$sql1 ="SELECT depthour.storeid, depthour.day, depthour.hour, SUM(depthour.qty) AS 'Total Quantity', SUM(depthour.value) AS 'Total Value', AVG(depthour.qty) AS 'Average Quantity', AVG(depthour.value) AS 'Average Value', SUM(depthour.value) / SUM(depthour.qty) AS 'Average Value Per Item', stores.storename, stores.storeID, stores.accountref
FROM stores
INNER JOIN depthour ON stores.storeID = depthour.storeID
WHERE accountref =1234 AND day='$day1' AND stores.storeid = '$storeid'";

Ray

[Jonny125]

Strangely enough I came in this morning and its working. Thanks for the help!