CODEWITHSUNDEEP
phpmysqlsql
user1405062
user1405062

Reputation: 85

MySQL Only do the insert if rows are not there

Hello I have a simple SQL SELECT and INSERT which this site helped me out with but now I need to make it so that it only does the insert for the rows which are not there.

So if the row is there then skip to the next one. I have it so when a user reaches 100 battles it gives them a reward for it but of course if it kept on doing the insert they would have 100s of rewards for doing the same 100 battles. So need to some how check to see if the user has got that reward then if not give them it?

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM users where wins >= 100") 
or die(mysql_error());  


$reward = '100 battles won' ;
$rewardimage = 'chnage after' ;
echo "<table border='1'>";
echo "<tr> <th>username</th> <th>wins</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table



mysql_query("INSERT INTO User_Rewards 
(username, reward_name, reward_image) VALUES('".$row['username']."', '".$reward."', '".$rewardimage."' ) ") 
or die(mysql_error());  


}

How would i go about doing that? Because at the moment its just keeps doing the insert But need it so if the user has the reward then do not do the insert for that user. The reward is stored in side the $reward.

Upvotes: 0

Views: 169

Answers (2)

Pritom
Pritom

Reputation: 1333

while($row = mysql_fetch_array( $result )) {
    if(mysql_num_rows(mysql_query("select * from User_Resards where username='".$row['username']."' ")) == 0) {
        mysql_query("INSERT INTO User_Rewards (username, reward_name, reward_image) VALUES('".$row['username']."', '".$reward."', '".$rewardimage."' ) ") or die(mysql_error());    
    }
}

You can pls try this one.

Upvotes: 0

tereško
tereško

Reputation: 58444

What you are looking for is UNIQUE KEY in combination with INSERT ... ON DUPLICATE KEY UPDATE.

Also, please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process . Instead you should learn about prepared statements and use either PDO or MySQLi. If you cannot decide, this article will help to choose. If you care to learn, here is a quite good PDO-related tutorial.

Upvotes: 4

Related Questions