Jump to content

Archived

This topic is now archived and is closed to further replies.

fractil

UPDATE query in While loop not possible?

Recommended Posts

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>

Share this post


Link to post
Share on other sites
[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*

Share this post


Link to post
Share on other sites
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!


[code]$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;
}[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.