[Multiple Criteria HTML Table from MYSQL]

1 hour ago, Barand said:

Alternatively you can do it in the query by defining the condition for each column and setting it 0 or 1 then SUMMING the columns grouped by class

I have only done the first five columns but you should get the idea

SELECT classroomname as Classroom
     , COUNT(*) as Enrolled
     , SUM(cconly) as `CC Only`
     , SUM(ehspi) as `EHS/PI`
     , SUM(ccpi) as `CC/PI`
     , SUM(ehsccpi) as `EHS/CC/PI`
FROM classrooms
        JOIN
     (
		SELECT classroomID
             , CASE WHEN childcaretype IS NOT NULL
                         AND hstype IS NULL
                         AND cpsstatus IS NULL
                    THEN 1
                    ELSE 0
                END as cconly
              , CASE WHEN hstype = 'EHS'
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1 
                     ELSE 0
                 END as ehspi
              , CASE WHEN childcaretype IS NOT NULL
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1
                     ELSE 0
                 END as ccpi
               , CASE WHEN childcaretype IS NOT NULL
                           AND hstype = 'EHS'
                           AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
					  THEN 1
                      ELSE 0
                  END as ehsccpi
		 FROM children	
     ) as childs USING (classroomid)
GROUP BY classroomname;

+-----------+----------+---------+--------+-------+-----------+
| classroom | Enrolled | CC Only | EHS/PI | CC/PI | EHS/CC/PI |
+-----------+----------+---------+--------+-------+-----------+
| Room 1    |       13 |       5 |      1 |     3 |         1 |
| Room 2    |       12 |       3 |      1 |     3 |         1 |
| Room 3    |       16 |       2 |      2 |     5 |         2 |
| Room 4    |       18 |       5 |      0 |     4 |         0 |
| Room 5    |       10 |       4 |      0 |     0 |         0 |
| Room 6    |       12 |       2 |      1 |     2 |         1 |
| Room 7    |       12 |       4 |      1 |     2 |         1 |
+-----------+----------+---------+--------+-------+-----------+

It would better if the cpsstatus were two columns

1. PI or PFA
2. AP or PEN

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. 

[Multiple Criteria HTML Table from MYSQL]

1 hour ago, requinix said:

If you need counts according to the classroom and some other columns then your query should group by the classroom and the other columns.

You'll have too many rows, of course. Can't really put it into the table like that.

So do some processing on the results before you try to display them. Use the classroom to build a "row", then look at the other columns to decide what should show up in each of the "columns".

$classrooms = [];
foreach (/* rows from the query */ as $row) {
	list(
		"ClassroomName" => $classroom,
		"childcaretype" => $cc,
		"hstype" => $hs,
		"cpsstatus" => $cps,
		"TotalChildren" => $total
	) = $row;

	if (!isset($classrooms[$classroom])) {
		$classrooms[$classroom] = [
			"KIDS ENROLLED" => 0,
			"CC Only" => 0,
			"EHS/PI" => 0,
			"CC/PI" => 0,
			...
		]
	}

	$classrooms[$classroom]["KIDS ENROLLED"] += $total;

	/* update other columns... */
}

ksort($classrooms); // sort by name

That's the basic idea: total up the values as you go through all the rows. Then your table displays what's in $classrooms.

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

[Multiple Criteria HTML Table from MYSQL]

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");

 

 

[SELECT QUERY from table with no relationship]

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.

[SELECT QUERY from table with no relationship]

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?

[SELECT QUERY from table with no relationship]

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

 

[SELECT QUERY from table with no relationship]

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

 

[SELECT QUERY from table with no relationship]

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.

 

[SELECT QUERY from table with no relationship]

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.

[SELECT QUERY from table with no relationship]

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>

 

 

 

[SELECT QUERY from table with no relationship]

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!

[SELECT QUERY from table with no relationship]

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.

 

 

[SELECT QUERY from table with no relationship]

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,

[SELECT QUERY from table with no relationship]

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?

[SELECT QUERY from table with no relationship]

2 hours ago, Barand said:

There are a couple of methods you could use

1. the data_id and button value that I showed you earlier, coupled with an ajax request to update the points
2. have a separate form for each row, put the userID and points values into hidden fields, and submit with the redeem button.

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 

 

 

[SELECT QUERY from table with no relationship]

2 minutes ago, Barand said:

Maybe it's time to go to plan B and use the data_id attribute instead of your INSERT ... SELECT?

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.

[SELECT QUERY from table with no relationship]

4 hours ago, Barand said:

Have you investigated that?

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

 

 

[SELECT QUERY from table with no relationship]

11 hours ago, Barand said:

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?

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";

 

 

[SELECT QUERY from table with no relationship]

7 minutes ago, Barand said:

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

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. 

[SELECT QUERY from table with no relationship]

1 hour ago, Barand said:

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?

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?

[SELECT QUERY from table with no relationship]

6 hours ago, Barand said:

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

 

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";

 

[SELECT QUERY from table with no relationship]

3 hours ago, Barand said:

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.

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>

 

[SELECT QUERY from table with no relationship]

2 hours ago, Barand said:

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.

writing a record using INSERT INTO SELECT query right?

[SELECT QUERY from table with no relationship]

On 4/12/2019 at 5:11 PM, Barand said:

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

 

 

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!

[SELECT QUERY from table with no relationship]

6 hours ago, Barand said:

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

 

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!