Getting number of items in a table column

[lionheart8]

Hi

I am quite new to Mysql as my question probably shows.

I need some help to get the number of contents in a column s1t1 in table "itmc_anmeldungen".

My attempt:

---------------------

$abfrage = "SELECT id,vorname,nachname,strasse,plz,ort,tel,email,firma,s1t1,s1t2,s1t3,s2t1,s2t2,s2t3,signupdate,user_ip,activated,lastlogin FROM itmc_anmeldungen ORDER BY id";

$result = mysql_query($abfrage) or die(mysql_error());

 

$totalsum = mysql_num_rows($result);

$pix = mysql_num_fields($result);

if($totalsum)

{

echo "There are <b>$totalsum</b> results in the database<P>";

echo $pix."<p>";

}

This i know returns the number of rows in a table.

---------------------------

$ergebnis = mysql_query('SELECT * FROM itmc_anmeldungen WHERE s1t1 != ""');

(I wanted the s1t1 fields that are not empty - this did not work)

 

$rex = mysql_num_fields($ergebnis);

 

if (!$rex)

{

die('Could not query:' . mysql_error());

}

else

{

echo "<p> There are <h4>$rex</h4> results in db.";

}

----------------------------

In the 2 cases, the number of results returned is the same, which is actually the total number of rows.

I however want to get the total number of items in each column with the second function, like s1t1 OR any other like "firma".

What function(s) do I use to do so?

Is there any function I can use to get the number of items in each individual column & I can select which to print, ie like those in the whole table itmc_anmeldungen?

 

Please note I'm a MySQL Newbie, so be clear & if possible use the examples above to answer my question. (I did not find any online tutorial addressing my question fast enough. I urgently need a solution)

 

Thanks & regards.

[toplay]

Doing:

 

SELECT * FROM itmc_anmeldungen WHERE s1t1 > ""      or  ... WHERE LENGTH(s1t1) > 0

 

and using mysql_num_rows() will return the actual rows and how many there are.

 

This:

 

SELECT count(*) AS total_non_empty_s1t1 itmc_anmeldungen WHERE s1t1 > ""      or  ... WHERE LENGTH(s1t1) > 0

 

will just return one row containing a value which would correspond to the number of rows that have something in s1t1 column.

 

I think that's what you're talking about. You didn't mention what type of column s1t1 (char, numeric, decimal, etc.).

 

See manual for string functions:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 

Please don't specify any type of urgency in your posts because it's against forum rules. This is a free forum and there is no guarantee that anyone will answer let along in the time frame you might be looking for. If you need urgent help, consider posting in the freelancing section for paid help. Thanks.

 

[toplay]

This way returns 6 rows containing a count and an identifier (column_name) to tell for which column the count is for:

 

SELECT 's1t1' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s1t1) > 0

UNION

SELECT 's1t2' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s1t2) > 0

UNION

SELECT 's1t3' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s1t3) > 0

UNION

SELECT 's2t1' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s2t1) > 0

UNION

SELECT 's2t2' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s2t2) > 0

UNION

SELECT 's2t3' AS column_name, count(*) AS total_row_count FROM itmc_anmeldungen WHERE LENGTH(s2t3) > 0

 

 

If you want just one row back that has the total for each column, then you would do something like this:

 

SELECT

            SUM(IF(LENGTH(s1t1) > 0, 1, 0)) AS total_s1t1_count

          , SUM(IF(LENGTH(s1t2) > 0, 1, 0)) AS total_s1t2_count

          , SUM(IF(LENGTH(s1t3) > 0, 1, 0)) AS total_s1t3_count

          , SUM(IF(LENGTH(s2t1) > 0, 1, 0)) AS total_s2t1_count

          , SUM(IF(LENGTH(s2t2) > 0, 1, 0)) AS total_s2t2_count

          , SUM(IF(LENGTH(s2t3) > 0, 1, 0)) AS total_s2t3_count

 

FROM itmc_anmeldungen

 

# optional: you could specify a where clause here

;

 

 

[lionheart8]

Hi

Many thanks for the quick response.

I tried the first bit with this:

 

$pinto = "SELECT count(*) AS total_non_empty_s1t1 FROM itmc_anmeldungen WHERE s1t1 > ''";

$pixie = mysql_query($pinto) or die(mysql_error());

echo "<p>$pixie";

 

In the DB table, there are 3 s1t1 entries out of a total of 5 rows. I expected 3 as the result, but I keep getting this:

 

Resource id #5

 

What should I do differently? [type of column by the way is varchar]

 

Regards

[fenway]

You need to use mysql_fetch_result( $pixie, 0, 0 ) or mysql_fetch_assoc() and the grab the otal_non_empty_s1t1 hash key.