CODEWITHSUNDEEP
phpmysqlinsert
Waqar Akbar
Waqar Akbar

Reputation: 25

MySql, Insert data to a table row from another table row (this row is just been created)

Not sure how to describe my problem, but lets try..

I am working on a small news portal php + mysql project I have two tables in mysql database.

1: news (
   n_id (primary key)
   n_title
   n_text
)

2: news_image (
   ni_id (primary key)
   n_id  (foreign key from news table)
   ni_name
   ni_caption
)

(n_ prefix is for new and ni_ is prefix for news_image)

news table will hold the news story and news_image table will store the names and captions for the images of the news. separate table for news images is because on later stage, i will be adding more than one images for each story. the script works fine (as i have tested it by entering data in data base through phpmyadmin.

Problem: I created a php script to add data to news table and to upload images to file system. it works fine. I used this query to add row to news table

INSERT INTO news
SET
n_title = '$title',
n_text = '$text'

n_id is the primary key (integer and is AUTO_INCREMENT).. this works fine but I want a query that will execute just after the execution of above query and will automatically insert a new row in news_image table, taking n_id of the row that has just been inserted in news table. ni_name and ni_capption in news_image table is not a problem, I can get it easily but getting the n_id from news table of the newly inserted row is the problem... How can I get n_id...????? Please provide a detailed answer as I am newbie to php + mysql.. Thanks for reading...waiting for reply..

Upvotes: 0

Views: 1510

Answers (4)

John Pixel
John Pixel

Reputation: 57

You can solve it directly in SQL with one querystring (I got the some problem some minutes ago :) ).

Try to shoot THIS at the database:

$sql = "INSERT INTO `news`(`n_txt`) VALUES ('Peace in Ukraine'); 
INSERT INTO `news_image`(`n_id`,`ne_name`) VALUES (LAST_INSERT_ID(),'smiling_merkel.jpg');"

Upvotes: 0

The_Fritz
The_Fritz

Reputation: 452

Have you tried getting the ID of the last inserted news item? Like:

$lastItemID = mysql_insert_id();

Then you can use it to create the entry for the image:

INSERT INTO news_image
SET
n_id= '$lastItemID',
ni_name = '$name',
ni_caption  = '$caption'

Upvotes: 1

Shehzad Nizamani
Shehzad Nizamani

Reputation: 2223

You can use mysql_insert_id(); function to get the last inserted id. Just after running the insert query use this line to get id $id = mysql_insert_id(); and you will have the id in $id;

Upvotes: 1

eggyal
eggyal

Reputation: 126055

It depends on the PHP extension through which you're accessing MySQL - they all have insert_id() type functions to obtain the last auto-incremented number from the current connection.

In particular, with PDO there's lastInsertId() and with MySQLi there's mysqli_insert_id(). You shouldn't be using the ancient MySQL extension, as it is no longer maintained and the community has begun the deprecation process.

Upvotes: 0

Related Questions