Hello,

I've wound up with a strange problem with my php script. The script goes into a SQL DB and checks the values of two different columns to see if they are equal.

If the columns are not equal, then I store all of the possible values from column 1 in an array. The values from that array are meant to be placed into column 2.

What I've written works almost correctly. The problem, which has already consumed 8hrs of time, is that only the last value of the array is stored in column 2.

I've imploded the array and stored the values as a string in a new variable which is referenced by the SQL update. No luck.

Any help is GREATLY appreciated. Here's the code:

<?

//DB connection info here

$query = "SELECT DISTINCT special FROM julib.subscriber_sub_ml_events";

$query2 = "SELECT special FROM julib.subscriber_sub_ml_events WHERE sub_id = 644546";

$currentspecial = @mysql_query($query);

$currentsub = @mysql_query($query2);

if($currentspecial != $currentsub) {

echo '<p><b>Not synched</b></p>';

while($row = mysql_fetch_array($currentspecial,MYSQL_NUM)) {

$DBvalues = implode(',' , $row);

echo $DBvalues; //Print out the array to ensure it's all there.

mysql_query("UPDATE subscriber_sub_ml_eventsCBbackup SET special= '$DBvalues' WHERE sub_id = 644546"); //Update the DB record

}
}

//Results in last value of array being stored in DB. Argh.

A PHP array is a special structure, that you cannot save as is in a db.
Now, the effect is that it parse each slots of the array, and keep only the last when creating the query.

What you need to do, is either to store the string, or to store a serialized version of the array, or to store several rows in your db, with each slots of the array in each row.
But given that you do an update, there is not really a possibility to have a "1 to n" relation with the subscriber_sub_ml_eventsCBbackup table, isn't it?

Thanks for the reply and the advice. I was storing the array in a string using the implode function already. That had the effect of updating SQL with just the last value of the array.

I tried serializing the array, but that had the same effect – last value of array stored in SQL.

I was pondering this part of your reply:

"But given that you do an update, there is not really a possibility to have a "1 to n" relation with the subscriber_sub_ml_eventsCBbackup table, isn't it?".

I don't understand what you mean immediately. Are you saying that using UPDATE will not work? UPDATE seems to work fine, but only the last value of the arry is actually getting stored.

I don't understand what you mean immediately. Are you saying that using UPDATE will not work? UPDATE seems to work fine, but only the last value of the arry is actually getting stored.

No, what I meant was if the table you update had a "1 to n" or "1 to 1" relationship with the user.
It it has a "1 to 1", it means that 1 row in the user table must have at most 1 row in the configuration table.
An "1 to n" means that 1 row in the user table can have several rows linked to it in the configuration table.

I tried serializing the array, but that had the same effect – last value of array stored in SQL.

Because if you want to have all the values of the array, you have to iterate over it, with something like:

PHP Code:

foreach($array_variable as $key=>$value){
  //you will have a different $value on each time you iterate here
} 


But looking further at your code, the problem might be that you do the implode where you fetch the value from the db, thus overwriting your variable each time.
Try this:

PHP Code:

<?php
//DB connection info here

$query = "SELECT DISTINCT special FROM julib.subscriber_sub_ml_events";
$query2 = "SELECT special FROM julib.subscriber_sub_ml_events WHERE sub_id = 644546";
$currentspecial = @mysql_query($query);
$currentsub = @mysql_query($query2);
if($currentspecial != $currentsub) {
    echo '<p><b>Not synched</b></p>';
    $recipient=array();
    while($row = mysql_fetch_array($currentspecial,MYSQL_NUM)) {
        //we construct an array containing every values returned by the query here
        $recipient[]=$row[0];
    }
    //and now that the array is complete, we implode it
    $DBvalues = implode(',' , $rrecipient);
    echo $DBvalues; //Print out the array to ensure it's all there.
    mysql_query("UPDATE subscriber_sub_ml_eventsCBbackup SET special= '$DBvalues' WHERE sub_id = 644546"); //Update the DB record
}
?>