Problem with Sub-query

[ballouta]

Hi,

I have a database named: operations

there are two tables in it: ordersmaster & orders

orders unique numbers, status and date are registered in ordersmaster

where all orders items are registered in orders table...

 

the second database is names: store and has the table books

it contains all the products info, SKU, price, name,  etc...

 

i am working now on a page that shows old and previous orders placed by a member.

everything is working fine but i have one problem.

the order table in the first database DOES not register the name on the item during the shopping  process, it only registers its itemcode or SKU. (it is called here ncode)

so somewhere in a while loop, I need to add a query that connects to the second database and get this item name.

here the problme exists:

 

i added these lines in the while loop that currently loops correctly on the order items and displays their ncode (item code)  not their names

<?php
//those three lines
include('../CMS/global.inc.php');
$result3 = mysql_query("SELECT * FROM `books` where `ncode` = '$itemcode'");
$row3 = mysql_fetch_array($result3);
?>

 

here are all these loops code:

 

<?php
$result = mysql_query("SELECT * FROM `ordersmaster` WHERE `member` = '$user' AND `status` = 'S' ");
while( $row = mysql_fetch_array($result) )
		{ //while ONE that loops on orders numbers

$result2 = mysql_query("SELECT * FROM `orders` WHERE `member` = '$user' AND `orderno` = '$row[orderno]' ");
echo "<tr><td width='327' valign='top'>";
while ($row2 = mysql_fetch_array($result2) ) // should display all order's items in real names THIS IS LINE 102
					{
include('../CMS/global.inc.php');
$result3 = mysql_query("SELECT * FROM `books` where `ncode` = '$row2[book]'");
$row3 = mysql_fetch_array($result3);

echo "<p dir ='rtl' style='text-indent: 15px; line-height:100%; margin-top:25px; margin-bottom:25px'>
	  <font face='Tahoma' size='2'><b>Book Name:</b> $row3[ncode] ($row2[qty]) </font></p>";

					}
//...
}
?>

 

the error I got when i added the above three lines is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcompany/public_html/shopping/order~status.php on line 102

 

can you help please

[Psycho]

Well the error tells you that the query failed. You should have some error handling on your queries. But, I alwasy suggest that queries be created as string variables so you can echo the entire query to the page when there is an error. In this case I suspect that $itemcode does not have the value you expect is has. You could try this:

 

include('../CMS/global.inc.php');
$query = "SELECT * FROM `books` where `ncode` = '$itemcode'";
$result3 = mysql_query($query) or die ("Error:<br />".mysql_error()."<br />Query:<br />$query");
$row3 = mysql_fetch_array($result3);

 

However, a more important issue, in my opinion, is the use of looping, nested queries. It is terribly inneficient and a huge overhead on resources. It also doesn't take advatage of the whole purpose of relational databases. You can get ALL the data you need with a single query by joining tables. This should be correct, but I don't have full information on your structure to be 100% positive.

SELECT * FROM `ordersmaster` 

    JOIN `orders` ON `orders.orderno` = `ordersmaster.orderno`
    JOIN `books` ON `books.ncode` = `orders.book`

WHERE `member` = '$user' AND `status` = 'S'

ORDER BY `ordersmaster.orderno` ASC

[ballouta]

Hi mjdamato

thanks for your help.

 

i just replaced those three code lines with yours and I got this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcompany/public_html/shopping/order~status.php on line 102

 

I just noticed an important issue, now for example i have two orders to show in the page i am working on. the code processes ONLY the first order correctly. and the error pasted above appears in the second order where it should display seven books.

 

As if the code is being lost when i add those three lines why?.

 

I thought about joining the two tables which is better than the nested loops as you said, but is it possible if each table exisit in a separate database?

 

[ballouta]

i am also very intrested in usingg efficent quries such as the one you provided.

it looks great. but how do i get the first order number and echo it. then show all items in this item (number).

 

please help

 

thank you

[Psycho]

There probably is some syntax error in my query, I just don't have your database and environment to test against. But, here is an explanation of how youwould display the results.

 

Your results might look like this (assuming sort by member and then by order):

ORDERNO  | MEMBER | BOOK
----------------------------------------------------
218668   | Bob    | Tom Sawyer
218668   | Bob    | Moby Dick
384698   | Bob    | Catcher in the Rye
156768   | Jane   | The Prophecy
156768   | Jane   | Astrology and You
156768   | Jane   | Where's Waldo
265978   | Doug   | PHP for Dummies

 

So, Bob has two orders and Jane and Doug have one order each, some with multiple books. Here is one way to display the records in a logical format:

while ($record = mysql_fetch_assoc($result))
{
  //Display the member title
  if ($current_member!=$record['member'])
  {
    $current_member = $record['member'];
    echo "<br /><b>{$current_member}</b>\n";
  }
  //Display the order title
  if ($current_order!=$record['orderno'])
  {
    $current_order = $record['orderno'];
    echo "<br />Order: {$current_order}<br />\n";
  }
  //Display the book
  echo " - {$record['book']}<br />";
}

 

