Ashley Posted August 21, 2011 Share Posted August 21, 2011 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 Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 21, 2011 Share Posted August 21, 2011 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' Link to comment Share on other sites More sharing options...
Ashley Posted August 21, 2011 Author Share Posted August 21, 2011 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 Link to comment Share on other sites More sharing options...
Ashley Posted August 22, 2011 Author Share Posted August 22, 2011 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 Link to comment Share on other sites More sharing options...
HartleySan Posted August 23, 2011 Share Posted August 23, 2011 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! Link to comment Share on other sites More sharing options...
Antonio Conte Posted August 23, 2011 Share Posted August 23, 2011 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. Link to comment Share on other sites More sharing options...
Ashley Posted August 23, 2011 Author Share Posted August 23, 2011 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! Link to comment Share on other sites More sharing options...
HartleySan Posted August 23, 2011 Share Posted August 23, 2011 Good to hear, Ashley. Good luck with your site. Link to comment Share on other sites More sharing options...
Larry Posted August 24, 2011 Share Posted August 24, 2011 And thanks, Ashely, for the nice words on the book! Link to comment Share on other sites More sharing options...
Recommended Posts