Jump to content

SELECT QUERY from table with no relationship


JoseN

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?

Link to comment
Share on other sites

  • 4 weeks later...
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,

Link to comment
Share on other sites

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

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
Link to comment
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  |
+--------------+----------------+-------------+--------------+

 

Link to comment
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!

Link to comment
Share on other sites

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
Link to comment
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>

 

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

 

Link to comment
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.

Link to comment
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.

 

Link to comment
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...

 

Link to comment
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';
               }

 

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

 

Link to comment
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?

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.