insert works but update doesn't

update database mysql

[xuletz]

Hi, this is my first message on this forum.
I got an account because i have an issue that I've been trying to figure out for 2 days now.

I have got a form that should either insert or update content in a database. The insert part works, as well as the first UPDATE, but the UPDATE in the loop freezes after some 29 loops every time. I've tracked the problem down to the ADOdb function ($db->Execute()). So could anyone please tell me what's wrong

This WORKS:

$query = "UPDATE audit_chestionar
 SET idNivel = '".$_POST["nivel"]."',
 idProces = '".$_POST["proces"]."', 
 idSubProces = '".$_POST["subproces"]."',
 idClient = '".$_POST["client"]."',
 dataAudit = '".$data."',
 schimbul = '".$_POST["schimb"]."',
 auditori = '".$auditoriId."',
 oras = '".$_SESSION["oras"]."',
 pers_auditat = '".$_POST["auditat"]."',
 respons_zona = '".$_POST["responsabil"]."',
 obs = '".$_POST["observ"]."' 
WHERE idAudit = '".$_SESSION["record"]["id"]."'";
if ($db->Execute($query) === false) { 
print 'error updating: '.$db->ErrorMsg().'<br/>'; }

This is immediately afterward and DOES NOT WORK as mentioned, freezing after a number of loops when executing the query

foreach($_POST["a"] as $key=>$value) {
$query = "UPDATE audit_raspunsuri
SET raspuns = '".$value."', 
observatie = '".$_POST["obs"][$key]."' 
WHERE idAudit = '".$_SESSION["record"]["id"]."'
AND idIntrebareAudit = '".$key."' ";
if ($db->Execute($query) === false) { 
 print 'error inserting: '.$db->ErrorMsg().'<br/>'; }
 }
pageRedirect($href."&do=list&page=".$page."&id=".$_SESSION['record']['id']);

I have checked all syntax and whatnot but the problem is that the query is not executed after a number of loops.
Thank you.

[rocknbil]

Welcome aboard xuletz. What happens when you add the following?

foreach($_POST["a"] as $key=>$value) {
if (! ($_SESSION["record"]["id"] > 0)) {
die("OOPS! trying to update a non number");
}
[b]if (! preg_match('/^[a-z]+$/i',$key)) {
die("OOPS! invalid key");
}

(Presuming key is a text value, alter to suit)

This might be evident if you remove the quotes from the record field. You don't need to quote a numeric query. However, if the value is blank,

$var=NULL;
select * from some_table where id='$var'

it will not error, it just won't work, records are all numeric and there's no '' (which evaluates to zero anyway.) But this

select * from some_table where id=$var

will give a mysql error, revealing the problem.

....WHERE idAudit = ".$_SESSION["record"]["id"]."
AND idIntrebareAudit = '".$key."'";

Again, not sure what $key is supposed to be, but always check it's what you expect when issuing any query.

[xuletz]

Thank you for your response.

key and id are both numeric. I've replaced the preg_match with is_numeric and none of the conditions apply.

I am certain that the problem is not with the variables but with the querying as a number of rows are updated before freezing, and this I cannot understand. I'm actually wondering if there might be something wrong with the table as the other update works.

[xuletz]

So, nobody else has a clue?

[CyBerAliEn]

Your second query is actually a single query that gets looped through "X" number of times from:

$_POST["a"]

You say the code just freezes? No errors?

I would examine the 'a' values --- there could be issues there. Perhaps some debugging could catch the issue? Have the code spit out something after each loop iteration; it might reveal a specific entry in 'a' that is causing the issue (if it truly is executing and "hanging up" after a certain point).

[xuletz]

the 'a' values are fine, I had already tried to see what it spits out, it's just a vector with numeric elements. :¦

[xuletz]

I was actually wondering if something may be wrong with the database since this is the only issue that occurs.