Page 1 of 2

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

Posted: Thu Jan 29, 2004 7:31 am
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.

Posted: Thu Jan 29, 2004 7:36 am
by Wayne
try removing the @ from the mysql_select_db and see if you get any errors.

Posted: Thu Jan 29, 2004 7:45 am
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

Posted: Thu Jan 29, 2004 8:05 am
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

Posted: Thu Jan 29, 2004 8:53 am
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

Posted: Thu Jan 29, 2004 9:57 am
by microthick
If these are all integer fields, the values should be unquoted in the SQL query.

Posted: Thu Jan 29, 2004 10:40 am
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

Posted: Thu Jan 29, 2004 7:58 pm
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?

Posted: Thu Jan 29, 2004 10:53 pm
by timvw
Little question: where does $login come from? (Sure you didn't mean $_POST['login'] or $_GET['login'] ?)

Posted: Fri Jan 30, 2004 2:46 am
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

Posted: Fri Jan 30, 2004 2:58 am
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

Posted: Fri Jan 30, 2004 3:08 am
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

Posted: Fri Jan 30, 2004 3:31 am
by twigletmac
But you have echoed $login to check that it's ok? It is returning the leading zero?

Mac

Posted: Fri Jan 30, 2004 3:51 am
by bluenote
Hello Mac,

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

-bluenote

Posted: Fri Jan 30, 2004 4:04 am
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.