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
268 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now