MySQL rows count with PHP and show result

Question

I want to make an API that when you access it it will show result in text format of how many rows are on my database.

Database info:

Username: predator_db

DB Name: predator_db

Table Name: database

I tried a couple of codes and I could not get it to work.

Code tried:

<?php
$con = mysql_connect("localhost","predator_db","PASS");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("predator_db", $con);

$result = mysql_query("select count(1) FROM database");
$row = mysql_fetch_array($result);

$total = $row[0];
echo "Total rows: " . $total;

mysql_close($con);
?>

Response Of Code: "Total Rows: " < Does not show how many rows. Error Log:

[05-Feb-2015 23:44:58 UTC] PHP Deprecated:  mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/predator/public_html/api/resolver/number.php on line 2
[05-Feb-2015 23:44:58 UTC] PHP Warning:  mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/predator/public_html/api/resolver/number.php on line 10

Did that, Still does same thing, does not say how many rows. @KimAlexander — Kevin Schwertz, Feb 05 '15 at 23:42
try `mysql_fetch_array($result,MYSQL_BOTH);` and tell me more about outputs you see — Alex, Feb 05 '15 at 23:44
Tried it, Nothing @KimAlexander I just edited with the errors I got when I used the code On My Question maybe that will help. — Kevin Schwertz, Feb 05 '15 at 23:49
so mysql is deprecated that is true, you should use msqli or PDO then — Alex, Feb 05 '15 at 23:49
http://stackoverflow.com/questions/883365/row-count-with-pdo/883382#883382 Not according to this. — Kevin Schwertz, Feb 06 '15 at 00:07
that link you've sent is about imaginated situation, in most cases, and in your particular case pdo will return number of rows — Alex, Feb 06 '15 at 00:13
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource (or mysqli\_result), boolean given](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole) — halfer, Feb 06 '15 at 01:14

Darren · Accepted Answer · 2015-02-06T00:10:11.933

1

You're trying to fetch the results from database... not from your actual database of predator_db.

I'll do it with the basics, but please look into MySQLi prepared statements and/or PDO.

$link = mysqli_connect("localhost", "predator_db", "PASS", "predator_db");
$result = mysqli_query($link, "select COUNT(id) AS count FROM `database`");
// make sure it worked
if(!$result) {
    die('Error: ' . mysqli_error($link));
} else {
    $num_rows = mysqli_fetch_assoc($result);
    // echo it
    echo "Rows: " . $num_rows['count'];
}

edited Feb 06 '15 at 00:10

answered Feb 06 '15 at 00:00

Darren

13,050
4
41
79

The question says the table name is `database` – ChicagoRedSox Feb 06 '15 at 00:03
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS count FROM predator_db' at line 1 – Kevin Schwertz Feb 06 '15 at 00:09
Fixed First Error, Then second error: Error: Table 'predator_db.predator_db' doesn't exist – Kevin Schwertz Feb 06 '15 at 00:10
Apologies, try the amended fix. @Predator – Darren Feb 06 '15 at 00:12

score 1 · Answer 2 · answered Feb 06 '15 at 00:12

First off, it's not a good idea to name a table after a reserved keyword like database. However, if you are going to go that route, you will always have to place the name in backticks ``. So, your query should be

$result = mysql_query("select count(1) FROM `database`");

Also, look into MYSQLi, as the old MySQL driver is deprecated.

score 0 · Answer 3 · answered Feb 06 '15 at 00:12

<?php
$link = mysqli_connect("localhost", "DB_USER", "DB_PASS", "DB_NAME");
$result = mysqli_query($link, "select COUNT(*) AS count FROM DB_NAME.DB_TABLE");
if(!$result) {
    die('Error: ' . mysqli_error($link));
} else {
    $num_rows = mysqli_fetch_assoc($result);
    echo "Rows: " . $num_rows['count'];
}
?>

MySQL rows count with PHP and show result

3 Answers3