Simplify PHP to MySQL

Question

I'm currently writing sort of a game and I need to simplify the PHP part over to MySQL.

Right now to update a column I do this (in PHP):

$res = $db->query("
SELECT succeeded
     , failed
     , prison 
  FROM users_crime_masteries 
 WHERE uid = $uid 
   AND cid = $crimeid
");
if($status == 'succeeded') {
        if(!$res->num_rows) {
            $db->query("INSERT INTO users_crime_masteries (uid, cid, succeeded) VALUES($uid, $crimeid, 1)");
            return 1;
        }
        else {
            $row = $res->fetch_object();
            $db->query("UPDATE users_crime_masteries SET succeeded = succeeded + 1 WHERE uid = $uid AND cid = $crimeid");
            return $row->succeeded += 1;
        }
    }

^ This is half of the function, but you get the idea.

What i'm basically trying to achieve within mySQL only, is that if there are no rows, insert a new row with cid and uid - if a row exists, update succeeded with + 1.

I've been browsing through stackoverflow and what i basically understand is that I have to use a INSERT ... ON DUPLICATE KEY UPDATE query.

But i've came across this comment: MySql Table Insert if not exist otherwise update

I do have a column named id, which is auto increment, and primary key. My question is, will this cause any issues?

My current MySQL version is: 5.7.24

Also, for this ON DUPLICATE KEY UPDATE do I have to make the uid and cid columns a key?

More Information:

Changed the queries to:

switch($status) {
        case 'succeeded' :
            $db->query("INSERT INTO users_crime_masteries (uid, cid, succeeded) VALUES($userid, $crimeid, 1) ON DUPLICATE KEY UPDATE succeeded = succeeded + 1");
        break;
        case 'failed' :
            $db->query("INSERT INTO users_crime_masteries (uid, cid, failed) VALUES($userid, $crimeid, 1) ON DUPLICATE KEY UPDATE failed = failed + 1");
        break;
        case 'prison' :
            $db->query("INSERT INTO users_crime_masteries (uid, cid, failed, prison) VALUES($userid, $crimeid, 1, 1) ON DUPLICATE KEY update failed = failed + 1, prison = prison + 1");
        break;
    }

Ran the following MySQL Query:

ALTER TABLE `users_crime_masteries`


ADD UNIQUE KEY `uid` (`uid`),
  ADD UNIQUE KEY `cid` (`cid`),

Did a few crimes with a different cid, checked through phpmyadmin but there's only one row, updating the succeeded field.

Have I missed something?

No issue @Love2Code. Just update it using the id or primary key. — AT-2017, Dec 23 '18 at 13:27
If you can include ADOdb, then you could use [replace()](http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:replace) method — Agnius Vasiliauskas, Dec 23 '18 at 14:03

score 2 · Accepted Answer · answered Dec 23 '18 at 13:24

2

If you add a unique key (uid, cid) to your table then ON DUPLICATE KEY UPDATE should work. Your id column will not be affected or cause problems.

You should furthermore take care that your code is not prone to sql injections, it looks like it could be.

answered Dec 23 '18 at 13:24

digijay

1,329
4
15
25

almost works! Can you check the edit i posted please? – Love2Code Dec 23 '18 at 13:50
1

I don't know your model exactly but I think it would rather be `ALTER TABLE users_crime_masteries ADD UNIQUE KEY crime_key (uid, cid);`. crime_key is just the name of the key, you're free to choose another name for it. – digijay Dec 23 '18 at 14:31

Simplify PHP to MySQL

1 Answers1