Query not working? 1

[ZOR]

The code below follows the database being open. I am trying to cycle through the records (Matching table ID with loop $i

I am doing this to get it working so I can then modify it to insert session variable values into the field Qty.
I tried using PhpMyAdmin to select a record manually, and entered a qty value, then got PHP code from the query it showed. Modified it hoping it would work but just plays dumb. Any pointers, and also does the query have to contain all the fields, can it not just contain the one I want to put a value in? thanks

$i=0;
while ($i < $num) {

$sql = 'UPDATE `template` SET `Location` = \'\', `OrderNumber` = \'\', `Caption` = \'\', `OrderDate` = NULL, `Group` = \'1\', `Description` = \'DBC-6M\', `FujiPartNumber` = \'P10W000028A\', `Duration` = \'6 mins\', `UnitPrice` = \'1.40\', `Qty` = \'100\', `OrdSubTotal` = \'0.00\', `RunningTotal` = \'0.00\' WHERE `ID` = $i;';

$i++;
}
mysql_close();

 

[ZOR]

Nearest got is:
$sql = 'UPDATE template SET Qty = 10 WHERE ID=4;';
Produced from PhpMyAdmin SQL converted for PHP but still does not work.

 

[Vragabond]

That SQL looks ok. The only problems I could see with it is if ID or Qty are not integer columns. What is the error you're getting? Do you check for mysql error when you run the query like [tt]mysql_query($sql) or die (mysql_error());[/tt]?

 

[jpadie]

Zor

your code does not actually run the query. is this an intended omission? i would suspect you need a mysql_query($sql) in there.

also i normally do not put the table name in backticks.

lastly, variables are not expanded in single quotes, so you will find that the $id is not expanded.

$sql = 'UPDATE template SET `Location` = \'\', `OrderNumber` = \'\', `Caption` = \'\', `OrderDate` = NULL, `Group` = \'1\', `Description` = \'DBC-6M\', `FujiPartNumber` = \'P10W000028A\', `Duration` = \'6 mins\', `UnitPrice` = \'1.40\', `Qty` = \'100\', `OrdSubTotal` = \'0.00\', `RunningTotal` = \'0.00\' WHERE `ID` = ' .$i; //NB CHANGED AT THE END

it does not matter for the above query but you should also look in the php manual about mysql_escape_string.

 

[ZOR]

Thought it might help to show more what I am trying to get working. This page is to enter the values of session variables into a field. With the update code previously posted I get no errors but nothing happens. Thanks

<?session_start()
?>
<?
$username="root";
// $password="password";
$database="Removed";
mysql_connect(localhost,$username);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM template";
$result=mysql_query($query);
$num=mysql_numrows($result);

// Loop for records
$i=0;
while ($i < $num) {

// Want to update record where ID=$i and insert into field Qty the value of $_SESSION['QTY'][$i]

$i++;
}
mysql_close();
// header("Location: index.php");
// header("Connection: close");
?>

 

[ZOR]

Bit nearer, but having problem with the where statement. If $i is on its own, it reports cannot find field. The way it is now does nothing, ie no errors etc. Soemone put me out of my misery? Thanks

<?session_start()
?>
<?
$username="root";
// $password="password";
$database="Removed";
mysql_connect(localhost,$username);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM template";
$result=mysql_query($query);
$num=mysql_numrows($result);
if (!$result)
die(mysql_error());
$i=0;
while ($i < $num) {
$query ='UPDATE template SET Qty = 1000 WHERE ID="$i";';
$result = mysql_query($query);
if (!$result)
die(mysql_error());
$i++;
}
echo("Updated");
mysql_close();
// header("Location: index.php");
// header("Connection: close");
?>

 

[ZOR]

One bit left.
Remarked out bit works, but tried all permutations to insert session variable value into field Qty.

//$query ="UPDATE template SET Qty = 1900 WHERE id = $i";
$query ="UPDATE template SET Qty = "$_SESSION['QTY'][$i]" WHERE id = $i";

Thanks

 

[ZOR]

Still in the same boat, whole day trying to replace hard number 1900 with session variable value. Where has everyone gone?

 

[Vragabond]

$query ="UPDATE template SET Qty = " . $_SESSION['QTY'][$i] . " WHERE id = $i";

Provided the session variable contains what you expect and Qty is an integer column, this should work.

 

[ZOR]

Thanks Vragabond, unfortunately I get an error?

Parse error: parse error, unexpected T_VARIABLE in C:\Program Files\xampp\htdocs\update.php on line 21


<?session_start()
?>
<?
$_SESSION['QTY'][4]="1000";
$username="root";
// $password="password";
$database="removed";
mysql_connect(localhost,$username);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM template";
$result=mysql_query($query);
$num=mysql_numrows($result);
if (!$result)
die(mysql_error());
$i=0;
while ($i < $num) {
$_SESSION['QTY'][$i]="100"
//$query ="UPDATE template SET Qty = 1900 WHERE id = $i";
$query ="UPDATE template SET Qty = " . $_SESSION['QTY'][$i] . " WHERE id = $i";
$result = mysql_query($query);
if (!$result)
die(mysql_error());
$i++;
}
echo("Updated");
mysql_close();
// header("Location: index.php");
// header("Connection: close");
?>

I am putting a dummy value in the session variable just to get it off the ground. Many thanks.

 

[ZOR]

Sorry Vragabond, it did work. I goofed on it at my end, line filling session variable was wrong, and did not have a ;
Many thanks, have a star!!

 

[jpadie]

Zor

please read my post above. i note that after i posted it you still reposted code that had variables inside single quotes. Vragabond fixed it for you with his code (note the different quotation marks) but you need to take in the point I made otherwise you will find it causing problems for you elsewhere.

one other tip: as a matter of course I enquote all mysql query parameters. the reason is that mysql does not care of numbers are quoted but absolutely cares if strings are not quoted.

Justin

 

[ZOR]

Many thanks Justin. Points noted. The code having table names in backticks came from PhpMyAdmin generating PHP code. I notice some poeple do it, some like yourself don't. Whats the rule? Thanks

 

[sleipnir214]

the reason is that mysql does not care of numbers are quoted

There have, on rare occasion, been situations where the use of quotes around MySQL numerical values have slowed queries.

As a rule, I recommend that you match the representation of the value to the type of column. If nothing else, it's a self-documentation of code thing.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ZOR]

Many thanks sleipnir, that answers it perfectly.

 

[sleipnir214]

Actually, the backticks around column names are a completely different thing.

There are MySQL reserved words which MySQL uses for other purposes, words such as "NOT" and "SELECT". You cannot use "SELECT" as a column name unless you surround the word with backticks. (Although it would be boneheaded in the extreme to name a column "SELECT" in the first place.)

PHPMyAdmin automatically puts backticks around all colum- and (I think) table-names in order to keep from running afoul of some user's using a reserved word as an identifier.

In the case of backticks, I strongly recommend against their use in your code. All those backticks do is allow you use MySQL reserved words as identifiers, which is a very unwise thing to do in the first place. Omitting the backticks will train you to avoid using reserved words as identifiers and make your queries more readable.



Want the best answers? Ask the best questions! TANSTAAFL!