6881

Count number of MySQL rows

Question:

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?

Answer1:

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.

<a href="http://www.php.net/manual/en/function.mysql-fetch-assoc.php" rel="nofollow">http://www.php.net/manual/en/function.mysql-fetch-assoc.php</a>

Answer2:

<a href="http://uk3.php.net/manual/en/function.mysql-num-rows.php" rel="nofollow">mysql_num_rows</a> 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.

<strong>Edit:</strong> As jeroen noted, if what you pasted is the entire code of that page then you have neglected to open a database connection. See <a href="http://uk3.php.net/manual/en/function.mysql-connect.php" rel="nofollow">mysql_connect</a> and <a href="http://uk3.php.net/manual/en/function.mysql-select-db.php" rel="nofollow">mysql_select_db</a>.

Answer3:

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); }

Answer4:

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);

Answer5:

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

Answer6:

You've forgot to run the query.

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

Answer7:

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

<ol><li>is the column really called recepiants or is it perhaps recipients?</li> <li>have you opened a database connection?</li> </ol>

Answer8:

The query is:

SELECT count(*) FROM table

Answer9:

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); ?>

Recommend

  • How to create new layer or new protocol with scapy?
  • Stylus not allowing basic subtraction
  • Synchronous XHR deprecation
  • firebase unauth with google doesn't allow change of user
  • Authentication - JavaScript - Logout issue
  • Does sql server minds the way records where inserted?
  • How to make SASS put relative paths in its output
  • Classic ASP URL Rewriting
  • How can I sync the html/session used between Django's 'Client' and Selenium's we
  • using html data-attributes as css-variable (i.e. text-shadow)
  • Plotting densities in R
  • Update Google Maps traffic layer without page reloading
  • Login not working in Firefox in Meteor
  • Invalid object name 'dbo.Item'
  • Jenkins: FATAL: Could not initialize class hudson.util.ProcessTree$UnixReflection
  • TextToSpeech.setEngineByPackageName() triggers NullPointerException
  • What and where is mdimport
  • Does it make sense to call System.gc() and Thread.sleep() when working on Bitmaps?
  • Installing iPhone App to iPhone
  • DomPDF {PAGE_NUM} not on first page
  • Javascript simulate pressing enter in input box
  • Is there a amazon webstore API for customers?
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • Get object from AWS S3 as a stream
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Importing jscolor library in angular 2
  • Upload files with Ajax and Jquery
  • A cron job substitute?
  • AngularJs get employee from factory
  • Proper way to use connect-multiparty with express.js?
  • Free memory of cv::Mat loaded using FileStorage API
  • JTable with a ScrollPane misbehaving
  • Memory offsets in inline assembly
  • Turn off referential integrity in Derby? is it possible?
  • How to get Windows thread pool to call class member function?
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize