SELECT QUERY from table with no relationship

[JoseN]

1 hour ago, Barand said:

You might want to look up "url rewriting".

I'd be more worried about broadcasting your user's secret answers

Thanks Barand! you mean broadcasting it here in the forum? it is just dummy data or you mean other way?

[Barand]

I meant it isn't data that you want to be displayed on a web page anywhere.

[JoseN]

On 4/17/2019 at 11:41 AM, Barand said:

You might want to look up "url rewriting".

I'd be more worried about broadcasting your user's secret answers

Hello Barand! 

 

I need some help again! I am trying to create a table like the attached image. The idea is to have this table with names coming from a users table... then be able to track the meals using checkboxes. The checkboxes checked would then be submitted via a button to a Meals table in MYSQL... I am thinking the table can be mealID, userID (Userstable), Breakfast, Lunch, Snack. The meals can be a tinyint datatype. I  totally don't know how to start doing this... 

 

 

Thanks in advance,

[Barand]

Don't create a table like your userstable with a column for each meal. The correct way to do is a table with a separate record for each meal. Only store records where the checkbox is checked

+----------------+                                   +----------------+
|  user          |                                   |  meal          |
+----------------+                                   +----------------+
|  user_id  (PK) |---+                         +-----|  meal_id  (PK) |
|  user_name     |   |                         |     |  description   |
+----------------+   |                         |     +----------------+
                     |                         |
                     |                         |
                     |    +----------------+   |
                     |    |  user_meal     |   |
                     |    +----------------+   |
                     +---<|  user_id (PK)  |   |
                          |  meal_id (PK)  |>--+
                          +----------------+

EDIT: You might also want to add "date" to the user_meal table

Edited May 10, 2019 by Barand

[JoseN]

22 minutes ago, Barand said:

Don't create a table like your userstable with a column for each meal. The correct way to do is a table with a separate record for each meal. Only store records where the checkbox is checked

+----------------+                                   +----------------+
|  user          |                                   |  meal          |
+----------------+                                   +----------------+
|  user_id  (PK) |---+                         +-----|  meal_id  (PK) |
|  user_name     |   |                         |     |  description   |
+----------------+   |                         |     +----------------+
                     |                         |
                     |                         |
                     |    +----------------+   |
                     |    |  user_meal     |   |
                     |    +----------------+   |
                     +---<|  user_id (PK)  |   |
                          |  meal_id (PK)  |>--+
                          +----------------+

 

Thanks Barand! Something like this? The user table I already have it.

 

 

Edited May 10, 2019 by JoseN

[Barand]

Your childmeal table data would be like this

+--------------+----------------+-------------+--------------+
| childmeal_id |  child_id      | meal_id     |  date        |
+--------------+----------------+-------------+--------------+
|      1       |      1         |     2       |  2019-05-06  |
|      2       |      1         |     3       |  2019-05-06  |
|      3       |      2         |     2       |  2019-05-06  |
|      4       |      2         |     4       |  2019-05-06  |
|      5       |      1         |     2       |  2019-05-07  |
|      6       |      1         |     3       |  2019-05-07  |
|      7       |      2         |     2       |  2019-05-07  |
|      8       |      2         |     3       |  2019-05-07  |
|      9       |      2         |     4       |  2019-05-07  |
+--------------+----------------+-------------+--------------+

 

[JoseN]

2 minutes ago, Barand said:

Your childmeal table data would be like this

+--------------+----------------+-------------+--------------+
| childmeal_id |  child_id      | meal_id     |  date        |
+--------------+----------------+-------------+--------------+
|      1       |      1         |     2       |  2019-05-06  |
|      2       |      1         |     3       |  2019-05-06  |
|      3       |      2         |     2       |  2019-05-06  |
|      4       |      2         |     4       |  2019-05-06  |
|      5       |      1         |     2       |  2019-05-07  |
|      6       |      1         |     3       |  2019-05-07  |
|      7       |      2         |     2       |  2019-05-07  |
|      8       |      2         |     3       |  2019-05-07  |
|      9       |      2         |     4       |  2019-05-07  |
+--------------+----------------+-------------+--------------+

 

Got it! Date for the timestamp! 

