nesting mysql queries?

[john_db54]

Here's an example of how I want my data to display on the page:

product base......| descrip'..| price
productA............| nice.......| £3.oo
----------------------------------------
product additive | mix ratio | quantity
productX............| 5:1........| 4
----------------------------------------
product base......| descrip'..| price
productA............| nicer......| £4.oo
---------------------------------------
product additive | mix ratio | quantity
productF............| 3:1........| 2
----------------------------------------
etc...

Here's the problem: The resulting rows for the 'product base' part are based on a query, say $query1, the resulting rows for the 'product additive' part is from another query, $query2.

In other words I need to perform $query1 which finds, say, where all the mix ratios=10:1, print the first resulting row, then perform $query2 which finds an additive product, print the resulting row, then loop through to find all the products where mix ratio=10:1 and then below each resulting row display its additive. I can't just have a link table saying that productX is always used with product additiveY, because that's not always the case.

Here's my code so far. However, it displays all the product bases, then below displays all the product additives (and, of course, I need the product additive to be displayed below each instance of the product base):

Code: Select all

if ($app == 'BG' || $app == 'SES' || $app == 'VB')
   {$query1 = "SELECT * FROM $table, $table1
               WHERE $table.id = $table1.product_id
               AND ty = 'o' AND APP LIKE '%$app%' ";}      

else    {$query1 = "SELECT *FROM $table , $table1
   WHERE $table.id = $table1.product_id AND   $table.ty != 'c'
                AND et etc";}                  
$result = mysql_query ($query);                  if ($row = mysql_fetch_array($result))

{  echo ("These Products match your criteria:<BR>");
  echo ("<table><tr><td width=70>product base</td>
<td width=70>description</td><td width=70>price</td></tr></table>");

do { 
echo ("<table><tr>");
 echo("<td width=70>{$row['product_base']}</a></td>");
  echo ("<td width=\"70\">{$row['mix']}</td>");
  echo ("<td width=\"70\">{$row['price']}</td>");
  echo ("</tr></table>");
 }
while($row = mysql_fetch_array($result));
}
else {
        echo("Sorry, no matching additive was found");
}

// find appropriate additive - second query //

$result2 = mysql_query ("SELECT * FROM $table
                        WHERE ty ='c'
                        AND WL LIKE '%$wk%' AND MX LIKE '%$mix%'
                        ORDER BY whatever ");                  
if ($row = mysql_fetch_array($result2))
{
 echo ("<table><tr><td width=70>product additive</td>
<td width=70>product additive</td></tr>");

do { 
echo ("<table cellpadding=\"1\"><td width=\"110\" bgcolor=\"#CCCCCC\" class=tabletxtl>");
  echo ("<b>{$row['prod']}</b>");
 echo ("</td></table>");
}
while($row = mysql_fetch_array($result2));}


If you have read this far, do you know what I mean? I'm stumped. Any ideas?

[Jay]

Two ways of doing this. One is when you're printing a row for the products, you can perform a second query to display the additive. However, this is very inefficient as it requires many queries to be performed.

The easiest way is to use table joins to get all the data out in one go. I can't advise you 'cos I don't know the setup of your db or what you're trying to access.

If you've already explored this option and joins can't be created, then I'd advise running 2 queries first if that's possible. And then create an array based on the results and output the Array.

[john_db54]

Thanks very much for your suggestion - the JOIn won't work for what I want. I think your arrays suggestion would. This bit is new to me - how do I go about populating the arrays, and then extractin the info in them.

Once again, thank you.

[Jay]

The easiest would be to learn how Arrays work. Best to read a good book. But in short, an array is a variable made up of two basic values:
$variable = array($key => $value);

The $value is returned when you access $variable[$key];

The $value can be an array too, which is how you get multi-dimensional arrays eg

$ford = ("Puma" => array("Price" => 12000,"Engine" => 1.7))

To get the price you'd use $ford["Puma"]["Price"];

Notice the Puma array has 2 array values (Price and Engine). You can have as many as you want in an array.

Like I said, best think is to learn how they work, and then use the arrays to print your table. It's almost the same as printing a row for each mysql_fetch_array(); except you're using an array

[john_db54]

Thank you, Jay. Would you recommend a title?

[Jay]

I read a book called 'A Programmers Introduction to PHP 4.0' which I thought was very good. I was a complete beginner and it was very simple to understand. It's be Apress and the author is WJ Gilmore. It's nearly year old now so I don't know if it'll be available.

I also bought another book called 'Building Dynamic Pages Using PHP and MySQL' by David Tansley. I bought that one originally (and the one above on the spur of the moment, dunno why), but it turned out to be very bad and basic!

The best thing to do is read a few books, and try and get the best you can afford. But read them to see what style suits you. The WJ Gilmore book basically gave a break down of all the functions and how they could be used, which I thought was very useful as I could instantly see whether a function would be suitable for me. The David Tansley book was very basic, almost for babies. It didn't contain enough detail and left many questions unanswered

So, my advise is to read a few chapters, and decide which style you like.