Getting the number of items in a database table column

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

wilku

First things first: you don't use result of mysql_query to echo the answer. You have to use mysql_fetch_row (or others from this family of functions):

$bingo = mysql_query($abfrage);
while ($row = mysql_fetch_row($bingo))
{
  var_dump($row);
}

Second thing - when you combine logical conditions do it this way (even if the conditions refer to the same column:

... WHERE column<>'' AND column IS NOT NULL

xprt007

Hi
Many thanks for the quick response. 🙂

I tried out

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

$bingo = mysql_query($abfrage);
while ($row = mysql_fetch_row($bingo))
{
var_dump($row);
}

and this is what I get:

array(1) { [0]=> string(1) "3" }

What should I do differently? I guess I am missing something above.

ALSO, supposing I also not want only to get the value for "s1t1", but also values for s1t2, s2t1, firms, names etc, how would one go about this?
I actually want to make a script to register people online, based on the number of those already registered. That's why I need to the number of those items.

Thanks in advance

wilku

That means that $row is an array of 1 element. This element, gives you the count you need. So use $row[0] as your count. When you want to count other columns, replace the name of the column.

As for the script you want to make - I know you stated, you're new to mysql/php, but don't expect people to write the script for you. The suggestions are only to help you learn. If you want to have the solution right away - hire someone to write it for you.

jeremyphphaven

Since you're only returning one row/result, here's how I like to handle.

In the SQL make the return be called count:

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

Then pull use PHP functions to get the data

$result = mysql_query($query);
$row = mysql_fetch_array($result,MYSQL_ASSOC);
echo 'Count - '.$row['count'];

laserlight

Actually, it would be simpler to use:

$result = mysql_query($query);
echo 'Count - ' . mysql_result($result, 0);

I dont understand why mysql_fetch_array() with MYSQL_ASSOC would be preferred to mysql_fetch_assoc().

xprt007

Hi all,

I am very grateful for your responses 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'];
}

... and in the process of learning and trying out the other suggestions.

Regards 🙂

jeremyphphaven

laserlight wrote:
I dont understand why mysql_fetch_array() with MYSQL_ASSOC would be preferred to mysql_fetch_assoc().

Wow, you are the "PHP WITCH".

To answer your question. It's a matter of preference. this way I can switch back and forth or use BOTH if need be in a tad simpler way.

As I've said many times, there is more than one way to skin a cat...