php mysql modify row data

Bananna man · 23-06-2006 06:17PM #1

Hey,

Im trying to write a script that will alter data stored in a row in a mysql database. Is this how you write the code if you want to change multiple fields at the same time or do i have to do seperate queries?

Thanks

[PHP] $sql = "UPDATE washtec_job_information
SET job_number = ".$job_number."
SET site_code = ".$site_code."
SET site_name = ".$site_name."
SET equip_code = ".$equip_code."
SET eng = ".$eng."
SET l_time = ".$l_time."
SET l_date = ".$l_date."
SET eq_op = ".$eq_op."
SET fault_description = ".$fault_description."
SET station = ".$station."
WHERE row_id = ".$row_id."
";[/PHP]

ikoonman · 23-06-2006 11:56PM

Your SQL query should be something like:

UPDATE washtec_job_information SET job_number=1, site_code=2, ... WHERE row_id=4

Naturally I've omitted some fields from your example. Just convert this to your inline code.

Is this what you are looking for?

Gosh · 23-06-2006 11:59PM

You only need one SET, text fields should be enclosed in single quotes and a comma after the field values

$sql = "UPDATE washtec_job_information
                            SET job_number = '" . $job_number . "',
                                site_code = '" . $site_code . "',
                                site_name = '" . $site_name . "',
                                equip_code = '" . $equip_code . "',
                                eng = '" . $eng . "'";
                                etc
                                etc

Bananna man · 24-06-2006 10:20AM

Nice one lads, i noticed i was missing the commas yestaerday but when i tried that it still didnt work. I have however left out the bloody ' around the strings..... again!! Me learning PHP is like training a chimp learning brain surgery.

Bananna man · 24-06-2006 11:27AM

Arrrgggghhhhhh :mad:

Now im getting this 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 'SET site_code = 'STAT-56', SET site

I have changed the php code to this:
[PHP] $sql = "UPDATE washtec_job_information
SET job_number = ".$job_number.",
SET site_code = '".$site_code."',
SET site_name = '".$site_name."',
SET equip_code = '".$equip_code."',
SET eng = '".$eng."',
SET l_time = '".$l_time."',
SET l_date = ".$l_date.",
SET eq_op = '".$eq_op."',
SET fault_description = '".$fault_description."',
SET station = '".$station."'
WHERE row_id = ".$row_id."
"; [/PHP]

Any ideas?

is_that_so · 24-06-2006 11:30AM

Is job number an integer or a literal? If it's not an integer you will need single quotes. Always good to have a quick

print $sql;
exit;
in your code to see what you are trying to insert/update etc.

Webmonkey · 24-06-2006 11:43AM

As said above you only use set once.
Try this:

[PHP]
$sql = "UPDATE washtec_job_information
SET job_number = '".$job_number."',
site_code = '".$site_code."',
site_name = '".$site_name."',
equip_code = '".$equip_code."',
eng = '".$eng."',
l_time = '".$l_time."',
l_date = ".$l_date.",
eq_op = '".$eq_op."',
fault_description = '".$fault_description."',
station = '".$station."'
WHERE row_id = '".$row_id."'
";
[/PHP]

Bananna man · 24-06-2006 11:55AM

Thanks lads, i didnt see that about putting in SET jusy the once. Got it workin now.

php mysql modify row data

Comments