How would I do the table form with the checkboxes? I can't see a way to start it!

[JoseN]

15 minutes ago, JoseN said:

Got it! Date for the timestamp! 

How would I do the table form with the checkboxes? I can't see a way to start it!

 

How about this? 

This is the SELECT query to get the names of the children.

 

    $q="SELECT childID, CONCAT(Firstname, ' ' ,Lastname) AS childname
          FROM Children";

 

This is the table...

 

 echo'
      <div class="table-responsive">
      <table class="table table-hover table-bordered table-sm">
        <thead class="thead-dark">
          <tr>
            <th class="text-center" scope="col">Child Name</th>
            <th class="text-center" scope="col">Breakfast</th>
            <th class="text-center" scope="col">Lunch</th>
            <th class="text-center" scope="col">Snack</th>
  
          
          </tr>
        </thead>
        <tbody>';


                
            while ($row = mysqli_fetch_array($r))
            {
              // Print out the records
              echo'
                    <tr>
                    <td>' . $row['childname'] . '</td>

                    <td></td>
                    <td></td>
                    <td></td>
                   
                    </tr>';

            } //end of while loop

      echo' </tbody>
      </table>
      </div>

 

 

 

Edited May 10, 2019 by JoseN

[Barand]

Nearly - you need the checkboxes. I'd do it like this

<?php
          
//
//  GET MEALS FOR COLUMN HEADS
//
$res=$db->query("SELECT meal_id
                      , description
                 FROM meal
                 ORDER BY meal_id     
                ");
$meals = [];
foreach ($res as $r) {
    $meals[ $r['meal_id'] ] = $r['description'];
}

$thead = "<tr><th>Name</th><th> " . join('</th><th>', $meals) . "</th></tr>\n" ;

//
// GET DATA FOR THE FORM
//         
$res = $db->query("SELECT child_id
                        , child_name
                        , meal_id
                   FROM child
                        CROSS JOIN meal
                   ORDER BY child_name, meal_id;     
                   ");
$tdata = '';
$prevchild = 0;

foreach ($res as $r) {
    if ($r['child_id'] != $prevchild) {             // is it a new child?
        if ($prevchild != 0) {
            $tdata .= "</tr>\n";                    // close previous row
        }
        $tdata .= "<tr><td>{$r['child_name']}</td>";      // start new row
        $prevchild = $r['child_id'];
    }
    $tdata .= "<td style='text-align: center'>
                   <input type='checkbox' name='meal[{$r['child_id']}][]' value='{$r['meal_id']}'
               </td>";
}
$tdata .= "</tr>\n";                                // close final row
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="creation-date" content="05/10/2019">
<title>Example</title>
</head>
<body>
<form method='POST'>
<table border="1" style="width: 500px; border-collapse: collapse;">
      <?=$thead?>
      <?=$tdata?>
</table>
<input type="submit" name="btnSubmit" value="Submit">
</form>
</body>
</html>

 

[Barand]

In anticipation of your next question - if you have the form as I suggested, then to process

//
//  PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    
    $stmt = $db->prepare("INSERT INTO childmeal (child_id, meal_id, meal_date) VALUES ( ?, ?, CURDATE() )");
    
    foreach ($_POST['meal'] as $cid => $meals) {
        foreach ($meals as $mid) {
            $stmt->execute( [ $cid, $mid ] );
        }
    }
}

 

[JoseN]

3 hours ago, Barand said:

In anticipation of your next question - if you have the form as I suggested, then to process

//
//  PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    
    $stmt = $db->prepare("INSERT INTO childmeal (child_id, meal_id, meal_date) VALUES ( ?, ?, CURDATE() )");
    
    foreach ($_POST['meal'] as $cid => $meals) {
        foreach ($meals as $mid) {
            $stmt->execute( [ $cid, $mid ] );
        }
    }
}

 

Great! Thanks Barand! I am still trying to get the table to come show. It is now coming up with your example. I just get a blank page when I run it. The only thing I get is the Submit button.

[JoseN]

6 minutes ago, JoseN said:

Great! Thanks Barand! I am still trying to get the table to come show. It is now coming up with your example. I just get a blank page when I run it. The only thing I get is the Submit button.

Got it! 

 

