one query 2 tables

[slimjim]

cant figure it out.

I need it something like this.

$query="SELECT * FROM subcat where categoryg='$cate' and subcat='$categoryb' and status='1' ORDER BY hits DESC";

But I also need the result from another table.

The above query works fine, but i need the totalc colum info that coinsides with categoryb

the other table is called categoryb
colums are :
categoryb | totalc

I need the totalc from the colum that matches subcat=categoryb

Please help me if you can... I know this seems simple but i cant figure it out.

[rachel2004]

If I understood you correctly, this is what you're after:

$query="SELECT DISTINCT s.*,
                        c.totalc
        FROM subcat AS s,
             categoryb AS c
        WHERE s.categoryg='$cate' 
        AND s.subcat='$categoryb' 
        AND s.status='1'
        AND s.subcat = c.categoryb 
        ORDER BY hits DESC";

[slimjim]

Yes it was, but you know what, I had use the wrong information, i been at this so long that i lost my mind. Sorry, if you could help me out with this.

Below is the code i have so far which isnt much.

I need to count table subcat and colum subcat where subcat colum matches category b

the below query shows me a list of all categorys, then in subcat table are a list of links and under colum subcat list the links categoryb

Categoryb list categorys like this...

categoryb | totalc

PHP | 23
ASP | 5

totalc meaning the amount of links in subcat/subcat

Should show up on page lik this...

ASP ( 7 )
PHP ( 23 )
and so on

Geeze i hope I make sence.....

<?
 /* Select all categories from PpetType table */ 

  $query = "SELECT * FROM categoryb ORDER BY catb_id ASC";              

  $result = mysql_query($query)                                   

       or die ("Couldn't execute query.");
$i = 0; 
while ($row = mysql_fetch_array($result))                      

  { 
     $i++; 
     if($i == 1) 
     { 
        echo "<tr>"; 
     } 
     extract($row);                                              

     echo "<td valign='top' width='50%' class='productType'>\n"; 
     echo "<img src=\"$SITE_URL/images/folder.gif\" align=\"absMiddle\" width=\"31\" height=\"30\">&nbsp;<a href=\"$SITE_URL/Dir/directory.php?cate=$cate&categoryb=$categoryb\">$categoryb</a></font>              

          </td> 
          "; 
     if($i == 2) 
     { 
       echo "</tr>"; 
       $i=0; 
      } 
  } 
  ?>

[rachel2004]

I must be tired too because I'm finding myself re-reading your post several times to pick out the info. No offense against your post, I'm just tired. It's been a long Monday.

I did see that you're asking for a count. Okay, so in addition to what I posted previously, just use the MySQL function COUNT() and give it an alias:

$query="SELECT DISTINCT s.*,
                        c.totalc,
                        COUNT(*) AS num
        FROM subcat AS s,
             categoryb AS c
        WHERE s.categoryg='$cate'
        AND s.subcat='$categoryb'
        AND s.status='1'
        AND s.subcat = c.categoryb 
        ORDER BY hits DESC";

I haven't tested the above query (as I'm about to power off for the day). Later in your code, you should be able to echo it's value as so:

echo $row['num'];

That may not be what you're looking for exactly, but hopefully it sets you on the right track if you end up continuing on this. Otherwise, I do believe our friends in Australia, Asia, and Europe will be coming online soon enough to assist where I am leaving off.

[slimjim]

No I am sorry, I am not making much sence.

Disregard the first post, I made a mistake.

The code below gets all of the categorys from categoryb

In the table subcat and in colum subcat

I need the count in subcat / subcat that will match the category names from categoryb

So that when you look at code below, it will be like this on page...

ASP ( 7 )
PHP ( 23 )
JAVA ( 9 )

and so on...

I hope i made more sence this time...
And also thank you for helping me out here.. I know your tired.

<?
 /* Select all categories from PpetType table */ 

  $query = "SELECT * FROM categoryb ORDER BY catb_id ASC";              

  $result = mysql_query($query)                                   

       or die ("Couldn't execute query.");
$i = 0; 
while ($row = mysql_fetch_array($result))                      

  { 
     $i++; 
     if($i == 1) 
     { 
        echo "<tr>"; 
     } 
     extract($row);                                              

     echo "<td valign='top' width='50%' class='productType'>\n"; 
     echo "<img src=\"$SITE_URL/images/folder.gif\" align=\"absMiddle\" width=\"31\" height=\"30\">&nbsp;<a href=\"$SITE_URL/Dir/directory.php?cate=$cate&categoryb=$categoryb\">$categoryb</a> ( // here will go count )</font>              

          </td> 
          "; 
     if($i == 2) 
     { 
       echo "</tr>"; 
       $i=0; 
      } 
  } 
  ?>

