Page 1 of 1
getting data from a multidimensional array for nuRunQuery
Posted: Wed Apr 17, 2024 10:02 am
by kknm
In nuRunPHPHidden() I got the data from the request in several lines and put it in an array.
Code: Select all
$query = nuRunQuery($sql);
$i = 1;
while ($r = db_fetch_row($query)) {
$a[$i] = [$r[0], $r[1], $r[2], $r[3], $r[4], $r[5], $r[6]];
$i++;
$query->closeCursor();
Now I need to enter the values from the array into the mySql database table using nuRunQuery().
Code: Select all
for ($i = 1; $i < count($a)+1; $i++) {
// nuDebug($a[$i][0], $a[$i][1], $a[$i][2] ,$a[$i][3]);
$uid=nuID();
$x="INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES ('$uid',$a[$i][0],'$a[$i][1]',' $a[$i][2]',' $a[$i][3]', '$a[$i][4]', '$a[$i][5]','$a[$i][6]')";
nuRunQuery($x);
Re: getting data from a multidimensional array for nuRunQuery
Posted: Wed Apr 17, 2024 12:15 pm
by nac
Hello kknm,
Would something like like this work?
Code: Select all
$s = nuRunQuery($sql);
$x="INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
while($r = db_fetch_row($s)){
nuRunQuery($x, [$r[0], $r[1], $r[2], $r[3], $r[4], $r[5], $r[6], $r[7], $r[8]]);
}
This assumes that your sql statement will return 8 columns that map to
avto_id, ctab, fr, ckol, ctn, akol, atn, stn in the table to be populated (
co_svod).
Neil
Re: getting data from a multidimensional array for nuRunQuery
Posted: Wed Apr 17, 2024 12:48 pm
by kknm
Thank you very much!!!
It worked like this:
Code: Select all
$s = nuRunQuery($sql);
while($r = db_fetch_row($s)){
$uid=nuID();
$x="INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES ('$uid','$r[0]', '$r[1]',' $r[2]', '$r[3]', '$r[4]','$r[5]', '$r[6]')";
nuRunQuery($x);
}
Re: getting data from a multidimensional array for nuRunQuery
Posted: Wed Apr 17, 2024 12:50 pm
by nac
Quick follow up...
I am not sure about this but if your sql result has only the 8 columns need to populate the co_svod table, in the correct order, then this simpler approach may work, as $r is an array with 8 values that map to the 8 question marks in the prepared statement.
Code: Select all
$s = nuRunQuery($sql);
$x="INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
while($r = db_fetch_row($s)){
nuRunQuery($x, $r);
}
Neil
Re: getting data from a multidimensional array for nuRunQuery
Posted: Wed Apr 17, 2024 12:59 pm
by nac
kknm wrote: ↑Wed Apr 17, 2024 12:48 pm
Thank you very much!!!
It worked like this:
Code: Select all
$s = nuRunQuery($sql);
while($r = db_fetch_row($s)){
$uid=nuID();
$x="INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES ('$uid','$r[0]', '$r[1]',' $r[2]', '$r[3]', '$r[4]','$r[5]', '$r[6]')";
nuRunQuery($x);
}
Hi - glad to hear that it worked. It is generally better to use prepared statements for this type of operation. It is more efficient, safer and it also means you don't have to worry about the data types. For me, it is easier to read, as well.
PDO delusions is quite a useful site that deals with this topic. (nuRunQuery is just a wrapper around PDO.)
Neil
Re: getting data from a multidimensional array for nuRunQuery
Posted: Mon Apr 22, 2024 4:18 pm
by kev1n
Thank you Neil for your explanation. I'd like to throw in an alternative way that uses
named parameters which offers some advantages over using question marks (?) in SQL queries:
Clarity: They provide meaningful names for placeholders, enhancing code readability.
No positional dependencies: Order of parameters in the array doesn't matter, reducing errors.
Self-documenting: Parameters serve as documentation within the query itself.
Parameter reuse: Allows reuse of parameters within the same query, improving flexibility.
Code: Select all
$query = "INSERT INTO co_svod (avto_id, ctab, fr, ckol, ctn, akol, atn, stn) VALUES (:avto_id, :ctab, :fr, :ckol, :ctn, :akol, :atn, :stn)";
$result = nuRunQuery($sql);
while ($row = db_fetch_row($result)) {
$params = [
':avto_id' => $row[0],
':ctab' => $row[1],
':fr' => $row[2],
':ckol' => $row[3],
':ctn' => $row[4],
':akol' => $row[5],
':atn' => $row[6],
':stn' => $row[7]
];
nuRunQuery($query, $params);
}
Re: getting data from a multidimensional array for nuRunQuery
Posted: Tue Apr 23, 2024 9:19 am
by kknm
Thanks Kevin for responding.
As you probably noticed, in my question I tried to follow your path with named parameters, but I did not correctly use the syntax for placing it in the data array that you provided here.
As for using question marks instead of variables, nubuider complained about them, but Neil's principle helped me.