[SOLVED] Still can't get this 'on duplicate key update' to work

[pcbguy]

I tried to use it and it gives me the "file has been uploaded message" from the bottom but when I check my database nothing has changed.

 

The help here has been awesome. Thanks everyone for your help so far.

 

The 'position' is the only unique key and is also the primary key.

 

<?php

//This is the directory where the images will be saved
$target="images/";
$target=$target.basename($_FILES['picture']['name']);

//This gets all the other information from the form
$position=$_POST['position'];
$year=$_POST['year'];
$make=$_POST['make'];
$model=$_POST['model'];
$price=$_POST['price'];
$description=$_POST['description'];
$picture=($_FILES['picture']['name']);

//Connects to Database
include "connect.php";

//Writes the information to the database
$query = "INSERT INTO for_sale (position,year,make,model,price,description,picture)
                      VALUES ('$position','$year','$make','$model','$price','$description','$picture')
          ON DUPLICATE KEY UPDATE
                      year='$year', make='$make', model='$model',
                      price='$price', description='$description', picture='$picture'";
mysql_query($query);

//Writes photo to the server
if(move_uploaded_file($_FILES['picture']['tmp_name'],$target))
{

	//Tells you if its all okay
	echo "The file" .basename($_FILES['uploadedfile']['name']). "has been uploaded, and your information has been added to the directory";
}
else{

	//Gives and error if its not
	echo "Sorry, there was a problem uploading your file.";
}
?>	

[trq]

Change....

 

mysql_query($query);

 

to

 

mysql_query($query) || die(mysql_error());

 

What do you get?

[pcbguy]

I get : "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE year='2007', mak"

 

Do I have the wrong MySQL version?

[pcbguy]

Where do I look on phpmyadmin to see the version of MySQL?

[revraz]

Missing SET after UPDATE?

[pcbguy]

Like this?

$query = "INSERT INTO for_sale (position,year,make,model,price,description,picture)
                      VALUES ('$position','$year','$make','$model','$price','$description','$picture')
          ON DUPLICATE KEY UPDATE SET
                      year='$year', make='$make', model='$model',
                      price='$price', description='$description', picture='$picture'";
mysql_query($query) || die(mysql_error());

 

 

 

 

Got same error. "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE SET year='2007',"

[pcbguy]

I seem to vaguely remember picking MySQL 4.0 when I set up the database on my host. Not sure why I would have picked 4.0 but I think I did. Maybe I need to redo it with a 5.0.

[revraz]

When you first go into phpmyadmin, it should say at the very top what version of phpmyadmin and mysql is running.

[pcbguy]

I found it. It is a MySQL 4.0 database. I believe the "ON DUPLICATE..." only works on 4.1 and up. I am setting up a new one with a 5.0 database. Hopefully that will solve the problem.

 

 

[pcbguy]

Does it matter if I use a MyISAM or InnoDB???

[pcbguy]

I changed the server to a MySQL 5.0 database. I still cannot get it to work. I get "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET year='2006', make='Mercedes', model='"

 

Any ideas?

 

<?php

//This is the directory where the images will be saved
$target="images/";
$target=$target.basename($_FILES['picture']['name']);

//This gets all the other information from the form
$position=$_POST['position'];
$year=$_POST['year'];
$make=$_POST['make'];
$model=$_POST['model'];
$price=$_POST['price'];
$description=$_POST['description'];
$picture=($_FILES['picture']['name']);

//Connects to Database
include "connect.php";

//Writes the information to the database
$query = "INSERT INTO forsale (position,year,make,model,price,description,picture)
                      VALUES ('$position','$year','$make','$model','$price','$description','$picture')
          ON DUPLICATE KEY UPDATE SET
                      year='$year', make='$make', model='$model',
                      price='$price', description='$description', picture='$picture'";
mysql_query($query) || die(mysql_error());

//Writes photo to the server
if(move_uploaded_file($_FILES['picture']['tmp_name'],$target))
{

	//Tells you if its all okay
	echo "The file" .basename($_FILES['uploadedfile']['name']). "has been uploaded, and your information has been added to the directory";
}
else{

	//Gives and error if its not
	echo "Sorry, there was a problem uploading your file.";
}
?>	

[pcbguy]

Took the SET out and it worked perfectly. Whew!!