Updating a MySQL table with UPDATE - Big Prolems...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Updating a MySQL table with UPDATE - Big Prolems...

Post by bluenote »

Hello alltogether,

i have BIG BIG problems with the UPDATE function:

Code: Select all

<?php
require_once('authenticate.inc');
require_once('connect.inc');

MYSQL_CONNECT($hostname,$username,$password) OR DIE("Database connection failed.");

@mysql_select_db("$dbName") or die("Database not found.");

switch ($login) {
	
	case '01': $uquery = "UPDATE staff SET staff.s01 = '".$sn01."', staff.s02 = '".$sn02."', staff.s03 = '".$sn03."', staff.s05 = '".$sn05."', staff.s06 = '".$sn06."', staff.s07 = '".$sn07."', staff.s08 = '".$sn08."', staff.s09 = '".$sn09."', staff.s10 = '".$sn10."', staff.s11 = '".$sn11."', staff.s12 = '".$sn12."', staff.s13 = '".$sn13."', staff.s21 = '".$sn21."', staff.s22 = '".$sn22."', staff.s23 = '".$sn23."', staff.s25 = '".$sn25."', staff.s26 = '".$sn26."', staff.s35 = '".$sn35."', staff.s39 = '".$sn39."', staff.s49 = '".$sn49."', staff.s50 = '".$sn50."', staff.s51 = '".$sn51."', staff.s52 = '".$sn52."', staff.s53 = '".$sn53."', staff.s54 = '".$sn54."', staff.s55 = '".$sn55."', s56 = '".$sn56."', staff.s58 = '".$sn58."', staff.s59 = '".$sn59."', staff.s60 = 'done', staff.s64 = '".$sn64."' WHERE staff.staff.s04 = '".$sc04."' AND staff.id = '".$cid."'"; break;
	case '02': $uquery = "UPDATE staff SET staff.s01 = '".$sn01."', staff.s02 = '".$sn02."', staff.s03 = '".$sn03."', staff.s05 = '".$sn05."', staff.s06 = '".$sn06."', staff.s07 = '".$sn07."', staff.s08 = '".$sn08."', staff.s09 = '".$sn09."', staff.s10 = '".$sn10."', staff.s11 = '".$sn11."', staff.s12 = '".$sn12."', staff.s13 = '".$sn13."', staff.s21 = '".$sn21."', staff.s22 = '".$sn22."', staff.s23 = '".$sn23."', staff.s25 = '".$sn25."', staff.s26 = '".$sn26."', staff.s35 = '".$sn35."', staff.s39 = '".$sn39."', staff.s49 = '".$sn49."', staff.s50 = '".$sn50."', staff.s51 = '".$sn51."', staff.s52 = '".$sn52."', staff.s53 = '".$sn53."', staff.s54 = '".$sn54."', staff.s55 = '".$sn55."', s56 = '".$sn56."', staff.s58 = '".$sn58."', staff.s59 = '".$sn59."', staff.s60 = 'done', staff.s64 = '".$sn64."' WHERE staff.staff.s04 = '".$sc04."' AND staff.id = '".$cid."'"; break;
	case '03': $uquery = "UPDATE staff SET staff.s01 = '".$sn01."', staff.s02 = '".$sn02."', staff.s03 = '".$sn03."', staff.s05 = '".$sn05."', staff.s06 = '".$sn06."', staff.s07 = '".$sn07."', staff.s08 = '".$sn08."', staff.s09 = '".$sn09."', staff.s10 = '".$sn10."', staff.s11 = '".$sn11."', staff.s12 = '".$sn12."', staff.s13 = '".$sn13."', staff.s21 = '".$sn21."', staff.s22 = '".$sn22."', staff.s23 = '".$sn23."', staff.s25 = '".$sn25."', staff.s26 = '".$sn26."', staff.s35 = '".$sn35."', staff.s39 = '".$sn39."', staff.s49 = '".$sn49."', staff.s50 = '".$sn50."', staff.s51 = '".$sn51."', staff.s52 = '".$sn52."', staff.s53 = '".$sn53."', staff.s54 = '".$sn54."', staff.s55 = '".$sn55."', s56 = '".$sn56."', staff.s58 = '".$sn58."', staff.s59 = '".$sn59."', staff.s60 = 'in progress', staff.s64 = '".$sn64."' WHERE staff.staff.s04 = '".$sc04."' AND staff.id = '".$cid."'"; break;
	case '04': $uquery = "UPDATE staff SET staff.s01 = '".$sn01."', staff.s02 = '".$sn02."', staff.s03 = '".$sn03."', staff.s05 = '".$sn05."', staff.s06 = '".$sn06."', staff.s07 = '".$sn07."', staff.s08 = '".$sn08."', staff.s09 = '".$sn09."', staff.s10 = '".$sn10."', staff.s11 = '".$sn11."', staff.s12 = '".$sn12."', staff.s13 = '".$sn13."', staff.s21 = '".$sn21."', staff.s22 = '".$sn22."', staff.s23 = '".$sn23."', staff.s25 = '".$sn25."', staff.s26 = '".$sn26."', staff.s35 = '".$sn35."', staff.s39 = '".$sn39."', staff.s49 = '".$sn49."', staff.s50 = '".$sn50."', staff.s51 = '".$sn51."', staff.s52 = '".$sn52."', staff.s53 = '".$sn53."', staff.s54 = '".$sn54."', staff.s55 = '".$sn55."', s56 = '".$sn56."', staff.s58 = '".$sn58."', staff.s59 = '".$sn59."', staff.s60 = 'in progress', staff.s64 = '".$sn64."' WHERE staff.staff.s04 = '".$sc04."' AND staff.id = '".$cid."'"; break;}
	
	$uerg = MYSQL_QUERY($uquery);	
