Forum Moderators: coopster
Sorry if this doesn't come across as clear very new to PHP and mysql.
What I'm tring to do is have a display of how many records are added into my database. I have 3 options shipped, returned or instock.
if you search for shipped I want it to display the total number of units that have be shipped at the top of the screen and the same for the other two.
On my seach.html page
shipped - option value = 1
returned -option value = 2
instock - option value = 0
"category" is the field name in the database.
Any help would be greatly appreciated. If you have any suggestion could you please specify where in the script I should place it... sorry like I said very new and trying to learn as much as I can.
This is my results.php page.
<td width="500">Returns = <img src="returns_image.png" border="2" /> Shipped = <img src="shipped_image.png" border="2" /> InStock = <img src="instock_image.png" border="2" />
<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td width="20"><b>id</b></td>
<td width="150"><b>Name</b></td>
<td width="20"><b>Lane ID</b></td>
<td width="100"><b>Mac address</b></td>
<td width="300"><b>Problem reported</b></td>
<td width="300"><b>Our findings</b></td>
<td width="40"><b>Staff Helping</b></td>
<td width="75"><b>Date</b></td>
<td width="75"><b>Action Taken</b></td>
</tr>
<tr>
<td>
MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
<?
//error message (not found message)begins
$XX = "No Record Found, to search again please click either back or search again";
$metode=$_POST["metode"];
$search=$_POST["search"];
//query searches of Returns and shipped
if ($metode=="1" or $metode=="2" or $metode=="0")
{
$search=$metode;
$metode='category';
}
//query details table begins
$query = mysql_query("SELECT * FROM LivePro WHERE $metode LIKE '%$search%'");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["id"];
$variable2=$row["name"];
$variable3=$row["lane_id"];
$variable4=$row["mac_address"];
$variable5=$row["problem_reported"];
$variable6=$row["our_findings"];
$variable7=$row["staff_helping"];
$variable8=$row["date"];
$variable9=$row["action_taken"];
$variable10=$row["category"];
//shows colors
switch($variable10)
{
case 0:
$variable11='#FFFFFF';
break;
case 1:
$variable11='#AAD0FF';
break;
case 2:
$variable11='#CFCCCC';
break;
}
//table layout for results
print ("<tr bgcolor=$variable11>");
print ("<td>$variable1 </td>");
print ("<td>$variable2 </td>");
print ("<td>$variable3 </td>");
print ("<td>$variable4 </td>");
print ("<td>$variable5 </td>");
print ("<td>$variable6 </td>");
print ("<td>$variable7 </td>");
print ("<td>$variable8 </td>");
print ("<td>$variable9 </td>");
print ("</tr>");
}
//below this is the function for no record!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>
You should run a query like the following to grab all of the counts from your table:
# connect to db server, select db
$types = [url=http://www.php.net/array]array[/url]('in-stock', 'shipped', 'returned');
$query = "SELECT `category`,[url=http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count]COUNT[/url](*) AS `nums` FROM `LivePro` GROUP BY `category`";
$result = [url=http://www.php.net/mysql-query]mysql_query[/url]($query) or [url=http://www.php.net/die]die[/url]([url=http://www.php.net/mysql-error]mysql_error[/url]());
echo '<table>';
while($row = [url=http://www.php.net/mysql-fetch-assoc]mysql_fetch_assoc[/url]($result)) {
if([url=http://www.php.net/array-key-exists]array_key_exists[/url]($row['category'], $types))
echo '<tr><td>' . $types[$row['category']] . '</td><td>' . $row['nums'] . '</td><tr/>';
}
echo '</table>';
That should definitely get you started :) You might also want to read up here: [dev.mysql.com...]
Thanks again for all your help.
Thanks again for your replies it is such a great help.
I hope I've explained it clearly enough.... I thing I started confusing myself :)
I managed to get my results page to show the number of shipped and returned for the perticular name I was searching. I just made a few changes to the code you showed me earlier. This is what I've done.
//shows totals when searched by namae
if ($metode =="name")
{
$types = array('In-stock=', 'Returned=', 'Shipped=',);
$query = "SELECT `category`,COUNT(*) AS `nums` FROM `LivePro` WHERE $metode LIKE '%$search%' GROUP BY `category`";
$result = mysql_query($query) or die (mysql_error());
}
echo '<table>';
while($row = mysql_fetch_assoc($result))
{
if(array_key_exists($row['category'], $types))
echo '<tr><td>' . $types[$row['category']] . '</td><td>' . $row['nums'] . '</td><tr/>';
}
echo '</table>';
What happens now is it is displayed like this:
Returned= 1
Shipped= 10
In-stock= 49
Returned= 7
Shipped= 211
What I would like it to do is Subtract the Returned from the Shipped and give me the total.
I would like it to look like this:
Location total=9 (total for that location)
In-stock= 49 (but still give the totals for all machines)
Returned= 7
Shipped= 211
//shows totals when searched by namae
if ($metode =="name")
{
$types = array('In-stock=', 'Returned=', 'Shipped=',);
$query = "SELECT `category`,COUNT(*) AS `nums` FROM `LivePro` WHERE $metode LIKE '%$search%' GROUP BY `category`";
$result = mysql_query($query) or die (mysql_error());
}
echo '<table>';
while($row = mysql_fetch_assoc($result))
{
if(array_key_exists($row['category'], $types))
$total[$row['category']] = $row['nums'];
}
$there = $total[2] - $total[1];
echo '<tr><td>On-Site=</td><td>'.$there.'</td></tr>';
echo '</table>';
Would still like to thank you for your help the other day.
