Help with mySQL/PHP for a simple restaurant ordering system

[seanty]

I'm a newbie to PHP and mySQL. Doing a simple on-line restaurant ordering system for a project.

 

With consideration to 2 tables that I have:

- ORDERS - holds orderid(primary key), userid(primary key in user table), total(total for billing), complete(flag for staff to set order as complete when processed), date

- ORDERSITIMISED - holds id(primary key), orderid(the key of ORDERS table), itemid(primary key in items table), and qty

 

I'm trying to have a loop which goes through each order in ORDERS and prints it to screen if its not set to 'COMPLETE'

Within each loop iteration I want to print the ordercontents from ORDERSITIMISED with orderid's that match the one in the main loop

 

I can print the list of orders no problem but having difficulty with printing the itemised list for each iteration. This is the error i get:

Live Orders

 

Order Number: 7. Timestamp: 2011-11-29 23:35:08. Total Due: £15.82

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\a3\live_orders.php on line 30

No order details found for order# 7Order Number: 9. Timestamp: 2011-11-30 01:04:16. Total Due: £0.00

 

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\a3\live_orders.php on line 30

No order details found for order# 9Order Number: 10. Timestamp: 2011-11-30 16:04:50. Total Due: £152.40

 

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\a3\live_orders.php on line 30

No order details found for order# 10

 

I've been trying various things and reading a good few tutorials but I'm obviously missing something fundamental here. Would i need to setup a join between tables and create a different query?

 

Code below:

<?php

session_start();
require('connect.php');

if ((@$_SESSION['adminflag'])==1){  //checks if adminflag is set to 1 

echo "Welcome, ".$_SESSION['username']." to the live order view!<br><a href='logout.php'>Click here</a> to logout.";


echo "<h1>Live Orders</h1>";

//display orders
//get orders from orders table that are NOT complete
$get = mysql_query('SELECT * FROM orders WHERE complete=0');
if (mysql_num_rows($get)==0){
	echo "<p>There are no active orders to display.";
	}
else{
//for each order in the order table that is NOT complete...
	while ($get_row = mysql_fetch_assoc($get)){
		//extract user id and order id to be used to query other tables
		$currentuserid = $get_row['userid'];
		$currentorderid = $get_row['id'];
		//print order id, date/time, and total due
		echo "Order Number: $currentorderid. Timestamp: ".$get_row['date'].".  Total Due: £".number_format($get_row['total'],2)."<p>";

		//now for each iteration of the above while look, get all items from table 'ordersitemised' that have that orderid
		$get_order = mysql_query('SELECT * FROM ordersitimised WHERE orderid=$currentorderid');
		if (mysql_num_rows($get_order)==0){
			echo "<p>No order details found for order# $currentorderid";
		}
		else{
			while ($get_order_row = mysql_fetch_assoc($get_order)){
				$currentitem = $get_details['itemid'];
				$currentitemqty = $get_details['qty'];
				echo "Item# $currentitem  Qty:$currentitemqty";
			}//end while
		} //end else
	}//end while
}//end else

}//end if

else{
//display unauthenticated message
die("You do not have permission to access this page!");
}
?>

[seanty]

Sorry I had been changing stuff there.. that second while loop should read:

 

while ($get_order_row = mysql_fetch_assoc($get_order)){

$currentitem = $get_order_row['itemid'];

$currentitemqty = $get_order_row['qty'];

echo "Item# $currentitem  Qty:$currentitemqty";

}//end while

 

Still same error though.

 

Any help much appreciated!

[scootstah]

This error is because the query failed. If you did

$get_order = mysql_query('SELECT * FROM ordersitimised WHERE orderid=$currentorderid') or die (mysql_error());

You would see that you have invalid SQL.

 

The reason you have invalid SQL is because you used single quotes and didn't concatenate. In PHP, single quotes mean "exactly this". So if you say 'WHERE orderid=$currentorderid', the SQL will look exactly like that - it doesn't get the value of $currentorderid. So you can either concatenate like this: 'WHERE orderid=' . $currentorderid or you can just use double quotes.

 

So this:

$get_order = mysql_query('SELECT * FROM ordersitimised WHERE orderid=' . $currentorderid);

 

Or this:

$get_order = mysql_query("SELECT * FROM ordersitimised WHERE orderid=$currentorderid");

 

Also, since you are new and learning, this is a good time to stop learning with the mysql functions. Mysql is old, outdated, slow, and insecure. You should be using mysqli functions instead.

 

http://php.net/manual/en/book.mysqli.php

 

You can use it almost the same as mysql functions, but it allows you to use prepared statements which means you won't be susceptible to SQL injection attacks.

 

Here's a quick snippet to select from a database using mysqli:

 

$conn = mysqli_connect('localhost', 'root', 'root', 'database');

$id = 1;

$query = mysqli_query($conn, "SELECT * FROM table WHERE id=$id");

if (mysqli_num_rows($query) > 0) {
while ($row = mysqli_fetch_assoc($query)) {
	echo '<pre>' . print_r($row, true) . '</pre>';
}
}

 

Note that this snippet doesn't use mysqli's prepared statements... so I recommend you go read on that subject for a bit. It's honestly not even worth learning to use regular mysql functions. You will never use them in any respectable program, and they take way too much effort to make it secure.

[xyph]

While I use prepared statements myself, there's not much effort added to make standard query calls secure. For basic queries, prepared statements require more code.

[seanty]

This error is because the query failed. If you did

$get_order = mysql_query('SELECT * FROM ordersitimised WHERE orderid=$currentorderid') or die (mysql_error());

You would see that you have invalid SQL.

 

The reason you have invalid SQL is because you used single quotes and didn't concatenate. In PHP, single quotes mean "exactly this". So if you say 'WHERE orderid=$currentorderid', the SQL will look exactly like that - it doesn't get the value of $currentorderid. So you can either concatenate like this: 'WHERE orderid=' . $currentorderid or you can just use double quotes.

 

 

This is the perfect example of how simple errors can bring things to a halt when learning something new. I was literally staring at the screen for hours and assuming there was something fundamentally wrong with calling a table inside another without having relationships setup.

 

Thanks so much for your reply - i have the " and ' embedded in my brain now in the php folder.. I noticed these but hadn't really thought about the difference between the two in this context.

 

From reading around I gathered that different flavours of mySQL are now the norm and I will definitely go that way before doing anything thats going 'live'

[scootstah]

While I use prepared statements myself, there's not much effort added to make standard query calls secure. For basic queries, prepared statements require more code.

 

I agree that the standard mysqli prepared syntax is pretty annoying and requires a lot more code. However, I have a mysqli wrapper library that I always use for small projects which make most queries only a couple lines.