Home Downloads Forums Shop FAQs Blog Contact Demo Login
Home
/
Forums
/
Complex filters in mysql?

Check the new Joomla Extension Builder

Complex filters in mysql?

br
brononius 28 Jun, 2013
Hey,

I'm trying to create a small report for my database (filled of course with chronofrom)...


I've got following situation:
COLA - COLB
user1 - ValueB
user2 - ValueB
user3 - ValueC
user1 - ValueD
user2 - ValueB
...

And would like to see something like:
Users B - C - D
User1: 1 - 0 - 1
User2: 2 - 0 - 0
User3: 0 - 0 - 1


In mysql (phpmyadmin), i'm able to do it with following formula:
SELECT
    `inven_soort`,
    SUM(CASE WHEN `inven_bestemming` = 'Nog in een asiel' THEN 1 ELSE 0 END),
    ...
    SUM(CASE WHEN `inven_bestemming` = 'In vrijheid' THEN 1 ELSE 0 END)
FROM
    `voc_chronoforms_data_inventaris_input`
GROUP BY
    `inven_soort`
ORDER BY
    `inven_soort`


And this gives me a rough buildup of a result table (see attachments for better idea).
But i would like to customize it a bit more (selection of date range with chronoformfields, nicer colomn names, no zero's...).
In another topic, I managed to search in the database with different criteria, what works really nice.

But before I start fighting with this 'reporting', I would like to hear your opinions.
I tried all day long with chronoform/MultiLoader/custom code, but pitty enough, i'm not a php/sql guys, only as a hobby. :wink:

Any idea/suggestions what's the best way to handle this?



My phpmyadmin things:
Gr
GreyHead 05 Jul, 2013
Hi brononius,

The DB MultiRecord Loader doesn't support MySQL expressions like SUM().

If you add a Custom Code action with code like this:
<?php
$db =& JFactory::getDBO();
$query = "
  SELECT
      `inven_soort`,
      SUM(CASE WHEN `inven_bestemming` = 'Nog in een asiel' THEN 1 ELSE 0 END) AS a,
      ...
      SUM(CASE WHEN `inven_bestemming` = 'In vrijheid' THEN 1 ELSE 0 END) AS z
    FROM `voc_chronoforms_data_inventaris_input`
    GROUP BY `inven_soort`
    ORDER BY `inven_soort`
";
$db->setQuery($query);
$form->data['list_data'] = $db->loadObjectList();
?>
This will add the data to the $form->data array and you can use a Custom Element element in your form Preview to display it:
<?php
echo "<table><thead><th>. . .</th></thead>
<tbody>";
foreach ( $form->data['list_data'] as $v ) {
  echo "<tr><td>UserID {$v->inven_soort}</td><td>{$v->a} - {$v->b} - {$v-z}</td></tr>";
}
echo '</tbody></table>';
?>

You could also use CONCAT() in the query if this makes the code simpler still.

Bob
br
brononius 05 Jul, 2013
Since i'm rarther new to php/mysql/chronoform,
I gave it a try, and came up with result (code) below.
So I bet it can be done better, but this proves that even a non programmer can use chronoforms. :wink:

Some extra information/demo can be found on: Animal inventory

Thanks for the help and guidance!



<?php
echo "<table id='InvenTable' align='center' STYLE=\"border-style: solid; border-color: #DDDDDD;\">";  
   echo "<tr bgcolor= '#DDDDDD' height='200px'>";  
      echo "<td align='center' valign='bottom'><font color='#6a8f3d'><b><u>Diersoorten</u></b></font></td>";  
      echo "<td><div class=\"verticalText\">Aantal dieren<div></td>";  
      echo "<td></td>";
      echo "<td><div class=\"verticalText\">Nog in een asiel</div></td>";  
      echo "<td><div class=\"verticalText\">Overgebracht<div></td>";    
      echo "<td><div class=\"verticalText\">Terug naar eigenaar<div></td>";  
      echo "<td><div class=\"verticalText\">Geplaatst bij particulier<div></td>";  
      echo "<td><div class=\"verticalText\">Geëuthanaseerd<div></td>";  
      echo "<td><div class=\"verticalText\">Gestorven<div></td>";  
      echo "<td><div class=\"verticalText\">In vrijheid<div></td>";  
      echo "<td></td>";
      echo "<td><div class=\"verticalText2\">Aangevallen door predator<div></td>";  
      echo "<td><div class=\"verticalText2\">Andere oorzaken<div></td>";  
      echo "<td><div class=\"verticalText2\">Botulisme<div></td>";  
      echo "<td><div class=\"verticalText2\">Breuk<div></td>";  
      echo "<td><div class=\"verticalText2\">Hengelsport<div></td>";  
      echo "<td><div class=\"verticalText2\">(Hoogspannings)draad<div></td>";  
      echo "<td><div class=\"verticalText2\">Inbeslagname<div></td>";  
      echo "<td><div class=\"verticalText2\">Klemkwetsuur<div></td>";  
      echo "<td><div class=\"verticalText2\">Nestjong/jong<div></td>";  
      echo "<td><div class=\"verticalText2\">Ondervoeding/uitputting<div></td>";  
      echo "<td><div class=\"verticalText2\">Prikkeldraad<div></td>";  
      echo "<td><div class=\"verticalText2\">Stookolie<div></td>";  
      echo "<td><div class=\"verticalText2\">Vast in schouw<div></td>";  
      echo "<td><div class=\"verticalText2\">Venster<div></td>";  
      echo "<td><div class=\"verticalText2\">Verdwaald<div></td>";  
      echo "<td><div class=\"verticalText2\">Vergiftiging<div></td>";  
      echo "<td><div class=\"verticalText2\">Verkeersslachtoffer<div></td>";  
      echo "<td><div class=\"verticalText2\">Vervuiling<div></td>";  
      echo "<td><div class=\"verticalText2\">Verwaarloosd<div></td>";  
      echo "<td><div class=\"verticalText2\">(Schot)Wonde<div></td>";  
      echo "<td><div class=\"verticalText2\">Ziekte<div></td>"; 
      echo "<td></td>";
   echo "</tr>"; 






   $result = mysql_query("SELECT inven_soort,inven_bestemming,COUNT(*) FROM voc_chronoforms_data_inventaris_input WHERE `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' GROUP BY inven_soort");  
   while($row=mysql_fetch_array($result))  
   {  
   echo "<tr>";  
      echo "<td class=\"cellInven2\">" . $row['inven_soort']  . "</td>";
      echo "<td class=\"cellInven3\">" . $row['COUNT(*)'] . "</td>";  
      echo "<td class=\"cellInven5\"></td>";

      echo "<td class=\"cellInven1\">"; 
         $Bestemming = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Nog in een asiel' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Bestemming, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $Bestemming = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Overgebracht' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Bestemming, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $BestemmingC = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Terug naar eigenaar' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($BestemmingC, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $BestemmingD = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Geplaatst bij particulier' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($BestemmingD, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $BestemmingE = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Geëuthanaseerd' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($BestemmingE, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $BestemmingF = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Gestorven' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($BestemmingF, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven1\">"; 
         $BestemmingG = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'In vrijheid' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($BestemmingG, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven5\"></td>";
  echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Aangevallen door hond/kat of hun natuurlijke predator
' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";  
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Andere oorzaken' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Botulisme' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Breuk' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
               echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Hengelsport' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";            
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Hoogspanningsdraad of vast in draad' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Inbeslagname' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Klemkwetsuur' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Nestjong/jong' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
               echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Ondervoeding/uitputting' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
               echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Prikkeldraad' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Stookolie' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vast in schouw' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Venster' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verdwaald' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vergiftiging' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                        echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verkeersslachtoffer' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                                 echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vervuiling' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                                 echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verwaarloosd' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                                 echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = '(Schot)Wonde' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
                                 echo "<td class=\"cellInven4\">"; 
         $Reden = mysql_query("SELECT COUNT(inven_redenvan) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Ziekte' AND `inven_soort` = '" . $row['inven_soort'] . "'");
         echo mysql_result($Reden, 0); 
         echo "</td>";
      echo "<td class=\"cellInven5\"></td>";
   echo "</tr>";  
}  





   echo "<tr bgcolor= '#DDDDDD' height='40'>";  
      echo "<td width='200' align='center'><font color='#6a8f3d'><b>Totalen</b></font></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";
      echo "<td class=\"cellInven5\"></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Nog in een asiel' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Overgebracht' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Terug naar eigenaar' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Geplaatst bij particulier' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Geëuthanaseerd' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'Gestorven' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td align='center'><b>";
         $Totalen1 = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_bestemming` = 'In vrijheid' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen1, 0); 
         echo "</b></td>";  
      echo "<td></td>";
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Aangevallen door hond/kat of hun natuurlijke predator' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Andere oorzaken' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Botulisme' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Breuk' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Hengelsport' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Hoogspanningsdraad of vast in draad' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Inbeslagname' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
               echo "<td align='center'><b>";
         $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Klemkwetsuur' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";

                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Nestjong/jong' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";

                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Ondervoeding/uitputting' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";

                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Prikkeldraad' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";

                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Stookolie' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";

                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vast in schouw' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Venster' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verdwaald' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  

                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vergiftiging' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verkeersslachtoffer' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Vervuiling' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Verwaarloosd' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                        echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = '(Schot)Wonde' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
                  echo "<td align='center'><b>";
                  $Totalen = mysql_query("SELECT COUNT(inven_soort) FROM voc_chronoforms_data_inventaris_input WHERE `inven_redenvan` = 'Ziekte' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo mysql_result($Totalen, 0); 
         echo "</b></td>";  
      echo "<td></td>";
   echo "</tr>";  
echo "</table><br>";  


$Totalen = mysql_query("SELECT COUNT(inven_type) FROM voc_chronoforms_data_inventaris_input WHERE `inven_type` = '1' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo "We hebben in totaal <b><font color=#6a8f3d>" .  mysql_result($Totalen, 0) . "</b></font> vogels op bezoek gehad.<br>";
$Totalen = mysql_query("SELECT COUNT(inven_type) FROM voc_chronoforms_data_inventaris_input WHERE `inven_type` = '2' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo "We hebben in totaal <b><font color=#6a8f3d>" .  mysql_result($Totalen, 0) . "</b></font> zoogdieren op bezoek gehad.<br>";
$Totalen = mysql_query("SELECT COUNT(inven_type) FROM voc_chronoforms_data_inventaris_input WHERE `inven_type` = '3' AND `" . $form->data['DatumInUit'] . "` BETWEEN '" . $form->data['DatumInUit'] . "' AND '" . $form->data['Datum2']. "' ");
         echo "We hebben in totaal <b><font color=#6a8f3d>" .  mysql_result($Totalen, 0) . "</b></font> amfibieën op bezoek gehad.<br>";
echo "<br><br><br>";
?>
Er
Erik66 18 Aug, 2013
Thanks for the excellent explanation and reply Greyhead.

One remark: there is a small typo in your reply where you explain how to list multiple records from a query. On the 4th line you write:

foreach ( $form->data['list_data'] as $v } {

The } near the end of the line should be a ) instead. Took me quite a while to figure out why your example didn't work🙂

Thank you !

Erik
Gr
GreyHead 19 Aug, 2013
Hi Erik,

Sorry about the typo and thanks for pointing it out. I've fixed it in my original post.

Bob
This topic is locked and no more replies can be posted.

ChronoForms v8 is now WordPress ready, ChronoMails is out

The JED needs volunteers!

Terms Of Use Privacy Policy Business Partners

We accept paypal logo  stripe logo

© 2006 - 2025 ChronoEngine.com