Jonny125

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

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

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.

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.

Thanks for the help, much appreciated.

Would you mind elaborating on on your answer little? I'm not entirely sure where to place or how to use this. My Code: <?php $connection = mysql_connect("localhost", "username", "password"); //connect to server with these creds, store in $connection variable if (!$connection) {die('Could not connect: ' . mysql_error());} //if $connection can not connect give error mysql_select_db("dbname", $connection); //select database name for $connection //-------------sql select query for daily stats $sql ="SELECT storeid, HOUR, SUM( qty ) AS 'Total Quantity', SUM( value ) AS 'Total Value', AVG( qty ) AS 'Average Quantity', AVG( value ) AS 'Average Value', SUM( value ) / SUM( qty ) AS 'Average Value Per Item' FROM depthour GROUP BY HOUR"; //echo "SQL Query used: "; echo $sql; $query = mysql_query($sql); //give resource the variables echo "<table border='1' cellpadding='2' cellspacing='3' width='100%'>"; // echo "<tr><th>Hour</th><th>Total Quantity</th><th>Total Value</th><th>Average Quantity</th><th>Average Value</th><th>Average Value per Item</th></tr>"; while ($row = mysql_fetch_array($query)) { //display results for hour defined by SQL if (!$query) { // add this check. die('Invalid query: ' . mysql_error()); } //-------------------End of SQL for daily stats echo "<tr><td>" .$row['HOUR']; echo "</td><td>" .$row['Total Quantity']; echo "</td><td>" .$row['Total Value']; echo "</td><td>" .$row['Average Quantity']; echo "</td><td>" .$row['Average Value']; echo "</td><td>" .$row['Average Value Per Item']; echo "</td></tr>"; } echo "</table>"; ?> echo "<tr><td>" .$row['HOUR']; is where the hour numbers 1 to 24 are displayed, and would like them to display as 01:00 to 24:00. Apologies for the novice understanding.

That looks even better than what I had in mind, thank you so much. You sir, are a legend. Now, to try and figure out how you did it lol.

Hi there, I've currently got my PHP echoing HTML code displaying the results of a MySQL statement in tables, for every department that there is, the page will display results per department in an additional table / row, and then for each store that there is there will be an additional set of the department tables as shown below: This is all good but its clearly not necessary to display an entire table per record of department, per store, what I would really like is to be able to have a set of tables, just per store that there is (currently only 2 and 1 blank entry) rather than a table set per department. So ideally with the current data, there would be just 3 table sets (1 per store), but each table displaying the data per department in additional columns rather than sets of tables. The way I got around this before was to write an SQL statement per column, where I would specify in the statement which day to Group by. This was fine when I only needed 7 columns (for days of the week) as there wouldn't be anymore than 7 days or 7 SQL statements. However with departments, there could be anywhere between 30 & 999 departments, and as I am a beginner in the PHP & MySQL world I am quite stumped as to how to do this efficiently / properly. Can anyone advise me how to do this, or if I'm extremely fortunate show me an example? as I'm learning explanations are just as valuable as the answer. I hope how I've explained it isn't too long winded and makes sense, if anything doesn't please say so Thank you in advance for any help. My code so far: <?php $connection = mysql_connect("localhost", "username", "password"); //connect to server with these creds, store in $connection variable if (!$connection) {die('Could not connect: ' . mysql_error());} //if $connection can not connect give error mysql_select_db("db_name", $connection); //select database name for $connection //sql select query for hour $sql ="SELECT storeid, dept, SUM( qty ) AS 'Weekly Total Quantity', SUM( value ) AS 'Weekly Total Value', AVG( avgqty ) AS 'Weekly Average Quantity Per Hour', AVG( avgvalue ) AS 'Weekly Average Value Per Hour', SUM( value ) / SUM( qty ) AS 'Avg Value Per Item' FROM depthour GROUP BY dept, storeid ORDER BY storeid, dept"; //echo "SQL Query used: "; echo $sql; $query = mysql_query($sql); //give resource the variables while ($row = mysql_fetch_array($query)) { //display results for hour entered by user if (!$query) { // add this check. die('Invalid query: ' . mysql_error()); } echo "<table border='1' cellpadding='2' cellspacing='3' width='70%'>"; echo "<tr><th colspan='2'>Weekly Statistics for Store: ".$row['storeid']; echo "</th></tr>"; echo "<tr><td width ='40%'>Department: </td><td width ='30%'>" .$row['dept']; echo "</td></tr>"; echo "<tr><td>Weekly Total Quantity: </td><td>" .$row['Weekly Total Quantity']; echo "</td></tr>"; echo "<tr><td>Weekly Total Value: </td><td>" .$row['Weekly Total Value']; echo "</td></tr>"; echo "<tr><td>Weekly Average Quantity Per Hour: </td><td>" .$row['Weekly Average Quantity Per Hour']; echo "</td></tr>"; echo "<tr><td>Weekly Average Value Per Hour: </td><td>" .$row['Weekly Average Value Per Hour']; echo "</td></tr>"; echo "<tr><td>Avg Value Per Item: </td><td>" .$row['Avg Value Per Item']; echo "</td></tr>"; echo "</table><br>"; }; ?>

