Connecting Users and Images PHP Help

At this point. you have two tables-users and Images-but no connection between them. That’s your next challenge. When you load a user from the users table and display his profile by using user.php, how do you determine which image in the images table you should display?

Clearly, you need some linkage between those two tables. You already have a unique ID for each entry in users (user _id) and in (image_id), which is a good starting place. The question becomes, does a user reference an image, or does an image reference a user?

Here’s the fundamental question you’ll ask over and over when you’re connecting two tables in a database: how are the two tables related? Better still, how are the two objects Your tables represents related?

For example, does a user have an image? Does a user have lots images? In this case, a single user has a single profile image, In database terms, that’s called a one-to-one relationship. One user is related to one image, As a result, you can create a new column in your users table, and in that column you can store the image _ id of that user’s profile image. You ~an make that change to your database like this:

mysql> ALTER TABLE users
-> ADD profile_pic_id int;
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0

Inserting an Image and then Inserting a User

Once an image is in images, you need to get that image’s ID and insert it into a user’s profile _pic _id column. At the moment, though, your script inserts into users before inserting into image.

Inserting an Image and then Inserting a User

At this point, you could look up the 10 of the user you inserted using mysql_ insert_id and store that in a variable. Then, you could get the image 10 by using mysql_insert_id again. Finally, you could update the profile_pic_id column of the new user’s row in users. That would work, and you’d end up with three different database interactions:

1. An INSERT to put the user’s information into users.
2. An INSERT to put the image information into
images.
3. An UPDATE to drop the new image’s 10 into
users.

These three steps might not seem like much, but every interaction withyour database consumes time and resources. As a general principle, you want to interact with your database as little as possible. That’s not to say you don’t work with a database; you just don’t make three or four calls if you can pull off the same task with one or two.

In this case, you can reduce the number of MySQL interactions from three to two:

1. INSERT the image into the images table (and get the 10 of that image in the process).
2. INSERT the new user into
users, and use the image 10 you just grabbed as part of the data you put into that INSERT.

Going from three MySQL interactions to two might sound like a minor issue. Then again, you just cut your database interactions by a third. If you can make fewer calls, do it.

Go ahead and wire up your INSERT statements accordingly:

Inserting an Image and then Inserting a User

From here, it’s just a matter of getting the 10 from your images INSERT and using it in the INSERT. But you know how to do that: you can use my sql_ insert_ id to grab the 10 of the row inserted into and then add that to your INSERT for users.

Connecting Users and Images

Put everything together, and your updated version of create user on» should look like this:

Connecting Users and Images

Try your code out by creating another user. Then, check to see what the last and highest inserted image 10 is from your images table:

This ID should be the same one that was inserted into your last inserted user in users:

Connecting Users and Images

You can see that when an image is inserted, the 10 of that image is dropped into users which demonstrates that you have a connection between a user and an image.

Joining Tables by Using WHERE

Now that you have a connection between and image and a user, you need a way to get an image for that user. First. utilize the user 10 to select the user you want:

// Build the SELECT statement
$select_query ; sprintf(“SELECT * FROM users WHERE user_id ; %d”,
$user_id) ;

This variable is just a sprintf version of code from show_user.php Make this change in your own version of show_user.php.

Notice that you get more than just user information, now. You also get the profile _pic _id for that user. This means that you can use this 10 to get the image for that user:

Connecting Users and Images

This code works, but it’s actually turning what is potentially one step into two. What you’re doing here is joining two tables: you have a piece of information-profile_ pic _Id in users and image_id in images-that connects the two tables.

CONNECT YOUR TABLES THROUGH COMMON COLUMNS

You also have a way to get only certain rows from a table: the WHERE clause. Putting this all together, you can get a user from users and an image from images where the user’s profile _pic _id matches the image’s image_id:

SELECT first_name, last_name, filename
FROM users, images
WHERE profile_pic_id = image_id;

Run this in MySQL, and you should see a result like the following example:

mysql> SELECT first_name, last_name, filename
FROM users, images
-> WHERE profile-pic_id = image_id;

Connecting Users and Images

For the first time, you’re connecting your tables together. In a single query, you’ve joined information in one table to corresponding information in another table. That’s a-big deal!

ALIAS YOUR TABLES (AND COLUMNS)

As cool as this query is, it’s a bit confusing. Take a look again:

SELECT first_name, last_name, filename
FROM users, images
WHERE profile_pic_id = image_id;

It’s obvious that first_name and last_name are columns from users. But, unless you really know your database structure, it’s not immediately clear where filename comes from. (Of course, you <IF intimately familiar with your database, so you know that filename is a column in images).

The same is true with profile _pic _id and image_id. Both are column names, but which column belongs to which table?

You can make this clear, though, by using table prefixes on your columns. For example, you can convert this query to something a bit more descriptive:

SELECT users.first_name, users. last_name, images.filename
FROM users, images
WHERE user~.profile_pic_id = images.image_id;

You’ll get the same result, but the query itself is a lot less ambiguous. Still, there’s another important fact to keep in mind here: programmers are lazy. Yup, it’s true; most programmers would rather type a single character-or at most two-if they can avoid typing five or ten. And SOL is happy to accommodate. You can alias a table by providing a letter or two after the table name and then using that letter as your prefix in the rest of the query:

SELECT u.first_name, u.last_name, i.filename
FROM users u, images i
WHERE u.profile_pic_id = i.image_id;

Once again, there’s nothing functionally different about this query, but it’s now both clear and succinct: a programmer’s best-case situation.

Posted on January 12, 2016 in Binary Objects and Image Loading

Share the Story

Back to Top