Any particular reason why the mysql_insert_id() command does not insert the latest id in the field of another table?
I’m getting the error: " Error adding new magazine entry: Duplicate entry ‘0’ for key ‘id_cover_tiers’ "
I had tested inserting info using the CMS, then in the DB deleted those entries. Is this causing the problem?
Thanks for any insight you can give!
Steve H
...
$sql_tiers = "INSERT INTO cover_tiers SET
tier_level = '$tier_level',
tier_item_name = '$tier_item_name',
tier_item_brand = '$tier_item_brand'
";
$cover_tiers_id = mysql_insert_id(); /* <<<<< get the recent auto-incremented ID (p133) */
if (isset($_POST['mag_name']))
$mag_name = $_POST['mag_name'];
$mag_name = mysql_real_escape_string($mag_name);
if (isset($_POST['mag_month']))
$mag_month = $_POST['mag_month'];
$mag_month = mysql_real_escape_string($mag_month);
if (isset($_POST['mag_year']))
$mag_year = $_POST['mag_year'];
$mag_year = mysql_real_escape_string($mag_year);
$sql_mag = "INSERT INTO mag_issue SET
mag_name = '$mag_name',
mag_month = '$mag_month',
mag_year = '$mag_year',
id_cover_tiers = '$cover_tiers_id'
";
mysql> describe cover_tiers;
±----------------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±-----------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | MUL | NULL | auto_increment |
| tier_level | tinyint(4) | NO | | 1 | |
| tier_item_name | tinytext | NO | | NULL | |
| tier_item_brand | tinytext | NO | | NULL | |
±----------------±-----------------±-----±----±--------±---------------+
4 rows in set (0.02 sec)
mysql> describe mag_issue;
±---------------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±---------------±-----------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | MUL | NULL | auto_increment |
| id_cover_tiers | int(10) unsigned | YES | UNI | NULL | |
| id_data_adv | int(10) unsigned | YES | UNI | NULL | |
| id_data_ed | int(10) unsigned | YES | UNI | NULL | |
| mag_name | varchar(15) | NO | | NULL | |
| mag_month | char(3) | NO | | NULL | |
| mag_year | year(4) | NO | | NULL | |
±---------------±-----------------±-----±----±--------±---------------+
7 rows in set (0.02 sec)
mysql>
What I had to do to fix this was to fill in the id_cover_tiers, id_data_adv, and id_data_ed fields with 9999999. Is there another way?
Thanks,
steve
I removed the Unique setting for the second table and now it seems to be working.
No, not working. $cover_tiers_id = mysql_insert_id(); is not inserting the id’s into the fields, but inserting 0 in each field.
Doesn’t the column need to be a primary key for that to work?
I changed each table’s “id” to Primary and it still inserts 0. In mag_issue I changed all the id_xxxx fields to Primary and get an error that 0 already exists. So it is still trying to insert 0.
Thanks,
Steve
You are actually executing $sql_tiers with mysql_query aren’t you?
I have this code later on. Is this what you mean?
<?php if (mysql_query($sql_tiers)) {
echo '<p>New tiers entry added</p>';
}
else {
echo '<p>Error adding new tiers entry: ' . mysql_error() . '</p>';
}?>
You need to execute the INSERT query, then request the last insert id before INSERT’ing another record.
# id, username, age
$res = mysql_query("INSERT INTO table (username, age)VALUES('AnthonySterling', 30);");
$id = mysql_insert_id();
I added an if statement:
$cover_tiers_id = mysql_insert_id(); // get the recent auto-incremented ID (p133)
if (!$cover_tiers_id)
{ exit('<p>Grabbing cover_tiers_id failed.</p>'); }
When executed, the fail message came up. So the 0 being entered is happening despite the code, not because of the code. The id is not being grabbed in the first place.
if (isset($_POST['tier_level']))
$tier_level = $_POST['tier_level'];
$tier_level = mysql_real_escape_string($tier_level);
if (isset($_POST['tier_item_name']))
$tier_item_name = $_POST['tier_item_name'];
$tier_item_name = mysql_real_escape_string($tier_item_name);
if (isset($_POST['tier_item_brand']))
$tier_item_brand = $_POST['tier_item_brand'];
$tier_item_brand = mysql_real_escape_string($tier_item_brand);
$sql_tiers = "INSERT INTO cover_tiers SET
tier_level = '$tier_level',
tier_item_name = '$tier_item_name',
tier_item_brand = '$tier_item_brand'
";
$cover_tiers_id = mysql_insert_id(); // get the recent auto-incremented ID (p133)
if (!$cover_tiers_id)
{ exit('<p>Grabbing cover_tiers_id failed.</p>'); }
If you read my last post, you’ll know why. 
ha ha, I posted that without refreshing the page first!
If I do the following, then the ID is successful wtih $cover_tiers_id = mysql_insert_id(). Thanks! However, I get the following error: Error adding new tiers entry: “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘50’ at line 1.” Yet, both tables are successfully inserting data. Why would the insert work, but still generate this error? I don’t see anything to do with “50” at line 1.
$cover_tiers_id = mysql_query("INSERT INTO cover_tiers SET
tier_level = '$tier_level',
tier_item_name = '$tier_item_name',
tier_item_brand = '$tier_item_brand'");
$cover_tiers_id = mysql_insert_id(); // get the recent auto-incremented ID (p133)
if (!$cover_tiers_id)
{ exit('<p>Grabbing cover_tiers_id failed. Database not updated.</p>'); }
if (isset($_POST['mag_name']))
$mag_name = $_POST['mag_name'];
$mag_name = mysql_real_escape_string($mag_name);
if (isset($_POST['mag_month']))
$mag_month = $_POST['mag_month'];
$mag_month = mysql_real_escape_string($mag_month);
if (isset($_POST['mag_year']))
$mag_year = $_POST['mag_year'];
$mag_year = mysql_real_escape_string($mag_year);
$sql_mag = mysql_query("INSERT INTO mag_issue SET
mag_name = '$mag_name',
mag_month = '$mag_month',
mag_year = '$mag_year',
id_cover_tiers = '$cover_tiers_id'
");
Try this:-
<?php
function insert($sql){
return array(
'sql' => $sql,
'success' => (bool)mysql_query($sql),
'last_id' => (int)mysql_insert_id(),
'last_error' => (string)mysql_error()
);
}
function get_from_post($key){
return !empty($_POST[$key]) ? mysql_real_escape_string($_POST[$key]) : '' ;
}
function exit_on_sql_error($error, $sql){
printf('ERROR: %s SQL: %s', $error, $sql);
exit;
}
$tiers = insert(sprintf(
"INSERT INTO cover_tiers (tier_level, tier_item_name, tier_item_brand) VALUES ('%s', '%s', '%s');",
get_from_post('tier_level'),
get_from_post('tier_item_name'),
get_from_post('tier_item_brand')
));
if(false === $tiers['success']){
exit_on_sql_error($tiers['last_error'], $tiers['sql']);
}
$issue = insert(sprintf(
"INSERT INTO mag_issue (mag_name, mag_month, mag_year, id_cover_tiers) VALUES ('%s', '%s', '%s', %d);",
get_from_post('mag_name'),
get_from_post('mag_month'),
get_from_post('mag_year'),
$tiers['last_id']
));
if(false === $issue['success']){
exit_on_sql_error($issue['last_error'], $issue['sql']);
}
You jumped a few grades ahead of me! LOL.
I’m not comfortable using this. If I need changes later, I’m not sure I’ll understand it well enough, sorry.
Steve H
Try it, if it works I’ll walk you through it. If not, it will help us diagnose what’s happening.
Worked perfectly!
My code was similar to the code used in Build Your Own Database Driven Website, so I don’t know why it wouldn’t work.