about mysql and php

Question

I want get count of rows from my table where X == 7

+----+----------+-------+---+   
| id | username | email | X |
+----+----------+-------+---+
|  1 |          |       | 7 |
|  2 |          |       | 7 |
|  3 |          |       | 7 |
|  4 |          |       |   |
|  5 |          |       |   |
+----+----------+-------+---+

There are 3 rows where X == 7

How can i get the number of those rows?

select count(*) as total from table where x=7 some thing like this! — , Feb 13 '13 at 12:05

Mahmoud Gamal · Answer 1 · 2013-02-13T12:42:22.777

3

SELECT COUNT(id) FROM yourtable where x = 7;

SQL Fiddle Demo

this will give you one value:

| COUNT(ID) |
-------------
|         3 |

If you want to do that for all the values of x's, add a GROUP BY:

SELECT x, COUNT(id) TheCount
FROM yourtable
GROUP BY x;

Updated SQL Fiddle Demo

edited Feb 13 '13 at 12:42

answered Feb 13 '13 at 12:05

Mahmoud Gamal

75,299
16
132
159

what is difference between count(*) and count(id)? – Feb 13 '13 at 12:07
@Akam : `count(*)` count NULL also. Check [**this**](http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col). – Fahim Parkar Feb 13 '13 at 12:15
1

@Akam - For this query, there isn't, they are the same, but it might differ if there are nullable values coming from the `id`. See [**this post**](http://stackoverflow.com/questions/1221559/count-vs-count1) for more details. – Mahmoud Gamal Feb 13 '13 at 12:16
that is why I prefer to use `COUNT(1)` – Fahim Parkar Feb 13 '13 at 12:19
i dont need there id i need how many row have the same X . and how can i show the number in my table . im sorry – Schyzo Phrény Feb 13 '13 at 12:25
like if there is 8 rows where X==7 i want show 8 in echo – Schyzo Phrény Feb 13 '13 at 12:29
@SchyzoPhrény Do you want to display all the rows that had `x = 7`? See my edit please. – Mahmoud Gamal Feb 13 '13 at 12:32
im sorry but you don't understand me i want to calcul how many rows have the same colomn – Schyzo Phrény Feb 13 '13 at 12:34
@SchyzoPhrény So this for all the values not for a specific value `x = 7` right? and which column should have the same value in? the `x` column? – Mahmoud Gamal Feb 13 '13 at 12:39
@SchyzoPhrény - See my edit please. – Mahmoud Gamal Feb 13 '13 at 12:42
i got the answer from Fahim Parkar thank u – Schyzo Phrény Feb 13 '13 at 12:42

score 3 · Answer 2 · edited May 25 '19 at 14:38

3

$sql = "SELECT count(*) FROM `table` WHERE X = 7"; 
$result = $con->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn();

From Count with PDO

As a note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 14:38

Zoe stands with Ukraine

25,310
18
114
149

answered Feb 13 '13 at 12:07

insertusernamehere

22,497
8
84
119

feel free to use that comment without attribute its created by php chat room not alone by me – NullPoiиteя Feb 13 '13 at 12:18
Ok, thanks @NullPointer. – insertusernamehere Feb 13 '13 at 12:22

score 1 · Answer 3 · answered Feb 13 '13 at 12:04

1

Just use a regular count in the SQl query.

SELECT COUNT(*) FROM `yourTable` WHERE `X`=7;

answered Feb 13 '13 at 12:04

Sirko

69,531
19
142
174

score 1 · Answer 4 · answered Feb 13 '13 at 12:10

1

if column x is of type INT use

SELECT COUNT(1) FROM tableName WHERE X=7

if column x is of type varchar use

SELECT COUNT(1) FROM tableName WHERE X='7'

I believe you have x column as integer...

answered Feb 13 '13 at 12:10

Fahim Parkar

29,943
41
156
270

score 0 · Answer 5 · answered Feb 13 '13 at 12:05

0

SELECT count(id) FROM table_name WHERE x='7'

But really, do a google search next time ^^

answered Feb 13 '13 at 12:05

Naryl

1,888
1
10
12

about mysql and php

5 Answers5