PDA

View Full Version : Array variables in MySQL UPDATES


peterl3233
11-05-2005, 04:18 PM
I'm trying to use a MYSQL fieldname as a key in an indexed array .. Maybe there is something basic I'm missing?

The statement : $sql13 = mysql_query("UPDATE $table1 SET recored1 = '$arrayvalue[ID]' " ); just refuses to place the correct arrayvalue in the table1. (no failure .. it just places a o.oo value in the records.

ID is in integer record in $table1.
$arrayvalue is an indexed array with a 100 or so values

Not sure if i'm very dumb or just too smart (prob the former) ?

Charter
11-06-2005, 12:15 PM
Assuming $arrayvalue['ID'] contains a legitimate value beforehand, try the following query:

$sql13 = mysql_query("UPDATE $table1 SET recored1 = '".$arrayvalue['ID']."'");

peterl3233
11-08-2005, 01:59 PM
I tried using the suggested notation <THEVALUE = '".$arrval['ID']."'> but no go ... HMMMM something really basic here that is escaping me .. Attached is a working program that demonstrates the problem .. Basically I see that the basic question is "How do you copy an array into a table?" ..

<?php
echo " learn about arrays <p>";

/* declare some relevant variables */
$DBhost = "localhost";
$DBuser = "bob";
$DBpass = "uhoo_babyloo";
$DBName = "thetest_db";
$table = "temptable";

$arrval = array(.01,101,202,303,404,5.05,6.06,7,8.08,9.09,10.1);

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable toconnect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName");
echo "found the database successfully <br>";

$sql2 = mysql_query("DROP TABLE $table") or die ('DROP table failed');
$sq14 = mysql_query("CREATE TABLE $table
(ID INT UNSIGNED NOT NULL,
THEVALUE decimal(8,2)NOT NULL,
PRIMARY KEY (ID))")
or die ("Create table failed");

$sql5 = mysql_query("INSERT into $table(ID) VALUES ('0')") or die ("INSERT failed");
$sql6 = mysql_query("INSERT into $table(ID) VALUES ('1')") or die ("INSERT failed");

// .......finished creating a sample simple table...............................

// .......now try to copy the array values into the table .. using subscripts ..
for ($i=0; $i<2; $i++)// this works but it is crazy way to do it &^%$# !
{
$sql10 = mysql_query("update $table set THEVALUE = '$arrval[$i]' where (ID = $i) ");
}

//$sql11 = mysql_query("update $table set THEVALUE = '$arrval[ID]' "); // this fails
//$sql12 = mysql_query("update $table set THEVALUE = '$arrval['ID']' "); // this fails
//$sql13 = mysql_query("update $table set THEVALUE = '".$arrval['ID']."' "); // this fails
//$sql14 = mysql_query("update $table set THEVALUE = '".$arrval[ID]."' "); // this fails

$sql20 = mysql_query("select * FROM $table") or die ("Query 12 failed");
while($row=mysql_fetch_row($sql20)) echo "<br>$row[0] .... $row[1]<br>\n";

peterl3233
11-08-2005, 02:18 PM
it is clear from the test program .... that the problem is that PHP/MYSQL won't accept a fieldname as a subscript to an array variable .. in an SELECT update statement .. Mannnnnn I tried every sort of syntax I could find and *&^%$ zip .. nothin' works .. Now , maybe if I really understood it or had some formal training I could get it ! darn

Charter
11-10-2005, 09:59 PM
The $arrval['ID'] doesn't have a value because the $arrval array doesn't have an ID key. With the $arrval array set as follows:

$arrval = array(.01,101,202,303,404,5.05,6.06,7,8.08,9.09,10.1);

It is equivalent to the following:

$arrval = array(0 => .01, 1 => 101, 2 => 202, 3 => 303, 4 => 404, 5 => 5.05, 6 => 6.06, 7 => 7, 8 => 8.08, 9 => 9.09, 10 => 10.1);

You could set the array keys as follows:

$arrval = array("ID_0" => .01, "ID_1" => 101, "ID_2" => 202, "ID_3" => 303, "ID_4" => 404, "ID_5" => 5.05, "ID_6" => 6.06, "ID_7" => 7, "ID_8" => 8.08, "ID_9" => 9.09, "ID_10" => 10.1);

And then do something like:

mysql_query("UPDATE $table SET THEVALUE = ".$arrval['ID_0']);

But then all values in the THEVALUE column would be set to $arrval['ID_0'] (.01 in this case) which probably isn't what you want, so you'd need a WHERE clause.