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
269 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

839 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