PHP MySQL update command not working

silverball

I have 2 tables in a MySQL database which are

'users' and
'tasks'

The 'users' table has 15 coloumns named 'task_name1' to 'task_name15'.

In the below PHP code, i need to update the information in the 'users' table for each of the columns
but the sql query for update (in the below code) is not working in PHP.

Can anyone help please to fix this problem. Thanks

##############

PHP Code below...

##############

If the value for a task is equal to "00001", then update the relevant column in the users table to $this_id (which i get from the user)

if($task1 == "00001") { mysql_query("UPDATE users SET task_name1 = '$this_id' WHERE id='$id'"); }

else if($task2 == "00001") { mysql_query("UPDATE users SET task_name2 = '$this_id' WHERE id='$id'"); }

else if($task3 == "00001") { mysql_query("UPDATE users SET task_name3 = '$this_id' WHERE id='$id'"); }

else if($task4 == "00001") { mysql_query("UPDATE users SET task_name4 = '$this_id' WHERE id='$id'"); }

else if($task5 == "00001") { mysql_query("UPDATE users SET task_name5 = '$this_id' WHERE id='$id'"); }

else if($task6 == "00001") { mysql_query("UPDATE users SET task_name6 = '$this_id' WHERE id='$id'"); }

else if($task7 == "00001") { mysql_query("UPDATE users SET task_name7 = '$this_id' WHERE id='$id'"); }

else if($task8 == "00001") { mysql_query("UPDATE users SET task_name8 = '$this_id' WHERE id='$id'"); }

else if($task9 == "00001") { mysql_query("UPDATE users SET task_name9 = '$this_id' WHERE id='$id'"); }

else if($task10 == "00001") { mysql_query("UPDATE users SET task_name10 = '$this_id' WHERE id='$id'"); }

else if($task11 == "00001") { mysql_query("UPDATE users SET task_name11 = '$this_id' WHERE id='$id'"); }

else if($task12 == "00001") { mysql_query("UPDATE users SET task_name12 = '$this_id' WHERE id='$id'"); }

else if($task13 == "00001") { mysql_query("UPDATE users SET task_name13 = '$this_id' WHERE id='$id'"); }

else if($task14 == "00001") { mysql_query("UPDATE users SET task_name14 = '$this_id' WHERE id='$id'"); }

else if($task15 == "00001") { mysql_query("UPDATE users SET task_name15 = '$this_id' WHERE id='$id'"); }

NogDog

Where do the $task1 through $task15 variables get set? Are you sure any/all of them exist, and if so any/all of them have the exact string value of "00001"?

silverball

NogDog wrote:
Where do the $task1 through $task15 variables get set? Are you sure any/all of them exist, and if so any/all of them have the exact string value of "00001"?

Hi Mate.

Apologies for my intial post.
I actually posted a correct working code in my first post above :eek:

The code that i am trying to fix is as below

  // YOU NEED TO MAKE THE BELOW CODE WORK...
		  // currently it is NOT updating the 'task' (in the 'task' table).. it is making new 'id' in task table (this is not good)... we want same 'id'... but below code not properly working
		  // we want the below code to 'update' the entry in the 'task' table... important to have the same 'id' primary kep as before we press the update/modify button..



	  $id= $_GET['id']; //this is the task 'id' primary key (from the task table)... this is good
		echo $_GET['id']; 
		$priority=$_GET['priority'];
		$starttime =$_POST['$starttime'];
	  mysql_query("UPDATE tasks SET taskname='$taskname' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET taskdescription='$taskdescription' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET startdate='$startdate' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET starttime='$starttime' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET enddate='$enddate' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET endtime='$endtime' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET priority='$priority' WHERE id='$id';");
	  mysql_query("UPDATE tasks SET status='$status' WHERE id='$id';");
	}
?>

Weedpacket

            $starttime =$_POST['$starttime'];

Well you have an extra '$' in there.... I'm going to guess that the other similar variables ($endtime, $enddate, etc.) are handled similarly.

