CODEWITHSUNDEEP
phpmysqlhtml
Thomas
Thomas

Reputation: 5089

Count number of MySQL rows

Im trying to count the number of rows in a MySQL database and report that number using PHP. I've read all the documentation and I think this piece of code should be working but no matter what I try, it fails.

<?php
session_start();
if ( isset($_SESSION['username']) ) {
    $username = $_SESSION['username'];
    $q = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
    $count = mysql_num_rows($q);
?>
<div class="user_info">Logged in as <?php echo $username; ?> | <a href="admin.php">Add a post</a> | <a href="experiment.php">View Posts</a> | <a href="inbox.php">Message Center <?php echo $count; ?> </a> | <a href="logout.php">Log Out</a></div>
<?php } else {?>

The script successfully reports $username but $count doesn't return a number. Any ideas? Am I missing a bracket or semi-colon somewhere?

Upvotes: 0

Views: 16642

Answers (9)

Software Testing Company
Software Testing Company

Reputation: 1

Here is the simple code and I am sure it will work for many out there. Current code will check if vaule is already in the database, if it already exists will not add the record to database, if it never exists already will add the record to database.

<?php
    $chkval =52364; 

    $con = mysql_connect("servername","username","password");

    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("databasename", $con);

    $result = mysql_query("SELECT column_name FROM table_name where column_name = '$chkval'");

    if(mysql_num_rows($result)>0)
        {
            echo "There are records";

        }
        else
        {
            mysql_query("INSERT INTO tablename (column_name)
                VALUES ('$chkval')");

                                echo "record added";

        }
            mysql_close($con);

 ?>

Upvotes: -2

elopez
elopez

Reputation: 109

The query is:

SELECT count(*) FROM table

Upvotes: 1

cantlin
cantlin

Reputation: 3226

mysql_num_rows expects a query resource – you're supplying a string. You need to perform the query before you can expect to know anything about its result.

$sql = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
if(!$query = mysql_query($sql))
  trigger_error(mysql_error().' SQL: '.$sql);
$count = mysql_num_rows($query);

I suspect the above code will also generate an error based on your typo of 'recipients' in the query. Always check the return value of mysql_query.

Edit: As jeroen noted, if what you pasted is the entire code of that page then you have neglected to open a database connection. See mysql_connect and mysql_select_db.

Upvotes: 8

Jared Farrish
Jared Farrish

Reputation: 49188

The thing you're missing is running the actual query:

<?php
session_start();
if ( isset($_SESSION['username']) ) {
    $username = $_SESSION['username'];
    $q = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
    $result = mysql_query($q);
    $count = mysql_num_rows($result);
?>
<div class="user_info">Logged in as <?php echo $username; ?> | <a href="admin.php">Add a post</a> | <a href="experiment.php">View Posts</a> | <a href="inbox.php">Message Center <?php echo $count; ?> </a> | <a href="logout.php">Log Out</a></div>
<?php } else {?>

Probably better would be to run a COUNT in the query, if that's all you need:

<?php
session_start();
if ( isset($_SESSION['username']) ) {
    $username = $_SESSION['username'];
    $q = "SELECT COUNT(*) AS Count FROM messages WHERE recepiants='$username' AND readcheck='T'";
    $result = mysql_query($q);
    $result = mysql_fetch_assoc($result)
    $count = $result['Count'];
?>
<div class="user_info">Logged in as <?php echo $username; ?> | <a href="admin.php">Add a post</a> | <a href="experiment.php">View Posts</a> | <a href="inbox.php">Message Center <?php echo $count; ?> </a> | <a href="logout.php">Log Out</a></div>
<?php } else {?>

And, as someone else noted, you need an active database connection.

http://www.php.net/manual/en/function.mysql-fetch-assoc.php

Upvotes: 6

Chris McClellan
Chris McClellan

Reputation: 1105

This is wrong (there needs to be an actual query):

$q = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
$count = mysql_num_rows($q);

it should be:

$q = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
$result = mysql_query($q, $connection);
$count = mysql_num_rows($result);

Upvotes: 3

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

You've forgot to run the query.

$res = mysql_query($q);
$count = mysql_num_rows($res);

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270609

You have not actually executed your query. 

$q = "SELECT * FROM messages WHERE recepiants='$username' AND readcheck='T'";
$result = mysql_query($q);
if ($result) {
  $count = mysql_num_rows($result);
}

Upvotes: 4

jeroen
jeroen

Reputation: 91734

A few things to check apart from running the actual query:

  1. is the column really called recepiants or is it perhaps recipients?
  2. have you opened a database connection?

Upvotes: 1

corroded
corroded

Reputation: 21564

You are evaluating $q as a string, you're missing a mysql_query() on $q

Upvotes: 2

Related Questions