error in php code last insert id

[Fiorentino01^]

Hi, I am working on an hotel booking system for a small hotel.I finally found a nice tutorial on HTML.it.It is in Italian but that's not a problem for me as Italian is my mother tongue.The site consists of the usual tables like clients, reservations, etc.I ran all the scripts from the command line and everything works fine. I still am not too sure of what I am doing but I figured that once I deliver the finished site to the hotel people, they are not going to want to fiddle with the command line, phpMyAdmin,MySQL etc.when they have to insert a reservation.So I started creating some user interface like a form to insert clients data as below.The problem is that last insert id business which is very important for the site to work.I searched the Internet but could not find a solution, I think the problem is that insert_id into @codeclient.Can you help me?this is the code involved.

INSERT INTO client SET
nominativo = 'Rossi Mario',
indirizzo = via Manzi 2 00153 Rome,
telefono ='06 86123920';
SELECT_LAST_INSERT_ID() INTO @codCliente;

<table width="300" border="0" align="center" cellspacing="1" cellpadding="0">
<tr><td><form name="form1" method="post" action="insert_ac.php">
<table width="100" border="0" cellspacing="1" cellpadding="3">
<tr><td colspan="3"><strong>Insert Data into MySQL Database</strong></td></tr>
<tr><td width="71">Nominativo</td>
<td width="6">:</td>
<td width="301"><input name="nominative" type="text" id="nominative"></td>
</tr>
<tr><td>Indirizzo</td>
<td>:</td>
<td><input name="indirizzo" type="text" id="indirizzo</td>
</tr>
<tr>
<td>Telefono</td>
<td>:</td>
<td><input name="telefono" type="text" id="telefono></td>
</tr>
<tr><td colspan="3" align="center"><input type="submit" name="submit" value="submit"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>



<?php

 $host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="Fiorentino##"; // Mysql password 
$db_name="hotel"; // Database name 
$tbl_name="clienti"; // Table name 

// Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form 
 $nominativo=$_POST['nominativo'];
 $indirizzo=$_POST['indirizzo'];
 $telefono=$_POST['telefono'];

 // Insert data into mysql 
 $sql="INSERT INTO $tbl_name SET(nominativo, indirizzo, telefono)VALUES('$nominativo', '$indirizzo', '$telefono')";
 SELECT LAST_INSERT_ID() INTO @codCliente;

 $result=mysql_query($sql);



// if successfully insert data into database, displays message "Successful". 
 if($result){
 echo "Successful";
 echo "<BR>";
 echo "<a href='insert.php'>Back to main page</a>";
 }

 else {
 echo "ERROR";
 }
 ?> 

 <?php 
// close connection 
 mysql_close();
 ?>

 And this is the error I get:Parse error: syntax error, unexpected 'LAST_INSERT_ID' (T_STRING) in C:\inetpub\wwwroot\Hotel Site\insert_ac.php on line 20

[Zagga]

Hi,

The call to get the last inserted ID needs to be placed after the query.
Delete line 53 then insert:
$last_id = mysql_insert_id();
between lines 60 and 61 (as you will only have an inserted ID if the query was successful)

[Fiorentino01^]

Hi, thanks I tried what you suggested, it does not work keep getting same error, I tried to insert it in different positions but nothing to do. I am afraid the code must be changed completely, all I need is to be able to use that form and insert the last id,unfortunately I am not good enough to do this.Thanks anyway for your help.

[Zagga]

Sorry, you say you want to 'insert the last id'? The command I gave you will find out the ID of the last inserted record, and save it as $last_id. If you
echo "Last ID is " . $last_id;
it should display the message, and the last inserted ID. Are you sure you deleted line 53 (from the above code: SELECT LAST_INSERT_ID() INTO @codCliente;)?

[Fiorentino01^]

