Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,344

  • Joined

  • Last visited

  • Days Won

    795

 Content Type 

Everything posted by Barand

  1. 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>
  2. 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 | +--------------+----------------+-------------+--------------+
  3. 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
  4. Barand
    Not tested, but you could try this Calc the resize factor (R) as sqrt(required_size / orig_size) Multiply the orig height and width by R to get new dimensions
  5. Barand
    That will give the number of entries in each contest. For the number of contests SELECT COUNT(DISTINCT contest_id) as contests FROM entries WHERE user_id = :user_id or, for all users SELECT user_id , COUNT(DISTINCT contest_id) as contests FROM entries GROUP BY user_id
  6. Barand
    The echo statement is actually three statements on one line. I had to look twice.
  7. Barand
    if you always redirect and die() you will never see the print_r() output. "$show_id" is not defined inside that function so will be null. Why don't you have a single POST variable (instead of your current 3 variables) called "status" which can have have a value of 1, 2, or 37? It would save all the repatetive coding.
  8. Barand
    @Mohonda Here's a working example which you can modify to use your classes <?php mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $conn = mysqli_connect(HOST, USERNAME, PASSWORD, 'test'); // // get the data and store in an array // $res = mysqli_query($conn, "SELECT prod_id , description , price FROM test_product ORDER BY price "); $products = mysqli_fetch_all($res, MYSQLI_ASSOC); // // put the data into chunks of 4 for output // $chunks = array_chunk($products, 4); ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <title>Sample</title> <style type="text/css"> .mySlide {display:none;} </style> <script> var slideIndex var numSlides $().ready(function() { var x = document.getElementsByClassName("mySlide"); numSlides = x.length slideIndex = 1; showSlide(); }) function plusIndex(n) { slideIndex += n if (slideIndex > numSlides) {slideIndex = 1} if (slideIndex < 1) {slideIndex = numSlides} showSlide(); } function showSlide() { $(".mySlide").css("display", "none") $(".mySlide[data-index=" + slideIndex + "]").css("display", "block"); } </script> </head> <body> <h2 class="w3-center">Slideshow</h2> <div class="w3-content w3-display-container w3-center"> <?php $n = 0; foreach ($chunks as $prods) { // ADD NEW SLIDE echo '<div class="mySlide w3-content w3-display-container w3-center" data-index="'. ++$n .'">'."\n"; foreach ($prods as $p) { // ADD SLIDE CONTENT echo "<div style='display:inline-block; width:40%; margin:8px; padding:8px; background-color:#EEE'> <h4>{$p['description']}</h4> <p>{$p['price']}</p> </div>\n"; } echo '</div>'."\n"; } echo '<button class="w3-button w3-black w3-display-left" onclick="plusIndex(-1)"><</button> <button class="w3-button w3-black w3-display-right" onclick="plusIndex(1)">></button>' ; ?> </div> </body> </html>
  9. Barand
    Revrese engineering your queries, it appears your table structure is like the model below. If I am right, the "studenttest" table is redundant. Your first and third queries are almost identical and both contain inefficient dependent subqueries. You could combine them into a single query select t.testid ,t.testname ,DATE_FORMAT(t.testfrom,'%d %M %Y') as fromdate ,DATE_FORMAT(t.testto,'%d %M %Y %H:%i:%S') as todate ,sub.subname ,prize ,COUNT(DISTINCT stdid) as attemptedstudents , MAX(totmarks) as maxmarks FROM ( SELECT s.stdid , s.stdname , sq.testid , IFNULL(SUM(q.marks),0) as totmarks FROM student s INNER JOIN studentquestion sq USING (stdid) LEFT JOIN question q ON sq.testid = q.testid AND sq.qnid = q.qnid AND sq.stdanswer = q.correctanswer GROUP BY sq.testid, s.stdid ) tots INNER JOIN test t USING (testid) INNER JOIN subject sub USING (subid) GROUP BY testid; +--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+ | testid | testname | fromdate | todate | subname | prize | attemptedstudents | maxmarks | +--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+ | 1 | Test 1 | 01 January 2019 | 31 January 2019 00:00:00 | Science | 50 | 5 | 40 | | 2 | Test 2 | 01 February 2019 | 28 February 2019 00:00:00 | Science | 100 | 3 | 60 | | 3 | Test 3 | 01 January 2019 | 31 January 2019 00:00:00 | Politics | 50 | 5 | 20 | | 4 | Test 4 | 01 February 2019 | 28 February 2019 00:00:00 | Politics | 100 | 3 | 30 | | 5 | Test 5 | 01 January 2019 | 31 January 2019 00:00:00 | History | 50 | 5 | 30 | | 6 | Test 6 | 01 February 2019 | 28 February 2019 00:00:00 | History | 100 | 3 | 25 | +--------+----------+------------------+---------------------------+----------+-------+-------------------+----------+ This query would give the ranking of the students for each test SELECT subname , stdname , @seq := IF(testid=@prevt, @seq+1, 1) as seq , @rank := IF(totmarks=@prevm, @rank, @seq)+0 as rank , @prevt := testid as testid , @prevm := totmarks as marks FROM ( SELECT s.stdid , s.stdname , sq.testid , IFNULL(SUM(q.marks),0) as totmarks FROM student s INNER JOIN studentquestion sq USING (stdid) LEFT JOIN question q ON sq.testid = q.testid AND sq.qnid = q.qnid AND sq.stdanswer = q.correctanswer GROUP BY sq.testid, s.stdid ORDER BY sq.testid, totmarks DESC ) tots INNER JOIN test t USING (testid) INNER JOIN subject sub USING (subid) JOIN (SELECT @seq:=0, @prevm:=0, @prevt:=0, @rank:=0) init; +----------+-----------+------+------+--------+-------+ | subname | stdname | seq | rank | testid | marks | +----------+-----------+------+------+--------+-------+ | Science | Student 5 | 1 | 1 | 1 | 40 | | Science | Student 2 | 2 | 2 | 1 | 25 | | Science | Student 3 | 3 | 3 | 1 | 15 | | Science | Student 4 | 4 | 3 | 1 | 15 | | Science | Student 1 | 5 | 5 | 1 | 5 | | Science | Student 3 | 1 | 1 | 2 | 60 | | Science | Student 1 | 2 | 2 | 2 | 0 | | Science | Student 2 | 3 | 2 | 2 | 0 | | Politics | Student 3 | 1 | 1 | 3 | 20 | | Politics | Student 1 | 2 | 1 | 3 | 20 | | Politics | Student 5 | 3 | 3 | 3 | 0 | | Politics | Student 2 | 4 | 3 | 3 | 0 | | Politics | Student 4 | 5 | 3 | 3 | 0 | | Politics | Student 1 | 1 | 1 | 4 | 30 | | Politics | Student 3 | 2 | 2 | 4 | 25 | | Politics | Student 2 | 3 | 3 | 4 | 15 | | History | Student 2 | 1 | 1 | 5 | 30 | | History | Student 4 | 2 | 2 | 5 | 25 | | History | Student 1 | 3 | 3 | 5 | 15 | | History | Student 3 | 4 | 4 | 5 | 5 | | History | Student 5 | 5 | 5 | 5 | 0 | | History | Student 3 | 1 | 1 | 6 | 25 | | History | Student 1 | 2 | 2 | 6 | 0 | | History | Student 2 | 3 | 2 | 6 | 0 | +----------+-----------+------+------+--------+-------+ I
  10. Barand
    In your first and third queries you refer to test,testfrom and test.testto yet you have "test.validto" in the final WHERE clause.
  11. Barand
    That is a sign that the query failed and "$result" contains "false" Check for sqlserver error messages
  12. Barand
    There is no point in your just reposting my code (which works). Post the code you are using if you are having problems.
  13. Barand
    Just about every professional coder in these forums would advise you to switch to PDO instead of mysqli, but if you insist <?php mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $conn = mysqli_connect(HOST, USERNAME, PASSWORD, 'test'); // // get the data and store in an array // $res = mysqli_query($conn, "SELECT prod_id , description , price FROM test_product ORDER BY price "); $products = mysqli_fetch_all($res, MYSQLI_ASSOC); // // put the data into chunks of 4 for output // $chunks = array_chunk($products, 4); ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Sample</title> </head> <body> <?php foreach ($chunks as $prods) { echo '<div>'."\n"; foreach ($prods as $p) { echo "<div style='display:inline-block; width:15%; margin:8px; padding:8px; background-color:#EEE'> <h4>{$p['description']}</h4> <p>{$p['price']}</p> </div>\n"; } echo '</div>'."\n"; } ?> </body> </html>
  14. Barand
    Your call to mysqli_fetch_all() is incorrect (See manual)
  15. Barand
    Example as requested <?php // // create some test data (uses PDO) // $db->exec("DROP TABLE IF EXISTS test_product"); $db->exec("CREATE TABLE test_product ( prod_id int not null auto_increment primary key, description varchar(50), price decimal(10,2) )"); $db->exec("INSERT INTO test_product (description, price) VALUES ('Product A', 49.99), ('Product B', 29.99), ('Product C', 9.99), ('Product D', 22.99), ('Product E', 29.99), ('Product F', 19.99), ('Product G', 129.99), ('Product H', 99.99), ('Product I', 74.99), ('Product J', 69.99); "); // // get the data and store in an array // $res = $db->query("SELECT prod_id , description , price FROM test_product ORDER BY price "); $products = $res->fetchAll(); // // put the data into chunks of 4 and output // $chunks = array_chunk($products, 4); ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Sample</title> </head> <body> <?php foreach ($chunks as $prods) { echo '<div>'."\n"; foreach ($prods as $p) { echo "<div style='display:inline-block; width:15%; margin:8px; padding:8px; background-color:#EEE'> <h4>{$p['description']}</h4> <p>{$p['price']}</p> </div>\n"; } echo '</div>'."\n"; } ?> </body> </html> Result
  16. Barand
    I meant it isn't data that you want to be displayed on a web page anywhere.
  17. Barand
    You might want to look up "url rewriting". I'd be more worried about broadcasting your user's secret answers
  18. Barand
    There are a couple of methods you could use the data_id and button value that I showed you earlier, coupled with an ajax request to update the points have a separate form for each row, put the userID and points values into hidden fields, and submit with the redeem button.
  19. Barand
    Maybe it's time to go to plan B and use the data_id attribute instead of your INSERT ... SELECT?
  20. Barand
    Have you investigated that?
  21. Barand
    That's the general theory (however, as with any rule, there may be exceptions in practice. For example, I expect my bank stores the closing balance on my last statement, otherwise it will have to go through 50 years of transactions to get the opening balance on my next statement). Storing the individual transactions that build up to the total gives you an audit trail. There is also a possibility that a stored total could get out of sync with the total of the individual transactions, and then you have two versions of the "truth".
  22. Barand
    Don't store totals (or any other derived data) in your tables. You get totals by requerying your data when required. You have a INSERT ... SELECT query. If you run the SELECT portion on its own, do you only get a single record every time?
  23. Barand
    "data-" attributes are useful, EG <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> $().ready( function() { $(".redeem").click( function() { var pts = $(this).val() var userid = $(this).data("id") alert("User: " + userid + "\nPoints: " + pts) }) }) </script> </head> <body> <button class='redeem' data-id='42' value='10' >Redeem</button> <button class='redeem' data-id='25' value='30' >Redeem</button> </body> </html>
  24. Barand
    When they redeem the milk reward (using the 10 points) write a record for -10 points for that user to the points table. That way, the next time you total the points it will be 10 less.
  25. Barand
    Create a table subquery to calculate the total points for each user and join that to the rewards table using the points total. (In my example I assume you have a table called points. SELECT first_name , last_name , total_points , reward FROM { SELECT first_name , last_name , SUM(points) as total_points FROM user u JOIN points p ON u.userID = p.userID GROUP BY u.userID } tot LEFT JOIN rewards r ON tot.total_points = r.valuePoints
×
×
  • 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.