Select

[ashbow97]

Right what I want to do (which I'm really stuggling with) is to select multiple tables from one database. I've looked online and have found some things, but not fully understood them. I have two tables, one calle 'website' and the other 'post1'. They both have identical columns, id, timestamp, author, email, url and message.

$entries = mysql_query("SELECT `timestamp`, `author`, `email`, `url`, `message` FROM `website` ORDER BY `timestamp` DESC");

Thanks :)

[bluegman991]

$entries = mysql_query("SELECT `t1`.`fields`,`t2`.`fields` FROM `table1` `t1` LEFT JOIN `table2` `t2` ON `t1`.`id`=`t2`.`id`");

thats how to connect 2 tables if you need to connect more you just copy and paste everything after FROM

[Karlos94]

What about using a NATURAL JOIN if the columns are identical.

[ashbow97]

I don't know, after a bit of researching, I found out it would be easier to make it so it inserts into multiple tables. Because if I had a join, I'd have to change the code on every single page when I create a new database. And if you're wondering what the hell I'm doing, I've created a blog for someone, but they wanted a bit at the bottom of the page which shows the latest comments. So what I could do, is insert it into two tables, one of which would show ALL of the comments made throughout the site... I know what I'm on about ;)

So does anybody know how I can insert into to multiple tables at once? :) Thanks for all the current help :D

[rulerofzu]

Why would you need to change the code on each page when you create a new database?

[ashbow97]

Right, I need a new table for each page, because each page has unique comments about the subject. But at the bottom of the page is the most recent comments for all of the pages.

If I used SELECT, I would have to edit the code every time I made a new table, so it includes that one.

If I made a seperate table, I could insert all the posts into that one, and just select that one table for the thing at the bottom. But I'd still need to send the information to the unique tables. So all I need to do with this one is code it so that when you post your comment, it inserts into two tables at once. Get me? So I was asking if you know a way to make the INSERT funtion insert into two tables (with identical columns) at the same time :)

[a_bertrand]

Seems a bit stupid for me to have a table for comments valid just for a single page and create another one for another page. Where conditions are there exactly to limit the results to what you are interested in, in this case the page you are displaying.

Also as far I'm aware of, you cannot insert something into multiple tables at the same time.

[ashbow97]

Oh right :/ How else would you be able to do it? The only way I can think of that would work is to either insert it into two tables, or select from multiple tables...

[a_bertrand]

what exactly do you need to do? If you explain your problem maybe we can give a better way to approach it. Also, selecting from multiple tables is not an issue as long as you have some way to join them.

[ashbow97]

Sorry :L Here is the page: http://www.dphgardenservices.co.uk/website.php

As you can tell, one of the main part of it is the comment feature. I've already got that sorted and it all works fine. If you look at the bottom of the page you will see a bit called 'Latest Comments', I'm trying to get that to work. So what I want it to do is select the 3 most recent posts throughout the site... and since there is a table for each page otherwise things would start to clash, I need to select multiple tables.

[rulerofzu]

Why dont you pull the latest using jquery.

Jquery call to a php page to pull the latest result based on the time. Use a json return and then append the result to your webpage.

[a_bertrand]

Ok, first of all, wrong design, ALL comments should be in one single table and not in multiple tables. The design you choose will make it simply harder to continue to work with it.

Anyhow if you want to "glue" multiple tables one after the other the command is UNION

so:

[mysql]

SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 UNION SELECT * FROM TABLE 3;

[/mysql]

http://dev.mysql.com/doc/refman/5.0/en/union.html

Again I would personally re-design your site / tables to have a single table for all comments.

[ashbow97]

Thank you! :) It now works pretty much exactly how I wanted it to :)

http://dphgardenservices.co.uk/test.php

I just have one last final question, as you can see, at the bottom of the page under 'latest comments' I have selected 3 posts from multiple tables... which is what I wanted. The only problem is, if someone were too post a very long comment on the subject it would make the page very messy. What I want to do (which is hopefully possible) is limit the amount of characters selected from the message row. So only 60 characters will be shown at the bottom of the page, and then put '...' at then end for them to go find out more :)

This bit is unrelivent. If you read through, you'll notice there is a \ before the 's... is that fixable?

[Djkanna]

[...]

Also as far I'm aware of, you cannot insert something into multiple tables at the same time.

Well there is triggers so it is possible.

[a_bertrand]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

[bineye]

@a_bertrand: It IS possible to insert into multiple tables, however, it would need 2 separate queries on execution, but still can be done.

@ashbow: You could have added all comments into a single table, given each page an ID, then recall the comments for that page by filtering by this ID, then you could have recalled the last added comments from the same table as well.

[a_bertrand]

bineye: if you read what I wrote: "you cannot insert something into multiple tables at the same time". Of course you can have multiple queries, but that wasn't the question.

For your suggestion about the page id, that's basically what every one tried to explain...

[bineye]

Yeah I know, but it seemed like lots of oddly complicated ways of going about it. I just thought I'd write it how I'd like to read it myself, nice and simple.