Updating two tables ( or is there another problem)

[dlyles]

I'm trying to run two queries to update two tables bases on one form.  Below is the code

[code]
$sql = "insert into receiveditems (ReceivedAmount, ReceivedNote, ReceivedBy, InspectedBy, ReceiveDate)
          VALUES ('" . mysql_real_escape_string($_POST['amountreceived']) .
                      "', '" . mysql_real_escape_string($_POST['comments']) .
                      "', '" . mysql_real_escape_string($_POST['receiveby']) .
                      "', '" . mysql_real_escape_string($_POST['inspby']) .
                      "', '" . mysql_real_escape_string($_POST['recdate']) . "')";
$result = mysql_query($sql) or die("Error 2: query: $sql<br>" . mysql_error());
echo "1 record added";

$partname=mysql_real_escape_string($_POST['partname']);
$newquery="select * from parts where PartName = '" . $partname . "'";
$newresult=mysql_query($newquery);

if ($newresult){
$balance = $row['balance'] + mysql_real_escape_string($_POST['amountreceived']);
$sql = "update parts set Group ID = '" . $mygroup . "', partname = '" . mysql_real_escape_string($_POST['partname']) . "',
ModelID = '" . $mymodel . "', Cost = '" . mysql_real_escape_string($_POST['cost']) . "',
balance = '" . $balance . "'";

echo $sql;
// $nresult = mysql_query($sql) or die("Error 2: query: $sql<br>" . mysql_error());
echo "Update record added";
}
else
{
$sql = "insert into parts (GroupID, ModelID, PartName, Cost, balance)
          VALUES ('".$mygroup."',
                      '".$mymodel."',
                      '".mysql_real_escape_string($_POST['partname'])."',
                      '".mysql_real_escape_string($_POST['cost'])."',
                      '".mysql_real_escape_string($_POST['amountreceived'])."')";
$nresult = mysql_query($nsql) or die("Error 2: query: $nsql<br>" . mysql_error());
echo "Insert record added";
}

[/code]

The first query that inserts into "receiveditems" works with no problem.  The second echos the result I want but there is no data in the database.  If it's a new record it treats it like a new one and tells me it inserted.  If it's supposed to update it tells me it updated, but it's doing neither.

[craygo]

On your second query you ran the query but did not return any data

add this
[code]$newresult=mysql_query($newquery);
$row = mysql_fetch_assoc($newresult);[/code]
which I think is returning your balance.

Ray

[dlyles]

Good point, however I'm still not getting this

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/opt/data/rgbdata/apache/htdocs/receiveaction.php on line 28


from
[code]
$row = mysql_fetch_assoc($newresult);
[/code]
Removing this line brings me back to no data being inserted.

[dlyles]

Never mind.  I found my problem.  I was trying to update where I should've inserted.  So once you sent the corrected code, it was trying to update a non-existing record.

Thanks.

[craygo]

Cool but you should add some debugging in anyway until you get things working right

[code]$newquery="select * from parts where PartName = '" . $partname . "'";
$newresult=mysql_query($newquery) or die("SQL error. SQL = ".$newquery."<br>".mysql_error());[/code]

Ray

[dlyles]

Ok, will do. Because things are still screwed up  ???.  I am now getting it to add to the db when it should and it tells me it added.  When it should update it tells me it updated, but it doesn't.  I'm telling it to add the old balance to the current amoundreceived where the partname equals the partname in the form.  If I have it insert all the data, it will.  Since I only want the new balance it tells me when I echo the statement that it's updating the balance to "50" when the old balance was 50 and the new amountreceived is 50.  Meaning the balance s/b 100.  What the heck am I doing wrong?
here's what I have
[code]
$sql = "insert into receiveditems (ReceivedAmount, ReceivedNote, ReceivedBy, InspectedBy, ReceiveDate)
          VALUES ('" . mysql_real_escape_string($_POST['amountreceived']) .
                      "', '" . mysql_real_escape_string($_POST['comments']) .
                      "', '" . mysql_real_escape_string($_POST['receiveby']) .
                      "', '" . mysql_real_escape_string($_POST['inspby']) .
                      "', '" . mysql_real_escape_string($_POST['recdate']) . "')";
$result = mysql_query($sql) or die("Error 1: query: $sql<br>" . mysql_error());
echo "1 record added";

$partname=mysql_real_escape_string($_POST['partname']);
$newquery="select * from parts where PartName = '" . $partname . "'";
$newresult=mysql_query($newquery);

$count=mysql_num_rows($newresult);
if($count == 0){
$sql = "insert into parts (GroupID, ModelID, PartName, Cost, balance)
          VALUES ('".$mygroup."',
                      '".$mymodel."',
                      '".mysql_real_escape_string($_POST['partname'])."',
                      '".mysql_real_escape_string($_POST['cost'])."',
                      '".mysql_real_escape_string($_POST['amountreceived'])."')";
$nresult = mysql_query($sql) or die("Error 2: query: $nsql<br>" . mysql_error());
echo "Insert record added";

}
else
{
$oldbalance = mysql_real_escape_string($_POST['amountreceived']);
$balance = $row['balance'] + $oldbalance;

$sql = "update parts set balance = '" . $balance . "' where PartName = '" . $partname . "'";
$newwresult=mysql_query($sql);


echo $sql;
// $nresult = mysql_query($sql) or die("Error 3: query: $sql<br>" . mysql_error());
echo "Update record added";
}
[/code]

[dlyles]

Never mind.  I forgot to include

[code]
$row = mysql_fetch_assoc($newresult);
[/code]
Thanks.  and for the debugging tip.