Getting Mysql encoded Content with PHP

[rilana]

Hi 

 

I am trying to write a dump to save data from a table in an excel. But I am having trouble with this wierd data entry, here is an example what it looks like:

:4:"name";s:31:"Zusätzlicher Betreff Hydraulik";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:0:"";}i:4;a:7:{s:2:"id";s:2:"17";s:4:"slug";s:22:"betreff-wasserabwasser";s:4:"name";s:23:"Betreff Wasser/Abwasser";s:4:"type";s:8:"checkbox";s:7:"options";s:250:"a:8: 

thats only a small part of it, but I think you get the idea. Alle the information I need is there, but it's hard to find because of the way it's coded.

My approach was trying to find out if it would work with json decode, but I am not evan sure if that is what it is called.

 

This is my approach:

//query get data
	$sql = mysql_query("SELECT * FROM wp_visual_form_builder_entries ORDER BY form_id ASC");
	$no = 1;
	$emparray = array();

	
	while($result = mysql_fetch_assoc($sql)){
		
		
	  $row = $result['data'];
      $emparray[] = $row;
	  
	  echo $emparray;
	  
	  json_decode( $emparray, true );

	  
	  //json_encode($emparray, true);
	  
	foreach ($emparray as $v) {
	    echo "Current value of \$emparray ist gleich $v.\n";
	}

But it still gives out the same format. [link removed]

 

Can you please lead me to the right direction? I have no Idea what to do. I would appreciate it!

 

Thank you!

Rilana

Edited March 12, 2016 by requinix
removed a link that looked like it was outputting real data for real people

[ginerjm]

1 - the sample you provided seems to be readable but when I read into deeper I sense that your sample is not a good one since there is a consistency problem. For ex., the 'a' element seems to indicate an array but there is no closing brace

2 - What is your table layout - just one field?

3 - You want to take data from this complex format and put it into a similary non-dataprocessing-friendly format? Why?

 

IMHO - the best approach would be to convert this table into a proper database format (looks like it might need at least 3 tables from your sample) and then you would have a useful data source. Which - if you really have to - would be easier to dump to excel.

[Jacques1]

unserialize()

[ginerjm]

Aha! So it's not an unknown format. Leave it to the Gurus to recognize that! Kudos Jacques1 - AGAIN!

[rilana]

Oh wow, thanks, I just ran an online tool called https://www.functions-online.com/unserialize.html and it give me an output of

array (
  0 => 
  array (
    'id' => '1',
    'slug' => '1',
    'name' => '',
    'type' => 'fieldset',
    'options' => '',
    'parent_id' => '0',
    'value' => '',
  ),
  1 => 
  array (
    'id' => '5',
    'slug' => 'anrede',
    'name' => 'Anrede',
    'type' => 'select',
    'options' => 'a:2:{i:0;s:4:"Herr";i:1;s:4:"Frau";}',
    'parent_id' => '0',
    'value' => 'Frau',
  ),
  2 => 
  array (
    'id' => '6',
    'slug' => 'vorname',
    'name' => 'Vorname',
    'type' => 'text',
    'options' => '',
    'parent_id' => '0',
    'value' => 'Irene',
  ),
  3 => 
  array (
    'id' => '7',
    'slug' => 'nachname',
    'name' => 'Nachname',
    'type' => 'text',
    'options' => '',
    'parent_id' => '0',
    'value' => 'Oppliger',
  ),
  4 => 
  array (
    'id' => '104',
    'slug' => 'firma',
    'name' => 'Firma',
    'type' => 'text',
    'options' => '',
    'parent_id' => '0',
    'value' => 'OLAER',
  ),
  5 => 
  array (
    'id' => '105',
    'slug' => 'abteilung',
    'name' => 'Abteilung',
    'type' => 'text',
    'options' => '',
    'parent_id' => '0',
    'value' => 'Marketing',
  ),
  6 => 
  array (
    'id' => '10',
    'slug' => 'email',
    'name' => 'Email',
    'type' => 'email',
    'options' => '',
    'parent_id' => '0',
    'value' => 'i.oppliger@bluewin.ch',
  ),
  7 => 
  array (
    'id' => '9',
    'slug' => 'sprache',
    'name' => 'Sprache',
    'type' => 'select',
    'options' => 'a:3:{i:0;s:7:"Deutsch";i:1;s:8:"Englisch";i:2;s:12:"Französisch";}',
    'parent_id' => '0',
    'value' => 'Deutsch',
  ),
  8 => 
  array (
    'id' => '2',
    'slug' => '2',
    'name' => '',
    'type' => 'verification',
    'options' => '',
    'parent_id' => '0',
    'value' => '',
  ),
  9 => 
  array (
    'id' => '3',
    'slug' => '3',
    'name' => '',
    'type' => 'secret',
    'options' => '',
    'parent_id' => '2',
    'value' => '',
  ),
  10 => 
  array (
    'id' => '4',
    'slug' => 'anmelden',
    'name' => 'Anmelden',
    'type' => 'submit',
    'options' => '',
    'parent_id' => '2',
    'value' => '',
  ),
)

So now I just need to figure out how to get only the data I need and save it in tables so I can write an xls. Wow thank you. Maby I will have a view questions on the way.

 

Thanks, Rilana

[Jacques1]

Your test script currently shows a complete dump of all customer requests. I suggest you take that offline immediately.

[rilana]

Thank you Guru, the Admin took care of that and I changed the url. I was reading a lot about unserialize() but I am having trouble to get it to work. For an exemple the following code does not execute all the data. When I just echo the $serialized_array it gives me all the content, but the var_dump does not give me all the content. Any Ideas?

$serialized_array = $result['data'];
	$deserialized_array = unserialize($serialized_array);
	var_dump($deserialized_array);

[Jacques1]

This is too vague.

 

Reduce $result['data'] to a minimal example and post it here. Then show the result you got and explain why that isn't the result you expected.

[rilana]

Oh I wished I could tell you what the pattern is, but I just dont see one. It seems like the first 42 Records have a bool(False) error. Below the output.

bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) bool(false) array(21) { [0]=> array(7) { ["id"]=> string(3) "501" ["slug"]=> string(3) "501" ["name"]=> string(0) "" ["type"]=> string( "fieldset" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [1]=> array(7) { ["id"]=> string(3) "505" ["slug"]=> string(7) "contact" ["name"]=> string(7) "Contact" ["type"]=> string( "checkbox" ["options"]=> string(141) "a:4:{i:0;s:22:"Product documentations";i:1;s:28:"Attending a sales consultant";i:2;s:14:"Please call me";i:3;s:23:"I have another question";}" ["parent_id"]=> string(1) "0" ["value"]=> string(23) "I have another question" } [2]=> array(7) { ["id"]=> string(3) "506" ["slug"]=> string(10) "hydraulics" ["name"]=> string(10) "Hydraulics" ["type"]=> string( "checkbox" ["options"]=> string(278) "a:9:{i:0;s:22:"Hydraulic accumulators";i:1;s:17:"Coolers and pumps";i:2;s:15:"Cooling systems";i:3;s:15:"Air Oil Coolers";i:4;s:13:"Plate Coolers";i:5;s:32:"Active cooling systems / Chiller";i:6;s:26:"Enclosure air conditioning";i:7;s:17:"Hydraulic filters";i:8;s:8:"Services";}" ["parent_id"]=> string(1) "0" ["value"]=> string(22) "Hydraulic accumulators" } [3]=> array(7) { ["id"]=> string(3) "508" ["slug"]=> string(14) "other-products" ["name"]=> string(14) "Other products" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [4]=> array(7) { ["id"]=> string(3) "507" ["slug"]=> string(17) "water-waste-water" ["name"]=> string(19) "Water / Waste Water" ["type"]=> string( "checkbox" ["options"]=> string(400) "a:10:{i:0;s:28:"Calculations and simulations";i:1;s:31:"Service and maintenance on site";i:2;s:33:"Shock absorber for drinking water";i:3;s:30:"Shock absorber for waste water";i:4;s:36:"Pressure vessels / Expansion vessels";i:5;s:17:"Pulsation dampers";i:6;s:40:"Small dampers for sanitary installations";i:7;s:28:"Bleeder and degassing valves";i:8;s:16:"No return valves";i:9;s:14:"Other services";}" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [5]=> array(7) { ["id"]=> string(3) "509" ["slug"]=> string(14) "other-products" ["name"]=> string(14) "Other products" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [6]=> array(7) { ["id"]=> string(3) "510" ["slug"]=> string(16) "personal-remarks" ["name"]=> string(16) "Personal remarks" ["type"]=> string( "textarea" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(187) "We are bidding on a job that includes parts for accumulator units. Please advise who I should send my inquiry to. A quote is needed no later then 12-31 for us to meet the bid close date." } [7]=> array(7) { ["id"]=> string(3) "519" ["slug"]=> string(13) "personal-data" ["name"]=> string(13) "Personal data" ["type"]=> string( "fieldset" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [8]=> array(7) { ["id"]=> string(3) "511" ["slug"]=> string(7) "company" ["name"]=> string(7) "Company" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(13) "Teletec Corp." } [9]=> array(7) { ["id"]=> string(3) "512" ["slug"]=> string(10) "department" ["name"]=> string(10) "Department" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(10) "Purchasing" } [10]=> array(7) { ["id"]=> string(3) "513" ["slug"]=> string(10) "first-name" ["name"]=> string(10) "First name" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(5) "Diane" } [11]=> array(7) { ["id"]=> string(3) "514" ["slug"]=> string(7) "surname" ["name"]=> string(7) "Surname" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(6) "Hunter" } [12]=> array(7) { ["id"]=> string(3) "515" ["slug"]=> string(13) "street-adress" ["name"]=> string(13) "Street adress" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(26) "5617 Departure Dr. Ste 107" } [13]=> array(7) { ["id"]=> string(3) "516" ["slug"]=> string(15) "postal-codecity" ["name"]=> string(16) "Postal code/City" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(13) "Raleigh 27616" } [14]=> array(7) { ["id"]=> string(4) "1427" ["slug"]=> string(7) "country" ["name"]=> string(7) "Country" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(3) "USA" } [15]=> array(7) { ["id"]=> string(3) "517" ["slug"]=> string(6) "e-mail" ["name"]=> string(6) "E-Mail" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(30) "dhunter@teleteccorporation.com" } [16]=> array(7) { ["id"]=> string(3) "518" ["slug"]=> string(5) "phone" ["name"]=> string(5) "Phone" ["type"]=> string(4) "text" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(10) "9199547300" } [17]=> array(7) { ["id"]=> string(3) "520" ["slug"]=> string( "language" ["name"]=> string( "Language" ["type"]=> string(5) "radio" ["options"]=> string(58) "a:3:{i:0;s:6:"German";i:1;s:6:"French";i:2;s:7:"English";}" ["parent_id"]=> string(1) "0" ["value"]=> string(7) "English" } [18]=> array(7) { ["id"]=> string(3) "502" ["slug"]=> string(17) "security-question" ["name"]=> string(17) "Security question" ["type"]=> string(12) "verification" ["options"]=> string(0) "" ["parent_id"]=> string(1) "0" ["value"]=> string(0) "" } [19]=> array(7) { ["id"]=> string(3) "503" ["slug"]=> string(31) "please-enter-a-two-digit-number" ["name"]=> string(31) "Please enter a two-digit number" ["type"]=> string(6) "secret" ["options"]=> string(0) "" ["parent_id"]=> string(3) "502" ["value"]=> string(2) "12" } [20]=> array(7) { ["id"]=> string(3) "504" ["slug"]=> string(4) "send" ["name"]=> string(4) "Send" ["type"]=> string(6) "submit" ["options"]=> string(0) "" ["parent_id"]=> string(3) "502" ["value"]=> string(0) "" } }

And the output with simple echo just part of it.

a:11:{i:0;a:7:{s:2:"id";s:1:"1";s:4:"slug";s:1:"1";s:4:"name";s:0:"";s:4:"type";s:8:"fieldset";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:0:"";}i:1;a:7:{s:2:"id";s:1:"5";s:4:"slug";s:6:"anrede";s:4:"name";s:6:"Anrede";s:4:"type";s:6:"select";s:7:"options";s:36:"a:2:{i:0;s:4:"Herr";i:1;s:4:"Frau";}";s:9:"parent_id";s:1:"0";s:5:"value";s:4:"Frau";}i:2;a:7:{s:2:"id";s:1:"6";s:4:"slug";s:7:"vorname";s:4:"name";s:7:"Vorname";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:5:"Irene";}i:3;a:7:{s:2:"id";s:1:"7";s:4:"slug";s:8:"nachname";s:4:"name";s:8:"Nachname";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:8:"Oppliger";}i:4;a:7:{s:2:"id";s:3:"104";s:4:"slug";s:5:"firma";s:4:"name";s:5:"Firma";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:5:"OLAER";}i:5;a:7:{s:2:"id";s:3:"105";s:4:"slug";s:9:"abteilung";s:4:"name";s:9:"Abteilung";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:9:"Marketing";}i:6;a:7:

[Jacques1]

Take one of the records that yield false and attach the complete record (assuming it doesn't contain any sensitive data).

[rilana]

This is the first record of the db. serialized it buts out bool(false)

a:11:{i:0;a:7:{s:2:"id";s:1:"1";s:4:"slug";s:1:"1";s:4:"name";s:0:"";s:4:"type";s:8:"fieldset";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:0:"";}i:1;a:7:{s:2:"id";s:1:"5";s:4:"slug";s:6:"anrede";s:4:"name";s:6:"Anrede";s:4:"type";s:6:"select";s:7:"options";s:36:"a:2:{i:0;s:4:"Herr";i:1;s:4:"Frau";}";s:9:"parent_id";s:1:"0";s:5:"value";s:4:"Frau";}i:2;a:7:{s:2:"id";s:1:"6";s:4:"slug";s:7:"vorname";s:4:"name";s:7:"Vorname";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:5:"Irene";}i:3;a:7:{s:2:"id";s:1:"7";s:4:"slug";s:8:"nachname";s:4:"name";s:8:"Nachname";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:8:"Oppliger";}i:4;a:7:{s:2:"id";s:3:"104";s:4:"slug";s:5:"firma";s:4:"name";s:5:"Firma";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:5:"OLAER";}i:5;a:7:{s:2:"id";s:3:"105";s:4:"slug";s:9:"abteilung";s:4:"name";s:9:"Abteilung";s:4:"type";s:4:"text";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:9:"Marketing";}i:6;a:7:{s:2:"id";s:2:"10";s:4:"slug";s:5:"email";s:4:"name";s:5:"Email";s:4:"type";s:5:"email";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:21:"i.oppliger@....ch";}i:7;a:7:{s:2:"id";s:1:"9";s:4:"slug";s:7:"sprache";s:4:"name";s:7:"Sprache";s:4:"type";s:6:"select";s:7:"options";s:67:"a:3:{i:0;s:7:"Deutsch";i:1;s:8:"Englisch";i:2;s:12:"Französisch";}";s:9:"parent_id";s:1:"0";s:5:"value";s:7:"Deutsch";}i:8;a:7:{s:2:"id";s:1:"2";s:4:"slug";s:1:"2";s:4:"name";s:0:"";s:4:"type";s:12:"verification";s:7:"options";s:0:"";s:9:"parent_id";s:1:"0";s:5:"value";s:0:"";}i:9;a:7:{s:2:"id";s:1:"3";s:4:"slug";s:1:"3";s:4:"name";s:0:"";s:4:"type";s:6:"secret";s:7:"options";s:0:"";s:9:"parent_id";s:1:"2";s:5:"value";s:0:"";}i:10;a:7:{s:2:"id";s:1:"4";s:4:"slug";s:8:"anmelden";s:4:"name";s:8:"Anmelden";s:4:"type";s:6:"submit";s:7:"options";s:0:"";s:9:"parent_id";s:1:"2";s:5:"value";s:0:"";}}

[Jacques1]

There's a string which is incorrectly declared to have 21 characters but only has 17 due to some kind of truncation:

"i.oppliger@....ch"

I assume you did that to anonymize the data?

 

Since you have an umlaut in the string (“Französisch”) there could be a character encoding issue. What's the exact strlen() of the encoded data? It should be 1952. Which character encoding does your database connection use? Execute this query within the Wordpress script and post the output:

SHOW VARIABLES LIKE  'character_set%';

Edited March 12, 2016 by Jacques1

[rilana]

I did the ...... by hand. sorry. The Script I don't have within wordpress. The show Variables is not working. But i checked the database, it says 

 

longtext utf8_general_ci
 

and wp config say's 

define('DB_CHARSET', 'utf8');

/** Der collate type sollte nicht geändert werden */
define('DB_COLLATE', '');

The data-field is created by a plugin called Visual Form Builder

[Jacques1]

What's the strlen() of the serialized data?

What does mb_detect_encoding($result['data']) say for the serialized data?

Can you deserialize the file I attached?

wp_serialized_fixed.txt

[rilana]

echo strlen($deserialized_array);

gives out 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

echo mb_detect_encoding($deserialized_array);

gives out ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII ASCII

 

Your provided example also gives out ASCII.

[Jacques1]

I'm talking about the serialized string. An array is not a string, trying to get its string length obviously makes no sense.

 

This back-and-forth is getting tedious, so I suggest you write the serialized string to a file and attach it to your post:

$serialized_data = $result['data'];
if (unserialize($serialized_data) === false)
{
    file_put_contents('/path/to/file', $serialized_data);
    exit;
}

The file path must be writable by the webserver, so make sure that's the case (when in doubt, use something like an upload directory).

 

Please attach the file, don't just post its content.

 

Also check the PHP error log. It should say exactly where the deserialization failed (“Notice: unserialize(): Error at offset ...”).

 

So I need two things:

1. A file containing the erroneous serialized string (which you get from the code above).
2. If available, the corresponding notice from the PHP error log.

[rilana]

hi and thank you for your patience. I tryed to get the error log, but shared host-person was not available.

I tryed a view things:

 

1. writing a txt File with your provided code «see attached»

2. realized it is not all the info like with echo «see attached file»

3. tryed to execute the provided code local on MAMP-Server «There was no txt File written and the ä ö ü where not set in utf8»

 

I am grateful for your patience with me. 

data-new.txt

echo.txt

[Jacques1]

OK. As I suspected, there's a character encoding issue.

 

The data was originally serialized with the UTF-8 encoding, which means special characters like umlauts take up multiple bytes. In the serialized data, each string is declared with its physical length in bytes:

s:19:"Kontaktmöglichkeit"

(that's 19 bytes due to the “ö” taking up 2 bytes)

 

However, when you retrieve the string, your application (or rather the database system) converts it to the ISO 8859-1 encoding, which means every character only takes up one byte. This leads to a mismatch between the declared string lengths and the actual string lengths.

 

As a quick workaround, you could try to convert the string back to UTF-8 before passing it to unserialize():

// convert original serialized string to UTF-8
$serialized_data = utf8_encode($result['data']);

var_dump( unserialize($serialized_data) );

This, of course, doesn't solve the underlying problem: Your database connection uses ISO 8859-1 when it should use UTF-8.

 

What does the database connection code for this script (minus the credentials) look like?

[rilana]

Yes you are right, the dump now is much longer. And it converts some of the ü in ue and so on. My DB Connection looks like this.

//connection to mysql
	mysql_connect("localhost", "", ""); //server , username , password
	mysql_select_db("");
	
	
	//query get data
	$sql = mysql_query("SELECT * FROM wp_visual_form_builder_entries");
	$no = 1;

	
	while($result = mysql_fetch_assoc($sql)){

[Jacques1]

mysql_set_charset() sets the character encoding of the connection.

mysql_connect(...);
mysql_set_charset('UTF8');

[rilana]

Wow Thank you! This worked! Not sure yet how to get the data out of the arrays and put it into an excel that makes sense, but I will try to read up on it.