Retrieving last entry???

[digi duck]

HI everyone, my site (http://gamesigs.co.uk) lets users create their own image with text placed on it. I have successfully managed to automatically store the information of the image into a db using this code:


mysql_connect('db4.awardspace.com','-----','----') or die( 'Unable to connect to Mysql');
mysql_select_db("joshuacottom_db") or die( "Unable to select database");
$img = "color={$_GET[color]}&name={$_GET[name]}&line2={$GET_[line2]}
&centre={$_GET[centre]}&x={$_GET[x]}&y={$_GET[y]}&angle=$_GET[angle]
&font={$_GET[font]}&size={$_GET[size]}&color_r={$_GET[color_r]}
&color_g={$_GET[color_g]}&color_b={$_GET[color_b]}";
$sql = 'TRUNCATE TABLE `sig`';
$sql = "INSERT INTO sig(user, image) VALUES('$name', '".mysql_real_escape_string($img)."')";
mysql_query($sql) or die("Error: ".mysql_error()."<br>SQL: $sql");
mysql_close();
?>


The image is created with gd library and the user is just called the name the enter in.

The above part works fine apart from it not emptying the table before adding a new one in.

Below is the code i use to retrieve the last one created...


$sql = "SELECT * FROM sig ORDER BY user DESC LIMIT 1";
$query = mysql_query($sql) or die("Error: ".mysql_error()."<br>SQL: $sql");
while($a = mysql_fetch_assoc($query)){
$img_path = $a['image'];
echo "<img src=\"creation.php?$img_path\"><br>";
}

mysql_close();

This works however it doesnt always show the last one.

Can you please help me by:
1) telling me how to clear the table when i put a new result in so there is always only 1.
2)If this cant be done how can i add a time or id field and then retrieve them by this?

Thankyou

[guelphdad]

the order by user desc will order your users but won't return the last item from that user, it would return any row.

so if you have Todd, Bill and Jim you are asking for Todd but not specifying which row.

You should use a timestamp column and that will automatically have the value inserted upon an INSERT or updated on an UPDATE to the row when anything in the row is updated.

From there, you can retrieve the most recent timestamp per user.

this item (http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml) will tell you how to retrieve the latest item per group.

if you then only want to compare that to the current user (say Todd is logged into your system), then you would add a condition of something like

user='Todd'

[ess]

You can get the last entry without using a timestamp by retrieving records submitted by a given user ...and ordering by table id . for example,

$sql = "SELECT * FROM `sig` where `user` = '". $user_name . "' ORDER BY id DESC LIMIT 1";

Cheers,
Ess

[guelphdad]

same idea though, you are including a column and ordering by that column.