Count rows in mysql with php when have specific string

Question

I have a table in my database named visitor_table . Within table i have a column named visitor_affiliate. I want to get the count of the rows when visitor_affiliate = "someurer" .

I want to get the count as number. I already have this code but i don't know how to get the count only for the rows containing the string. I currently get the number of all rows,

$result = mysql_query("SELECT * FROM visitor_table");   
$num_rows = mysql_num_rows($result);    
echo "$num_rows Rows\n";

Please don't use `mysql_*` functions in new code! They are [in the process of being deprecated.](http://us2.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated) — esqew, Jan 18 '13 at 14:49
mysql functions are going to be obsolete soon! use `PDO`..see this... http://php.net/pdo . they are safer and more semantic. but this post does not show any kind of research work.. — Praveen Puglia, Jan 18 '13 at 14:54
i tried all answers and i get Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in — Stauroula Xalkia, Jan 18 '13 at 15:24

score 3 · Answer 1 · edited May 23 '17 at 10:34

You can ask MySQL to return the count:

SELECT COUNT(*) FROM visitor_table WHERE visitor_affiliate = 'someurer'

You shouldn't be using the ancient (deprecated as of PHP v5.5.0, soon to be removed entirely) MySQL extension for writing new code—use instead the improved MySQLi extension or the PDO abstraction layer, both of which enable you to pass variables to the database in a safe, parameterised, fashion that ensures they are not evaluated for SQL (and therefore prevents SQL injection attacks):
```
$dbh = new PDO("mysql:dbname=$dbname", $username, $password);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$qry = $dbh->prepare('
  SELECT COUNT(*) FROM visitor_table WHERE visitor_affiliate = ?
');
$qry->execute(['someurer']);
echo $qry->fetchColumn(), ' rows';
```

score 1 · Accepted Answer · answered Jan 18 '13 at 14:48

1

$result = mysql_query("SELECT * FROM visitor_table WHERE `visitor_affiliate` = 'someurer'");
$num_rows = mysql_num_rows($result);
echo $num_rows . " Rows\n";

answered Jan 18 '13 at 14:48

esqew

42,425
27
92
132

score 0 · Answer 3 · answered Jan 18 '13 at 14:49

0

With what little information you have given just try this-

$result = mysql_query("SELECT * FROM visitor_table Where visitor_affiliate like '%someurer%'");
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows\n";

answered Jan 18 '13 at 14:49

Suresh Kamrushi

15,627
13
75
90

1

@Stauroula: you should avoid using mysql_* function as they are deprecated. – Suresh Kamrushi Jan 18 '13 at 14:50
i tried that and i get this Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in – Stauroula Xalkia Jan 18 '13 at 15:35
It means your query is failing. Just try to run query in query browser. – Suresh Kamrushi Jan 18 '13 at 15:37
i tried it without Where visitor_affiliate like '%someurer%' but i still not get the count of rows..... – Stauroula Xalkia Jan 18 '13 at 15:49

bashleigh · Answer 4 · 2013-01-18T15:35:09.507

0

The deprecated MySQL:

$res = mysql_query('SELECT * FROM visitor_table');
echo 'Number of rows:'.mysql_num_rows($res);

Procedural MySQLi:

$tu = mysqli_prepare($DBH,'SELECT * FROM visitor_table');
mysqli_execute($tu);
$num_rows = mysqli_num_rows($tu);
echo $num_rows.' rows\n';

mysql has been deprecated. Please use mysqli. Using MySQLi bind_param to get your result:

$someuser = 'Dave';
$DBH = mysqli_connect('localhost','user','pass','database');
$query = mysqli_prepare($DBH,'SELECT * FROM visitor_table WHERE user = ?');
mysqli_bind_param($query,'s',$someuser);
mysqli_execute($query);
mysqli_bind_result($id,$user,$tabCol1,$tabCol2);
while(mysqli_fetch_result){
    $row = $row +1;
    echo $user.': was found in the record with ID of: '.$id;
}
echo 'total records found:'.$row;

That's one way of doing it as I'm not completely 100% sure about using a mysqli_num_row() with the query above.

edited Jan 18 '13 at 15:35

answered Jan 18 '13 at 14:57

bashleigh

8,813
5
29
49

i get this Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in – Stauroula Xalkia Jan 18 '13 at 15:22
@StauroulaXalkia which example did you use? `mysql_num_rows();` and `mysqli_num_rows();` need a resource parameter. If you look at the top example I've used `mysql_num_rows($res);` $res is my query variable. Which is the equivalent to writing `mysql_num_rows(mysql_query('SELECT * FROM visitor_table'));`. `mysql_num_rows();` will always need a parameter :) – bashleigh Jan 18 '13 at 15:27
Actually i used mysql_num_rows and got the warning i wrote....when i am using Mysqli example the page cannot be found.... i get 404 error – Stauroula Xalkia Jan 18 '13 at 15:30
@StauroulaXalkia 404 error :S that's a strange one. Can't be related to using mysqli unless you haven't put in your localhost and other parameters for the mysqli_connection correctly? But they wouldn't return as a 404. come to think of it I think I've used the wrong connection method for mysqli procedural :p lol I'll update :) – bashleigh Jan 18 '13 at 15:34

Count rows in mysql with php when have specific string

4 Answers4