?>
authenticate.inc and connect.inc do their work, and my vars are also initialized withe the correct values... but :cry: NOTHING :( happens! The code causes no log errors, the ZEND Debug server does not complain, and the database is not updated.

The mysql err.log is also empty:

040129 08:30:16 mysqld started
/export/home/local/mysql-3.23.53-sun-solaris2.7-sparc/bin/mysqld: ready for connections

bluenote# date
Thu Jan 29 14:33:42 MET 2004

Does anyone have a tip?
-bluenote

Help would be great.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

try removing the @ from the mysql_select_db and see if you get any errors.
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

I have done so; then, i ran the UPDATE script again. This is the extract of the mysql err.log afterwards:

bluenote# date
Thu Jan 29 14:44:13 MET 2004
bluenote# pg bluenote.err
040129 08:30:16 mysqld started
/export/home/local/mysql-3.23.53-sun-solaris2.7-sparc/bin/mysqld: ready for connections

NOTHING again :cry: :cry: !

I just can't find any error in my code even after trying to do so for 4 days now...

Greez,
-bluenote
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

Instead of the switch{} block posted above, i've also tried

Code: Select all

<?php
switch ($login) {
	
	case '01': $uquery = "UPDATE staff SET staff.s01 = '{$sn01}', staff.s02 = '{$sn02}', staff.s03 = '{$sn03}', staff.s05 = '{$sn05}', staff.s06 = '{$sn06}', staff.s07 = '{$sn07}', staff.s08 = '{$sn08}', staff.s09 = '{$sn09}', staff.s10 = '{$sn10}', staff.s11 = '{$sn11}', staff.s12 = '{$sn12}', staff.s13 = '{$sn13}', staff.s21 = '{$sn21}', staff.s22 = '{$sn22}', staff.s23 = '{$sn23}', staff.s25 = '{$sn25}', staff.s26 = '{$sn26}', staff.s35 = '{$sn35}', staff.s39 = '{$sn39}', staff.s49 = '{$sn49}', staff.s50 = '{$sn50}', staff.s51 = '{$sn51}', staff.s52 = '{$sn52}', staff.s53 = '{$sn53}', staff.s54 = '{$sn54}', staff.s55 = '{$sn55}', s56 = '{$sn56}', staff.s58 = '{$sn58}', staff.s59 = '{$sn59}', staff.s60 = 'done', staff.s64 = '{$sn64}' WHERE staff.staff.s04 = '{$sc04}' AND staff.id = '{$cid}'"; break;
	case '02': $uquery = "UPDATE staff SET staff.s01 = '{$sn01}', staff.s02 = '{$sn02}', staff.s03 = '{$sn03}', staff.s05 = '{$sn05}', staff.s06 = '{$sn06}', staff.s07 = '{$sn07}', staff.s08 = '{$sn08}', staff.s09 = '{$sn09}', staff.s10 = '{$sn10}', staff.s11 = '{$sn11}', staff.s12 = '{$sn12}', staff.s13 = '{$sn13}', staff.s21 = '{$sn21}', staff.s22 = '{$sn22}', staff.s23 = '{$sn23}', staff.s25 = '{$sn25}', staff.s26 = '{$sn26}', staff.s35 = '{$sn35}', staff.s39 = '{$sn39}', staff.s49 = '{$sn49}', staff.s50 = '{$sn50}', staff.s51 = '{$sn51}', staff.s52 = '{$sn52}', staff.s53 = '{$sn53}', staff.s54 = '{$sn54}', staff.s55 = '{$sn55}', s56 = '{$sn56}', staff.s58 = '{$sn58}', staff.s59 = '{$sn59}', staff.s60 = 'done', staff.s64 = '{$sn64}' WHERE staff.staff.s04 = '{$sc04}' AND staff.id = '{$cid}'"; break;
	case '03': $uquery = "UPDATE staff SET staff.s01 = '{$sn01}', staff.s02 = '{$sn02}', staff.s03 = '{$sn03}', staff.s05 = '{$sn05}', staff.s06 = '{$sn06}', staff.s07 = '{$sn07}', staff.s08 = '{$sn08}', staff.s09 = '{$sn09}', staff.s10 = '{$sn10}', staff.s11 = '{$sn11}', staff.s12 = '{$sn12}', staff.s13 = '{$sn13}', staff.s21 = '{$sn21}', staff.s22 = '{$sn22}', staff.s23 = '{$sn23}', staff.s25 = '{$sn25}', staff.s26 = '{$sn26}', staff.s35 = '{$sn35}', staff.s39 = '{$sn39}', staff.s49 = '{$sn49}', staff.s50 = '{$sn50}', staff.s51 = '{$sn51}', staff.s52 = '{$sn52}', staff.s53 = '{$sn53}', staff.s54 = '{$sn54}', staff.s55 = '{$sn55}', s56 = '{$sn56}', staff.s58 = '{$sn58}', staff.s59 = '{$sn59}', staff.s60 = 'in progress', staff.s64 = '{$sn64}' WHERE staff.staff.s04 = '{$sc04}' AND staff.id = '{$cid}'"; break;
	case '04': $uquery = "UPDATE staff SET staff.s01 = '{$sn01}', staff.s02 = '{$sn02}', staff.s03 = '{$sn03}', staff.s05 = '{$sn05}', staff.s06 = '{$sn06}', staff.s07 = '{$sn07}', staff.s08 = '{$sn08}', staff.s09 = '{$sn09}', staff.s10 = '{$sn10}', staff.s11 = '{$sn11}', staff.s12 = '{$sn12}', staff.s13 = '{$sn13}', staff.s21 = '{$sn21}', staff.s22 = '{$sn22}', staff.s23 = '{$sn23}', staff.s25 = '{$sn25}', staff.s26 = '{$sn26}', staff.s35 = '{$sn35}', staff.s39 = '{$sn39}', staff.s49 = '{$sn49}', staff.s50 = '{$sn50}', staff.s51 = '{$sn51}', staff.s52 = '{$sn52}', staff.s53 = '{$sn53}', staff.s54 = '{$sn54}', staff.s55 = '{$sn55}', s56 = '{$sn56}', staff.s58 = '{$sn58}', staff.s59 = '{$sn59}', staff.s60 = 'in progress', staff.s64 = '{$sn64}' WHERE staff.staff.s04 = '{$sc04}' AND staff.id = '{$cid}'"; break;}

?>
and

Code: Select all

<?php
switch ($login) {
	
	case '01': $uquery = "UPDATE staff SET staff.s01 = '$sn01', staff.s02 = '$sn02', staff.s03 = '$sn03', staff.s05 = '$sn05', staff.s06 = '$sn06', staff.s07 = '$sn07', staff.s08 = '$sn08', staff.s09 = '$sn09', staff.s10 = '$sn10', staff.s11 = '$sn11', staff.s12 = '$sn12', staff.s13 = '$sn13', staff.s21 = '$sn21', staff.s22 = '$sn22', staff.s23 = '$sn23', staff.s25 = '$sn25', staff.s26 = '$sn26', staff.s35 = '$sn35', staff.s39 = '$sn39', staff.s49 = '$sn49', staff.s50 = '$sn50', staff.s51 = '$sn51', staff.s52 = '$sn52', staff.s53 = '$sn53', staff.s54 = '$sn54', staff.s55 = '$sn55', s56 = '$sn56', staff.s58 = '$sn58', staff.s59 = '$sn59', staff.s60 = 'done', staff.s64 = '$sn64' WHERE staff.staff.s04 = '$sc04' AND staff.id = '$cid'"; break;
	case '02': $uquery = "UPDATE staff SET staff.s01 = '$sn01', staff.s02 = '$sn02', staff.s03 = '$sn03', staff.s05 = '$sn05', staff.s06 = '$sn06', staff.s07 = '$sn07', staff.s08 = '$sn08', staff.s09 = '$sn09', staff.s10 = '$sn10', staff.s11 = '$sn11', staff.s12 = '$sn12', staff.s13 = '$sn13', staff.s21 = '$sn21', staff.s22 = '$sn22', staff.s23 = '$sn23', staff.s25 = '$sn25', staff.s26 = '$sn26', staff.s35 = '$sn35', staff.s39 = '$sn39', staff.s49 = '$sn49', staff.s50 = '$sn50', staff.s51 = '$sn51', staff.s52 = '$sn52', staff.s53 = '$sn53', staff.s54 = '$sn54', staff.s55 = '$sn55', s56 = '$sn56', staff.s58 = '$sn58', staff.s59 = '$sn59', staff.s60 = 'done', staff.s64 = '$sn64' WHERE staff.staff.s04 = '$sc04' AND staff.id = '$cid'"; break;
	case '03': $uquery = "UPDATE staff SET staff.s01 = '$sn01', staff.s02 = '$sn02', staff.s03 = '$sn03', staff.s05 = '$sn05', staff.s06 = '$sn06', staff.s07 = '$sn07', staff.s08 = '$sn08', staff.s09 = '$sn09', staff.s10 = '$sn10', staff.s11 = '$sn11', staff.s12 = '$sn12', staff.s13 = '$sn13', staff.s21 = '$sn21', staff.s22 = '$sn22', staff.s23 = '$sn23', staff.s25 = '$sn25', staff.s26 = '$sn26', staff.s35 = '$sn35', staff.s39 = '$sn39', staff.s49 = '$sn49', staff.s50 = '$sn50', staff.s51 = '$sn51', staff.s52 = '$sn52', staff.s53 = '$sn53', staff.s54 = '$sn54', staff.s55 = '$sn55', s56 = '$sn56', staff.s58 = '$sn58', staff.s59 = '$sn59', staff.s60 = 'in progress', staff.s64 = '$sn64' WHERE staff.staff.s04 = '$sc04' AND staff.id = '$cid'"; break;
	case '04': $uquery = "UPDATE staff SET staff.s01 = '$sn01', staff.s02 = '$sn02', staff.s03 = '$sn03', staff.s05 = '$sn05', staff.s06 = '$sn06', staff.s07 = '$sn07', staff.s08 = '$sn08', staff.s09 = '$sn09', staff.s10 = '$sn10', staff.s11 = '$sn11', staff.s12 = '$sn12', staff.s13 = '$sn13', staff.s21 = '$sn21', staff.s22 = '$sn22', staff.s23 = '$sn23', staff.s25 = '$sn25', staff.s26 = '$sn26', staff.s35 = '$sn35', staff.s39 = '$sn39', staff.s49 = '$sn49', staff.s50 = '$sn50', staff.s51 = '$sn51', staff.s52 = '$sn52', staff.s53 = '$sn53', staff.s54 = '$sn54', staff.s55 = '$sn55', s56 = '$sn56', staff.s58 = '$sn58', staff.s59 = '$sn59', staff.s60 = 'in progress', staff.s64 = '$sn64' WHERE staff.staff.s04 = '$sc04' AND staff.id = '$cid'"; break;}

?>
Both with the same result - NOTHING.
-bluenote
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

I found a bug:

Code: Select all

<?php
WHERE staff.staff.s04 = '".
?>
should be

Code: Select all

<?php
WHERE staff.s04 = '".
?>
and fixed it, but, you know... NOTHING again!
-bluenote
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

If these are all integer fields, the values should be unquoted in the SQL query.
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

Hi,

i have three INT fields - and only two of them (s08 and s09) should be updated :roll:

Here's the table structure:

id int(11) Nein
s01 enum('01', '02', '03', '04') Nein 01
s02 char(1) Nein
s03 enum('01', '02', '03', '04', '05', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23') Nein 01
s04 varchar(4) Ja NULL
s05 varchar(255) Nein
s06 varchar(255) Nein
s07 varchar(10) Nein
s08 smallint(5) Nein 0
s09 smallint(5) Nein 0
s10 varchar(100) Nein
s11 varchar(20) Nein
s12 varchar(100) Nein
s13 enum('01', '02') Nein 01
s21 varchar(255) Nein
s22 enum('yes', 'no') Nein no
s23 varchar(255) Nein
s25 text Nein
s26 text Nein
s32 date Nein 0000-00-00
s33 varchar(40) Nein
s35 char(2) Nein
s39 enum('yes', 'no') Nein no
s49 enum('yes', 'no') Nein no
s50 varchar(255) Nein
s51 char(2) Nein
s52 char(2) Nein
s53 varchar(4) Nein
s54 char(2) Nein
s55 char(2) Nein
s56 varchar(4) Nein
s58 date Nein 0000-00-00
s59 date Nein 0000-00-00
s60 enum('in progress', 'done') Nein in progress
s61 varchar(50) Nein
s62 varchar(50) Nein
s63 varchar(50) Nein
s64 varchar(4) Nein

-bluenote
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

This probably isn't the cause of the error, but s56 = '$sn56' is the only assignment without a staff. infront of the column name.

Anyway, I still believe it could be an issue with having single quotes where they don't need to be. I've been trained to never use single quotes around integer fields and datetime fields.

So, what I would do is remove the single quotes surrounding the new values for s08 and s09 and also remove the single quotes surrounding your date values s32, s58, and s59.

Most OS / MySQL version combinations allow you to use single quotes around integers, but not all of them will. Sometimes, it will try to implicitly convert the data to a string, causing a bad insert/update attempt.

By the way, have you tried using if/then/else structure rather than the case structure JUST to see if it might be the case structure that's the problem?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Little question: where does $login come from? (Sure you didn't mean $_POST['login'] or $_GET['login'] ?)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What happens when you use a default case:

Code: Select all

switch($login) {
    case '1':
         // bunch of code
    case '4':
        // bunch of code
        break;
    default:
        echo 'The value of $login is: '.$login.' and it doesn''t match any of the case values.';
}
Mac
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

Hello to everyone,

sorry that I didn't answer quicker, but to eliminate the possibility of an OS / MySQL conflict, i build up 5 Apache 1.3.29 / PHP 4.2.2 / MySQL packages last night:
  • WinNT4-SP6 / MySQL 3.23.53
  • Win2k / MySQL 3.23.53
  • WinXP Pro / MySQL 3.23.53
  • Sun Solaris 8 / MySQL 3.23.53
  • SuSe Linux / MySQL 3.23.53
additionally to my existing Solaris 7 environment. Afterwards, i dumped the data from the Solaris 7 machine into all the others.

The next step was I fixed the things you told me (removing single qoutes around date and integer fields, adding the "staff." to the s56 field), then i copied the libraries and PHP Scripts to the Web directories. So after all, I had 6 identical setups exept for the OS.

I tried the UPDATE thing on every machine, and - yes, NOTHING again! No updated row, no mysql error, no PHP error...

Just for explanation: the whole thing is about to maintain personell data. Certain people (Admin, Secretary, etc.) are allowed to add new users, to edit existing entries or to delete them, depending on the group they belong to (Admin, Secretary, etc.). The $login var can have values from 01 (Admin) to 09 (Guest) and is retrieved from another table with IP addresses, PIN numbers and the Group numbers during the login to the "back office". If a new user should be added, there are two forms - one for Master Data like name, username, userid and so on, and one for contact information like mail address and phone number. Before submitting the second form to the insert procedure, a script checks wether the user already exists or not and if so, it gives out a warning. Then the editor can choose between "Exit procedure" (which just terminates the process), "Procced and delete old entry", "Proceed and keep old entry" and "Update old entry with new values".
-bluenote
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

Hi,

to twigletmac [i think i still have to pay you a beer ;-))]: i worked around with an default case, but the $login var is part of the result (log_s01) of this query:

Code: Select all

<?php
$log_query = "SELECT staff_login.log_s01, staff_login.log_s03, staff_login.log_s06, staff_login.log_s08 from staff_login WHERE staff_login.log_s03 = '$remote_ip' LIMIT 0,1";
?>
and it MUST have a value between '01' and '04' - otherwise, the whole script wouldn't be on display at all.

To microthick: yes (i forgot to say, sorry :cry: ), i tried around with if / else statements. Unfortunately, with the same result i had with the cases... NOTHING!
-bluenote
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

But you have echoed $login to check that it's ok? It is returning the leading zero?

Mac
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

Hello Mac,

I havent echoed $login before - now i've done it, and it does return the leading zero.

-bluenote
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Use :
$uerg = mysql_query($uquery) or die(mysql_error());

Also echo $uquery and copy and paste the result into a mysql prompt (or phpMyAdmin etc..) and see if it works 'outside' of your script.
Post Reply