Solved

a select statement that allows me to get the count of ads per category where showstatus is equal to 1

Posted on 2007-03-17
21
270 Views
Last Modified: 2013-12-13
Im trying to do a select statement that allows me to get the count of ads per category where showstatus is equal to 1

one table is tbl_ads and the other is tbl_ads_to_categories
in the tbl_ads has the ad_id and also the show_status
in the tbl_ads_to_categories, it has the ads_id and the category_id.

I believe I have to use count(ads_id) somewhere and GROUP BY categories_id and a JOIN cos there are two different tables with the same column.
 
both tables tbl_ads and tbls_ads_to_categories have the same column 'ads_id'


I tried using this statement
$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories WHERE tbl_ads_to_categories.ads_id = tbl_jobs.ads_id AND show_status = '1' GROUP BY categories_id";

I get an error saying

Unknown column 'tbl_ads.ads_id' in 'where clause'

Any clues?
0
Comment
Question by:Alpha_AI
  • 11
  • 7
  • 3
21 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 18740903
"in the tbl_ads has the ad_id "

"Unknown column 'tbl_ads.ads_id' in 'where clause'"


Shouldn't it be 'tbl_ads.ad_id'  ?
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18740910
you need to do a JOIN to select columsn from multiple tables.

can you post your table structures
0
 

Author Comment

by:Alpha_AI
ID: 18740911
no the colum is called

ads_id

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:yodercm
ID: 18740922
Check your table structure.  The table tbl_ads does not have a column ads_id.
0
 

Author Comment

by:Alpha_AI
ID: 18740926
how do i do a join in this situation?
0
 
LVL 27

Expert Comment

by:yodercm
ID: 18740933
There is NO reference to 'tbl_ads.ads_id' in this select statement.


$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories WHERE tbl_ads_to_categories.ads_id = tbl_jobs.ads_id AND show_status = '1' GROUP BY categories_id";

so you could not have gotten THAT error message from THIS select.  Please post the correct select statement and the correct error message.

Also, I notice that the select statement uses a table called tbl_jobs.  Where did that come from?
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18740963
$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories ,tbl_jobs WHERE tbl_ads_to_categories.ads_id = tbl_jobs.ads_id AND show_status = '1' GROUP BY categories_id";

if there arent any columns with the same name in tbl_ads_to_categories & tbl_jobs this should work ....

if not please specify which columns belong to which table
0
 

Author Comment

by:Alpha_AI
ID: 18740972
yeah sorry,

Correct statement

$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories WHERE tbl_ads_to_categories.ads_id = tbl_ads.ads_id AND show_status = '1' GROUP BY categories_id";

correct error message

Unknown column 'tbl_ads.ads_id' in 'where clause'

I originally started of with

$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories GROUP BY categories_id";

that gave me the count of ads per category. But in my database there are some ads which have show_status = 0
so which means the count i was getting was wrong.

and the show_status wasn't in the tbl_ads_to_categories table but in the tbl_ads table instead.

structure of the tables

tbl_ads

ads_id       TYPE: int(11)  autoincrement
show_status   TYPE:tinyint(3)


tbl_ads_to_categories

ads_id        TYPE:int(11)
categories_id   TYPE:int(11)

categories_id are numbers which are different categories



0
 

Author Comment

by:Alpha_AI
ID: 18740979
It was showing all the records because there wasnt a condition in place (show_status = 1)
now im trying to set a condition.

Cheerrs,

Ben
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18740995
$query = "SELECT a.*, a.COUNT(ads_id) as number_ads FROM tbl_ads_to_categories as a, tbl_jobs as b WHERE a.ads_id = b.ads_id AND b.show_status = '1' GROUP BY a.categories_id";
0
 

Author Comment

by:Alpha_AI
ID: 18741009
sorry tbl_jobs shouldnt be there.

there is no tbl_jobs
I accidentally put tbl_jobs in instead of tbl_ads
its tbl_ads not tbl_jobs

Cheers

Ben
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18741019
$query = "SELECT a.*, a.COUNT(ads_id) as number_ads FROM tbl_ads_to_categories as a, tbl_ads as b WHERE a.ads_id = b.ads_id AND b.show_status = '1' GROUP BY a.categories_id";
0
 

Author Comment

by:Alpha_AI
ID: 18741021
I tried your code steel

and it said

Column 'ads_id' in field list is ambiguous
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 200 total points
ID: 18741026
oups sorry here you go
$query = "SELECT a.*, COUNT(a.ads_id) as number_ads FROM tbl_ads_to_categories as a, tbl_ads as b WHERE a.ads_id = b.ads_id AND b.show_status = '1' GROUP BY a.categories_id";
0
 

Author Comment

by:Alpha_AI
ID: 18741055
ok.

I dont have a clue what you wrote there but the error is not coming up.
can you tell me what that code did cos although the error is gone i have no clue what that code does.

can you put those values into

$ads_in_category array for me please?

Ben

so i can get access to them easily.

ya know like
$ads_in_category[1]
$ads_in_category[2]
...
..
$ads_in_category[5]
0
 

Author Comment

by:Alpha_AI
ID: 18741058
so the count of ads in category 1 go to $ads_in_category[1]
...
..

and count of ads in category 5 go to $ads_in_category[5]

Cheers

Ben
0
 

Author Comment

by:Alpha_AI
ID: 18741062
Points increase
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18741083
I did a join of  tbl_ads_to_categories and tbl_ads for more info go to the link below

http://dev.mysql.com/doc/refman/4.1/en/join.html
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18741085
while($row = mysql_fetch_object($query)) {

$ads_in_category[$row->categories_id] = $row->number_ads;

}
0
 

Author Comment

by:Alpha_AI
ID: 18742249
while($row = mysql_fetch_object($query)) {

$ads_in_category[$row->categories_id] = $row->number_ads;

}

doesnt work it gives me an error:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource
0
 

Author Comment

by:Alpha_AI
ID: 18742386
sorry fixed that problem
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question