PHP/MySQL Joins

Craig321

Craig321

Craig321

Soldato
Joined
2 May 2004
Posts
19,950
Hi,

I'm coding a small, simple message board type system (I'm bored :p).

Basically when you make a new thread it'll be entered into the 'threads' table - the threads table will contain the title of the thread and an auto increment ID (threadid).

The content of the thread will then be entered into the 'posts' table along with the thread ID.

The fields linking both of these tables together will be 'threadid' - 'threadid' will be auto increment on the 'threads' table and normal INT on the 'posts' table.

So, basically when I create the query for a new thread I need to be able to somehow enter the auto increment number that's created in the 'threads' table into the 'posts' table so they can be joined when the SELECT query is performed.

I was hoping there's some kind of 'join' one line query I can do?

Hope that all makes sense :p

Thanks,
Craig.
 
Just to add to the post above...you may want to use a stored procedure or routine for this. It will be simpler for your code and more efficient as you are allowing the database to do the processing of data. You are doing multiple inserts for effectively one transaction - if that makes sense.
 
Pho said:
After your first insert do-
PHP: 
SELECT LAST_INSERT_ID();
this will give you the inserted ID :).

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
Click to expand...

Perfect, thanks very much

brainchylde said:
Just to add to the post above...you may want to use a stored procedure or routine for this. It will be simpler for your code and more efficient as you are allowing the database to do the processing of data. You are doing multiple inserts for effectively one transaction - if that makes sense.
Click to expand...

Not sure what you mean?

Using what Pho suggested I currently have:
Insert 1: inserts the thread title into the 'threads' table (which creates the unique ID as well)
Code: 
INSERT INTO threads (title) VALUES ('$title')

Insert 2: inserts the post into the 'posts' table along with the threadid using LAST_INSERT_ID() to link the two tables
Code: 
INSERT INTO posts (threadid, post) VALUES (LAST_INSERT_ID(), '$post')

Works perfectly. Not sure if it's the most efficient way to do it though?
 
Last edited:
Craig321 said:
Not sure what you mean?
Click to expand...

MySQL introduced Stored Procedures in MySQL 5. Essentially, you're stored procedure acts like a custom 'function' for the database.

Your stored procedure would take a number of parameters, and then do the work you wanted it to do. So for example, yours would be something along the lines of......

Code: 
INSERT INTO Threads (threadCol1,threadCol2) VALUES(threadParam1,threadParam2)

INSERT INTO Posts (postsCol1,postsCol2)
    VALUES(LAST_INSERT_ID(),postsParam)

Then in your PHP you would call the stored procedure, pass the parameters and that is it.

Where as, if you did each individual query, you would do...

insert into thread......

retrieve the last id.....

insert into posts....

so you make 3 calls to the database. You also then need to consider what happens if say, the first insert is successful, but the insert into posts fails. You end up with a situation where you have a thread, with no posts. You could handle this fairly easily in a stored proc.

I was doing something similar in SQL Server recently which has XML support. I passed XML generated in my code to a stored procedure which did 3 different inserts using data from the XML and retrieving insert ids back as required.

Edit:

Don't get me wrong- there is nothing inherently wrong with the approach suggested by Pho - it is perfectly correct. However, it is not the most efficient method of doing what you want to do.
 
Last edited:
Just a thought on the LAST_INSERT_ID() command - if, for example, two people posted at the same time could the query pick up the LAST_INSERT_ID() from the query that's being made by the other person, which would end up with posts and threads everywhere on a busy message board?

Not 100% sure on exactly how the LAST_INSERT_ID() command works, which is why I'm asking :)

I will look into what you've suggested above as well, thank you :)
 
Last edited:
Craig321 said:
Just a thought on the LAST_INSERT_ID() command - if, for example, two people posted at the same time could the query pick up the LAST_INSERT_ID() from the query that's being made by the other person, which would end up with posts and threads everywhere on a busy message board?

I will look into what you've suggested above as well, thank you :)
Click to expand...

Probably not - although it depends if you terminate your connection. MySQL handles everything on a per connection basis. your LAST_INSERT_ID will be for the last insert from your connection. It doesn't matter that another client (or instance of your webpage) has attempted to carry out the same set of queries.


What you would need to avoid is......

<connect to mysql>
<query>
<disconnect>
<connect>
<query>
<disconnect>

This would cause you problems with LAST_INSERT_ID.
 
Cool, so the two separate INSERT queries that I mentioned in #4 would be fine regardless of how many other clients were submitting the same/similar query?

Thanks :)
Craig.
 
Last edited:
As far as I'm aware, No. Maybe someone else could comment further but in PHP you would use mysql_connect() to connect and mysql_close() to close. In between you would use mysql_query and alike to query the database...
 
Ah, cool. Thanks again :)

So basically, when working on this type of stuff I should get into the habit of doing the following for every query (or at least the queries that need it)?
PHP: 
$result = mysql_query($sql, $dbconn);

Thanks,
Craig.
 
Last edited:
Thanks very much for all the help both brainchylde and Pho :)

I'm not really worrying about security at the moment (just getting the initial queries etc. created so it works properly and efficiently). I'm quite used to tightening things up from my previous registration system project (thanks mostly to http://php.robm.me.uk/ and other sources).
 
You must log in or register to reply here.
Back
Top Bottom