Hi, I am afraid there was a misunderstanding, I don't need to display the last inserted id which I know how to do.The purpose of inserting that command :select_ last_insert_id into @codcliente; is to assign that value to a variable which will be used later to update different tables when inserting the reservation and calculate the price.As I said unfortunately the tutorial is in Italian but if you want to see the whole project to see what I am talking about go to:http://www.html.it/pag/32165/tutorial-di-esempio-database-prenotazioni-alberghiere-i/ and part II http://www.html.it/pag/32166/tutorial-di-esempio-database-prenotazioni-alberghiere-ii/

[Zagga]

I'm affraid my grasp of Italian is worse than my grasp of SLQ :)
I may still have the wrong end of the stick, but once you have the value of the auto incremented field, you can then do with it what you please.

// Insert data into mysql
$sql="INSERT INTO $tbl_name SET(nominativo, indirizzo, telefono)VALUES('$nominativo', '$indirizzo', '$telefono')";
$result=mysql_query($sql);
$last_id = mysql_insert_id();

The variable $last_id is now the value of the auto increment field that was created by the INSERT statement above. For example, if the INSERT command just added the tenth record to the table, $last_id would be 10. You can now insert this variable into another table if needed, or do whatever you like with it.

[diafol]

With this type of usage, you may find a transaction useful:

$conn = mysqli_connect($host, $user, $password);
mysqli_select_db($conn, $db);

//IMPT to prevent auto commit
mysqli_autocommit($conn, FALSE);
$done=true;
mysqli_query("INSERT INTO table1 (field1, field2) VALUES ('value1', 'value2')") or $done=false; 
mysqli_query("SET @codCliente = LAST_INSERT_ID()") or $done=false;
mysqli_query("INSERT INTO table2 (user_id,fieldA,fieldB) VALUES (@codCliente, 'valueA', 'valueB')") or $done=false;

if(!$done)
{
    mysqli_rollback($conn);
} else {
    mysqli_commit($conn);
}
mysqli_close($conn);

NOT TESTED
You could however AFAIK, just use the mysqli_insert_id() instead of the 'SET' query and use a php variable in the second INSERT query, which I assume is what Zagga was saying.

[Fiorentino01^]

Hi guys thanks for your help but still I cannot get it to work. Let me explain what I need exactly. I found that tutorial for a small hotel reservation.It works beautifully if I run everything from the command line.I can insert a new client, that last insert id business(insert into@cliente) is stored in a variable so that when I insert a reservation it automatically calculates the price using the store procedure, updates the table supplements if there is a supplement say for full pension or extra bed etc.Now what I don't know is this. I cannot expect the hotel people to use a site if they have to run it from the command line or use phpMyAdmin to insert a reservation, I figure that I must create an user interface to make it easy for them to manage the site.How can I do that? I thought I started with the clients table but is hard to do.It works without that last_insert_id deal but unfortunately that is most important thing, without that the site does not work.In other words I need a form to insert name,address and telephone of clients and to save the last inserted id for the above reason.I am sorry to bother you but I am getting frustrated, hope you can solve my problem.Also I am a beginner with php so please don't give me just a code snippet that I would not know how to use it, I need the whole code for the form and the php to make it work.Thank you very much I appreciate it.

[diafol]

Also I am a beginner with php so please don't give me just a code snippet that I would not know how to use it, I need the whole code for the form and the php to make it work

That's not what we do, I'm sorry.

There should be no code for the form - that should just be html markup.

The routine, I assume will be like this (quick and dirty):

$field1 = $_POST['field1'];
$field2 = $_POST['field2'];
$field3 = $_POST['field3'];
$field4 = $_POST['field4'];

$stmt = $mysqli->prepare("INSERT INTO table1 (`field1`,`field3` ) VALUES (?,?)");
$stmt->bind_param('ss', $field1, $field3);
$stmt->execute();
$id = $stmt->insert_id
$stmt2 = $mysqli->prepare("INSERT INTO table2 (`field2`,`field4`,`id`) VALUES (?,?,?)");
$stmt2->bind_param('ssd', $field2, $field4, $id);
$stmt2->execute();