JoseN

Thanks so much barand! So, your idea can be done in one single query? Unfortunately, PI PEN, PI APP, PFA PEN, and PFA APP are options that belong to the cpsstatus. In my idea, PEN means Pending and APP means Approved.

Thanks requinix! I will try out your idea! Thanks for your response.

Hello Everyone - I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do: I am trying to do something like this but in PHP. This is my data from MYSQL database. The Table is called Children This is a quick explanation of how each column on the first screenshot should be filled from the database. This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? $r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren FROM Children JOIN Classrooms ON Children.classroomID = Classrooms.classroomID GROUP BY Classrooms.ClassroomName");

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.

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?

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'; }

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

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.

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.

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>

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!

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

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,

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

Barand - I got it to work using the OnClick button! I was able to pass the userID and rewardID using only one button. Now when i redeem a reward for a specific user, it inserts the reward negative value to the points table just for that single user. The total points column now it looks better. I hope this definitely worked! I will test it later to make sure. I got a different question. Is it possible to hide or encrypt the names of the PHP files in the URL when an user is browsing them? For example, www.domain.com/view_users.php to something like this www.domain.com/tsds$ds.php After redeeming rewards

With the data_id attribute will I be able to insert the reward value when clicking just one redeem button? the goal would be to just have one button in every record that will be able to insert the reward negative value into the right user and getting the right reward.

I just did it.. when using just SELECT when clicking redeem for one user it shows the negative value assign to that user but also other users. In the screenshot attached I redeemed beer for George which the reward is 5 points, it assign the -5 to George but also to Sandy. That is not good! Lol

Barand - Too good to be true! It is not working.... When redeeming a reward it inserts the negative values (reward value) to all users in the points table. I think it is because the userID is not being picked up when clicking the redeem button. Any ideas? Below is the current INSERT query which runs when clicking the button. Thanks in advance. $rewardid = $_GET['rewardID']; $qq = "INSERT INTO points (userID, loginTime, pointsEarned) SELECT usersTable.userID, NOW(), (0 - rewardListTable.valuePoints) AS redeemPoints FROM users usersTable, rewardsList rewardListTable, points pointsTable WHERE usersTable.userID = pointsTable.userID AND rewardListTable.rewardID = $rewardid GROUP BY pointsTable.userID";

Got it! Thanks a lot again. You have been pretty helpful. I am a beginner in the PHP and databases world so your help has been very helpful.

Thanks Barand! I got it to work!! the problem for the double entering was that I was not grouping the user by its ID. I just used in the INSERT INTO SELECT query the GROUP BY clause. That was the problem! I HOPE! LOL! I will test it more. regarding the storage of the totals. is it best practices to not store calculated values in a database?

Thanks a lot for all your help Barand! I think a made it work with the current button I have. I just added this line to the mysql query. WHERE usersTable.userID = pointsTable.userID AND rewardListTable.rewardID = $rewardid"; Instead of getting the userID from the button I am getting the rewardID and I am just matching the userID from the users Table to the Points Table userID. It seems to be working BUT now I have another problem and maybe it is related to the above line of code. When I redeem a reward it inserts the negative value of the reward into the points table as it is supposed. It does that only the first time a reward is redeemed. Any time after that, it double inserts the negative value of any reward I redeem instead of only one time. Below are some screen shots and the code I have in the INSERT query which I believe where the problems is. What do you think? When redeeming milk (10 points) the first time When redeeming Beers d (5 points). This when redeeming for a second time INSERT INTO QUERY $qq = "INSERT INTO points (userID, loginTime, pointsEarned) SELECT usersTable.userID, NOW(), (0 - rewardListTable.valuePoints) AS redeemPoints FROM users usersTable, rewardsList rewardListTable, points pointsTable WHERE usersTable.userID = pointsTable.userID AND rewardListTable.rewardID = $rewardid"; This UPDATE query rungs after the insert $query = "UPDATE users usertable INNER JOIN (SELECT userID, SUM(pointsEarned) as totalpoints FROM points GROUP BY userID) pointstable ON usertable.userID = pointstable.userID SET usertable.TotalPoints = pointstable.totalpoints";

I got to work this query... it gets the userID from the delete/redeem button on the table then using the rewardID (33) assigns the reward's value to the points table. It seems that is working but I need to get the rewardID from somewhere because I can't manually entered in the code. is it possible to get two values from one single button. I am just thinking a loud here. Thanks Barand. $userid = $_GET['userID']; $qq = "INSERT INTO points (userID, loginTime, pointsEarned) SELECT usersTable.userID, NOW(), (0 - rewardListTable.valuePoints) AS redeemPoints FROM users usersTable, rewardsList rewardListTable WHERE usersTable.userID = $userid AND rewardListTable.rewardID = 33"; mysqli_query ($dbc, $qq); BUTTON <div class="btn-group"> <div class="col text-center"> <button type="submit" class="btn btn-sm btn-primary" onclick="check('.$row['userID'].'); return false;">Delete</button> </div> SCRIPT FOR BUTTON <script type="text/javascript"> function check(userID) { if (confirm("Are you sure you want to delete this?")) this.location.href = "?userID="+userID; }</script>

writing a record using INSERT INTO SELECT query right?

Barand - Quick question... if want to delete the actually reward from the table and the associated points from the user total points.. how could I do that? basically instead of the redeem button in there I would have a delete button. Then for example, I would want to delete the Milk reward from Abel Villa but once I do that the 10 points from that ward will be subtracted from Abel's total points. Any ideas? I am not really sure how to go about that. Thanks in advance!

Barand - Thank you so much! It worked! I didn't know It was possible to join two tables without any relationship. You just showed me how. Thanks again!