Updating a DB

[sylesia]

Ok, so I am using the following code in an attempt to update my DB from information entered in by a form.  I already checked every single variable I wish to input and the values are exactly what I want.  The WHERE statement includes both primary keys, and the vars are proper and have correct variable values that match with the DB.  No idea why this will not update at all... so annoying.  Any idea?  Year is not used right now, but will be implemented later.  It goes thru no problem, and goes where directed, but won't update the DB
[code]
<?
session_start();

$userName = $_SESSION['usersName'];
$year = $_SESSION['year'];
$cardNumber = $_SESSION['cardNumber'];


$conn = mysql_connect("localhost", "yyy", "xxx);
mysql_select_db("evaluation", $conn);


$valid = mysql_query("SELECT * FROM users WHERE UserName = '$userName'", $conn);
$line = mysql_num_rows($valid);

if (($line == NULL) || ($userName == NULL)){
header("Location: http://localhost/srh/index1.html");
}
else
{
// if $year =='junior';
// {
$strongDimension = $_POST['StrongDimensions'];
$impDimension = $_POST['ImpDimensions'];

$evalUser = $_POST['EvalUser'];
$date = $_POST['Date'];
$unitPosition = $_POST['UnitPosition'];
$unit = $_POST['Unit'];
$time = $_POST['Time'];

$comments = $_POST['Comments'];
$strong = $_POST['Strong'];
$improve = $_POST['Improve'];
$actions = $_POST['Actions'];


while ($arrayLoop != 2)
{
switch($arrayLoop)
{
case 0:
if ($strongDimension != '')
{
foreach ($strongDimension as $key => $value)
{
$s = $key;
if($key < 3)
{
if ($key != 0)
{
$strongInput = $strongInput . "," . $value;
}
else
{
$strongInput = $value;
}
}
}
}
break;
case 1:
if ($impDimension != '')
{
foreach ($impDimension as $key => $value)
{
$i = $key;
if($key < 3)
{
if ($key != 0)
{
$impInput =  $impInput . "," . $value;
}
else
{
$impInput = $value;
}
}
}
}
break;

}
$arrayLoop = $arrayLoop + 1;
}
while($s != 3)
{
$strongInput = $strongInput . "," . "XX";
$s = $s + 1;
}
while($i != 3)
{
$impInput =  $impInput . "," . "XX";
$i = $i + 1;
}

//                      echo $comments;
$sql = <<<HERE
UPDATE yellowcard
SET
EvaluatorName ='$evalUser',
UnitPosition ='$unitPosition',
Date ='$date',
Unit ='$unit',
LengthOfTime ='$time',
Summary ='$comments',
StrongDimension ='$strong',
WeakDimension ='$improve',
Improve ='$actions'
WHERE
CardNumber = $cardNumber
UserName = $userName
HERE;

mysql_query($sql, $conn);
/*
if ($strongDimension != '')
{
$sql = <<<HERE
UPDATE yellowcard
SET
StrongDim ='$strongDimension',
WHERE
CardNumber = $cardNumber
UserName = $userName
HERE;
mysql_query($sql);
}
if ($impDimension != '')
{
$sql = <<<HERE
UPDATE yellowcard
SET
WeakDim ='$impDimension',
WHERE
CardNumber = $cardNumber
UserName = $userName
HERE;
mysql_query($sql);
}*/
mysql_close();
header("Location: http://localhost/srh/MS_III_Page.php");
// }
}
?>
[/code]

[willfitch]

Place an "or die(mysql_error())" after your update statement

[sylesia]

Ok, adding the or Die in, it hits me where it is checking to make sure that there is a valid user apparently.  And in the user table, there is indeed a valid user with the username ts1 (UserName is that table's primary key)

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 ' UserName = ts1' at line 13

if I drop the ' ' around the $userName, I get the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\srh\yellowedit.php on line 14

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files\xampp\htdocs\srh\yellowedit.php:14) in C:\Program Files\xampp\htdocs\srh\yellowedit.php on line 17

[JasonLewis]

try replacing line 13 with this:
[code=php:0]
$valid = mysql_query("SELECT * FROM users WHERE `UserName`='{$userName}'", $conn);
[/code]

not sure but give it a crack

[sylesia]

It does not work, in fact, it throws me to the front page to log in as it is not a valid userName when done like that :-/

[jsladek]

Add [code]print ("<hr>SQL:$sql<hr>");[/code] to the code and lets see what it looks like.

Also the line [code]$valid = mysql_query("SELECT * FROM users WHERE UserName = '$userName'", $conn);[/code]

for some reason looks like  [b]usersWHERE [/b] is all one word but when I cust and paste it they are more than a space apart, make sure there is nothing funny going on there

-John

[btherl]

Regarding this error:

[code=php:0]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\srh\yellowedit.php on line 14[/code]


Make sure ALL your mysql_query() calls check for failure of the query.  The simplest way to do that is like this:

[code=php:0]mysql_query($sql) or die("Error in $sql: " . mysql_error());[/code]


If all your queries do that and you still get that error, it means you are trying to use something which is not a mysql result as a mysql result.

[sylesia]

Ok, fixed the WHERE issue, though the spacing does not seem to have any effect what so ever on the issue.  Adding in the comment (and the orDie everywhere I use the mysql_query) I get the following

SQL: UPDATE yellowcard SET EvaluatorName ='Tom Gieko', UnitPosition ='PL', Date ='05DEC04', Unit ='RWB', LengthOfTime ='', Summary ='asdf', StrongDimension ='Mine ', WeakDimension ='Rocks ', Improve ='Def ' WHERE CardNumber = 1 UserName = ts1
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 'UserName = ts1' at line 14

Now, the SQL is right, that is what it should say, so I know that it is doing some of it right, but for whatever reason, it simply will not update my DB.  And am I using the $valid = mysql_query("SELECT * FROM users WHERE UserName = '$userName'", $conn) or die(mysql_error());
$line = mysql_num_rows($valid);
right?  Or is there a better way to find out if there is something in the DB? (all I am using it for is to make sure the user logged in is in the DB so that its a legit thing they are there)

[jsladek]

I think you need an AND in there after the WHERE
[code]SQL: UPDATE yellowcard SET EvaluatorName ='Tom Gieko', UnitPosition ='PL', Date ='05DEC04', Unit ='RWB', LengthOfTime ='', Summary ='asdf', StrongDimension ='Mine ', WeakDimension ='Rocks ', Improve ='Def ' WHERE CardNumber = 1 UserName = ts1
[/code]

[code]SQL: UPDATE yellowcard SET EvaluatorName ='Tom Gieko', UnitPosition ='PL', Date ='05DEC04', Unit ='RWB', LengthOfTime ='', Summary ='asdf', StrongDimension ='Mine ', WeakDimension ='Rocks ', Improve ='Def ' WHERE CardNumber = 1 AND UserName = ts1
[/code]

-John

[sylesia]

I think that helped a bit... never occurred to me to add the AND to it, but now I get this.
[code]
SQL: UPDATE yellowcard SET EvaluatorName ='Tom Gieko', UnitPosition ='PL', Date ='05DEC04', Unit ='RWB', LengthOfTime ='', Summary ='asdfdta', StrongDimension ='Mine ', WeakDimension ='Rocks ', Improve ='Def ' WHERE CardNumber = 1 AND UserName = ts1
Unknown column 'ts1' in 'where clause'
[/code]

And my table in the DB looks like this
[code]
ts1  1  Tom Gieko  PL  05DEC04  RWB  12    ME,PH,XX  Mine  CO,IP,EM,XX  Rocks  EM,XX  Yes  MO,XX  Sure  Def
[/code]
So i am not sure why it will not do the ts1 column 

[JasonLewis]

is still think that the $username variable should be wrapped in ' and ' but... i may be wrong...:)