Ah i see, I also needed to put the table header row outside of the loop so that wouldn't repeat either. Thanks for the help!

Hi there, I'm trying to display the results of a MySQL query in PHP and HTML, however for some reason, it is creating a table for each row of results. When it should be just one table, containing the results for each row returned. At the moment its just dummy data so theres only 3 hours, but with real data would show 24 hours (or 24 rows, not including the table header). This is how it looks: And this is the code: <?php $connection = mysql_connect("localhost", "username", "password"); //connect to server with these creds, store in $connection variable if (!$connection) {die('Could not connect: ' . mysql_error());} //if $connection can not connect give error mysql_select_db("db_name", $connection); //select database name for $connection //-------------sql select query for daily stats $sql ="SELECT storeid, HOUR, SUM( qty ) AS 'Total Quantity', SUM( value ) AS 'Total Value', AVG( qty ) AS 'Average Quantity', AVG( value ) AS 'Average Value', SUM( value ) / SUM( qty ) AS 'Average Value Per Item' FROM depthour GROUP BY HOUR"; //echo "SQL Query used: "; echo $sql; $query = mysql_query($sql); //give resource the variables while ($row = mysql_fetch_array($query)) { //display results for hour defined by SQL if (!$query) { // add this check. die('Invalid query: ' . mysql_error()); } //-------------------End of SQL for daily stats echo "<table border='1' cellpadding='2' cellspacing='3' width='100%'>"; echo "<tr><th>Hour</th><th>Total Quantity</th><th>Total Value</th><th>Average Quantity</th><th>Average Value</th><th>Average Value per Item</th></tr>"; echo "<tr><td>" .$row['HOUR']; echo "</td><td>" .$row['Total Quantity']; echo "</td><td>" .$row['Total Value']; echo "</td><td>" .$row['Average Quantity']; echo "</td><td>" .$row['Average Value']; echo "</td><td>" .$row['Average Value Per Item']; echo "</td></tr></table><br>"; } ?> I'm quite new to the MySQL and PHP world so if you can see what I'm doing wrong, an explanation is just as valuable as a fix is. Any coding tips are also very much appreciated, thank you in advance for any help!

thank you.

Hi there, I'm a beginner with PHP and MySQL so please bare with me if this is easier or harder than I imagine. I currently have a HTML page with a form that includes the select feature, so that the user can enter what hour of the day, and day of the week they would like to query. The submit button takes the user to the PHP page where the mysql select statement is processed and displays the data from the db. This all works fine. However, the days are stored as tinyint field type in the database and are entered as 1 to 7, rather than days of the week. What this means is that when the user sees the retrieved data on the php page, the hour and day is just a number rather than the actual hour and/or day. Is there a way, in PHP, to do what the HTML select feature is doing on the HTML page (shows 01:00 to 24:00 for hours whereas in the db its 1 to 24, and Monday to Sunday for days whereas in the db it is 1 to 7) But do it on the PHP page with the returned sql data? Obviously it wouldn't actually need to let the user select, an option, but show the user the useful information (hours & days) as opposed to raw data (1-7 & 1-24). Thank you for reading, any help is much appreciated.