CODEWITHSUNDEEP
phpmysql
Spyros
Spyros

Reputation: 197

Inserting item with multiple categories on an MySQL DB

I am trying to create a basic eshop as an assignment for online orders(not for use, just so we learn to use PhP and MySQL). My first draft had very simple tables that had all basic functionallity. However the proffessor asked we should set up the products so one item could belong to more than one categories(I originally had the category table as a simple lookup table). I changed my DB schema as follows:

DB Schema for products

While changing the rest of the PhP and MySQL commands and queries was not really hard, I came to a dead end when I need to add a new product and/or add categories to it. I used to take the category id from the categories <select> in php and insert it as a variable into mysql, but I cannot do it in this database, as I do not have the new items id(auto increment) before it is actually inserted into the products table.

The only solution I can come up is insert the product in its table, select its id via latest date and then immediately insert that id along with any category ids the user chooses (these are in a html table along with a checkbox). However I do not think that is a proper way, and I am certain there is a far more elegant procedure. If so, would anyone please give me a pointer as to which?

Upvotes: 1

Views: 3209

Answers (3)

Phil Cross
Phil Cross

Reputation: 9302

You can use the query to find the next auto increment value of a table

SELECT AUTO_INCREMENT AS nextAutoId 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '[database_name]' 
   AND TABLE_NAME = '[table_name]'

For example:

test_database.test_table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1          | Pies         |
| 2          | Chips        |
+------------+--------------+

SELECT AUTO_INCREMENT AS nextAutoId 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'test_database' 
   AND TABLE_NAME = 'test_table' 

// Outputs: 3

Upvotes: 0

Glitch Desire
Glitch Desire

Reputation: 15023

mysqli_insert_id function of PHP (documentation) will return the auto-incremented ID of the inserted object. Code looks something like this:

$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->query("INSERT INTO products (stuff) VALUES (stuff)");
$product_id = $mysqli->insert_id;

Additionally, you won't be using mysql library which is deprecated (assuming you're using it right now).

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157896

Quite contrary, the way you choose is either elegant and straightforward, save for only getting inserted goods id which have to be gotten using dedicated API call intended for that. Every mysql API in PHP have corresponding method, mysqli_insert_id() for example

Upvotes: 0

Related Questions