I am trying to make a INSERT by UNIQUE and I am getting stuck, What I want too know is how I would do is make it so Column A is ALWAYS UNIQUE unless Column C has two different values and then it will insert into the database a second row for Column A where Column C is different.
a|b|c
-----
1|2|3
2|4|5
1|6|7
So I am currently using this sql table
CREATE TABLE `Player` (
`id` int(11) NOT NULL auto_increment,
`playername` varchar(255) NOT NULL,
`SteamID` varchar(255) NOT NULL UNIQUE,
`position` varchar(255) NOT NULL,
`lastlogin` varchar(255) NOT NULL,
`approved` varchar(255) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
With this query
$sql1= "INSERT IGNORE INTO Player (playername, SteamID, position, lastlogin, approved) VALUES ('$name', '$id', '$position', '$lastlogin', '$approve')";
It inserts the way I want so there is only 1 of each SteamID but it also will not insert if the same SteamID has two values for position.
EDIT:
What I have is a xml file I load up and insert data into the database based on the data in the xml file, What I need is below.
$xml = simplexml_load_file("players.xml");
foreach($xml->children() as $player)
{
$id = $player->attributes()->id;
$profile = new SteamProfile($id);
$name = mysql_real_escape_string($profile->getUsername());
$lastlogin = $player->attributes()->lastlogin;
$position = $player->lpblock->attributes()->pos;
$sql1= "INSERT IGNORE INTO Player (playername, SteamID, position, lastlogin, approved) VALUES ('$name', '$id', '$position', '$lastlogin', '$approve')";
if (!mysql_query($sql1,$connection))
{
die('Insert Error: ' . mysql_error());
}
}
so the xml file gets read and then the data in the file gets inserted into the database. sometimes the xml file would contain
<player id="76561197961716203" lastlogin="8/22/2014 10:49:28 PM">
<acl id="76561197961543041"/>
<acl id="76561197988417990"/>
<lpblock pos="273,93,-102"/>
<lpblock pos="1322,62,-1711"/>
</player>
at which point I would need a second row created for the second <lpblock pos=
So its currently inserting
|60|SwordFish |76561197961716203|273,93,-102|8/22/2014 10:49:28 PM|Yes|2014-08-24 15:28:16|
and it should be inserting
|60|SwordFish |76561197961716203|273,93,-102|8/22/2014 10:49:28 PM|Yes|2014-08-24 15:28:16| |61|SwordFish |76561197961716203|1322,62,-1711|8/22/2014 10:49:28 PM|Yes|2014-08-24 15:28:16|
I'm not sure what you are trying to achieve, but you can set a UNIQUE restraint on a combination of columns, in your case SteamID and position:
CREATE TABLE `Player` (
`id` int(11) NOT NULL auto_increment,
`playername` varchar(255) NOT NULL,
`SteamID` varchar(255) NOT NULL,
`position` varchar(255) NOT NULL,
`lastlogin` varchar(255) NOT NULL,
`approved` varchar(255) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_combination_of_two_fields` (`SteamID`,`position`)
) ENGINE=InnoDB;
Thanks for you help Jeroen I did this and its working.
foreach($player->children() as $lpblock)
{
$position = $lpblock->attributes()->pos;
if(!empty($position))
{
$sql1= "INSERT IGNORE INTO Player (playername, SteamID, position, lastlogin, approved) VALUES ('$name', '$id', '$position', '$lastlogin', '$approve')";
if (!mysql_query($sql1,$connection))
{
die('Insert Error: ' . mysql_error());
}
}
}
However I would like all of the SteamID's inserted and not just the ones with a $position set. When I take this out and try it adds the correct rows but also adds another row with a blank $position as if there is a SteamID that's blank when there is none.
if(!empty($position)){
}