...

View Full Version : Count Specific Records In DB



Troy297
01-27-2007, 01:50 AM
Hey All,

I am making a private messaging script as an addon for a script I have already made and was wondering how I would go about counting the number of messages in a table that are to a certain user.... eg... the database

+--------------------+
|todj | message |
--------------------- |
|User 1 | Blah blah... |
|User 2 | Blah blah... |
|User 1 | Blah blah... |
+--------------------+

Now what query would I use to ask it how many messages are there for User 1 and then echo it in text form? Something like this maybe?


<?php
$result = mysql_query("COUNT to WHERE to="$_SESSION['username']");
?>

The table also contains a subject, date, and fromdj field if it makes any difference. Any help is always great! Thanks again!

Majoracle
01-27-2007, 05:12 AM
Your table should have an ID row. Just select the count, like this:


<?php

$query = mysql_query("SELECT count(id) AS `total` FROM `table_name` WHERE `to`='$_SESSION[username]'");
$result = mysql_fetch_array($query);
$total = $result['total'];

?>

Troy297
01-27-2007, 07:48 PM
I'm not sure what happened... heres the error that I got...


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/qscriptz/public_html/radiodjpanel/navigation.php on line 108

and the actual code....


<?php
include('connect.php');
if($_SESSION['rank'] == 'Administrator'){
echo "
<b>Radio</b><br>
links go here
<p>
<b>Messaging</b><br>
-<a href='inbox.php'>View Inbox (";
$query = mysql_query("SELECT count(id) AS total FROM rp_pm WHERE to='$_SESSION[username]'");
$result = mysql_fetch_array($query);
$total = $result['total'];
echo ")</a><br>
-<a href='newmessage.php'>New Message</a><br>
<p>
<b>Your Account</b><br>
links go here
<p>
<b>Admin Panel</b><br>
links go here....
<p>";
}
?>


So basically I want the link to show "View Inbox(#)" and where the # is where the current number of messages that have the "to" as their username. Any other ideas? Thanks again!

Fumigator
01-27-2007, 08:11 PM
There's an error with your query that you need to catch. Put a check just after the mysql_query() call (and assign the query text to a string so you can display it):



$sql = "SELECT count(id) AS total FROM rp_pm WHERE to='$_SESSION['username']'";
$query = mysql_query($sql);
if (!$result) {
die("SQL ERROR! query: $sql<br />error text: ".mysql_error());
}

Troy297
01-27-2007, 09:30 PM
Thanks for the adivce but I played around with the code above and got it to work. I used the following...


$result = mysql_query("SELECT COUNT(id) FROM rp_pm WHERE todj='$_SESSION[username]'");
$total = mysql_fetch_array($result);
echo "View Inbox (". $total['COUNT(id)'] .")";


Thanks for all the help!

Fumigator
01-28-2007, 04:20 AM
I guess if you're prepared to spend a day on every query error you run across, you can ignore my suggestion :p

Or you can check the query to see if it worked and if not display an error telling you exactly why it didn't work...

guelphdad
01-28-2007, 04:27 AM
Ooh, ooh, I'm in for the lengthy trouble shooting option! ;-)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum