Getting the number of items in a database table column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xprt007
Forum Newbie
Posts: 2
Joined: Mon Feb 25, 2008 5:45 am

Getting the number of items in a database table column

Post by xprt007 »

Hi
I am a php/mysql newbie ...
I want to get the number of items(number people registered for some event) in different table columns so if they are more than a a certain number X, no more registrations are possible.
I tried that using the following methods given me in another forum:
------------
SELECT COUNT(*)
FROM itmc_anmeldungen
WHERE s1t1 <> '';

To retrieve the count of rows where s1t1 is not null:

SELECT COUNT(*)
FROM itmc_anmeldungen
WHERE s1t1 IS NOT NULL;

And you can combine them:

SELECT COUNT(*)
FROM itmc_anmeldungen
WHERE s1t1 IS NOT NULL AND <> '';
------------------------

... with the syntax below:

$abfrage = "SELECT COUNT(*) FROM itmc_anmeldungen WHERE s1t1 <> ''";

$bingo = mysql_query($abfrage);

echo "<b>$bingo</b>";

For some reason, the first 2 give the result: "Resource id #4"
and the third in exactly the same format, ie with "WHERE s1t1 IS NOT NULL AND <> ''"

does not output anything.

In the database, there are currently 3 "s1t1" items, which I expect as the result.

I suppose there is something wrong with my query ...
What should I do differently?
As said, I know very basic Mysql/php, so I request for some specific answers. (I have got a good number of suggestions from different php forums, but either they dont give the expected answer OR, I do not know how to use the suggested queries to get to the result)

OR ... is there any other or better way of getting the result?

Thanks & regards
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Getting the number of items in a database table column

Post by EverLearning »

As stated in php manual:
For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
You need to use result of the mysql_query() as an argument for mysql_fetch_row|() or some other mysql_fetch_* function

Read
http://php.net/mysql_query
and
http://www.php.net/manual/en/function.m ... ch-row.php
for more information.

Also

Code: Select all

SELECT COUNT(*)
FROM itmc_anmeldungen
WHERE s1t1 IS NOT NULL AND <> '';
is not valid, WHERE clause is wrong, the column name is missing for the second expression. Correct code is

Code: Select all

SELECT COUNT(*)
FROM itmc_anmeldungen
WHERE s1t1 IS NOT NULL AND s1t1 <> '';
For SELECT statement syntax read:
http://dev.mysql.com/doc/refman/5.0/en/select.html
Google around for tutorials for PHP and MySql.

Good luck :)
xprt007
Forum Newbie
Posts: 2
Joined: Mon Feb 25, 2008 5:45 am

Re: Getting the number of items in a database table column

Post by xprt007 »

Hi
I am very grateful for your response and very helpful explanations.
I used this to get the results from that column:
$abfrage1 = "SELECT COUNT(*) as total FROM itmc_anmeldungen WHERE s1t1 <> ''";
$bingo = mysql_query($abfrage1);
while ($row = mysql_fetch_array($bingo))
{
echo $row['total'];
}
Regards :)
ECJughead
Forum Newbie
Posts: 13
Joined: Fri Mar 07, 2008 9:14 am

Re: Getting the number of items in a database table column

Post by ECJughead »

I too get the resource id#4 when performing my query. I've tried many things and can't seem to get the results back so I can use them.

<?php
function confirm_query($type_set) {
if (!$type_set) {
die("Database query failed: " . mysql_error());
}
}

function get_types() {
global $connection;
$query = "SELECT type
FROM types";
$type_set = mysql_query($query, $connection);
confirm_query($type_set);
return $type_set;
}

$type_set = get_types();
echo $type_set;
?>

What am I missing?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Getting the number of items in a database table column

Post by Zoxive »

ECJughead wrote:What am I missing?
Exactly what the other user was missing.

All you are doing right now is doing the query, now you need to get the data the query selected.

mysql_fetch_row();

mysql_fetch_assoc();

Take a look at the examples too.
ECJughead
Forum Newbie
Posts: 13
Joined: Fri Mar 07, 2008 9:14 am

Re: Getting the number of items in a database table column

Post by ECJughead »

I understand now! Thanks!
Post Reply