The result would look like this:

 

Bob

Order: 218668

- Tom Sawyer

- Moby Dick

 

Order: 384698

- Catcher in the Rye

 

Jane

Order: 218668

- The Prophecy

- Astrology and You

- Where's Waldo

 

Doug

Order: 265978

- PHP for Dummies

[ballouta]

thanks alot mjdamato for your help.

I just opened a new PHP page to test all your code.

the result expected taht your wrote is your last reply is correct but i still have smthg wrong in my code.

 

The error I got is:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/dcompany/public_html/shopping/order~status2.php on line 20

 

my current code is:

<?php session_start() ?>
<html>
<head></head>

<body>
<?php

include('../CMS/operations.inc.php');
include('../CMS/global.inc.php');
mysql_query("SET CHARACTER_SET_RESULTS=NULL");
$user = $_SESSION['authen'];

	$result = mysql_query("SELECT * FROM `ordersmaster` 

		JOIN `orders` ON `orders.orderno` = `ordersmaster.orderno`
		JOIN `books` ON `books.ncode` = `orders.book`
		WHERE `member` = '$user' AND `status` = 'S'
		ORDER BY `ordersmaster.orderno` ASC ");

	while ($record = mysql_fetch_assoc($result)) //this is line 20
	{
	  //Display the member title
	  if ($current_member!=$record['member'])
	  {
		$current_member = $record['member'];
		echo "<br /><b>{$current_member}</b>\n";
	  }
	  //Display the order title
	  if ($current_order!=$record['orderno'])
	  {
		$current_order = $record['orderno'];
		echo "<br />Order: {$current_order}<br />\n";
	  }
	  //Display the book
	  echo " - {$record['book']}<br />";
	}

?>
</body>
</html>

 

again, the tables structures are:

ordersmaster:

==========

orderno | member | status | date

--------------------------------------

215849 | Bob      | S      | 25-11-2008

215820 | Bob      | S      | 25-11-2008

215855 | Jane      | S      | 25-11-2008

215840 | Bob      | S      | 25-11-2008

215949 | Bob      | S      | 25-11-2008

215800 | Bob      | S      | 25-11-2008

 

In this table, the orderno is unique, it is only for registering the orders numbers and the other details.

 

orders

======

 

ID | orderno | member | book | price | qty

----------------------------------------------

51 | 215849  | Bob    |AZ2015| 2.15  | 1

52 | 215849  | Bob    |AZ2016| 2.00  | 1

53 | 215849  | Bob    |AZ2017| 2.00  | 2

54 | 215849  | Bob    |AZ2018| 2.00  | 1

55 | 215849  | Bob    |AZ2019| 2.00  | 1 (all these are the items of order number 215849 only)

and so on

 

books

=====

 

ncode  | bname  | price | pages | cover | isbn etc..

--------------------------------------------------

AZ2015 | Music  | 2.15  | 46    | hard  | 97899532412

 

Thank you again for your help.

[Psycho]

Change this

$result = mysql_query("SELECT * FROM `ordersmaster` 
JOIN `orders` ON `orders.orderno` = `ordersmaster.orderno`
JOIN `books` ON `books.ncode` = `orders.book`
WHERE `member` = '$user' AND `status` = 'S'
ORDER BY `ordersmaster.orderno` ASC ");

 

To this:

$query = "SELECT * FROM `ordersmaster` 
JOIN `orders` ON `orders.orderno` = `ordersmaster.orderno`
JOIN `books` ON `books.ncode` = `orders.book`
WHERE `member` = '$user' AND `status` = 'S'
ORDER BY `ordersmaster.orderno` ASC ";
$result = mysql_query($query) or die (mysql_error()."<br><br>$query");

 

It won't fix the problem, but it will give you more details about the problem.

[ballouta]

the error is:

 

Table 'dcompany_store.ordersmaster' doesn't exist

 

SELECT * FROM `ordersmaster` JOIN `orders` ON `orders.orderno` = `ordersmaster.orderno` JOIN `books` ON `books.ncode` = `orders.book` WHERE `member` = 'mdaouk79@gmail.com' AND `status` = 'S' ORDER BY `ordersmaster.orderno` ASC

[ballouta]

if i add the database name before each table name, the error changes:

 

Unknown column 'dcompany_operations.orders.orderno' in 'on clause'

 

SELECT * FROM dcompany_operations.`ordersmaster` JOIN dcompany_operations.`orders` ON dcompany_operations.`orders.orderno` = dcompany_operations.`ordersmaster.orderno` JOIN dcompany_store.`books` ON `books.ncode` = `orders.book` WHERE dcompany_operations.`ordersmaster`.`member` = 'mdaouk79@gmail.com' AND `status` = 'S' ORDER BY `ordersmaster.orderno` ASC

 

I couldn't continue more than this

[Psycho]

That error makes no sense. I don't see "dcompany_operations" anywhere in the original query. Are you using the exact same query as in Reply #6