Question.... why does the query doesn't return anything when running it like this:

$res=$dbc->mysqli_query();

 

it does return data with your example but is it secure?

$res=$dbc->query();

 

Why not running it like this? Just wondering...

$res = "SELECT meal_id, description
                 FROM meal
                 ORDER BY meal_id     
                ";

mysqli_query($dbc, $res); // Run the query.

 

[Barand]

5 hours ago, JoseN said:

Why not running it like this? Just wondering...

$res = "SELECT meal_id, description
                 FROM meal
                 ORDER BY meal_id     
                ";

mysqli_query($dbc, $res); // Run the query.

 

You need to assign the output from mysqli_query() to a result object.

[JoseN]

12 hours ago, Barand said:

You need to assign the output from mysqli_query() to a result object.

Got it! Thanks! 

 

The Process Posted Data example code not working... I tested it and it is not inserting the data into the database table. Not sure if I am doing something wrong. The code goes in the same file right? Before the HTML form...

 

[Barand]

It should be the first bit of code to be processed in the file.

My code uses PDO, you use mysqli, so what is your code for that bit?

[JoseN]

8 hours ago, Barand said:

It should be the first bit of code to be processed in the file.

My code uses PDO, you use mysqli, so what is your code for that bit?

 

Still not working! It seems everything else is fine but this line... 

$stmt->execute( [ $cid, $mid ] );

 

I think the the statement is not executing. I did a small test.. it always return DIDNT WORK.

 

  if ($stmt->execute([ $cid, $mid ])) 
              {
                  echo'WORKED';
               } else {
                  echo'DIDNT WORK';
               }

 

[Barand]

The clue was in this statement

13 hours ago, Barand said:

My code uses PDO, you use mysqli,

If you bothered to use the PHP reference manual you would see that the execute command in mysqli does not work the same way as it does in PDO. With PDO you can pass the parameters for a prepared statemet as an array in the execute call (as I have). With mysqli you have to explicitly bind the parameters first then call execute().

The mysqli version of the code would be

//
//  PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    
    $stmt = $db->prepare("INSERT INTO childmeal (child_id, meal_id, meal_date) VALUES ( ?, ?, CURDATE() )");
    $stmt->bind_param('ii', $cid, $mid);
    
    foreach ($_POST['meal'] as $cid => $meals) {
        foreach ($meals as $mid) {
            $stmt->execute();
        }
    }
}

 

[JoseN]

4 hours ago, Barand said:

The clue was in this statement

If you bothered to use the PHP reference manual you would see that the execute command in mysqli does not work the same way as it does in PDO. With PDO you can pass the parameters for a prepared statemet as an array in the execute call (as I have). With mysqli you have to explicitly bind the parameters first then call execute().

The mysqli version of the code would be

//
//  PROCESS POSTED DATA
//
if ($_SERVER['REQUEST_METHOD']=='POST') {
    
    $stmt = $db->prepare("INSERT INTO childmeal (child_id, meal_id, meal_date) VALUES ( ?, ?, CURDATE() )");
    $stmt->bind_param('ii', $cid, $mid);
    
    foreach ($_POST['meal'] as $cid => $meals) {
        foreach ($meals as $mid) {
            $stmt->execute();
        }
    }
}

 

Thanks Barand!

I did some research and I knew I needed to use the bind statement and I actually did but I never released that I didn't need to include the cid and mid variables in the execute(). I was adding them in there... Thanks a lot! I am still learning the PHP language and I was not aware of the PDO style.

Which one is better to use? PDO or Mysqli?

[Barand]

PDO

With mysqli you get a result object when you use mysqli_query() or a statement object if you use mysqli_prepare. These two object types have different sets of methods.

With PDO the result and statement objects are the same - so only one set of methods to remember. Also, with PDO, you are not limited to mysql databases.

[JoseN]

2 minutes ago, Barand said:

PDO

With mysqli you get a result object when you use mysqli_query() or a statement object if you use mysqli_prepare. These two object types have different sets of methods.

With PDO the result and statement objects are the same - so only one set of methods to remember. Also, with PDO, you are not limited to mysql databases.

Mmm! I should be focusing in learning PDO then... Thanks a lot for all the help with these hands on practice I am going through.