[slimjim]

$query="SELECT subcat, COUNT(*) AS count FROM subcat THEN SOMETHING";
and also
$query="SELECT * FROM categoryb ORDER BY catb_id ASC";

//Some how I need these two together So that categoryb listings will have the count next to them
//from subcat / subcat count query

[slimjim]

Can anyone help me on this please today !!!

[rachel2004]

$query = "SELECT s.subcat,
                 COUNT(s.subcat) AS count,
                 c.*
          FROM subcat AS s,
               categoryb AS c
          GROUP BY c.catb_id";

[slimjim]

Couldn't execute query it said....

<?
 /* Select all categories from PpetType table */ 

$query = "SELECT s.subcat, 
                 COUNT(s.subcat) AS count, 
                 c.* 
          FROM subcat AS s, 
               categoryb AS c 
          GROUP BY c.catb_id";
            $result = mysql_query($query)                                   

       or die ("Couldn't execute query.");
$i = 0; 
while ($row = mysql_fetch_array($result))                      

  { 
     $i++; 
     if($i == 1) 
     { 
        echo "<tr>"; 
     } 
     extract($row);                                              

     echo "<td valign='top' width='50%' class='productType'>\n"; 
     echo "<img src=\"$SITE_URL/images/folder.gif\" align=\"absMiddle\" width=\"31\" height=\"30\">&nbsp;<a href=\"$SITE_URL/Dir/directory.php?cate=$cate&categoryb=$categoryb\">$categoryb</a></font>              

          </td> 
          "; 
     if($i == 2) 
     { 
       echo "</tr>"; 
       $i=0; 
      } 
  } 
  ?>

[rachel2004]

<?php
 /* Select all categories from PpetType table */ 

$query = "SELECT s.subcat,
                 COUNT(s.subcat) AS count,
                 c.*
          FROM subcat AS s,
               categoryb AS c
          GROUP BY c.catb_id";
            $result = mysql_query($query)                                   

       or die ("Couldn't execute query: ".mysql_error()); 

Make the change to your code as I have it above, rerun the script and then post the error message that you get.

[slimjim]

Couldn't execute query. is the error

<?php 
 /* Select all categories from PpetType table */  

$query = "SELECT s.subcat, 
                 COUNT(s.subcat) AS count, 
                 c.* 
          FROM subcat AS s, 
               categoryb AS c 
          GROUP BY c.catb_id"; 
            $result = mysql_query($query)                                    

       or die ("Couldn't execute query: ".mysql_error()); 
       $i = 0; 
while ($row = mysql_fetch_array($result))                     

  { 
     $i++; 
     if($i == 1) 
     { 
        echo "<tr>"; 
     } 
     extract($row);
     echo "<td valign='top' width='50%' class='productType'>\n"; 
     echo "<img src=\"$SITE_URL/images/folder.gif\" align=\"absMiddle\" width=\"31\" height=\"30\">&nbsp;<a href=\"$SITE_URL/Dir/directory.php?cate=$cate&categoryb=$categoryb\">$categoryb</a></font>              

          </td> 
          "; 
     if($i == 2) 
     { 
       echo "</tr>"; 
       $i=0; 
      } 
  } 
  ?>

[rachel2004]

That's all it says? I can't help you if I don't know what MySQL reports as the problem.

[slimjim]

Ok i think i got it, but how do i display the count for each ?

[rachel2004]

In your query, just use:

COUNT(table.column) AS foo

like I did for one of your columns. Notice that I used aliases for your tables (s for subcat and c for categoryb), so when you add the extra COUNT() selections, it should be like this:

COUNT(s.column) AS foo

or

COUNT(c.column) AS bar

[slimjim]

I don't understand cause am i not counting the subcat colum now ?

[rachel2004]

I have no idea what you meant with your last sentence/question.

[slimjim]

With the below code: I am counting the table subcat / colum subcat right ? Cause if so how do i show the count ? If not i just got lost... 🙂

<?php 
 /* Select all categories from PpetType table */  

$query = "SELECT s.subcat, 
                 COUNT(s.subcat) AS num, 
                 c.* 
          FROM subcat AS s, 
               categoryb AS c 
          GROUP BY c.catb_id"; 
            $result = mysql_query($query)                                    

       or die ("Couldn't execute query: ".mysql_error());  

[rachel2004]

echo $row['num'];

[slimjim]

What I am getting for results are these :

ASP ( 8 )
PHP ( 8 )
Perl ( 8 )

and so on...

IT should be like
ASP ( 2 )
PHP ( 5 )
Perl ( 1)

I think it is counting the whole colum subcat

[rachel2004]

Post the table definitions for both tables so I can recreate it. Also, post your code as it exists with your latest changes.