PHP, mysql, primary key remains unchanged for two records

Question

I've got following code, as seen, I have two INSERT statements, which insert two records - one with current date and the second one -1 day. The problem seems to be with a mysql_insert_id. I'm getting following error:

Duplicate entry '28' for key 'PRIMARY'

Looks like the ID remains the same for both statements and also the first "INSERT" is added without any trouble, the problem is at the line where trying to add the second record into the same table. Here's the script:

<?
include("session.php");
include("database_common.php");
if (isset($campaignName) & isset($campaignRedirect))    {
    $dataTable = 'qrData_'.$_SESSION['displayName'];
    $statTable = 'qrStat_'.$_SESSION['displayName'];
    $query = mysql_query("INSERT INTO ".$dataTable." VALUES(".mysql_insert_id($connection).", '".$campaignRedirect."', '".$campaignName."');", $connection);
    $statBlank1 = mysql_query("INSERT INTO ".$statTable." VALUES(".mysql_insert_id($connection).", CURDATE() - INTERVAL 1 DAY, 0, '".$campaignName."');", $connection);
    $statBlank2 = mysql_query("INSERT INTO ".$statTable." VALUES(".mysql_insert_id($connection).", CURDATE(), 0, '".$campaignName."');", $connection);
    if ($statBlank1) echo "stat 1 ok";
    else echo mysql_error($connection);
    if ($statBlank2) echo "stat 1 ok";
    else echo mysql_error($connection);
    if ($query) die("<center>Kampaň úspešne vytvorená<br><br><button      onclick='parent.jQuery.fancybox.close();' name='submit' class='btn btn-primary'>Zatvoriť</button></center>");
    else die("<center>Vyskytla sa chyba. Prosím, zopakujte Vašu požiadavku.</center>"); 
}
?>

Here's a table structure:

`id` int(11) NOT NULL AUTO_INCREMENT,  
`date` date DEFAULT NULL,  `usageCount` int(11) DEFAULT NULL,  
`campaign` varchar(45) DEFAULT NULL,  PRIMARY KEY (`id`)

any suggestions?

You're mixing `mysql_*` and `mysqli_*` functions. That won't work. — Jay Blanchard, May 06 '15 at 12:03
Firstly, `mysqli_report` is for `mysqli_` and not for `mysql_` functions. — Funk Forty Niner, May 06 '15 at 12:03
whats the primary key of `qrStat_#` - does it include `campaignName` ? — amdixon, May 06 '15 at 12:05
remove parameter from mysql_insert_id..i.e. only mysql_insert_id is to be written.. — lakshman, May 06 '15 at 12:05
@JayBlanchard - Yep, you're right. my mistake, but I've already removed it. — Lukas Syn Durjan, May 06 '15 at 12:12
Already removed *what*? All we can see is the code with the mixed API's. Because of that we cannot provide an answer. — Jay Blanchard, May 06 '15 at 12:13
what about `INSERT ... ON DUPLICATE KEY UPDATE` https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html or `INSERT ... SELECT` https://dev.mysql.com/doc/refman/5.0/en/insert-select.html — Funk Forty Niner, May 06 '15 at 12:16
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 06 '15 at 12:19
Can you post the schema of your tables, I'm fairly certain your `$statBlank1` & `$statBlank2` are conflicting with each other. — Jonathan, May 06 '15 at 13:19
`id` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `usageCount` int(11) DEFAULT NULL, `campaign` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) — Lukas Syn Durjan, May 06 '15 at 15:28

Lukas Syn Durjan · Accepted Answer · 2015-05-07T08:34:57.603

Okay guys, I made it a bit amish, but it works. I'm getting last id directly from mysql and increase it by 1 in a next record like this:

$statBlank1 = mysql_query("INSERT INTO ".$statTable." VALUES(ID, CURDATE() - INTERVAL 1 DAY, 0, '".$campaignName."');");
$statBlank2 = mysql_query("INSERT INTO ".$statTable." VALUES(LAST_INSERT_ID() + 1, CURDATE(), 0, '".$campaignName."');", $connection);

ID in the first query is undefined, however table structure always changes NULL value to NOT NULL, which will be, in this case the next incremented value. Hope somebody will find this helpful.

lakshman · Answer 2 · 2015-05-06T12:22:02.093

-1

remove parameter from mysqli_insert_id..i.e. only mysqli_insert_id is to be written.. & echo the query to be inserted..you will get the value what is being inserted..use mysqli_free_result after each insert query

edited May 06 '15 at 12:22

answered May 06 '15 at 12:07

lakshman

656
4
18

Look at the comments under the question - this is not the problem. – Jay Blanchard May 06 '15 at 12:09
yes but the id remains unchanged due to above activity..if mysql_insert_id only will be used new value will be generated for each inserted query. – lakshman May 06 '15 at 12:11
There are more problems than that. The OP is mixing API's which will not work. – Jay Blanchard May 06 '15 at 12:12
Tried, result remain the same. First record is added second one gets duplicate id. – Lukas Syn Durjan May 06 '15 at 12:14
*"okay..use mysqli_free_result after each insert query"* - OP is using `mysql_`. – Funk Forty Niner May 06 '15 at 12:17
1

He should, but he is not. – Jay Blanchard May 06 '15 at 12:18

PHP, mysql, primary key remains unchanged for two records

2 Answers2