HI everyone, my site 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:

PHP 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...

PHP Code:

$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

You can do an:

DELETE FROM sig WHERE user='$name'

Before saving your pic. It would work, but it's ugly.
Plus, sa you use a varchar field as key (I think), it's way from being an ideal case regarding the indexes.
You'd better use a numeric field as the primary key, which will link to another record holding the user name, username, and so on...

To be optimal, what you should do here, is re-create this table, with a primary key on the user field.
The primary key will prevent the database to allow more than 1 sig for a given user.
That way, no need to delete old records, they cannot exist.
Then, use a query like this:

INSERT INTO sig 
  (user, image) 
VALUES
  ('$name','$img')
ON DUPLICATE KEY UPDATE image='$img'

Basically, you always try to insert data, but when the primary key will prevent the insertion (because a sig for this user already exists), it will automatically make an update of the existing record with your new image.

I dont know how portable this query can be !
I know it works for mysql5.0+, but I don't know if mysql4 can handle this syntax.
I don't know neither if the sql98 standard include this, so it may be proprietary for mysql.
I let you do the tests.

Originally Posted by tripy

Then, use a query like this:

INSERT INTO sig 
  (user, image) 
VALUES
  ('$name','$img')
ON DUPLICATE KEY UPDATE image='$img'

Basically, you always try to insert data, but when the primary key will prevent the insertion (because a sig for this user already exists), it will automatically make an update of the existing record with your new image.

I dont know how portable this query can be !

4.1.0+ is required according to http://dev.mysql.com/doc/refman/4.1/...duplicate.html