woodplease Posted September 20, 2010 Share Posted September 20, 2010 i have two tables, and i want to display data from the most recent entry of the two(i.e. display just one entry). i'm joining together 2 tables so that i can get this entry.Both tables contain the date, so i'm ordering the results of the query so that the most recent comes first. My tables are: TOPIC topic_id topic _name section_sub_id date POST post_id post_name section_sub_id date I'm using the following code to get the last result, but nothing is being echoed $join =mysql_query("SELECT topic.*, post.* FROM topic LEFT JOIN post ON topic.section_sub_id=post.section_sub_id WHERE post.section_sub_id = " .$row2['section_sub_id']. " ORDER BY topic.date") or die("Select Error :" . mysql_error()); $latest= mysql_fetch_row($join); echo $latest['post.date']; i'm not sure if there is something wrong with the sql, or with the php. ANy help would be great Quote Link to comment Share on other sites More sharing options...
litebearer Posted September 20, 2010 Share Posted September 20, 2010 LIMIT 1 http://www.phpsimplicity.com/tips.php?id=1 Quote Link to comment Share on other sites More sharing options...
Miss_Rebelx Posted September 20, 2010 Share Posted September 20, 2010 Well I suggest running your SQL query through with PHPMyAdmin (or otherwise, directly to your database) to make sure it's not an SQL issue. Also I beleive that mysql_fetch_row() only returns a numerical array - hense you can't access it with field names like post.date. Try mysql_fetch_assoc() instead. Quote Link to comment Share on other sites More sharing options...
the182guy Posted September 20, 2010 Share Posted September 20, 2010 The problem here is you're using mysql_fetch_row which does not return the result with text keys as you're trying to use (post.date). It returns the result as numerical keys only. Change it to mysql_fetch_assoc() if you want to access the result like that. As litebearer says use LIMIT 1, but that will not change anything as far as your script is concerned, it will simply increase performance and code readability. Quote Link to comment Share on other sites More sharing options...
vungee Posted September 20, 2010 Share Posted September 20, 2010 You can dump the variable $latest to the screen using the following: var_dump($latest); Without functional changes you can get the entry using the following: echo $latest[3]; Quote Link to comment Share on other sites More sharing options...
woodplease Posted September 20, 2010 Author Share Posted September 20, 2010 right, i've run the sql through phpmyadmin, and it seems fine, as it didnt give any errors. i've also changed it so that its mysql_fetch_assoc(), but still nothing is being echoed. I've also included the LIMIT constraint. Still nothing is being echoed. i'm not sure whether or not i am echoing out the right thing ($latest['post.date']), as i want to echo out the most rescent date. Alss, the date is stored as integers, and converted into date format afterwards My code currently looks like this: $join =mysql_query("SELECT topic.*, post.* FROM topic LEFT JOIN post ON topic.section_sub_id=post.section_sub_id WHERE post.section_sub_id = " .$row2['section_sub_id']. " ORDER BY topic.date LIMIT 1") or die("Select Error :" . mysql_error()); $latest= mysql_fetch_assoc($join); echo $latest['post.date']; Quote Link to comment Share on other sites More sharing options...
the182guy Posted September 20, 2010 Share Posted September 20, 2010 When you ran it through phpmyadmin was the field called post.date or just date? Quote Link to comment Share on other sites More sharing options...
woodplease Posted September 20, 2010 Author Share Posted September 20, 2010 which part of the query are talking about? Quote Link to comment Share on other sites More sharing options...
Miss_Rebelx Posted September 20, 2010 Share Posted September 20, 2010 I suggest doing the dump like vungee suggested above. Then you should know the name of the fields that your SQL query is returning. Your SQL statement itself seems to work since you tested it in PHPMyAdmin. Now you need to make sure your echoing it properly - and as the182guy suggests it's probably that your field is called date when it's returned from the query. To be sure, do the dump. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.