Changing View_Users.Php To Query Data From A Single Table To Two Tables

[Ashley]

Just bought this book a few weeks ago and already it is proving to be fantastic, however I have already become stuck on something already

 

In the "view_users.php" file, the following query is used for collecting the data from one table

$q = "SELECT last_name, first_name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY $order_by LIMIT $start, $display";

 

If the information fields I wanted to recall was spread across two tables how would I need to adapt the code. I have seen the example on page 173

Select * From messages INNER JOIN forums ON messages.forum_id =forums.forum_id WHERE forums.name ='kindling'

using the above structures I have managed to get a basic table to list users in php but as soon as I paste the code into "view_users.php", I am unable to display the table with the users listed Do I need to change something else from the file "view_users.php" to change it from reading the query as a single table to multi-table structure ?

 

Thank you

 

Ashley

[Antonio Conte]

You need to join data to the members-table to. I don't don't know all your table names, but this should about do it. You could maybe edit the messages.* to spesific data you want to display.

 

SELECT users.user_id, 
users.last_name AS lastname, 
users.first_name AS firstname, 
DATE_FORMAT(users.registration_date, '%M %d, %Y') AS user_date,
messages.*

FROM users as users
INNER JOIN messages AS messages ON ( users.user_id = messages.user_id )
INNER JOIN forums as forums ON ( messages.forum_id = forums.forum_id )

WHERE forums.name = 'kindling'

 

[Ashley]

Thank you, Antonio for your fast reply, I am going to try the way you have said and hopefully I should be able to make it work

[Ashley]

I have been working with the code Antonio posted above but I must still be doing something wrong

 

My first table 'users' has the fields

user_id, username

 

My Second table 'account'

account_id, user_id, last_name, first_name, registration_date

 

The line below works fine displaying data from users table, but fails to display 'account' table information

$q = "SELECT last_name, first_name, username, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr, user_id FROM users ORDER BY $order_by LIMIT $start, $display";

 

 

From Antonio's post,

 

SELECT users.user_id,

users.last_name AS lastname,

users.first_name AS firstname,

DATE_FORMAT(users.registration_date, '%M %d, %Y') AS user_date,

messages.*

 

FROM users as users

INNER JOIN messages AS messages ON ( users.user_id = messages.user_id )

INNER JOIN forums as forums ON ( messages.forum_id = forums.forum_id )

 

To Mine

 

$q="SELECT users.user_id,

users.last_name AS lastname,

users.first_name AS firstname,

DATE_FORMAT(account.registration_date, '%M %d, %Y') AS registration_date,

messages.*

 

FROM users as users

INNER JOIN account AS account ON ( users.user_id = account.user_id )ORDER BY $order_by LIMIT $start, $display";

 

If anyone is able to help further it would be much appreciated, I assume the same code I use in view_user.php for the query is the same code for edit_user.php

[HartleySan]

In general, when selecting data using a join, you have to provide both the table name and the column name separated by a period. For example, imagine the following:

 

users table
 id
 name

accounts table
 id
 name
 balance

 

If I perform a join and request the following data:

 

SELECT id, name...

 

Then MySQL does not know which id and name I want. As such, I need to specify the table name, similar to what Antonio did in his example. For example, the following:

 

SELECT users.id, accounts.name...

 

Selects the id from the users table and the name from the account table (duh, right?).

 

Note that even when there is no ambiguity between columns in tables, you still need to provide the table name as well. Using this principle, I think you'll very quickly see where the issue is. Good luck!

[Antonio Conte]

SELECT 
users.user_id AS user_id, 
users.username AS username,
account.account_id AS accound_id, 
account.user_id AS account_user_id, 
account.last_name AS lastname, 
account.first_name AS firstname, 
DATE_FORMAT(account.registration_date, '%M %d, %Y') AS registration_date

FROM users AS users
INNER JOIN account AS account ON ( users.user_id = account.user_id )

ORDER BY $order_by 

LIMIT $start, $display

 

This should be working.

 

Try to understand what Hartley wrote. It's an easy and good explanation.

[Ashley]

Thank you, Antonio Conte and HartleySan, I have successfully managed to get it working. Hopefully these posts should help other people who have encountered the same problem.

 

Now I am going to try and see if I can get the edit edit_user.php working!

[HartleySan]

Good to hear, Ashley.

 

Good luck with your site.

[Larry]

And thanks, Ashely, for the nice words on the book!