PHP with MySQL SELECT COUNT(*) query.

Experts-Exchange.com team,

I have a site build that has users create Teams in a table named TEAMS.  I would like to be able to have all users be able to click on "teams.php" and the 1st line of data show...

"There are X teams registered."

What I have currently in "teams.php"

<?php
$con = mysql_connect("localhost", "username", "password");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("database_name", $con);

$sql="SELECT COUNT(*) FROM teams";
$result=mysql_query($sql);

echo "There are currently " . $result .  " registered.";

?>

Open in new window


This is not doing the trick.  I get.

"There are currently Resource Id #2 registered."

How can I successfully represent how many teams are currently registered on my site?

-Nick
NCollinsBBPAsked:
Who is Participating?
 
Lukasz ChmielewskiCommented:
try setting also a field name for the result:

<?php
$con = mysql_connect("localhost", "username", "password");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("database_name", $con);

$sql="SELECT COUNT(*) as cnt FROM teams";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);

echo "There are currently " . $row[cnt] .  " registered.";

?>

Open in new window

0
 
cyberkiwiCommented:
$sql="SELECT COUNT(*) FROM teams";
$result=mysql_query($sql);
if (!$result) {     die($message);    }

$row = mysql_fetch_assoc($result);
echo "There are currently " . $row[0] .  " registered.";
0
 
cyberkiwiCommented:
mysql_query returns a resourceId, from which you can use mysql_fetch_assoc or equivalent to retrieve rows from the result.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NCollinsBBPAuthor Commented:
I used your above sample and am now receiving nothing in between "currently" and "registered".  I have 8 teams in the team database right now so I should receive 8.
0
 
NCollinsBBPAuthor Commented:
That did the trick!  You guys rock.  Thank you for your time.

-Nick
0
 
cyberkiwiCommented:
$sql="SELECT COUNT(*) FROM teams";
$result=mysql_query($sql);
if (!$result) {     die($message);    }

$row = mysql_fetch_array($result, MYSQL_NUM);
echo "There are currently " . $row[0] .  " registered.";


Because the count(*) column is not named, it would be easier to retrieve by number, and just use $row[0].
The previous usage of mysql_fetch_assoc returned a string-indexed result, which should have used $row["COUNT(*)"]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.