display just one result

[woodplease]

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

[litebearer]

LIMIT 1

 

http://www.phpsimplicity.com/tips.php?id=1

[Miss_Rebelx]

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.

[the182guy]

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.

[vungee]

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];

[woodplease]

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'];

 

[the182guy]

When you ran it through phpmyadmin was the field called post.date or just date?

[woodplease]

which part of the query are talking about?

[Miss_Rebelx]

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.