How to count columns of a database table?

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)

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

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

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

Thanks in advance, paying customers are first of course!

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

Thanks for your time Bob. It works beautifully!

please delete this one - double post.<br><br>Post edited by: moontear, at: 2008/01/10 21:05