Inserting into two tables

[garry]

Hi, I needed some help with a query I was making. I am trying to use user supplied data to insert into two separate tables in one database but the second table that is inserted into needs to get information from the first table that was inserted from (the new id created) Here's an example of the code:

 

$album = clean_full($_POST['album']);
$artistid = $_POST['artist'];
$year = (int)($_POST['year']);
$review =  clean_body($_POST['review']);
$user_id = $_SESSION['user_id'];

$query = "INSERT
		  INTO albums
		  SET
		  	id = '', // This id will auto-increment in the database but I need to get the number it is assigned
			artist_id = '$artistid',
			title = '$album',
			year = '$year',
			user_id = '$user_id',
			created_at = NOW()
		 ";

$query1 = "INSERT
		  INTO reviews
		  SET
		  	id = '',
			artist_id = '$artistid',
			album_id = '?WHERE DO I GET THIS?', // I need to get the id from the previous insert and place it here
			body = '$review',
			user_id = '$user_id',
			created_at = NOW()
		 ";

$result = mysql_query($query);

 

I would really appreciate any help

[redarrow]

use sessions or select the first insert then insert agin via the new selected data....

[garry]

There is no easier way of getting the id that the first row in the first query is assigned? I thought there might be a way with PHP..?

[redarrow]

doing this blind cheek it out please alter what needs to be..........

 

session way.

 

<?php session_start();

$_SESSION['album'] = clean_full($_POST['album']);
$_SESION['artist'] = $_POST['artist'];
$_SESSION['year'] = (int)($_POST['year']);
$_SESSION['review'] =  clean_body($_POST['review']);
$artist_id = $_SESSION['user_id'];


$query = "INSERT INTO albums (artist_id ,title,year,user_id,created_at)
values('$artist_id,".$_SESSION['artist'].",'".$_SESSION['album'].",'".$_SESSION['year']."','$artist_id',NOW())";
$result=mysql_query($query)or die (mysql_error());


$query1 = "INSERT INTO reviews (artist_id ,title,year,user_id,created_at)
values('$artist_id,".$_SESSION['artist'].",'".$_SESSION['album'].",'".$_SESSION['year']."','$artist_id',NOW())";
$result1=mysql_query($query1)or die (mysql_error());

?>

[garry]

But that will set the artistid as the users id, this isn't what I want as I will have multiple users adding multiple reviews.

 

I simply need a way to get the id that has just been set by the database through the auto-increment

[garry]

Anyone?! I'd really appreciate some help on this!

[mga_ka_php]

use mysql_insert_id.

 

like this

 

$album = clean_full($_POST['album']);

$artistid = $_POST['artist'];

$year = (int)($_POST['year']);

$review =  clean_body($_POST['review']);

$user_id = $_SESSION['user_id'];

 

$query = "INSERT

  INTO albums

  SET

  id = '', // This id will auto-increment in the database but I need to get the number it is assigned

artist_id = '$artistid',

title = '$album',

year = '$year',

user_id = '$user_id',

created_at = NOW()

";

 

 

$result = mysql_query($query);

 

$prev_id=mysql_insert_id();

 

$query1 = "INSERT

  INTO reviews

  SET

  id = '',

artist_id = '$artistid',

album_id = $prev_id // I need to get the id from the previous insert and place it here

body = '$review',

user_id = '$user_id',

created_at = NOW()

";

 

mysql_insert_id() = get the id from the previous sql statement