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
– Kevin Schwertz, Commented Feb 5, 2015 at 23:42

Darren · Accepted Answer · 2015-02-06 00:10:11Z

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 6, 2015 at 0:10

answered Feb 6, 2015 at 0:00

Darren

13.1k4 gold badges44 silver badges81 bronze badges

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

4 Comments

user2136840 Over a year ago

The question says the table name is database

Kevin Schwertz Over a year ago

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 Over a year ago

Fixed First Error, Then second error: Error: Table 'predator_db.predator_db' doesn't exist

Darren Over a year ago

Apologies, try the amended fix. @Predator

user2136840 · Accepted Answer · 2015-02-06 00:12:37Z

1

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.

answered Feb 6, 2015 at 0:12

user2136840

Comments

Kevin Schwertz · Accepted Answer · 2015-02-06 00:12:22Z

0

<?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'];
}
?>

answered Feb 6, 2015 at 0:12

Kevin Schwertz

1333 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySQL rows count with PHP and show result

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related