Count entries from 2 tables

misunderstood · #1 30-May-2004, 14:21

Gosh I am full of questions today

I use the following code for a menu from entries from my database

Quote:

$result=mysql_query("SELECT * FROM listing_type");
// got result now count the rows HERE so its not repeated within loop!!
$numRows=mysql_num_rows($result);
//
for($i=0;$i<$numRows;$i++){
$row=mysql_fetch_assoc($result);
//
blah blah
//
<a href='type_listing.php?type=".$row["id"]."' target=_self title='".$row["name"]."'>".$row["name"]."</a>.....

What I would also like to do is count the number of listings that are associated to the listing_type from the listings table.

The menu would then look like:
Type 1 (4)
Type 2 (0)
Type 3 (5)
Type 4 (13)
I have tried various methods but still cant get it right. Any helpful links? I have used google but just cant seem to find the right solution

JdS · #2 30-May-2004, 16:53

If you can describe both tables and paste just a couple of sample data, I can try to suggest something. Perhaps a well placed join could eliminate the need to use sub queries?

misunderstood · #3 30-May-2004, 17:06

OK the 2 tables and important columns are:
table listing_type with columns id name
table listings with type_id

I call the listing_type name ie House plus its id 1
what I then want to do is count all the rows (say 4) with House in the listings table.
I then display: House (4)

sample data
listing_type
1 House
2 Flat
3 Appartment

listing table

bnq 1
asda 1
halfords 3
kwiks 2
morrisons 1
lidle 3

Display would be:

House (3)
Flat (1)
Appartment (2)

JdS · #4 30-May-2004, 17:11

OK misunderstood, that looks fairly easy to do - BUT, I am on my way out. If no one has suggested code before I get back, you can expect something from me later today.

JdS · #5 31-May-2004, 09:06

OK, I am finally done with this issue...

Now about your SQL code... you forgot to mention if the `name` in the listing table is a unique/multiple data field. Anyway, looking at your example table structures above, this is the best I can suggest:

PHP Code:


<?php

$sql  = "SELECT  `listing_type`.*, COUNT(`listing`.`id`) as `total`  FROM  `listing_type` LEFT JOIN `listing`
ON `listing`.`type_id`=`listing_type`.`id`
GROUP BY `listing`.`type_id`";

?>

.. or something like that. Use EXPLAIN to optimise the SQL further, tweaking it with indexes/joins and such.

misunderstood · #6 31-May-2004, 14:17

Thanks JdS but I managed to work something out before I saw the posting

.
This a bit long winded I expect.

Quote:

$result=mysql_query("SELECT * FROM $list_type");
$numRows=mysql_num_rows($result);
for($i=0;$i<$numRows;$i++){
$row=mysql_fetch_assoc($result);
$id=$row["id"];

$rel= "SELECT * FROM $list WHERE $id=type_id " ;
$res = mysql_query($rel, $con) or die (mysql_error());
$num1 = mysql_numrows($res);

Print Results

JdS · #7 31-May-2004, 16:45

Sorry I took a bit of time with my reply but I am sure you can appreciate the rush you get when you're just about to upload a site

Yes, your code will work as well but think of all the queries to the DB!?

I follow this guideline: you always try to minimise the number of queries you make to the DB. So if you had a choice of making just one query instead of multiple than you do the ONE.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Selecting from 2 tables at the same time	soviet	MySQL / PHP Forum	2	07-Nov-2003 23:15
count	skyloon	MySQL / PHP Forum	1	23-Jul-2003 05:49
count date	skyloon	MySQL / PHP Forum	2	01-Jul-2003 01:28
drop down menu's in tables	chalaska	Web Design Forum	4	03-Aug-2002 18:59