how to extract array variables from xml sheet or database?

SilverNodashi · Oct 24, 2012

Hi,

I hope someone can help me. I need to extract certain details ("Full Name and Surname", "Email", "Contact Number) from a MySQL DB but can't figure out how to do this. The application in question, "Grunion Contact Form" for Wordpress saves the data in the DB, in an array, as follows:

Code:

Array
(
    [Full Name and Surname:] =&gt; Sharon Somerset
    [Email] =&gt; [email protected]
    [Contact Number:] =&gt; 08xxxxxx
    [Alternative Number] =&gt; 011xxxxxx
    [Please list convenient time to be contacted] =&gt; 
    [Medical Aid Quote] =&gt; Yes
    [Insurance Quote] =&gt; 
    [Policy Update] =&gt; 
    [Friend's Name and Surname:] =&gt; 
    [Friend's Contact Number:] =&gt; 
    [How did you find us?] =&gt; Google
)

Each entry is in it's own database row, inside "longtext" field.

How can I easily extract "Full Name and Surname", "Email", "Contact Number" data from a few thousands rows like this, and re-save in a simple table or CSV file to import into something like phplist

ViperGTI · Oct 24, 2012

I'm don't really know anything about MySQL and even in MS SQL, my text processing skills are not great... but a small .NET app should be able to do it quickly and easily.

SilverNodashi · Oct 24, 2012

Thanx Viper, but I'm not a .NET developer, nor could I find anything which can do this. Any suggestions?

ViperGTI · Oct 24, 2012

Can you export the table to a MS Access database or some format other format? It should take 10 minutes to write something quickly.

SilverNodashi · Oct 24, 2012

ViperGTI said:
Can you export the table to a MS Access database or some format other format? It should take 10 minutes to write something quickly.

Well, I should be able to. It's in MySQL by default, which can export to anything. Wordpress exported it as an XML sheet and I then imported it into Excel to see if I can do anything with it, but can't figure out how to extract those arrays from the data presented.

ViperGTI · Oct 24, 2012

If you can export it as MS Access or otherwise as XML, do that and send me a copy of the file with just 3 records in it (zipped). You can replace the cell and email with random characters. I'll PM you my email address.

Nod · Oct 24, 2012

You can use the array function in php: http://php.net/manual/en/control-structures.foreach.php
The basics:

Code:

foreach($arr as $value) {
  echo $value;
}

What could work better if the keys are not numerical:

Code:

foreach($arr as $key => $value) {
    echo "$key $value";
}

SilverNodashi · Oct 24, 2012

Nod, the problem is that the data which I need is "embedded" inside a string / array, inside the selective database table.
The structure is as follows:

Database "wp-data" -> table "wp_posts" > row / field "post_content". There are a few thousand rows, each with the following content (with varying data in each field)

Array
(
[Full Name and Surname:] => Sharon Somerset
=> [email protected]
[Contact Number:] => 08xxxxxx
[Alternative Number] => 011xxxxxx
[Please list convenient time to be contacted] =>
[Medical Aid Quote] => Yes
[Insurance Quote] =>
[Policy Update] =>
[Friend's Name and Surname:] =>
[Friend's Contact Number:] =>
[How did you find us?] => Google
)
[/QUOTE]

When I import it into Access, Access breaks down the data into rows as follows:
[QUOTE]
meta_key
_feedback_author_email -> michelle@xxxxxxx
_feedback_author -> michelle
_feedback_author_url ->
_feedback_subject ->
_feedback_ip ->
_feedback_contact_form_url ->
_feedback_all_fields -> a:8:{s:22:"Full Name and Surname:";s:8:"michelle";s:5:"Email";s:22:"michelle@xxxxx";s:15:"Contact Number:";s:10:"082xxxxxxx";s:17:"Medical Aid Quote";s:3:"Yes";s:15:"Insurance Quote";s:0:"";s:13:"Policy Update";s:0:"";s:26:"Friend's Name and Surna
_feedback_extra_fields -> a:6:{s:15:"Contact Number:";s:10:"0xxxxxxx";s:17:"Medical Aid Quote";s:3:"Yes";s:15:"Insurance Quote";s:0:"";s:13:"Policy Update";s:0:"";s:26:"Friend's Name and Surname:";s:6:"graham";s:24:"Friend's Contact Number:";s:10:"0829xxxxx";}

[/QUOTE]

Now, I need to extract the client's contact details from the "_feedback_all_fields " and "_feedback_extra_fields" rows, as follows:

[QUOTE]"Full Name and Surname:";s:8:"michelle" -> "Full Name: Michelle"
"Email";s:22:"michelle@xxxxx" -> "Email: michelle@xxxxx"
"Contact Number:";s:10:"082xxxxxxx" -> "Contact Number: 082xxxxxxx"
"Friend's Contact Number:";s:10:"0829xxxxx" -> "Friend's Contact Number: 0829xxxxx"
[/QUOTE]

I hope this helps?

At the end of the day I need to export those names, emails and contact details to use in phplist and a CRM

guest2013-1 · Oct 24, 2012

SoftDux-Rudi said:
Nod, the problem is that the data which I need is "embedded" inside a string / array, inside the selective database table.
The structure is as follows:

Database "wp-data" -> table "wp_posts" > row / field "post_content". There are a few thousand rows, each with the following content (with varying data in each field)

When I import it into Access, Access breaks down the data into rows as follows:

Now, I need to extract the client's contact details from the "_feedback_all_fields " and "_feedback_extra_fields" rows, as follows:

I hope this helps?

At the end of the day I need to export those names, emails and contact details to use in phplist and a CRM

Nod didn't give you the whole answer. Do what he did, but loop through the database

[database loop]
$arr = $fieldindatabase_containing_the_array
[array loop suggested by Nod]
[/arrayloop]
[/database loop]

Really not difficult

Nod · Oct 24, 2012

You get this out of the database:

Code:

Array
(
    [Full Name and Surname:] =&gt; Sharon Somerset
    [Email] =&gt; [email protected]
    [Contact Number:] =&gt; 08xxxxxx
    [Alternative Number] =&gt; 011xxxxxx
    [Please list convenient time to be contacted] =&gt; 
    [Medical Aid Quote] =&gt; Yes
    [Insurance Quote] =&gt; 
    [Policy Update] =&gt; 
    [Friend's Name and Surname:] =&gt; 
    [Friend's Contact Number:] =&gt; 
    [How did you find us?] =&gt; Google
)

Now assign it to a php variable:

Code:

$myNewArray = $RESfromQuery;

You are creating an array in effect:

Code:

$myNewArray = Array
(
    [Full Name and Surname:] =&gt; Sharon Somerset
    [Email] =&gt; [email protected]
    [Contact Number:] =&gt; 08xxxxxx
    [Alternative Number] =&gt; 011xxxxxx
    [Please list convenient time to be contacted] =&gt; 
    [Medical Aid Quote] =&gt; Yes
    [Insurance Quote] =&gt; 
    [Policy Update] =&gt; 
    [Friend's Name and Surname:] =&gt; 
    [Friend's Contact Number:] =&gt; 
    [How did you find us?] =&gt; Google
);

Some testing might be needed, but this is the general idea.
Just loop through all the values you get from the DB, and do what is needed with each result.

Nod · Oct 24, 2012

For the content that looks like:

Code:

_feedback_all_fields -> a:8:{s:22:"Full Name and Surname:";s:8:"michelle";s:5:"Email";s:22:"michell e@xxxxx";s:15:"Contact Number:";s:10:"082xxxxxxx";s:17:"Medical Aid Quote";s:3:"Yes";s:15:"Insurance Quote";s:0:"";s:13:"Policy Update";s:0:"";s:26:"Friend's Name and Surna...

It is serialized. You should be able to unserialize it to get the original array again.

nakedpeanut · Oct 24, 2012

So my php skill are non existent, but a quick python script could easily transform that data for you.

Just looking at it:
a:8: indicates an array of length 8
{} encapsulate the array
s:22: indicates that the element is of type string i assume and 22 characters long.

It's basically a stream of data that can be interpreted quite easily.

guest2013-1 · Oct 25, 2012

nakedpeanut said:
So my php skill are non existent, but a quick python script could easily transform that data for you.

Just looking at it:
a:8: indicates an array of length 8
{} encapsulate the array
s:22: indicates that the element is of type string i assume and 22 characters long.

It's basically a stream of data that can be interpreted quite easily.

PHP:

unserialize($arr);

*done*

zippy · Oct 26, 2012

MySQL 5.1 has some XML functions.

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

So if you have 5.1 and above you can try those

Join the MyBroadband community

Get started

how to extract array variables from xml sheet or database?

SilverNodashi

Expert Member

ViperGTI

Well-Known Member

SilverNodashi

Expert Member

ViperGTI

Well-Known Member

SilverNodashi

Expert Member

ViperGTI

Well-Known Member

Nod

Honorary Master

SilverNodashi

Expert Member

guest2013-1

guest

Nod

Honorary Master

Nod

Honorary Master

nakedpeanut

Expert Member

guest2013-1

guest

zippy

Honorary Master