CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-arraymysqli
Andy Gee
Andy Gee

Reputation: 3345

MySQL UPDATE or INSERT depending on the existance of an ID

I'm tracking visitor movements on a number of similar websites and have assigned goals to track with various values for each goal. As these goals are created dynamically I also need to update them dynamically. The relevant part of the mysql structure is as follows and it depicts the same 4 goals on 2 sites. Note: The goals will always be the same for each site.

CREATE TABLE IF NOT EXISTS `goals` (
  `idsite` int(11) NOT NULL,
  `idgoal` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `allow_multiple` tinyint(4) NOT NULL,
  `revenue` float NOT NULL,
  PRIMARY KEY (`idsite`,`idgoal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `goals` (`idsite`, `idgoal`, `name`, `allow_multiple`, `revenue`) VALUES
(10, 1, 'Product Benefits Video',   0,  2),
(10, 2, 'Agent Benefits Video',     0,  4),
(10, 3, 'Social Network Video',     0,  6),
(10, 4, 'MMP Overview Video',       1,  8),
(13, 1, 'Product Benefits Video',   0,  2),
(13, 2, 'Agent Benefits Video',     0,  4),
(13, 3, 'Social Network Video',     0,  6),
(13, 4, 'MMP Overview Video',       1,  8);

The above insert command is created from an array as folows along with the site_id.

$config['trackings'][1] = array('name'=>'Product Benefits Video',   'value'=>'2',   'multi'=>'0');
$config['trackings'][2] = array('name'=>'Agent Benefits Video', 'value'=>'3',   'multi'=>'0');
$config['trackings'][3] = array('name'=>'Social Network Video', 'value'=>'4',   'multi'=>'0');
$config['trackings'][4] = array('name'=>'MMP Overview Video',       'value'=>'5',   'multi'=>'1');

I will never need to delete a goal but I would like to know how to change the values and if the goal does not exist add it to all of the sites.

Example if I supply a new array as follows:

$config['trackings'][1] = array('name'=>'Product Benefits Video',   'value'=>'6',   'multi'=>'0');
$config['trackings'][2] = array('name'=>'Agent Benefits Video', 'value'=>'7',   'multi'=>'0');
$config['trackings'][3] = array('name'=>'Social Network Video', 'value'=>'8',   'multi'=>'1');
$config['trackings'][4] = array('name'=>'MMP Overview Video',       'value'=>'9',   'multi'=>'1');
$config['trackings'][5] = array('name'=>'MMP Details Video',        'value'=>'10',  'multi'=>'1');

I would like to add goal 5 and update the values and allow_multiple of the existing goals for each site.

I've thought about truncating the table and rewriting all the values from scratch but there are thousands of sites. I've also played with replace, insert update and ended up pulling out my hair.

Is there an easier way to do this then emptying the lot?

Edit: The End result would look like this

(10, 1, 'Product Benefits Video',   0,  6),
(10, 2, 'Agent Benefits Video',     0,  7),
(10, 3, 'Social Network Video',     1,  8),
(10, 4, 'MMP Overview Video',       1,  9),
(10, 5, 'MMP Details Video',        1,  10),
(13, 1, 'Product Benefits Video',   0,  6),
(13, 2, 'Agent Benefits Video',     0,  7),
(13, 3, 'Social Network Video',     1,  8),
(13, 4, 'MMP Overview Video',       1,  9);
(13, 5, 'MMP Details Video',        1,  10);

Edit: This will do all the updating quite easily

foreach($config['trackings'] as $id => $track)
{
    $s = "UPDATE `goals` SET `revenue` = ".$track['value'].", `allow_multiple` = ".$track['multi']." WHERE `idgoal` = '".$id."'";
}

But how to add the missing goal (5) at the same time?

Upvotes: 1

Views: 119

Answers (3)

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

Do you have a table of all sites? If yes, you can use REPLACE like this:

REPLACE INTO goals (idsite, idgoal, name, allow_multiple, revenue)
SELECT idsite, 5, 'MMP Details Video', 1, 10 FROM sites;

Here sites is a table of all sites that have column idsite with the same meaning as column idsite in table goals.

This statement inserts new record into goals table for each site defined in sites table with values idside, 5, 'MMP Details Video', 1, 10, where idsite is a value taken from idsite column of sites table. If record with the same idsite and idgoal already exists, it is replaced with new one.

Upvotes: 1

Andy Gee
Andy Gee

Reputation: 3345

OK, I've figured it out, I broke it into 2 parts.

$s = "SELECT MAX(`idgoal`) AS slice FROM goals";    //4
$slice = 4;
$track_update = array_slice($config['trackings'], 0, $slice, true);

foreach($track_update as $id => $track){ //4 queries
    $s = "UPDATE `goals` SET `revenue` = ".$track['value'].",
            `allow_multiple` = ".$track['multi']." WHERE `idgoal` = '".$id."'";
}

//now to add new values
$s = "SELECT DISTINCT(`idsite`) AS mid FROM goals";     //array(10,13)
$result = array(10,13);

$track_add = array_slice($config['trackings'], $slice, count($config['trackings']), true);

foreach($track_add as $id => $track){  //1 query for each site
    foreach($result as $idsite){
        $sx[] = "(
                        ".$idsite.", ".$id.",
                        '".$track['name']."',
                        ".$track['multi'].",
                        ".$track['value']."
                    )";
    }

    $s = "INSERT INTO `goals`
            (`idsite`, `idgoal`, `name`, `allow_multiple`, `revenue`) 
            VALUES ".implode(',',$sx);
}

5 Queries in total:

UPDATE `goals` SET `revenue` = 6, `allow_multiple` = 0 WHERE `idgoal` = '1'
UPDATE `goals` SET `revenue` = 7, `allow_multiple` = 0 WHERE `idgoal` = '2'
UPDATE `goals` SET `revenue` = 8, `allow_multiple` = 1 WHERE `idgoal` = '3'
UPDATE `goals` SET `revenue` = 9, `allow_multiple` = 1 WHERE `idgoal` = '4'
INSERT INTO `goals` (`idsite`, `idgoal`, `name`, `allow_multiple`, `revenue`) 
VALUES 
(10, 5, 'MMP Details Video', 1, 10),
(13, 5, 'MMP Details Video', 1, 10);

Upvotes: 0

Husman
Husman

Reputation: 6909

You have a few options:

use INSERT IGNORE INTO table

or INSERT … ON DUPLICATE KEY UPDATE

Check out the mysql docs for more info on either command

Upvotes: 1

Related Questions