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 in 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.

?>

not sure if this will help but you are missing some quotes in your code:

mysql_query("UPDATE subscriber_sub_ml_eventsCBbackup SET special='$DBvalues' WHERE sub_id = '644546'");

It all boils down to one problem:

The SQL array that I create is not storable even though it is printable.

Implosion and serialization both fail to accomplish putting all the values into a string that can be attached to a variable and sent to a database.

I need to find a way to get all of the values generated from the SQL array into another SQL table.

//...
$currentspecial = @mysql_query($query);
 
$currentsub = @mysql_query($query2);
 
if($currentspecial != $currentsub) {
//...

There is no way to compare two arrays in a such way. Let's see... you are comparing $currentspecial with $currentsub and actually you are comparing the Resource ID's returned by the mysql_query() function, not arrays of data.

To compare two columns of data, we first need to check if their length is equal. Next, if length is the same, we check every row from those columns and we "break" at first false result. If we passed rows equal to length then we have similar columns.

There you go a code snippet. It updates second column to have values as in first column.

<?php

function checkData(&$d1, &$d2)
{
	if(count($d1) != count($d2))
		return false;
	
	for($i = 0; $i < count($d1); $i++)
	{
		if($d1[$i] !== $d2[$i])
		{
			return false;
		}
	}
	
	return true;
}

mysql_connect('localhost', 'root', '');
mysql_select_db('test_db');

$data1 = Array();
$data2 = Array();

$sql = "SELECT col1 FROM test_tbl";
$stmt = mysql_query($sql);

while($row = mysql_fetch_assoc($stmt))
{
	$data1[] = $row['col1'];
}

$sql = "SELECT col2 FROM test_tbl";
$stmt = mysql_query($sql);

while($row = mysql_fetch_assoc($stmt))
{
	$data2[] = $row['col2'];
}

$c1 = count($data1);

if(!checkData($data1, $data2))
{
	$sql = "DELETE FROM test_tbl";
	mysql_query($sql);

	for($i = 0; $i < $c1; $i++)
	{
		$sql = "INSERT INTO test_tbl (col1, col2) VALUES ('".$data1[$i]."','".$data1[$i]."')";
		mysql_query($sql);
	}
}


?>

i dont understand the problem? why do you want to compare the 2 col if theyre equal? when you can just do that before you start storing it to the database.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.