Selecting multiple tables

[White_Lily]

Hi i have no at the moment but was more or less wondering if someone could give a link to an example, or maybe post the code.

 

 

I was wondering in an SQL query on a PHP page how do I select multiple tables?

 

 

I know how to select one table...

 

 

$sql = "SELECT * FROM users";
$result = mysql_query($sql);


if (!result)
{


}
else
{


}

 

 

 

 

however I was wondering how I select MORE than 1 table?

[Mahngiel]

JOIN

[White_Lily]

 

So what your saying is, is i should do this:

 

$sql = "SELECT * FROM users LEFT JOIN forum";
$result = mysql_query($sql);


if(!$result)
{


}
else
{


}

[Jessica]

Did you try it?

[Psycho]

There is no way for us to answer your question from what you have provided. We need to know the "context" of what you are trying to achieve. JOIN would probably be the solution for most situations. But, in others a UNION might be correct. But, JOINs have many, many ways to be used. So, just saying "JOIN" is the answer is understating how it would be used. Not only are there many types of JOINs but how you would define the JOIN is significant.

 

For example, this

SELECT * FROM users LEFT JOIN forum

 

would likely NEVER be used. Why? Because it JOINs every forum record on every user record. That wouldn't make any sense. It all depends on what you are trying to achieve. If you wanted to get information about users from the users table and you wanted a list of the posts each user has made you might have a query such as this:

SELECT *
FROM users
LEFT JOIN forum
  ON users.id = forum.user_id

 

Trying to explain JOINs in a forum is not feasible. There is many resources available to understand how to effectively set up and utilize a database. Tizag has some good tutorials. They are not very in-depth, but they will give you the very basics from which to start learning:

 

http://www.tizag.com/mysqlTutorial/

 

 

NOTE: Although I used '*' in my example (for brevity), that is a poor practice in my opinion, and should almost never be used. Instead you should explicitly list out the fields you want. Explaining why would take more time than I am willing to take here.

[White_Lily]

I haven't even begun to start building the forum yet, just want to get things clear before starting.

 

 

Basically what I want to be able to do is allow people to use their registered profile username and avatar on the forum when they create a topic or post. The problem is, is that the forum has its own tables while the username(s) and avatar(s) are in the "users" tables.

[Mahngiel]

these are very common tasks, and you will likely use a join to do so.

[Jessica]

That's not a problem, that's basic relational data design.

[Psycho]

Basically what I want to be able to do is allow people to use their registered profile username and avatar on the forum when they create a topic or post. The problem is, is that the forum has its own tables while the username(s) and avatar(s) are in the "users" tables.

 

Right. So when you create a post record you would ONLY store the user_id in the forum table (called a foreign key reference). Then when you want to display the post along with the user info you select the record(s) from the forum table and JOIN the user's name and avatar (etc.) from the users table. It might look something like this:

SELECT forum.title, forum.text, forum.post_date, users.user_id, users.username, users.avatar
FROM forums
JOIN users ON forums.user_id = users.user_id
WHERE forum.forum_id = 12 -- Or whatever id of the post you want to get

 

As stated above, this is basic database functionality. This is NOT the place to be given an education on this. I provided a link above that would get you started. There are tons of other resources available. Go start learning. Then, if you have a specific problem or question go ahead and post in the appropriate forum here (this is the PHP forum).

[White_Lily]

Correct me if im wrong - but dont i use PHP to build the forum, and dont i use PHP to do the query thats in question. If im wrong please do correct me.

[Mahngiel]

Correct me if im wrong - but dont i use PHP to build the forum, and dont i use PHP to do the query thats in question. If im wrong please do correct me.

 

Yes you do, sort of.  PHP has functions that wrap around the mysql queries, else you would never be able to assign the returned values.

 

The old way is to use mysql_query, mysqli_query, and the newer PDO

[Psycho]

Correct me if im wrong - but dont i use PHP to build the forum, and dont i use PHP to do the query thats in question. If im wrong please do correct me.

 

You may use PHP to connect to and execute queries. But one does not necessitate the other. You can use other databases with PHP and you can use different web based scripting languages with MySQL. They are completely different technologies. If you used that logic then you might as well post questions about setting up an Apache server in here since you can run PHP on it. Or even post HTML, CSS, and JavaScript questions here since you can create the output for those using PHP. We have dedicated forums for specific technologies to help the posters and the helpers.

 

There are multiple dedicated forums here specifically for databases. PHP is a scripting language. It only provides the "hooks" in order to interact with a database. There is nothing in the PHP manual about how to use databases.

 

Forum DO #5

Do take the time to post in the proper section