Re: [PHP-DB] Displaying groups from SELECT
What I did to somewhat solve the problem of over calling mysql was to
initiate the query outside the loop (SELECT prod_name query), put the
results into an array and then use the array inside the while($cats) loop.
For example,
$sql=SELECT prod_name, cat_id FROM products;
$result=mysql_query($sql, $conn);
Be sure to get the cat_id too! The go through the results and create an
associative array. I don't have my php book in front of me so I can't
write out the code verbatim. Plus it's Saturday morning.. the alcohol
hasn't left my brain from last night... :)
Then, inside your while ( $cats = mysql_fetch_array( $result ) ) loop,
you call your array and compare each line with some equality test on
cat_id. That should do the trick.
metin
PS. there's probably an mysql query that could do what you want.. just
can't think of it right now.
On Fri, 06 Jun 2003 23:55:46 -0400 Becoming Digital wrote:
Thanks, Gürhan, but I think I needed to explain things better. As is
generally
a good idea, the categories are referenced in the product table by
ID, not name.
Additionally, this is something along the lines of what I already
had. I was
trying to use only one query and make PHP do the remaining work.
Here's the code I'm currently using, which I should have posted in
the first
place.
?
$query = SELECT * FROM categories;
$result = mysql_query( $query );
$rows = mysql_num_rows( $result );
print ul\n;
while ( $cats = mysql_fetch_array( $result ) )
{
print li .$cats[cat_name] ./li;
$queryB = SELECT prod_name FROM products
WHERE prod_cat= .$cats[cat_id];
$resultB = mysql_query( $queryB );
print ul;
while ( $items = mysql_fetch_array( $resultB ) )
{
print li .$items[prod_name] ./li;
}
print /ul;
}
print /ul;
?
The more I think about it, the more it seems like I'll just have to
use two
queries. I just didn't want to do so bcs the second query will run
at least
four times and it seemed inefficient.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Gürhan Özen [EMAIL PROTECTED]
To: Becoming Digital [EMAIL PROTECTED]
Cc: PHP-DB [EMAIL PROTECTED]
Sent: Friday, 06 June, 2003 23:07
Subject: Re: [PHP-DB] Displaying groups from SELECT
On Fri, 2003-06-06 at 21:49, Becoming Digital wrote:
I'm wearing the stupid hat today, so please pardon this. I know I
must be
overlooking something.
I have a small catalogue with two tables (categories, products)
from which I'm
trying to display items. I'm trying to print the contents as below
without
using two queries, but I'm having a difficult time with it.
cat1
prod1
prod2
cat2
prod1
prod2
etc.
I think this came up fairly recently, but I cannot for the life of
me figure
out
what search terms would answer this question. As you can see from
the message
subject, I don't even know how to refer to my problem. Thanks a
lot for all
your help.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
Hi Ed,
The magic word is DISTINCT :)
$query=SELECT DISTINCT(category) AS cat_name FROM table_name;
$result=mysql_query($query);
print ul;
while ($row=mysql_fetch_array($result)) {
print li.($row[cat_name]).;
$query1=SELECT productname FROM tablename WHERE
category=.($row[cat_name]).;
$result1=mysql_query($query1);
while ($row1=mysql_fetch_array($result1)) {
print li.($row1[productname]).;
}
print /ul;
}
print /ul;
I hope this helps..
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Displaying groups from SELECT
hey, you can join the two queries and make only one...
?
function get_inventory()
{
$query = SELECT c.cat_id, p.product_name FROM categories as c LEFT JOIN
products as p on (c.cat_id = p.cat_id);
$result = mysql_query( $query );
if ( mysql_num_rows($query) 0 )
{
while ( $row = mysql_fetch_array($query, MYSQL_ASSOC) )
{
$res_array[] = array('cat_id' = $row['cat_id'],
'product_name' = $row['product_name']
);
}
if (is_array($res_array)
{
return $res_array;
}
else
{
return 0;
}
}
else
{
return 0;
}
}
//MAIN
if (!$inventory = get_inventory());
{
echo 'There is no result for your search.';
}
else
{
$counter = count($inventory);
for ($i=0; $i$counter; $i++)
{
print ul\n;
$current_inventory = $inventory[$i['cat_id']];
$peek_inventory = $inventory[$i+1['cat_id']];
if ($current_inventory != $peek_inventory)
{
print li . $current_inventory ./li;
print /ul\n;
print ul;
print li . $inventory[$i['product_name']] . /li;
print /ul\n;
}
else
{
print li . $inventory[$i['product_name']] . /li;
}
print /ul;
}
}
?
i think this work...didn't and couldn't test it... hope it's not too
late
KD
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Displaying groups from SELECT
On Fri, 2003-06-06 at 21:49, Becoming Digital wrote:
I'm wearing the stupid hat today, so please pardon this. I know I must be
overlooking something.
I have a small catalogue with two tables (categories, products) from which I'm
trying to display items. I'm trying to print the contents as below without
using two queries, but I'm having a difficult time with it.
cat1
prod1
prod2
cat2
prod1
prod2
etc.
I think this came up fairly recently, but I cannot for the life of me figure out
what search terms would answer this question. As you can see from the message
subject, I don't even know how to refer to my problem. Thanks a lot for all
your help.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
Hi Ed,
The magic word is DISTINCT :)
$query=SELECT DISTINCT(category) AS cat_name FROM table_name;
$result=mysql_query($query);
print ul;
while ($row=mysql_fetch_array($result)) {
print li.($row[cat_name]).;
$query1=SELECT productname FROM tablename WHERE
category=.($row[cat_name]).;
$result1=mysql_query($query1);
while ($row1=mysql_fetch_array($result1)) {
print li.($row1[productname]).;
}
print /ul;
}
print /ul;
I hope this helps..
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Displaying groups from SELECT
Thanks, Grhan, but I think I needed to explain things better. As is generally
a good idea, the categories are referenced in the product table by ID, not name.
Additionally, this is something along the lines of what I already had. I was
trying to use only one query and make PHP do the remaining work.
Here's the code I'm currently using, which I should have posted in the first
place.
?
$query = SELECT * FROM categories;
$result = mysql_query( $query );
$rows = mysql_num_rows( $result );
print ul\n;
while ( $cats = mysql_fetch_array( $result ) )
{
print li .$cats[cat_name] ./li;
$queryB = SELECT prod_name FROM products
WHERE prod_cat= .$cats[cat_id];
$resultB = mysql_query( $queryB );
print ul;
while ( $items = mysql_fetch_array( $resultB ) )
{
print li .$items[prod_name] ./li;
}
print /ul;
}
print /ul;
?
The more I think about it, the more it seems like I'll just have to use two
queries. I just didn't want to do so bcs the second query will run at least
four times and it seemed inefficient.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: Grhan zen [EMAIL PROTECTED]
To: Becoming Digital [EMAIL PROTECTED]
Cc: PHP-DB [EMAIL PROTECTED]
Sent: Friday, 06 June, 2003 23:07
Subject: Re: [PHP-DB] Displaying groups from SELECT
On Fri, 2003-06-06 at 21:49, Becoming Digital wrote:
I'm wearing the stupid hat today, so please pardon this. I know I must be
overlooking something.
I have a small catalogue with two tables (categories, products) from which I'm
trying to display items. I'm trying to print the contents as below without
using two queries, but I'm having a difficult time with it.
cat1
prod1
prod2
cat2
prod1
prod2
etc.
I think this came up fairly recently, but I cannot for the life of me figure
out
what search terms would answer this question. As you can see from the message
subject, I don't even know how to refer to my problem. Thanks a lot for all
your help.
Edward Dudlik
Becoming Digital
www.becomingdigital.com
Hi Ed,
The magic word is DISTINCT :)
$query=SELECT DISTINCT(category) AS cat_name FROM table_name;
$result=mysql_query($query);
print ul;
while ($row=mysql_fetch_array($result)) {
print li.($row[cat_name]).;
$query1=SELECT productname FROM tablename WHERE
category=.($row[cat_name]).;
$result1=mysql_query($query1);
while ($row1=mysql_fetch_array($result1)) {
print li.($row1[productname]).;
}
print /ul;
}
print /ul;
I hope this helps..
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
