Simplify PHP to MySQL

问题: 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 s...

问题:

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?


回答1:

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.

  • 发表于 2018-12-29 15:25
  • 阅读 ( 221 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除