SELECT QUERY from table with no relationship

[JoseN]

hello all -

 

I need help figuring out an idea I want to do in my PHP web app using a MYSQL query. The picture attached explains my idea. Can you guys help me out?

 

Thank You in Advanced,

[Barand]

Why?

The principle behind relational databases is that attributes, like names, are stored in only one place. There is no good reason for duplicating them in another table. Or are you talking about output html tables - it isn't clear.

The values in your example data do not match the description of what you are attempting to do (unless you think 30 and 6663 are a match) making full understanding difficult.

[requinix]

How do you know how many points a user has?

[JoseN]

5 minutes ago, Barand said:

Why?

The principle behind relational databases is that attributes, like names, are stored in only one place. There is no good reason for duplicating them in another table. Or are you talking about output html tables - it isn't clear.

The values in your example data do not match the description of what you are attempting to do (unless you think 30 and 6663 are a match) making full understanding difficult.

Barand -

I am so sorry! My slow ass was not  paying attention when I posted the topic. I just fixed the image. Basically, I want to output that just user has earned a reward based on the total points they earned.I wanted to keep the rewards separate from the users since the rewards table will be updated often with different rewards. I am not sure If it is a little bit better explained. thank you for your help.

[JoseN]

10 minutes ago, requinix said:

How do you know how many points a user has?

requinix -

A user logins to the web. Then, login time, date , user name, and earned points are inserted in another table (not shown). The points are being inserted based on the days the user logged in. For example, if the user logs in on a Sunday, then he gets 5 points, if he logs in on a Monday, then he gets 15 days... so on. During the week is 15 points and weekends 5 points. Thanks for your help!

[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

 

[JoseN]

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!

[JoseN]

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!

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

[JoseN]

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?

[JoseN]

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>

 

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

 

[JoseN]

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

 

[Barand]

1 hour ago, JoseN said:

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

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?

[JoseN]

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?

[Barand]

52 minutes ago, JoseN said:

is it best practices to not store calculated values in a database?

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

[JoseN]

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. 

[JoseN]

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

 

 

[Barand]

15 hours ago, Barand said:

You have a INSERT ... SELECT query. If you run the SELECT portion on its own, do you only get a single record every time?

Have you investigated that?

[JoseN]

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

 

 

[Barand]

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

[JoseN]

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.

[Barand]

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.

[JoseN]

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 

 

 

[Barand]

You might want to look up "url rewriting".

I'd be more worried about broadcasting your user's secret answers