Buy Now
Sign in

How to count columns of a database table?

moontear , January 10 2008, 18:50
M
moontear 28
January 10 2008, 18:50 #4860
Hey there,

beginner question. I count rows like this (and it's working like a charm):

global $database;
$sql = "SELECT * FROM #__chronoforms_2";
$database->setQuery($sql);
$rows = $database->loadObjectList();
$cntrows = count($rows);

echo "<p>There are currently <strong>".$cntrows."</strong> records.</p>


How do i count columns now? My table currently looks like this:
ID
Name
AddName2
AddName3
AddName4

the thing is that the user is able to enter only a name or additional names, so code should take care of that too. The end result should be something like "There are currently x records with y additional names."

Something like IF column "Name2" NOT NULL count++ AND IF COLUMN "Name3" NOT NULL count++... - I just have no clue how to write that in PHP.

Thanks in advance
moontear

PS: I know there is the "mysql_numfields" function, but I don't know how to implement it with the Joomla special coding (e.g. $database->setQuery($sql); etc)
GreyHead 64
January 10 2008, 20:31 #4864
Hi moontear,

You can count the table columns but I'm not sure that's the best way to go (depends a bit what you want to do with it).

Why not put the count into the database.

1. Create a dummy hidden field in your form
<input type='hidden' name='count' value='' />
2. In the OnSubmit before field write a bit of code to count the fields and set a value for the dummy field:
<?php
$name_array = array('Name', 'AddName1, 'AddName2, 'AddName3);
$count = 0;
foreach ( $name_array as $name ) {
if ( $_POST[$name] != "" ) {
$count += 1;
}
}
$_POST['count'] = $count;
?>
Then you can use {count} in your email template in the same way as any other field name and you can recover it from the database when you need it. (I understand that this is a duplication of data but it makes life easier in this instance.)

Bob

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
M
moontear 28
January 10 2008, 20:54 #4869
Very elaborate steps and very good, but it doen't count old values.
I basically want to use this code: http://us2.php.net/manual/en/function.mysql-num-fields.php , but don't know how to implement it currectly with chronoforms and Joomla.

I want to use the column count for exactly the example as described above. The user is able to enter name 1, name 2, name 3, name 4 etc, whereas name 2-x are optional.
For a general overview form i just want to have the TOTAL NUMBER OF ADDITIONAL NAMES. E.g. "There are 3 name 1's and 20 additional names".

It is a non-crucial function, but is very useful for overview and seems not to hard to implement with "normal PHP+SQL" meaning without all the Joomla initialization etc.

Dennis
GreyHead 64
January 10 2008, 21:57 #4875
Hi Dennis,

I don't have time to check the code right now but I'm not sure that mysql_num_fields will do what you want. I don't think it check if the fields are empty or not and, in your example, will just return 4 - name 1, name 2, name 3, name 4

I can write a foreach loop that will do what you want I think. I'll have a go once I've got some client work complete.

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
M
moontear 28
January 10 2008, 22:25 #4876
Thanks in advance, paying customers are first of course!
GreyHead 64
January 11 2008, 00:06 #4878
Hi moontear,

This seems to work for me (in the 'OnSubmit before email' box):
<?php
global $database;

$count_rows = $count_names = 0;
$sql = "
SELECT *
FROM #__chronoforms_2";
$database->setQuery($sql);
$rows = $database->loadObjectList();
$count_rows = count($rows);
$name_array = array("name", "addname1", "addname2", "addname3"«»);
foreach ( $rows as $row ) {
foreach ($name_array as $name) {
if ( $row->$name != "" ) {
$count_names = $count_names + 1;
}
}
}
echo "<p>There are currently <strong>$count_rows</strong> records and <strong>$count_names</strong> names.</p>";
?>
You'll need to check field names and the database table name; and probably redirect the output somewhere more useful!

Bob
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
M
moontear 28
January 11 2008, 02:02 #4880
Thanks for your time Bob. It works beautifully!
M
moontear 28
January 11 2008, 02:02 #4881
please delete this one - double post.<br><br>Post edited by: moontear, at: 2008/01/10 21:05