Looping SQL Statement Problem

Somehow, the code below does not return any result nor was the table updated.
If I ran the echo statements individually in the loop, correct results are displayed. However, when I include the UPDATE statement, nothing happens.

I'm puzzled. Help, please?

mysql_connect($db_host, $db_login, $db_pwd) or die("Cannot connect to database!");
mysql_select_db($db_name) or die("Cannot select database!");

$sql = "SELECT tbl_t.qty, tbl_t.lid FROM tbl_t, tbl_l WHERE tbl_t.pid = '" . $_POST['pid'] . "' AND tbl_t.lid=tbl_l.lid";

$result = mysql_query($sql);
while ($row=mysql_fetch_array($result))
{
   echo $row["qty"];
   echo $row["lid"];

   mysql_query("UPDATE tbl_l SET tbl_l.qty = tbl_l.qty + '" . $row["qty"] . "' WHERE tbl_l.lid = '"  . $row["lid"] . "'"); 
}

Code (php):

 

Its because mysql_query("UPDATE tbl... ruins data from your "SELECT... query. You need to store all data in array.

// array mysql_fetch_all(query:resource [, kind:string (default:'assoc' | 'row')])
function mysql_fetch_all($query, $kind = 'assoc') {
   $result = array();
   $kind = $kind === 'assoc' ? $kind : 'row';
   eval('while(@$r = mysql_fetch_'.$kind.'($query)) array_push($result, $r);');
   return $result;
}

PHP:

 

Thanks. I'll give it a go.

 

Should be something like that

mysql_connect($db_host, $db_login, $db_pwd) or die("Cannot connect to database!");
mysql_select_db($db_name) or die("Cannot select database!");
 
$sql = "SELECT tbl_t.qty, tbl_t.lid FROM tbl_t, tbl_l WHERE tbl_t.pid = '" . $_POST['pid'] . "' AND tbl_t.lid=tbl_l.lid";
[COLOR="Blue"]
$query = mysql_query($sql);

// storing SELECTed data
while($r = mysql_fetch_array($query)){
    array_push($result, $r);
}

foreach ($result as $row){[/COLOR]
   echo $row["qty"];
   echo $row["lid"];
   mysql_query("UPDATE tbl_l SET tbl_l.qty = tbl_l.qty + '" . $row["qty"] . "' WHERE tbl_l.lid = '"  . $row["lid"] . "'"); 
} 

Code (markup):

 

Oh, looks like its impossible to edit my post, here's quick fix

mysql_connect($db_host, $db_login, $db_pwd) or die("Cannot connect to database!");
mysql_select_db($db_name) or die("Cannot select database!");
 
$sql="SELECT tbl_t.qty,tbl_t.lid FROM tbl_t,tbl_l WHERE tbl_t.pid ='". [COLOR="Red"]$_POST['pid'][/COLOR]. "' AND tbl_t.lid=tbl_l.lid";

$query = mysql_query($sql);

// storing SELECTed data
[COLOR="Blue"]$result = array();[/COLOR]
while($r = mysql_fetch_array($query)){
    array_push($result, $r);
}

foreach ($result as $row){
   echo $row["qty"];
   echo $row["lid"];
   mysql_query("UPDATE tbl_l SET tbl_l.qty = tbl_l.qty + '" . $row["qty"] . "' WHERE tbl_l.lid = '"  . $row["lid"] . "'"); 
} 

Code (markup):

and you probably need some security check before using db (the problem marked red above), like

if (! is_int($_POST['pid'])){
	// alert, bad value or hack attempt
	// do something	
        echo "Error";
        exit;
} 

Code (markup):

 

hmm... I tried the codes but my the result of the query returned nothing on the browser...

 

Do you have any relevant data in database? Check if "SELECT ..." actually return something.

 

MrSupplier, my apologies. I had an extra closing curly way down in my code which I did not realize.
Your code turned out alright. It worked. Thanks.