MySQL Query problem

almystersv · February 9, 2008

Hi Guys,

I have a page in my website that displays all orders in the database that are "Awaiting Approval". This is a query that selects all from 'productorder', 'orders' and 'product' tables where status = 'Awaiting Approval'.

What I would like to do is display on the homepage a notification counting how many orders there are that are "Awaiting Approval"

but this would be running a count on a query rather than on a table.

Is this possible??

Thanks

revraz · February 9, 2008

Just do a SELECT COUNT query and pull the number over.

almystersv · February 9, 2008

Hi,

Thanks for the quick response.

How after executing the query do i get it to display the count on the page.

Here is my SQL query

SELECT COUNT (*) FROM orders o, productorder p WHERE p.orderID = o.orderID AND p.status = 'Awaiting Approval'

almystersv · February 9, 2008

The Query I posted above, Works in phpMyAdmin when entering it manually, but i am gettin this error message when running the code in php...

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM orders o, productorder p WHERE p.orderID = o.orderID AND p.status = 'Awa' at line 1

Any ideas why?!

PLEASE :-\

p2grace · February 9, 2008

Can you post the php code you used to run the query.

Thanks.

almystersv · February 9, 2008

Hi,

this is the homepage (welcome.php) that will be displaying the output, but it is calling the 'NotificationManagerMenu.php' page that has the query on it.

welcome.php

<?php
session_start();
if (isset($_SESSION['username']) == false){
	header("Location: login.php");
	exit();
}

require "connect.php";
?>
<!DOCTYPE HTML PUBLIC "//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>BIS Portal</title>
<link rel="stylesheet" type="text/css"
href="mystylelogin.css" />

</head>

<body>

<?php
switch ($_SESSION['type']) 
{
	case 'user':
    	include ("header.php");
	include ("NotificationMenu.php");
    	break;

	case 'manager':
    	include ("header.php");
	include ("NotificationManagerMenu.php");
    	break;
  
	case 'admin':
	include ("headerAdminMain.php");
	include ("NotificationMenu.php");
	break;
}
?>

<div id="header">Messages</div>


<div id="OrdersBasketTable">
<table border="0" width="802">
<hr />
<tr>
  		<th align="left" > </th>
  		<th align="left" > </th>
  		<th align="left" > </th>
  		<th align="left" > </th>
  		<th align="left" > </th>
</tr>
<tr>
  		<th width="3%" align="left" > </th>
	<th width="20%" align="left" ><h3><a href="welcome.php?var=msgTitle">Title</a></h3></th>
        <th width="52%" align="left" ><h3><a href="welcome.php?var=msgBody">Content</a></h3></th>
        <th width="14%" align="left" ><h3><a href="welcome.php?var=msgDate">Date & Time</a></h3></th>
        <th width="11%" align="left" ><h3><a href="welcome.php?var=username">User</a></h3></th>
</tr>
<?php
$orderby = $_GET['var'];
$query = "SELECT * FROM message, employee WHERE message.empID = employee.empID ORDER BY $orderby desc LIMIT 0, 10";
$result = mysql_query($query, $connection)
or die ("MySQL Error: ".mysql_error());
while($row = mysql_fetch_array($result))
	{ ?>
<tr>
  		<td> </td>
	<td><?php echo $row['msgTitle']?></td>
	<td><?php echo $row['msgBody']?></td>
	<td><?php echo $row['msgDate']?></td>
	<td><?php echo $row['fName']?> <?php echo $row['sName']?></td>
</tr>
<tr>
  		<td align="center"> </td>
  		<td align="center"> </td>
  		<td> </td>
  		<td> </td>
  		<td> </td>
  		<td width="0%"> </td>
</tr>
<?php } ?>
</table>
</div>

</body>
</html>

NotificationManagerMenu.php

<?php
require "connect.php";

$query2 = "SELECT COUNT (*) FROM orders o, productorder p WHERE p.orderID = o.orderID AND p.status = 'Awaiting Approval'";
$result2 = mysql_query($query2, $connection)
or die ("MySQL Error: ".mysql_error());
?>

<link rel="stylesheet" type="text/css"
href="mystylelogin.css" />

<div id="NotificationMenu"><img src="images/NotificationMenu.gif" /></div>

<div id="NotificationMenuTable">
<table width="99%" border="0">
  <tr>
    <td><div align="center"><h2><u>Notifications</u></h2></div></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td>You have... </td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
  </tr>
  <tr>
    <td><div align="center"><a href="pendingOrders.php">View Pending Orders</a></div></td>
  </tr>
</table>
</div>

Thanks

almystersv · February 9, 2008

any ideas guys?? :-\

rhodesa · February 9, 2008

First...change your require()s to require_once(). This allows you to keep including connect.php, but if it's already been included, it won't keep including (and reconnecting).

on NotificationManagerMenu.php:

<?php
require_once("connect.php");
$query2 = "SELECT COUNT (*) FROM orders o, productorder p WHERE p.orderID = o.orderID AND p.status = 'Awaiting Approval'";
$result2 = mysql_query($query2, $connection)
or die ("MySQL Error: ".mysql_error());
        $row= mysql_fetch_row($result);
        $count = $row[0];
....
?>

jacksonmj · February 9, 2008

Remove the space between COUNT and (*):

$query2 = "SELECT COUNT(*) FROM orders o, productorder p WHERE p.orderID = o.orderID AND p.status = 'Awaiting Approval'";

almystersv · February 9, 2008

Hi,

Thanks.

I changed the code and am now gettin this error?!

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Build\NotificationManagerMenu.php on line 5

phpSensei · February 9, 2008

Hi,

Thanks.

I changed the code and am now gettin this error?!

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Build\NotificationManagerMenu.php on line 5

then you probably misspelled or there is an incorrect row in your query. Try adding TICKs to the Column names and Single quotes to the values. This way, if the MYSQL name is reserved, the ticks will skip that and read it as a mysql row.

rhodesa · February 9, 2008

nope...my bad...$result2 not $result

$row= mysql_fetch_row($result2);

almystersv · February 9, 2008

Ye thats what was wrong, thanks rhodesa.

Do you know now how i can ouput the count onto the screen.

Like...

You have 3 (count) orders awaiting approval.?

rhodesa · February 9, 2008

<p>You have <?php echo $count; ?> orders awaiting approval.</p>

and if you don't want it shown if there is nothing to do...

<?php if($count) echo "<p>You have {$count} orders awaiting approval.</p>"; ?>

almystersv · February 10, 2008

Superb,

Thanks alot for your help.

I've got just one more question, sorry :-\

Is there anyway of editing the query to count just the DISTINCT orderID's as it is duplicating many because one order has many products and it is counting them each time?!

Thanks, this is the last question

rhodesa · February 10, 2008

I am pretty sure you can do:

$query2 = "SELECT COUNT(DISTINCT(orderID)) FROM productorder WHERE status = 'Awaiting Approval'";

Sign In

MySQL Query problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information