Adding an ad to a mysql database; Point me in the right direction please...

Question

Currently, I have an 'add your ad here' page, with a form.

Now, the user must select what category he will want to post an ad into...

So I have a table for each category in mysql.

Now my question, how would I make each ad unique, with an ID nr? If I have several tables, the 'auto increment' might turn out to be the same in some ads, am I right? How is this usually solved? Assigning a random nr to each ad with javascript?

Remember this: I also have a 'picture upload' file input where I must set the picture name the same as the ad id...

Thanks

UPDATE: If I put all ads in one table, wont this slow down the search of the database? Isnt there any other way to do this?

Answer 1

Yes you would make each each ad unique by using an ID which in this case should probably be auto incremented. To solve the problem of multiple ads with the same ID put all ads in one table and add a column that stores the ad category.

Immediately after you insert a new ad into the database use the function mysql_insert_id() in order to get the last auto incremented id. Use that for your picture name.

Good luck.

Answer 2

As long as you don't manually reset the id, setting auto increment will always be bigger than the last one added.

If you want do things differently, use a randomly generated UUID. MYSQL has a built in UUID() function to help here.

Answer 3

Hi Camran, would it be possible to create one table with a column to track the category of each ad inserted? That way the auto generated id will always be unique. And this approach might be easier to maintain in the long run.

Answer 4

This is a problem of normalization. Ad's are a distinct class of object, and all the ads should be in the SAME table. Likewise categories should be in the same table, and then you should put a category id in your ad record, assuming you need to bring in category data.

Edit: Nah, shouldn't be an issue. I've seen newspaper ad systems run on that model that have millions of ads (they usually store by ad, and then by individual "run date" so 1 ad record could have a hundred run date records), and don't take significant time to query. Just figure out how you're going to be accessing the ads (by date, by category, etc) and set up your indexes accordingly, and you should be fine.

Answer 5

It sounds like you need to work on your db schema. I'd suggest one table for all ads regardless of type (include an ad_type field to differentiate them if you like) with an auto_increment primary key. If the ads can only have one picture, you can store the path to the file in the ad table too. Otherwise, make a separate ad_pictures table that has a foreign key referencing the ad_id.

When you insert into your ad table, you can obtain the last_insert_id() and use it for creating the ad_pictures INSERT query.

Answer 6

If you absolutely need to know the picture name before the form is submitted, then you might want to consider generating an UUID when displaying the form. Use the UUID as the ad ID and the picture name.

But chances are, what you want is this: accept the picture upload during form submission, process the submission, insert the row, get the row ID, and then save the picture based on that ID.

As long as you properly normalize your database (e.g., only one table for all ads, only one table for all categories, and perhaps an intermediate table linking the two) and use an auto incremented primary key as the ad ID, then you should be fine.