I want to initialise a 'ticket' table with some ticket IDs. To do this, I want to insert 120 ticket IDs into the table. However, at every 10th statement, MySQL tells me that the ID already exists and thus won't let me insert it. Here's my code:
//make a query
$insert_ticket_query = "INSERT INTO ticket (id) VALUES (?)";
$insert_ticket_stmt = $mysqli->stmt_init();
$insert_ticket_stmt->prepare($insert_ticket_query);
$insert_ticket_stmt->bind_param('s', $ticket_id);
$mysqli->autocommit(FALSE); //start transaction
for($i=0;$i<NO_GUESTS;$i++){
$id = generate_id($i);
$ticket_id = format_id($id, $prefix['ticket'], $suffix['ticket']);
$t_id = $ticket_id;
//echo '<p>'.$ticket_id.'</p>';
$result = $mysqli->query("SELECT * FROM ticket WHERE id='".$ticket_id."'");
$row_count = $result->num_rows;
if($row_count == 0){
$result->close();
if($insert_ticket_stmt->execute()){
$mysqli->commit();
echo "<p>".$t_id."added to the ticket table!</p>";
}
else{
$mysqli->rollback();
echo "problem inserting'".$t_id."' to the ticket table";
}
}
else{
echo "<p>".$t_id."already exists, so not adding it!</p>";
$result->close();
}
}
$mysqli->autocommit(TRUE);
$insert_ticket_stmt->close();
?>
Here are the functions used in the code:
function generate_id($integer){
//generate an ID based on the $string parameter
//$id = "000000000000XZLH";
$string = (string) $integer;
$length = strlen($string);
$id_string = "10000000";
for($i=0;$i<$length;$i++)
$id_string[$i] = $string{$i};
return $id_string;
}
function format_id($id_string, $id_prefix, $id_suffix){
$id = "0000000000000000";
//apply the prefix
for($i=0;$i<4;$i++)
$id[$i] = $id_prefix{$i};
//apply the id
for($i=4,$j=0;$j<8;$i++,$j++)
$id[$i] = $id_string{$j};
//apply the suffix
for($i=12,$j=0;$j<4;$i++,$j++)
$id[$i] = $id_suffix{$j};
return $id;
}
Here's the log that's returned to the browser:
TCKT00000000YDLOadded to the ticket table!
TCKT10000000YDLOadded to the ticket table!
TCKT20000000YDLOadded to the ticket table!
TCKT30000000YDLOadded to the ticket table!
TCKT40000000YDLOadded to the ticket table!
TCKT50000000YDLOadded to the ticket table!
TCKT60000000YDLOadded to the ticket table!
TCKT70000000YDLOadded to the ticket table!
TCKT80000000YDLOadded to the ticket table!
TCKT90000000YDLOadded to the ticket table!
TCKT10000000YDLOalready exists, so not adding it!
TCKT11000000YDLOadded to the ticket table!
TCKT12000000YDLOadded to the ticket table!
TCKT13000000YDLOadded to the ticket table!
TCKT14000000YDLOadded to the ticket table!
TCKT15000000YDLOadded to the ticket table!
TCKT16000000YDLOadded to the ticket table!
TCKT17000000YDLOadded to the ticket table!
TCKT18000000YDLOadded to the ticket table!
TCKT19000000YDLOadded to the ticket table!
TCKT20000000YDLOalready exists, so not adding it!