One for the PHP Guru's...

[KrisCons]

Hi all,

Think this is one for the guru's out there.

I have an old table structure which needs to be exported into a new table structure.

The old structure is as follows:

field1, field2, field3

field3 is a memo field which stores result data in an array in the following format:

1,1,2#1,2,1#,1,3,1#1,4,1# and so on....

I need to move the old structure of field1 and field 2 into the new table but at the same time breaking up the array in field3 and insert the individual numbers into individual fields (field4, field5, field6) in the new table of the same record.

Take the above array as an example:

1,1,2# = Section 1, Question 1, Answer Provided = 2, (which is = 1,1,2) and then i used a # to separate each element in the array.

So my new structure should be:

field1, field2, field3, field4, field5 (where field3, 4 and 5 contain the individual elements from the array in the old structure)

Its way above my head.

Thanks in advance.
K.

[bpat1434]

<?php
$sq = "INSERT INTO `new_table` (field1, field2, field3, field4, field5) VALUES";  

// We'll use this for our second query, to do all the inserts and such...

$query = "SELECT * FROM `table` ORDER BY field1 ASC"; // Get all info from last table
$result = mysql_query($query) or die(mysql_errno().'<br>'.mysql_error());
// Query for results or kill script with error message

while($r = mysql_fetch_array($result))
{
  // Explode the item on the "#" sign....
  $arr = explode('#', $r['field3']);

  foreach($arr as $item)
  {
    // Get each number in csv...
    list($m1, $m2, $m3) = explode(',', $item);

$sq .= " ('$r['field1']', '$r['field2']', '$m1', '$m2', '$m3'),";
  }
}
$sq = substr($sq, 0, -1); // Remove the final "," which isn't needed and will cause errors

// Debugging line... just comment it out when it's working:
$str = str_replace('), ', '),<br>', $sq); // Just make sure query has info you need
echo $str;

$rslt = mysql_query($sq) or die(mysql_errno().'<br>'.mysql_error()); // Run the new INSERT query
if(($rows = mysql_affected_rows($rslt))>0)
{
  echo 'Successfully inserted '.$rows.' rows into the new database!!';
}
else
{
  echo 'Something went wrong....<br>'.mysql_error();
}

Perhaps something like that would get you moving....

That is all assuming that your primary key field for the new table is not field1 AND field1 can have duplicates....

[KrisCons]

Hi

I dont understand where the insert into my 'new table' occurs?

Is there information missing from the emaple you provide above? I have tried running the above code but it doesn;t seem to work initially. It also deosnt echo the 'str_replace('), ', '),<br>', $sq);' part in the middle.

Any suggetsions?

Thanks for the prompt reply.
K

[bpat1434]

This inserts our created query:

$rslt = mysql_query($sq) or die(mysql_errno().'<br>'.mysql_error()); 

It's just after the while() loop, sorry for not commenting it...