Jump to content


Photo

UPDATE query in While loop not possible?


  • Please log in to reply
2 replies to this topic

#1 fractil

fractil
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 23 June 2006 - 12:08 AM

I am using a odbc connection to query a ms sql database. I can successfully connect to and query the database from a php script. I need to UPDATE multiple entries and have written a While loop that successfully advances my variables which represent the table columns used in the WHERE clause. I then inserted the query statement into the While loop and ran the script. The script runs with no errors. All expected variable results are echoed (so I can watch/test it's progress). But the query is only executed once, the first time through the loop. I am perplexed. My code is included below.

<html>
<head><title>priceupdate.php</title></head>
<body>
<?php
$x="'21-2000SL'";
$y="'11-1000SL'";
$Width=24;
$Height=12;
$dsn="sandman";
$username="sa";
$password="linford";

$sqlconnect=odbc_pconnect($dsn,$username,$password);

$sqlquery="UPDATE [Price Grid] SET ListPrice = (SELECT ListPrice FROM [Price Grid] WHERE (PartNo = $x) AND (Width = $Width) AND (Height = $Height)) WHERE (PartNo = $y) AND (Width = $Width) AND (Height = $Height)";

while($Width <= 72)
{
while($Height <= 72)
{
echo "$Width X $Height\n<br>";
$process=odbc_exec ($sqlconnect, $sqlquery);
$Height = $Height + 6;
}

$Height = 17.9375;

while($Height <= 72)
{
echo "$Width X $Height\n<br>";
$process=odbc_exec ($sqlconnect, $sqlquery);
$Height = $Height + 6;
}

$Height = 12;
$Width = $Width + 6;
}

odbc_close($sqlconnect);

?>
</body>
</html>
"One of the main causes of the fall of the Roman Empire was that, lacking zero, they had no way to indicate successful termination of their C programs"

#2 fractil

fractil
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 23 June 2006 - 02:34 PM

[quote name='fractil' date='Jun 22 2006, 06:08 PM' post='387023']
I am using a odbc connection to query a ms sql database.

*bump*

"One of the main causes of the fall of the Roman Empire was that, lacking zero, they had no way to indicate successful termination of their C programs"

#3 fractil

fractil
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 23 June 2006 - 05:51 PM

I figured it out. You can use UPDATE in a While loop. I was updating my $Width and $Height variables, but I was not updating my $sqlquery variable which had $Width and $Height embedded in it. Once I started updating my $sqlquery variable every time the $Width and $Height variables changed every executed as expected.

Cheers!


$x="'21-2000SL'";
$y="'11-1000SL'";
$Width=24;
$Height=12;
$dsn="sandman";
$username="sa";
$password="linford";

$base_query = "UPDATE [Price Grid] SET ListPrice = (SELECT ListPrice FROM [Price Grid] WHERE (PartNo = ".$x.") AND (Width = [Width]) AND (Height = [Height])) WHERE (PartNo = ".$y.") AND (Width = [Width]) AND (Height = [Height])";

while($Width <= 72)
{
    while($Height <= 72)
    {
        echo "$Width X $Height\n<br>";
        $sqlquery = str_replace( "[Width]", $Width, $base_query );
        $sqlquery = str_replace( "[Height]", $Height, $sqlquery );
        $sqlconnect=odbc_connect($dsn,$username,$password);
        $process=odbc_exec ($sqlconnect, $sqlquery);
        odbc_close($sqlconnect);
        $Height = $Height + 6;
    }

    $Height = 17.9375;

        while($Height <= 72)
        {
            echo "$Width X $Height\n<br>";
            $sqlquery = str_replace( "[Width]", $Width, $base_query );
            $sqlquery = str_replace( "[Height]", $Height, $sqlquery );
            $sqlconnect=odbc_connect($dsn,$username,$password);
            $process=odbc_exec ($sqlconnect, $sqlquery);
            odbc_close($sqlconnect);
            $Height = $Height + 6;
        }

    $Height = 12;
    $Width = $Width + 6;
}

"One of the main causes of the fall of the Roman Empire was that, lacking zero, they had no way to indicate successful termination of their C programs"




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users