Retrieving data from multiple tables and making multiple pages?

chelf

Hello again,

First I want to say thank you to all of you that have helped me in the past with all of my questions. I have gotten far in PHP and MySQL in the past two weeks. I designed a site that pulls information from a database and displays it on the website. However I have run into some questions:

1. I have a database with multiple tables. One table consists of a user profile type of some sort, the other table consists of a Bio of that user. Now I want to link the profile with the bio so I can display information from both tables. How can I do that. I understand the following code:

Expand|Select|Wrap|Line Numbers

 
$results = my_sql_query("SELECT * from profile WHERE user = 'username'");

while ($row = mysql_fetch_array($results))

{

echo "user: " . $row['username']

}

How can I get information from the second table to display? They both have a common field called username that match.

Also, can anyone show me how to display results in multiple result pages?

Thanks!

debasisdas

You simply need to join both the tables to retrive data.

Atli

Hi.

You can JOIN them together, essentially creating one big table out of them both.

Like, if you had these two tables:

Expand|Select|Wrap|Line Numbers

 
/* User */

+--------+----------+

| UserID | UserName |

+--------+----------+

|      1 | First    |

|      2 | Second   |

+--------+----------+

/* UserInfo */

+-----------+-----------+----------+

| UserID_FK | FirstName | LastName |

+-----------+-----------+----------+

|         1 | First     | User     |

|         2 | Second    | User     |

+-----------+-----------+----------+

To get all the info (ID, username, real name) from a single query, you can JOIN them using the UserID.

Like:

Expand|Select|Wrap|Line Numbers

 
SELECT

  u.UserID,

  u.UserName,

  i.FirstName,

  i.LastName

FROM User AS u

INNER JOIN UserInfo AS i

  ON u.UserID = i.UserID_FK

Which would give you:

Expand|Select|Wrap|Line Numbers

 
+--------+----------+-----------+----------+

| UserID | UserName | FirstName | LastName |

+--------+----------+-----------+----------+

|      1 | First    | First     | User     |

|      2 | Second   | Second    | User     |

+--------+----------+-----------+----------+

I used an INNER JOIN there, but that is not always the best type of join to use.
See the differences between the different joins here.

chelf

Thank you again!! I used the LEFT JOIN statement and was able to work things out. Can you show me a sample code on how to break search results into multiple pages?

Atli

That would be pagination.
There is a lot of material on that topic on the www.
Try a Google search using pagination as they keyword.

This tutorial looks pretty good.

Retrieving data from multiple tables and making multiple pages?

re: Retrieving data from multiple tables and making multiple pages?

re: Retrieving data from multiple tables and making multiple pages?

re: Retrieving data from multiple tables and making multiple pages?

re: Retrieving data from multiple tables and making multiple pages?

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about