Hi,

Thanks for taking a look at this. I need to generate a table for each product with checkboxs by querying the database, but i keep having problems. The table i need to generate should look like something below, where the "x" represents a checkbox which is checked.

Temp | Item 1 | Item 2 | Item 3 | Item 4 | (test codes)

A X X X
B X X X

C X X X X

This is quite difficult to explain, but i'll try. The first column represents all the temeratures in a temperature datbase table. I then need to query all the tests in the test database table, represented by the test codes, i.e item 1, item 2, etc.

I then need to query a third table test_temp databse table to see if the if the temp and test for a product is in the test_temp table, if so then a checkbox should be checked. If not, then an unchecked checkbox is displayed. The end user should be able to check and uncheck a checkbox if a product has a new test at test temperature.

Database Tables

Temperature: temp_id (PK), temp_name

Test: test_id (PK), test_name

Test_Temp: test_temp_id (PK), test_id (FK), temp_id (FK), product_id (FK)

Thanks in advance for any help, advice, suggestions. Have a nice day!!

Regards,

Steven

    To be honest, I didn't fully understand your explanation. However, I think I can provide some help. Here is a possible implementation:

    <?php
$temp_ids = array('A', 'B', 'C'); //obtained from database
$product_ids = array('1', '2', '3'); //obtained from database

$data = array();

foreach($temp_ids as $temp_id){
    $data[$temp_id] = array();
    foreach($product_ids as $product_id){
        $data[$temp_id][$product_id] = false;
    }
}

$query = $db->query('SELECT temp_id, product_id FROM Test_Temp');

while($result = $query->fetch()){
    $temp_id = $result['temp_id'];
    $product_id = $result['product_id'];
    $data[$temp_id][$product_id] = true;
}
?>

    The $data array then holds boolean values indicating for each combination of temp_id and product_id whether a test exists. I hope this helps a little.

      Hi,

      this is what I understood:

      The contents of Test are the headers of the item columns of the table

      The contents of Temperatures are the first cell of each row

      Each record in Test_Temp means a checked checkbox in the cell matching the current Temperature/Test combination.
      Which means:
      a) that there can be only one product for a given Temperature/Test combination

      b) you want to display a separate table for each product.

      The simplest solution would be to fetch all records of Test and Temperature and iterate through the records to display the table.
      for each Temperature/Test combination (and possibly the current product) you query Test_Temp to check if a record exists in which case you set the checked attribute of the checkbox input.

      A more complicated solution with fewer queries may be possible by using LEFT/INNER/WHATEVER JOINS. But that depends a little bit on what you want to do exactly ... see a) and b) above.

      I hope that I understood you completely ...

        Yeah, that was also my understanding. I decided to instead create an array of boolean values representing the check boxes (true = checked, false = not checked) and then run a single query on the Test_Temp table and use the results to change the relevant boolean values in the array. I did it this way so you'd only have to run 3 queries max to get what you needed. However, if the table is large then there is the problem of the array becoming larger than the allowed memory limit.

          Write a Reply...