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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.