Jump to content

how to reset a counter in a for loop


piano0011

Recommended Posts

Hey guys!

 

I am trying to create a counter for giving the user a sticker, each time they input something into the database. At the moment, I can display the stickers on the screen but it just keeps going and won't stop. I guess that Is because of my condition as it is set to less than the row. Do I need to add a second for loop there? Here is my current code that is working .

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>


<?php
session_start();
include_once 'includes/dbh.php';
include_once 'header.php';

$sql = "SELECT * FROM practice_diary WHERE user_uid = ?;";

    $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);

                    $result = mysqli_stmt_get_result($stmt);
                    $resultCheck = mysqli_num_rows($result);

                             

   $increment = 5;
   $points = 0;
   $reset = 5;
   
        

         for ($i = 1; $i<= $resultCheck; $i++) {     
            
                echo '<div class="star1">
             <i class="fas fa-star fa-10x"></i></div>';
            
            
            
               

             if ($i == $increment) {
                $increment += 5;
                $points += 100;
                 
                 $sql = "UPDATE rewards
                        Set reward_points = ?
                        WHERE user_uid = ?
                        ";

                 $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "is", $points, $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);
                             }
                         
                          }
                         }

                         $sql = "SELECT * FROM rewards WHERE user_uid = ?;";

                         $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo 'SQL error';
                        } else {
                            mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                            mysqli_stmt_execute($stmt);
                                         
                            $result = mysqli_stmt_get_result($stmt);
                            

                            while ($row = mysqli_fetch_assoc($result)) {

                                if ($row['reward_points'] == 500) {
                                    $reward = array('RockStar Rookie');
                                    $reward_earned = implode(',', $reward);

                                    $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    } 
                                }
                                else {
                                        if ($row['reward_points']== 0) {
                                            $reward = array('RockStar Todler');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 5000) {
                                            $reward = array('RockStar Rookie');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 50000) {
                                            $reward = array('RockStar Padawan');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 500000) {
                                            $reward = array('RockStar Jedi');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } 

                                        }
                                    }
                                }
                            }
                            }           
                            }
                            } 
                          

       

?>

 

However, I tried to add the following lines into my code but it just keeps looping endlessly..

for ($i = 1; $i<= $resultCheck; $i++) {     
            
                echo '<div class="star1">
             <i class="fas fa-star fa-10x"></i></div


if ($i == 5) {

$i =0;

}

 

Link to comment
Share on other sites

Of course.. I am so silly.... The $x has to be more than $resultCheck for it to be false.... I have been advised to just give a random sticker instead of doing it this way and I have got the following code, which does work only if I used if ($x == $increment) instead of if ($resultCheck ==$increment)... I thought that both should work because from my understanding, $resultCheck is the number of rows that the user has? This is the working code: I forgot as well but do we need to add backticks here to format the code? I really appreciate that you guys have replied to my questions... cheers!

 

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>


<?php
session_start();
include_once 'includes/dbh.php';
include_once 'header.php';

$sql = "SELECT * FROM practice_diary WHERE user_uid = ?;";

    $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);

                    $result = mysqli_stmt_get_result($stmt);
                    $resultCheck = mysqli_num_rows($result);

                             

   
   $reset = 5;
   
   $x = 0;

 $stickers = array('<div class="star1"><i class="fas fa-star fa-5x"></div></i>', '<i class="fas fa-gift fa-5x"></i>', '<i class="fas fa-crow fa-5x"></i>','<i class="fas fa-dove fa-5x"></i>','<i class="fas fa-feather fa-5x"></i>','<i class="fas fa-feather-alt fa-5x"></i>','<i class="fas fa-fish fa-5x"></i>');
       $random_key = array_rand($stickers); 

        if ($resultCheck >0) {
           
           
             echo $stickers[$random_key];
           
         
             $increment = 5;
             $points = 0;
           
            
         
            for ($x = 1; $x <= $resultCheck; $x++) {
            
            
            
            if ($x == $increment) {
                
               
                $increment += 5;
                $points += 100;
                 
                 $sql = "UPDATE rewards
                        Set reward_points = ?
                        WHERE user_uid = ?
                        ";

                 $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "is", $points, $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);
                             }
                         
                          }


                         }
                      
                       
                     

                         $sql = "SELECT * FROM rewards WHERE user_uid = ?;";

                         $stmt = mysqli_stmt_init($conn);
                        if (!mysqli_stmt_prepare($stmt, $sql)) {
                            echo 'SQL error';
                        } else {
                            mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
                            mysqli_stmt_execute($stmt);
                                         
                            $result = mysqli_stmt_get_result($stmt);
                            

                            while ($row = mysqli_fetch_assoc($result)) {

                                if ($row['reward_points'] == 500) {
                                    $reward = array('RockStar Rookie');
                                    $reward_earned = implode(',', $reward);

                                    $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    } 
                                }
                                else {
                                        if ($row['reward_points']== 0) {
                                            $reward = array('RockStar Todler');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 5000) {
                                            $reward = array('RockStar Rookie');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 50000) {
                                            $reward = array('RockStar Padawan');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } else {
                                            if ($row['reward_points']== 500000) {
                                            $reward = array('RockStar Jedi');
                                            $reward_earned = implode(',', $reward);

                                            $sql = "UPDATE rewards
                                            SET reward_earned = ?
                                            WHERE user_uid = ?
                                           ";

                                     $stmt = mysqli_stmt_init($conn);
                                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                                        echo 'SQL error';
                                    } else {
                                        mysqli_stmt_bind_param($stmt, "ss", $reward_earned, $_SESSION['u_uid']);
                                        mysqli_stmt_execute($stmt);
                                    }
                                        } 

                                        }
                                    }
                                }
                            }
                            }  

                            }     
                           } 
}

                        

