Jump to content
JoseN

SELECT QUERY from table with no relationship

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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... 

 

Untitled.png.3310168150fb9da1833abd13ad71cff8.png

 

Thanks in advance,

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Barand

Share this post


Link to post
Share on other sites
Posted (edited)
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.

 

Untitled.png.36b0a0adc7672383e24621e2c556978a.png

 

Edited by JoseN

Share this post


Link to post
Share on other sites

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  |
+--------------+----------------+-------------+--------------+

 

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Posted (edited)
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>

 

 

 

Untitled.png.a9dbe1aa98703bdd614a8211b8a0db38.png

Edited by JoseN

Share this post


Link to post
Share on other sites

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>

 

Share this post


Link to post
Share on other sites

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 ] );
        }
    }
}

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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...

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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';
               }

 

Share this post


Link to post
Share on other sites

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();
        }
    }
}

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.