MySQL - Count Total Number of Rows in PHP

MySQL - count total number of rows in php

<?php
$con = mysql_connect("server.com","user","pswd");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("db", $con);

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

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

mysql_close($con);
?>

How to get total number of rows in a MySQL database?

Use the schema:

SELECT SUM(TABLE_ROWS) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '{DB_NAME}'

That is what phpmyadmin is using.

Source: Get record counts for all tables in MySQL database

How to count number of rows in MySQL table (PHP PDO)

you are overriding the $stmt after execute using fetchAll()

so instead try this

$stmt->execute();
 $totalrows = $stmt->rowCount(); 
 echo $totalrows;
 // Pick up the result as an array
 $result = $stmt->fetchAll();

How can I count the numbers of rows that a MySQL query returned?

Getting total rows in a query result...

You could just iterate the result and count them. You don't say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

This is exposed in PHP, for example, as the mysqli_num_rows function. As you've edited the question to mention you're using PHP, here's a simple example using mysqli functions:

$link = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

echo "$num_rows Rows\n";

Getting a count of rows matching some criteria...

Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:

SELECT COUNT(*) FROM foo WHERE bar= 'value';

Get total rows when LIMIT is used...

If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();

For very large tables, this isn't going to be particularly efficient, and you're better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

count the number of rows in mysql?

How do we count them back together? LIKE: 1 2 3 4 5. i not want use of id in column database

select list_of_fields,@rn:=@rn+1 as row_num
from table,(select @rn:=0) as r order by id

Count number of rows in Mysql using PHP7

The mysqli_ functions actually accept the link as the first argument, as it is non-optional like it is with the normal mysql_ functions.

This should work as you expect:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect("host", "username", "password","db_name");
mysqli_set_charset($link, "utf8mb4");

$result = mysqli_query($link, "SELECT count(*) FROM blackandwhite");
$num_rows = mysqli_fetch_row($result)[0];

echo "$num_rows Rows\n";

MySQL rows count with PHP and show result

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

select count(*) from table of mysql in php

You need to alias the aggregate using the as keyword in order to call it from mysql_fetch_assoc

$result=mysql_query("SELECT count(*) as total from Students");
$data=mysql_fetch_assoc($result);
echo $data['total'];

Trying to count total number of rows in a table using PHP and Mysql

$sql=$db->prepare('SELECT id FROM students');
$sql->execute();
$sql->store_results();
$total=$sql->num_rows();
$sql->close();
return$total;

Returning row data

function getStudents(){
    $sql=$db->prepare('SELECT id, fname, lname FROM students');
    $sql->execute();
    $sql->bind_result($id,$fname,lname);
    while($sql->fetch()){
        $students[]=array('id'=>$id,'fname'=>$fname,'lname'=>$lname);
    }
    $sql->close();
    return$students;
}
$students=getStudents();
//if you print $students it should look: Array([0]=>Array('id'=>1,'fname'=>'ak','lname'=>'pk')[1]=>Array('id'=>2,'fname'=>'aks','lname'=>'pks')[2]=>Array('id'=>3,'fname'=>'akss','lname'=>'pkss'))

With this array you can use a foreach.

echo"<table><th>ID</th><th>fname</th><th>lname</th>";
foreach($students as$student){
    //now the array students is 'split' into student. student is now Array('id'=>1,'fname'=>'ak','lname'=>'pk'); and so on.
    echo"<tr><td>".$student['id']."</td><td>".$student['fname']."</td><td>".$student['lname']."</td></tr>";
}
echo"</table>";

Same page processing.

<?php 
    function getStudents(){
        $db=new mysqli('local','user','pass','database');//obviously put outside of function if used elsewhere
        $sql=$db->prepare('SELECT id, fname, lname FROM students');
        $sql->execute();
        $sql->bind_result($id,$fname,$lname);
        while($sql->fetch()){
            $students[]=Array('id'=>$id,'fname'=>$fname,'lname'=>$lname);
        }
        $sql->close();
        return$students;
    }
    $students=getStudents();
?>
<!DOCTYPE html>
<html>
<head>
    <title></title>
    <!--other doc head stuff here-->
</head>
<body>
    <nav><ol><li><a href="">Home</a></li></ol></nav>
    <!-- more body stuff blahh blahh-->
    <table>
        <tr>
            <th>ID</th>
            <th>First name</th>
            <th>Last name</th>
        </tr>
        <?php
            foreach($students as $student){
                echo"<tr><td>".$student['id']."</td><td>".$student['fname']."</td><td>".$student['lname']."</td></tr>";
            }
        ?>
    </table>
</body>
</html>

Now it's all neatly within one page and you don't have to fuss about using $_GET. Hope this helps :)

Please also note that mysql is depreciated. mysqli is now what is used.

---

Related Topics