1

I have two inputs:

Basket Name:  <input type="text">

AND

Fruits<select name="fruits">
   // Some java code that populate my drop down list from a SELECT sql 
   // that is pulling from my database table called 'Fruits'
   // i.e. <option value='SELECT fruitID FROM fruits'> 'SELECT fruitName FROM fruits' </option>    
   ...
  </select>

And I want to do the following two SQL inserts at the same time when my <form> is submitted:

SQL 1: Insert 'Basket Name' into a table called 'Baskets';

SQL 2: Insert the 'fruitID' (i.e. <option value = "01"> Apple </option>) into an associative table called 'Baskets_Fruits'

My table structures:

BASKETS table:

basketID -> AUTO_INCREMENT int(11), NOT NULL

basketName -> varchar(255)

BASKETS_FRUITS table:

basketID_FK -> foreign key of 'basketID' from Baskets table

fruitID_FK -> foreign key of 'fruitID' from Fruits table

FRUITS table:

fruitID_FK -> AUTO_INCREMENT int(11), NOT NULL

fruitName -> varchar(255)

How may I obtain the current basketID_FK if basketID that is referenced is AUTO-INCREMENTED? I'm not quite sure how to code the SQL syntax for getting the value of an AUTO-INCREMENTED column.

EDIT:

Do I do something like:

INSERT INTO baskets_fruits (basketID_FK, fruitIDFK)
(SELECT baskets.basketID, fruits.fruitID FROM baskets, fruits
WHERE baskets.basketID = "Foo" AND fruits.fruitID = "BAR");

But what would the "Foo" and "Bar" syntax be like?

5120bee
  • 689
  • 1
  • 14
  • 36

1 Answers1

0

Would something like this work? This obviously would be after the insert, then you can manipulate whatever you would like after.

SELECT * FROM BASKETS
INNER JOIN BASKETS_FRUITS ON basketID = basketID_FK
WHERE basketName = 'whatever the basket name is'

This would give you the key based on a specific basketName where you can then find the fruitID_FK if needed.

Airo
  • 70
  • 13