How can I link other MySQLI data from other tables to a certain user and echo it.

o8codex

I’m trying to link other tables in php with mysqli procedural do I have to use a foreign key to do this for example lets just say I have a post table and a user table how can I show the user their data base on those two tables connected to that user. Please give me a very tiny simple example how I can do this?

maxxd

If I'm reading this correctly, this is a MySQL question - PHP's mysqli and PDO extensions will try to run whatever query you pass to them. So, it sounds like what you're asking about are MySQL JOINS. Assuming your database is set up correctly (normalized), your example is a very simple one. Let's take the table structure below:

	Posts 		Users
|-----------------|-----------------|
|       PostID     |       UserID     |
|       UserID     |	Name      |
|-----------------|-----------------|

(Sorry about the display, but hopefully you get the idea).

Note that you've got UserID in both tables - in the Users table, it's the primary key. It's the foreign key in Posts. With this set up, you can use this query to get the post ID and user name:

SELECT      u.Name
                ,u.UserID
                ,p.PostID
FROM Posts p
LEFT JOIN Users u
       ON p.UserID = u.UserID

While I'm at it, let me recommend using PDO instead of MySQLi - it's a much more user-friendly system, especially when dealing with prepared statements and variable binding (which you should be using if you're not already).

o8codex

Thanks for your response I have a question tho so is there many way to do this? Some people said I need a foreign key some people say I only need a SQL JOIN command and some people say I can do this with pure PHP with tables that have a column that haves the same name in it. So is there many ways to do this or is there only way because I plan to make a social network that's why i'm asking around so I can combine other tables that relates to the user.

NogDog

You do not need to define a foreign key in order to be able to do a table join in your SQL. The foreign key provides a mechanism to ensure the integrity of the relationship between the tables, but is not a requirement to do the join itself. In the Ruby on Rails world-view, they generally prescribe to not define foreign keys, and leave that logic in the application code -- but that is not by any means a universal opinion. 🙂

As far as the PHP-only approach, I'm not sure I understand what "they" mean by that, but I suspect I'd be looking a the SQL join approach if it were me. It's a typical approach and, assuming the tables are indexed appropriately, can perform very well in terms of speed.

maxxd

I agree with NogDog about the PHP-only method of doing this - the only way I can see that working would be to pull all the records for both tables into arrays, then collate the arrays programmatically. Basically, doing the work that a JOIN would do, only doing it less efficiently while using more resources. As far as actually creating a foreign key, again as NogDog says, it's not strictly necessary. However, it does come with speed benefits as SQL can cache the indexes between the tables and run the query quicker. On top of that, you also get benefits in terms of record integrity during update and delete operations.