if I changed the following lines:

if ($x == $increment) {
                
               
                $increment += 5;
                $points += 100;
                 
                 $sql = "UPDATE rewards
                        Set reward_points = ?
                        WHERE user_uid = ?
                        ";

                 $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "is", $points, $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);
                             }
                         
                          }


                         }

 

to this, then it won't work:

if ($resultCheck == $increment) {
                
               
                $increment += 5;
                $points += 100;
                 
                 $sql = "UPDATE rewards
                        Set reward_points = ?
                        WHERE user_uid = ?
                        ";

                 $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "is", $points, $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);
                             }
                         
                          }


                         }

 

It will just give the user a 100 points but it won't add up anymore points if I don't use $x...

Link to comment
Share on other sites

1 minute ago, Barand said:

And still you aren't using code tags!

So, I have to use the quote tags around my codes? I am just hoping not to be spoon fed too much but to be guided because I like to learn things that way. I am just hoping that you guys won't change the code too much but back to my curiosity, I was wondering why that $resultCheck did not work in terms of updating my sql query? 

 

I am trying to make a practice diary, where the user can submit records of their practice to be inserted into the database and each time that they submit it, there will be a random sticker appearing on the page.... thanks!

Link to comment
Share on other sites

1 hour ago, Barand said:

And still you aren't using code tags!

Quote
Quote

 

 

I have the following screen so far and I am happy with the layout.... but as mentioned above, I am not sure why $resultCheck does not work, it doesn't increment the $increment value but it does when I am using $x...

image.png

Link to comment
Share on other sites

5 hours ago, piano0011 said:

I am just hoping that you guys won't change the code too much

Pity, because about 80% of that code is repetative and redundant

For example, in this code it looks as though you loop for number of records and every f records you update the table with an extra 100 points. If they have 100 records you update the same record 20 times.

        for ($x = 1; $x <= $resultCheck; $x++) {
            if ($x == $increment) {
                $increment += 5;
                $points += 100;
             
                $sql = "UPDATE rewards
                    Set reward_points = ?
                    WHERE user_uid = ?
                    ";

                $stmt = mysqli_stmt_init($conn);
                if (!mysqli_stmt_prepare($stmt, $sql)) {
                    echo 'SQL error';
                } else {
                    mysqli_stmt_bind_param($stmt, "is", $points, $_SESSION['u_uid']);
                    mysqli_stmt_execute($stmt);
                }
                     
            }
        }

Why not calculate how many points they should get and update once

$totalPoints = intdiv($resultcheck, 5) * 100;

Also, if you are updating in a loop you should prepare and bind the params before the loop. Inside the loop you just execute with new values.

The next section is even worse, where you select the record you have just updated so that you can update it again. You only select a single record yet you process it with a loop???. Again you should determine what the reward_earned value should be then update, not keep repeating the code over and over. Those two updates should be one update query only.

Link to comment
Share on other sites

6 hours ago, piano0011 said:

So, I have to use the quote tags around my codes? I am just hoping not to be spoon fed too much but to be guided because I like to learn things that way. I am just hoping that you guys won't change the code too much but back to my curiosity, I was wondering why that $resultCheck did not work in terms of updating my sql query?

You need to use CODE tags - not QUOTE tags. E.g. [ CODE ] $foo = 'bar'; [ /CODE ] (or use the <> button in the editor) will be presented like this

$foo = 'bar';