Oh, and you can (and should) do all of those with a single query.
"UPDATE tasks SET taskname='$taskname', taskdescription='$taskdescription'.....WHERE...".

And unless you trust whatever is sending you this data completely, you should sanitise it first (see the [man]mysql_query[/man] page for some examples and pointers).

Clarkey_Boy

I have only read this thread briefly, but from what I understand it can become a whole lot more efficient. For instance, each user at the moment is limited to 15 tasks. Try adding a table which has 2 fields - userID and taskID. Each user can have unlimited tasks that way, and the table links to the task via the taskID and the user table via the userID.

Also, I dont think the update statements will work (they may do, but I think it will set taskname = "$taskname" as it is in single quotes - it will not set taskname = the value represented by $taskname).

You can also have more than one field updated in an update statement. ie the following...

mysql_query("UPDATE tasks SET taskname='$taskname' WHERE id='$id';");
mysql_query("UPDATE tasks SET taskdescription='$taskdescription' WHERE id='$id';");
mysql_query("UPDATE tasks SET startdate='$startdate' WHERE id='$id';");
mysql_query("UPDATE tasks SET starttime='$starttime' WHERE id='$id';");
mysql_query("UPDATE tasks SET enddate='$enddate' WHERE id='$id';");
mysql_query("UPDATE tasks SET endtime='$endtime' WHERE id='$id';");
mysql_query("UPDATE tasks SET priority='$priority' WHERE id='$id';");
mysql_query("UPDATE tasks SET status='$status' WHERE id='$id';");

...becomes...

mysql_query("UPDATE tasks SET taskname='$taskname', taskdescription='$taskdescription', startdate='$startdate', starttime='$starttime', enddate='$enddate', endtime='$endtime', priority='$priority', status='$status' WHERE id='$id'");

Hope this helps,

Clarkey

alchemistGM

This works:

$result = mysql_query("UPDATE `siteData` SET aboutus='$aboutus' WHERE id='$id'");
$result = mysql_query("UPDATE `siteData` SET visiontitle='$visiontitle' WHERE id='$id'");
$result = mysql_query("UPDATE `siteData` SET vision='$vision' WHERE id='$id'");
$result = mysql_query("UPDATE `siteData` SET missiontitle='$missiontitle' WHERE id='$id'");
$result = mysql_query("UPDATE `siteData` SET mission='$mission' WHERE id='$id'");

This doesn't:

$result = mysql_query("UPDATE `siteData` SET aboutus='$aboutus', visiontitle='$visiontitle', vision='$vision', missiontitle='$missiontitle', mission='$mission' WHERE id='$id'");

No idea why, I would love to use the all-on-one-line version. It simply doesn't work.

"Fear of small things is no big deal"

dagon

alchemistGM, don't hijack existing threads, start your own.

alchemistGM

Thanks Dagon,

I agree, but was not hijacking - I was responding to Clarkey Boy who suggested correctly to put the queries all into a single query. I was doing exactly that with my own code but had spent several hours trying to get my single all-at-once query to work, and it wasn't, yet when broken into singles they were working.

But yes, I was talking about my own code, rather than the original post, and so my comment might be unhelpful unless others shared any of the same issues, which I wasn't sure.

For me, turns out I had an apostrophe in one of the data, and so individually all but one of them worked. But all together the whole query failed. Using mysql_real_escape_string fixed the problem.

savi16

Hi All,

Clarkey Boy;10854939 wrote:
Also, I dont think the update statements will work (they may do, but I think it will set taskname = "$taskname" as it is in single quotes - it will not set taskname = the value represented by $taskname).

It will work, was under the same imression as you, but checked and it all worked 🙂

Silverball, try pasting the code query your script generates into i.e. phpMyAdmin and see what happens.

Had a similar issue, and filtering the input data plus swapping id='$id' to id=$id worked.

Regards

bradgrafelman

@: You do realize that you've replied to a thread that was started over 2.5 years ago... right?

savi16

LOL :glare:
I'm not the only 1 tho 🙂