CODEWITHSUNDEEP
phpmysql
user2320500
user2320500

Reputation: 169

Storing values in two MySQL tables

I have a scenario in which I am not sure about what to do.

I have a website where a user can update their status. I am allowing the use of hash tags so a possible user post might look like:

Went for a great hike today!! #hiking

Now, I intend to store the post in a table appropriately named "POSTS" which is structured like this:

post_id | user_id | text | date

Now, when a user submits the form which holds the post text I run a script to create an array to get all of the hash tag terms the user used and then store them in an array. So then I can loop through that array and insert the tags into the aptly named "TAGS" table. Now the structure of this table is this:

tag_id | post_id | user_id | tag

The only problem with this is that I do not know the post_id of the post until after I insert the data into the "POSTS" table (post_id is the primary key and is auto increment). Now, I was thinking I could just SELECT the last row of data from the "POSTS" table for that user (after I insert the post), and then in turn use the returned post_id for my query that inserts the tag data into the "TAGS" table. This seems like not the best way? My question is:

Is this the best solution or is there a better way to go about this scenario?

I am brand new to Stack Overflow, so don't please down vote me. Comment and tell me what I am doing wrong and I will learn and ask better questions.

Thanks

Upvotes: 1

Views: 85

Answers (3)

Tommy Hui
Tommy Hui

Reputation: 1336

This sort of depends on which version of mysql you're using and how you want to organize your code.

Option 1. Do exactly what you've said. PHP would contain the code to manage the database and how data is stored into the database. The only drawback that I see in what you've outlined is if there's an issue with dealing with the hashtags, then possibly you would have a post that is inserted to the database, but the hash part did not successfully complete. For certain applications (like a bank account), this may not be acceptable and this is what database transactions are for.

Option 2. Another way to handle this would be to write a mysql stored procedure that does both the insert and handling the hash tags. The stored procedure could also wrap the whole thing in a transaction so that your database is consistent. Note that this requires a version of mysql that supports stored procedures. The bad side of doing this is that you would have to write in mysql, which is different from PHP.

Both mysql and PHP can handle this application logic/datastore logic. It is a matter of how you want to organize the code. I would prefer keeping the layers distinct. Even if you are to do this in PHP, at least have a separate class that deals with the database and not do anything else. When your code gets bigger, having a separate class or module or namespace that manages these types of code really makes them easier to change and to test.

Upvotes: 0

Djimmr
Djimmr

Reputation: 84

Have a new column in both tables - unique_id - which holds a string you generate in code before querying the database. That way you have an id to tie posts and tags together before submission. I use this method all the time for similar applications.

Only issue is uniqueness, but there a variety of ways to generate unique ids (I normally use a mixture of timestamps and hashing).

Upvotes: 0

Andrej Bestuzhev
Andrej Bestuzhev

Reputation: 674

You can get last insterted ID very simply: mysql_insert_id() if you don't use PDO or using function lastInsertId() if you do.

Upvotes: 1

Related Questions