As to the issue with $resultCheck. That variable is defined once at the top of the script - it never changes. So the condition if ($resultCheck == $increment)  {  would only pass if the number of initial results is exactly the same as the $increment value. I have a suspicion that you think the value of $resultCheck will dynamically change in the loop as records are consumed. 

As Barand has stated, this code has a lot of flaws. Before writing any code, I suggest getting a piece of paper and create a rough outline of the logic flow instead of trying to determine the logic as you write the code. As you start writing out the code you might find flaws in the originally planned logic and that's OK. but, by having a rough plan before you start you will be better able to make changes as you go instead of coding yourself into a corner.

Link to comment
Share on other sites

If I am right about the rules for assigning points,  it would look like this

<?php
/************************************************************************************
**  DATABASE CONNECTION STUFF - normally in an included file
*/
            define("HOST",'localhost');
            define("USERNAME",'????');
            define("PASSWORD",'????');

            function pdoConnect($database) 
            {
                $dsn = "mysql:dbname=$database; host=".HOST."; charset=utf8";

                $db = new pdo($dsn, USERNAME, PASSWORD, 
                    [
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_EMULATE_PREPARES => false,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                    ]);
                return $db;
            }
/***********************************************************************************/
session_start();
$db = pdoConnect('test');

$stickers = [       '<div class="star1"><i class="fas fa-star fa-5x"></div></i>',        // only this one has <div> and tag nesting is wrong!
                    '<i class="fas fa-gift fa-5x"></i>', 
                    '<i class="fas fa-crow fa-5x"></i>',
                    '<i class="fas fa-dove fa-5x"></i>',
                    '<i class="fas fa-feather fa-5x"></i>',
                    '<i class="fas fa-feather-alt fa-5x"></i>',
                    '<i class="fas fa-fish fa-5x"></i>'
            ];
$random_key = array_rand($stickers); 
$sticker = $stickers[$random_key];   // not sure what this achieves - only tags and no content would be output.

$rewards = [    0  => 'RockStar Toddler',
              500  => 'RockStar Rookie',
             5000  => 'Rockstar Expert',
            50000  => 'RockStar Padawan',
           500000  => 'RockStar Jedi' 
           ];                       

$stmt = $db->prepare("SELECT COUNT(*) as total FROM practice_diary WHERE user_uid = ?");
$stmt->execute( [ $_SESSION['user_uid'] ] );
$resultCheck = $stmt->fetchColumn();
//
//  determine the points value
//
$points = intdiv($resultCheck,5) * 100;

//
// determine the reward level
//
$rewardLevel = '';
foreach ($rewards as $val => $level) {
    if ($points >= $val) {
        $rewardLevel = $level;
    }
}

$stmt = $db->prepare("UPDATE rewards
                      SET
                          reward_points = ?,
                          reward_earned = ?
                      WHERE user_uid = ?");
$stmt->execute( [ $points,
                  $rewardLevel,
                  $_SESSION['user_uid']
                  ]);
?>
<html>
<head>
<title>Sample</title>
</head>
<body>
     Currently you have a ranking of <b><?=$rewardLevel?></b>
     Currently you have earned <b><?=$points?></b> points
     <!-- OUTPUT YOUR STICKERS HERE -->
</body>
</html>

Note - I used PDO instead of mysqli

  • it's better
  • it's easier
  • It stops you just copy/pasting ?
Link to comment
Share on other sites

Thanks but also maybe you misunderstood my looping process.. The user should be able to keep getting points whenever they update the information into the database... It should keep incrementing and with that points, they can eventually use it to buy promotion things... That is a good idea, to use array but I am not used to pdo at the moment, just mysqli_stmt... but for my if ($x == $increment), that should be correct too right if I am not using an array but I think an array is a good idea... simpler... my way is just the harder way....

Link to comment
Share on other sites

FYI

The rewards table is actually redundant as it only contains totals that are derived from the record count in the diary table.

You whole script could be achieved with a single query

SELECT user_uid
     , diary_records
     , points
     , CASE
            WHEN points BETWEEN 0 AND 499 THEN 'Toddler'
            WHEN points BETWEEN 500 AND 4999 THEN 'Rookie'
            WHEN points BETWEEN 5000 AND 49999 THEN 'Expert'
            WHEN points BETWEEN 50000 AND 499999 THEN 'Padawan'
            ELSE 'Jedi'
        END as rank
FROM (
        SELECT user_uid
             , COUNT(*) as diary_records
             , FLOOR(COUNT(*)/5) * 100 as points
        FROM practice_diary
        GROUP BY user_uid
        ) as pd
;

+----------+---------------+--------+---------+
| user_uid | diary_records | points | rank    |
+----------+---------------+--------+---------+
| P101     |         27776 | 555500 | Jedi    |
| P102     |             1 |      0 | Toddler |
| P103     |         13750 | 275000 | Padawan |
| P104     |          2385 |  47700 | Expert  |
+----------+---------------+--------+---------+

 

Link to comment
Share on other sites

Appreciate that! Will give that a try! You have written rank in the last column, do you mean case? I guess I could just name of anything, so long that they match? You also did write END as rank, are you using a select statement? almost the <select> <break>? sorry for the confusion...

Link to comment
Share on other sites

"rank" is the column alias for the CASE...END expression. Without it, the column name in the results would be thw whole case expression.

It is far easier to code

echo $row['rank'];

than

echo $row['CASE WHEN points BETWEEN 0 AND 499 THEN 'Toddler' WHEN points BETWEEN 500 AND 4999 THEN 'Rookie' WHEN points BETWEEN 5000 AND 49999 THEN 'Expert' WHEN points BETWEEN 50000 AND 499999 THEN 'Padawan' ELSE 'Jedi' END'];

I am using two SELECT statements,

The inner one is a table subquery which behaves as a temporary table called "pd" containing the uid and the counts for each user. (See tutorial link in my sig)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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