[jsladek]

How about quotes around tsl

-John

[sylesia]

Tried quotes around $userName, and tried without, both without success. 
Not sure though what you mean about quotes around ts1, no where are there any, or should not be and when echoed, none are there.

[jsladek]

can you post the sql to create the table you are using?

-John

[sylesia]

I actually use php MyAdim to do it, but looking at the MySQL lines, I believe this is the code used by it to create the table
[code]
Table structure for table `yellowcard`
--

CREATE TABLE `yellowcard` (
  `UserName` varchar(10) collate latin1_general_ci NOT NULL,
  `CardNumber` int(3) NOT NULL,
  `EvaluatorName` varchar(26) collate latin1_general_ci NOT NULL,
  `UnitPosition` varchar(15) collate latin1_general_ci NOT NULL,
  `Date` varchar(7) collate latin1_general_ci NOT NULL,
  `Unit` varchar(10) collate latin1_general_ci NOT NULL,
  `LengthOfTime` int(3) NOT NULL,
  `Summary` varchar(2048) collate latin1_general_ci NOT NULL,
  `StrongDim` varchar(12) collate latin1_general_ci NOT NULL,
  `StrongDimension` varchar(450) collate latin1_general_ci NOT NULL,
  `WeakDim` varchar(12) collate latin1_general_ci NOT NULL,
  `WeakDimension` varchar(450) collate latin1_general_ci NOT NULL,
  `EvalStrong` varchar(12) collate latin1_general_ci NOT NULL,
  `EvalStrongDimension` varchar(450) collate latin1_general_ci NOT NULL,
  `EvalWeak` varchar(12) collate latin1_general_ci NOT NULL,
  `EvalWeakDimension` varchar(450) collate latin1_general_ci NOT NULL,
  `Improve` varchar(450) collate latin1_general_ci NOT NULL,
  `EvalUserName` varchar(10) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`UserName`,`CardNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
[/code]

[jsladek]

[code]UPDATE `yellowcard` SET `EvaluatorName` = 'Tom Gieko',`UnitPosition` = 'PL',`Date` = '05DEC04',`Unit` = 'RWB',`LengthOfTime` = '',`Summary` = 'asdfdta',`StrongDimension` = 'Mine',`WeakDimension` = 'Rocks',`Improve` = 'DEF' WHERE `UserName` ='1' AND `CardNumber` ='2' LIMIT 1 ;[/code]

This SQL should work for you.

-John