Array to DB

[smilesmita]

hi there,

I have converted my xml string into array of this form.I wanted to know how i can put the array values and data into a database table...how shud i extract it.for example

SubscriptionFile is the column in my table and i want the value of it to get poplulated in the table.

Array
(
[SubscriptionFile] => xxxxxxxxxxxxxxx
[FileName] => xxxxxxxx1_1xxxxxxxx
[StatusType] => UUnread

 => A
[Description] => Active
[Manifest] => Cxxxxxxxxxxxxx
4601 MACIE STCHARLOTTENC28217-1810US
CEHG4220 LAURA KOPPEHOUSTONTX770165029US

[Shipper] => xxxxxxxxxxxxxx
4601 MACIE STCHARLOTTENC28217-1810US

[ShipperNumber] => xxxxxxxxxx
[Address] => 4xxxxxxxxxxxx
[ShipTo] =>
Cxxxxxxxxxxx
[ConsigneeName] => xxxxxxxxxxx
[AddressLine1] => 4601 MACIE ST
[City] => CHARLOTTE
[StateProvinceCode] => NC
[PostalCode] => 28217-1810
[CountryCode] => US
[ReferenceNumber] => xxxxxxxxxxxxxx
[Service] => 003
[PickupDate] => 20070731
[ScheduledDeliveryDate] => 20070803
[Package] => xxxxxxxxxxxxxxx
[Activity] => xxxxxxxxxxxx
[Date] => 20070731
[Time] => 101300
[TrackingNumber] => 1xxxxx
[PackageServiceOptions] =>
[BillToAccount] => xxxxxxxxxxx
[Option] => xxxxxxxxxx
[Number] => xxxxxxxxxxx
[SubscriptionFile] => xxxxxxxxxxxxxxx
[FileName] => xxxxxxxx1_1xxxxxxxx
[StatusType] => UUnread
[Code] => A
[Description] => Active
[Manifest] => Cxxxxxxxxxxxxx
4601 MACIE STCHARLOTTENC28217-1810US
CEHG4220 LAURA KOPPEHOUSTONTX770165029US

[Shipper] => xxxxxxxxxxxxxx
4601 MACIE STCHARLOTTENC28217-1810US

[ShipperNumber] => xxxxxxxxxx
[Address] => 4xxxxxxxxxxxx
[ShipTo] =>
Cxxxxxxxxxxx
[ConsigneeName] => xxxxxxxxxxx
[AddressLine1] => 4601 MACIE ST
[City] => CHARLOTTE
[StateProvinceCode] => NC
[PostalCode] => 28217-1810
[CountryCode] => US
[ReferenceNumber] => xxxxxxxxxxxxxx
[Service] => 003
[PickupDate] => 20070731
[ScheduledDeliveryDate] => 20070803
[Package] => xxxxxxxxxxxxxxx
[Activity] => xxxxxxxxxxxx
[Date] => 20070731
[Time] => 101300
[TrackingNumber] => 1xxxxx
[PackageServiceOptions] =>
[BillToAccount] => xxxxxxxxxxx
[Option] => xxxxxxxxxx
[Number] => xxxxxxxxxxx)
)

[Horizon88]

You'll want to use a foreach loop to build the query, most likely.

something where you build the sql query at the start, then add the array values as you go through the loop. I don't know mySQL queries really well, so that's probably not even a valid way to do that bit, but:

$sql = 'INSERT INTO table ';
foreach ($yourArray as $key => $value) {
    $sql .= '('.$key.') VALUES ('.$value.')';
}
mysql_query($sql);

Anyone who's a bit craftier with mySQL wanna clean up that query for him? 🙂

[nemonoman]

Yes, yours will fail.
Again assuming that array keynames are the same as db column names.

$count=count($yourarray);
$sql="INSERT INTO myTable SET ";
$i=1;
foreach($yourarray as $key=>$value){
    $sql.="$key='$value' ";
    if($i<$count){
         $sql.=',';
    }
    $i++;
}

[bpat1434]

Or, forget the foreach loop and just serialize the array and store it in the database: [man]serialize[/man]

If you don't need to search on the data, just serialize it and insert it.

If you're talking about just one array key/value pair, then just do:

$query = "INSERT INTO `tablename` (`Subscriptionfile`) VALUES ('" . mysql_real_escape_string($array['SubscriptionFile']) . "')";