JoseN Posted April 17, 2019 Author Share Posted April 17, 2019 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2019 Share Posted April 17, 2019 I meant it isn't data that you want to be displayed on a web page anywhere. Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 10, 2019 Author Share Posted May 10, 2019 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, Quote Link to comment Share on other sites More sharing options...
Barand Posted May 10, 2019 Share Posted May 10, 2019 (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 May 10, 2019 by Barand Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 10, 2019 Author Share Posted May 10, 2019 (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. Edited May 10, 2019 by JoseN Quote Link to comment Share on other sites More sharing options...
Barand Posted May 10, 2019 Share Posted May 10, 2019 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 | +--------------+----------------+-------------+--------------+ Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 10, 2019 Author Share Posted May 10, 2019 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! Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 10, 2019 Author Share Posted May 10, 2019 (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> Edited May 10, 2019 by JoseN Quote Link to comment Share on other sites More sharing options...
Barand Posted May 10, 2019 Share Posted May 10, 2019 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> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 10, 2019 Share Posted May 10, 2019 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 ] ); } } } Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 11, 2019 Author Share Posted May 11, 2019 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. Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 11, 2019 Author Share Posted May 11, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2019 Share Posted May 11, 2019 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. Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 11, 2019 Author Share Posted May 11, 2019 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... Quote Link to comment Share on other sites More sharing options...
Barand Posted May 11, 2019 Share Posted May 11, 2019 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? Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 12, 2019 Author Share Posted May 12, 2019 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'; } Quote Link to comment Share on other sites More sharing options...
Barand Posted May 12, 2019 Share Posted May 12, 2019 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(); } } } Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 12, 2019 Author Share Posted May 12, 2019 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 12, 2019 Share Posted May 12, 2019 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. Quote Link to comment Share on other sites More sharing options...
JoseN Posted May 12, 2019 Author Share Posted May 12, 2019 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.