Apply MySQL COUNT() in php Query w/ Selected Column Names

Question

I am trying to apply COUNT() in a query and I have it working like so:

$sql = "SELECT id, Title, images, recdate, 'item' AS type FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14)";

I've tried:

1. $sql = "SELECT id, Title, images, recdate, 'item' AS type COUNT(*) FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14)";

2. $sql = "SELECT COUNT(*) id, Title, images, recdate, 'item' AS type FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14)";

3. $sql = "SELECT COUNT(id), Title, images, recdate, 'item' AS type FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14)";

4. $sql = "SELECT id, Title, images, recdate, 'item' AS type FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14) COUNT(*)";

What am I doing wrong?

You need to supply a GROUP BY statement as well if you use COUNT. What are you trying to do? — Halcyon
– Halcyon, Commented Jan 13, 2012 at 19:12
But this works perfectly fine: $sql = "SELECT COUNT(*) FROM ads_list WHERE to_days(now())<=(to_days(recdate)+14)"; No GROUP BY(). — OldWest
– OldWest, Commented Jan 13, 2012 at 19:16

Tim · Accepted Answer · 2012-01-13 19:25:10Z

1

Count(*) is a group by function, it returns the count of rows selected. You must have a group by function on any columns which are not included in the group by clause. So, you can do:

SELECT count(*) as 'Count' FROM ads_list WHERE ...

but you can't (directly) select one group by function and four standard columns. What are you trying to achieve with the count(*)?

For total rows, you can use mysql_num_rows($result_resource). If you need the rows and the count (and you are using standard mysql library) that is probably the best way to get what you want. If you don't need the rows (only the count) then use count(*).

edited Jan 13, 2012 at 19:25

answered Jan 13, 2012 at 19:17

Tim

1,0116 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Apply MySQL COUNT() in php Query w/ Selected Column Names

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related