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

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?
Alpha_AIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
"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
steelseth12Commented:
you need to do a JOIN to select columsn from multiple tables.

can you post your table structures
0
Alpha_AIAuthor Commented:
no the colum is called

ads_id

0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Cornelia YoderArtistCommented:
Check your table structure.  The table tbl_ads does not have a column ads_id.
0
Alpha_AIAuthor Commented:
how do i do a join in this situation?
0
Cornelia YoderArtistCommented:
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
steelseth12Commented:
$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
Alpha_AIAuthor Commented:
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
Alpha_AIAuthor Commented:
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
steelseth12Commented:
$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
Alpha_AIAuthor Commented:
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
steelseth12Commented:
$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
Alpha_AIAuthor Commented:
I tried your code steel

and it said

Column 'ads_id' in field list is ambiguous
0
steelseth12Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alpha_AIAuthor Commented:
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
Alpha_AIAuthor Commented:
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
Alpha_AIAuthor Commented:
Points increase
0
steelseth12Commented:
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
steelseth12Commented:
while($row = mysql_fetch_object($query)) {

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

}
0
Alpha_AIAuthor Commented:
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
Alpha_AIAuthor Commented:
sorry fixed that problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.