[RESOLVED] Mysql Update from $ mystery

davidosullivan

Hey there,

I am trying to get Mysql to update a field in a table to nothing (i.e. delete the fields contents).

I am using this code:

switch ($_POST['action'])
{
case "Delete Image":

$sql = "UPDATE properties SET $image= 'NULL' WHERE id = $id";
$result = mysql_query($sql) or die(mysql_error());
break;

	case "Upload Image";

	break;

I have used the same elsewhere and i know from print_r ing around that the values I want for $image are coming through as are the $id. The reason for $image are that the user is chosing from a bunch of delete buttons that have an 'image' name and the value of the table field to delete in them. I was hoping to not have to write a script for every delete button on the site and just send any image deletes here.

The error I am getting though is:

'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 '= 'NULL' WHERE id =' at line 1'

but I have tried it every which way and can't work out whats wrong. Its not adding the value of $image basically and changing the ''s around or changing 'NULL' to 'Fred Flintstone' doesn't make any difference either.

Please help someone, I just can't see what I am doing wrong...

bpat1434

Umm... where you have $image needs to be a column name... not the value of the column...

So your update needs to be:

UPDATE properties SET image_url='NULL' WHERE id='$id'

davidosullivan

Ahh bugger so I can't set the url from the string then? thats what I am trying to do. I am trying to select the column name based on which button the user presses ($image) is this not possible?

bpat1434

Well, you might have to put it inside quotes to get it to work....

davidosullivan

I'm British btw. 'Bugger' means 'darn!' over here. no insult intended...

bpat1434

I figured as much.... and none was taken in the first place 🙂

davidosullivan

No joy there either.

I tried:
$sql = "UPDATE properties SET \"$image\"='NULL' WHERE id = $pid";
and $sql = "UPDATE properties SET "$image"='NULL' WHERE id = $pid";

the first one gives me this:

Array ( [pid] => 1 [image] => property_image_1 [action] => Delete Image ) 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 '""='NULL' WHERE id =' at line 1

(includes thr print_r results of $_POST)

and the second gives me PHP sulk:

Parse error: syntax error, unexpected T_VARIABLE in C:\wamp\www\andaluciandreamrentals\testing\image_transact.inc.php on line 7

Oh how I long for a wooden club and firelit cave...

davidosullivan

and:

sql = "UPDATE properties SET '$image'='NULL' WHERE id = $pid";

btw

davidosullivan

'property_image_1' is the field in my table I want to make blank (or actually reset to a default image if anyone knows how to do this). But it could be 'property_image_2' aswell, or 3, or 4.
Should I just do an else if thing like:

if ($_POST['image']='property_image_1')
{ $sql = "UPDATE properties SET property_image_1='NULL' WHERE id = $pid";

for each button? That just seems a bit clunky to me.

bpat1434

Try concatenation:

$sql = "UPDATE properties SET ".$image."='NULL' WHERE id = $pid";

davidosullivan

jees, even that gives me:

UPDATE properties SET property_image_1='NULL' WHERE id =
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 '' at line 1

(including a print_r of $sql)its obviously not accepting anything with a $ infront of it why could that be?

davidosullivan

very curious,

without quotes around null I get;

UPDATE properties SET ".$image."=NULL WHERE id = $pidYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '".$image."=NULL WHERE id = $pid' at line 1

with quotes I get PHP sulk....

davidosullivan

Thanks very much for your help by the way bpat, you are a nominee for the Mother Teressa Award as far as I'm concerned...

bpat1434

nominee for the Mother Teressa Award as far as I'm concerned...

Mother Theresa (proper spelling) was not actually a good lady... she was very prejudice and such. But that's another story....

Let me do a little testing on my end here.... what PHP version are you using?

~Brett

davidosullivan

I am using PHP 5 and mySQL4, my webhost uses PHP 4.x but if I switch wamp to use PHP 4 then MySQL doesn't work with my include $conn methods where as the webhosts one works fine. I know I should use 4 but I can't get around the problem so can't test anything

davidosullivan

Thanks for the spelling and history lesson. I was, of course, referring to Mother Teressa of Ipswitch who has been nominated for a sainthood for the selfless livelong dedication she has shown to helping computer programmers in need from across the globe. She is a good lady.

Blulagoon

davidosullivan wrote:
jees, even that gives me:

UPDATE properties SET property_image_1='NULL' WHERE id =
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 '' at line 1

(including a print_r of $sql)its obviously not accepting anything with a $ infront of it why could that be?

Am I missing something, or is this accepting the $image string (property_image_1) but failing because there is nothing set for id ?

I appreciate it might be a problem this far down the line, and I don't know exactly what you are trying to achieve, but if you are still having problems you might want to reconsider your database design and maybe achieve what you want to achieve through a join of two or more tables ?

Blu

davidosullivan

nope the id seems to be coming through in the $_POST as is the information I want $image to contain. Whats happening is that MySQL will not decipher anything that is refered to via a $ (neither the $image nor the $id). On other pages in my site its working and I copied and adjusted this code of working pages. Hence the mystery of the thread title.

On the occasion you are quoting above, I had typed in 'property_image_1' rather than typing in the $image that contains that information. but I'd like the field property_image_1 to be able to change as well as the id so I can delete user selected images from user selected properties.

Maybe I should be using a seperate table for the image refrences. I'm not sure. At the moment I have 5 fields in the properties table that contain links to 5 images (which I can specifically select owning to the field name- allowing there to be a 'mian' image). I could have an images table where I had a property id and pic id and picture link. Would this be better? I am planning on having 5 pictures for lots of things, i.e. facilities, regions etc so all these would need seperate image tables of their own, where as at the moment I am containing the links in fields in the 'facilities' table, 'regions' table etc.

bpat1434

Well funnily enough I did some testing, and it works!!!!

mySQL Setup

CREATE TABLE `davidosullivan` (
  `id` int(11) NOT NULL auto_increment,
  `prod_image_1` text,
  `prod_image_2` text,
  `prod_image_3` text,
  `prod_title` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `davidosullivan` (`id`, `prod_image_1`, `prod_image_2`, `prod_image_3`, `prod_title`) VALUES (1, 'images/image1.gif', 'images/image2.gif', 'images/overview.jpg', 'Air'),
(2, 'images/gip1.jpg', 'images/gip2.gif', 'images/theGipper.png', 'The Gipper'),
(3, 'images/image14.gif', 'images/theClaw.jpg', 'images/theClaw2.jpg', 'The Claw');

The PHP & Form:

<?php

$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('database_name', $conn);

showTable();

$dd_query = "SELECT id, prod_title FROM davidosullivan ORDER BY id ASC";
$dd_res = mysql_query($dd_query);

echo '
	<tr>
		<form action="'.$_SERVER['PHP_SELF'].'" method="post">
		<td colspan="2">
			<select name="prod_id">';
while($row = mysql_fetch_array($dd_res))
{
	echo '
				<option value="'.$row['id'].'">'.$row['prod_title'].'</option>';
}
echo '
			</select>
		</td>';
echo '
		<td><input type="submit" name="product_image" value="prod_image_1"></td>
		<td><input type="submit" name="product_image" value="prod_image_2"></td>
		<td><input type="submit" name="product_image" value="prod_image_3"></td>
		</form>
	</tr>
</table>';

if(isset($_POST) && !empty($_POST['prod_id']))
{
	$query = "UPDATE davidosullivan SET ".$_POST['product_image']."=NULL WHERE id='".$_POST['prod_id']."'";
	echo '<pre>'.$query.'</pre>';
	$result = mysql_query($query);
	if(!$result){ echo mysql_error(); }
	if(mysql_affected_rows($conn) > 0)
	{
		echo 'Query Successful!!';
	}
	else
	{
		echo 'Query Failed!!';
	}
}

function showTable()
{
	echo '
<table>
	<tr>
		<th>ID</th><th>Product Name</th><th>Image 1</th><th>Image 2</th><th>Image 3</th>
	</tr>';
	$query = "SELECT * FROM davidosullivan ORDER BY id ASC";
	$result = mysql_query($query);
	while($row = mysql_fetch_array($result))
	{
		echo '
	<tr>
		<td>'.$row['id'].'</td>
		<td>'.$row['prod_title'].'</td>
		<td>'.$row['prod_image_1'].'</td>
		<td>'.$row['prod_image_2'].'</td>
		<td>'.$row['prod_image_3'].'</td>
	</tr>';
	}
}

?>

Now, when I do the update, it updates just fine!!!

davidosullivan

ok bpat you are a dude of great magnitude!

Let me see if I can incorporate that/ work out why mine isn't working from yours. (Yours, by some strange alchemy, works fine by the way).

get the champers ready...