How to perform a MySQL query and retrieve data from an xml formated field?

NormsIM · July 15, 2018, 1:24am

I have a field “post_vars” in MySQL db that has XML text similar to the following:

<postback>
  <mc_gross>-1.00</mc_gross>
  <invoice>bill-9BA7054514E7A16992FACB7D55F15B10</invoice>
  <protection_eligibility>Eligible</protection_eligibility>
  <payer_id>DEWPJGLH65RFS</payer_id>
  <address_street>7722 SE 16th Ave</address_street>
</postback>

I have a sql query such as:

			$orderResult2 = mysqli_query($db,
			"
				SELECT post_vars FROM orders 	WHERE order_id = $orderID
			");
			$orderRows2 = mysqli_num_rows($orderResult2);
			$order2 = mysqli_fetch_array($orderResult2);
			echo '<pre>'; print_r($order2); echo '</pre>'; //WSS: Array Print
		}

The result that I get when looking at $order2 is just the values and is similar to the below. Only the pair values are returned and there are no labels in the return:

-1.00bill-9BA7054514E7A16992FACB7D55F15B10EligibleDEWPJGLH65RFS7722 SE 16th Ave

How would I actually return the contents of the field? Ultimately what I want to get to is a format that looks like this:

mc_gross = -1.00 | invoice = bill-9BA7054514E7A16992FACB7D55F15B10 | protection_eligibility = eligible | payer_id = DEWPJGLH65RFS | address_street = 7722 SE 16th Ave

John_Betong · July 15, 2018, 2:05am

Hi @NormsIM and a warm welcome to the SP Forums.

Try using var_dump(…); instead of print_r(…);

Mittineague · July 15, 2018, 3:51am

It looks like you are SELECTing a single field - post_vars

Are you able to ALTER the table or CREATE a new table so that the fields more closely mirror the XML element names?

SamA74 · July 15, 2018, 8:37am

Yes, the sensible solution would be to use a table that stores single values in labelled columns, as databases are supposed to be.
If you want the final output in xml, there are tools in php for that such as SimpleXML.

On a side note:-

Do use prepared statements where you have variables in your query.

NormsIM · July 15, 2018, 11:42pm

Yes, I’m selecting a single field as you correctly pointed out. I can’t really alter the table as it is set up by a purchased script that does a lot of things. I can read the table and do various selects on it, but I can’t (without great trepidation) change any of the structure of the table. Unfortunately the vendor of the software (they are no longer around) has this post_vars field as one of many fields in the record. All the info in this field is stored in xml format and I’m trying to get access to the labels and values so I can reformat.

But… as you can see reading the field via the SELECT doesn’t return the full content of the field - not even the xml format… it only returns the values. I can view the field in phpMyAdmin and when I do a SELECT query within phpMyAdmin I can see the full content of the field. But when I run the query via PHP it just returns the concatenated values.

NormsIM · July 15, 2018, 11:46pm

Thanks for the additional pointers SamA74. I must admit, I’m kinda a neub when it comes to SQL. Just enough to be dangerous.

What do you mean by “prepared statements”? It could be that the real query that I’m running is that way. I shortened the original query and xml just to eliminate a lot of stuff that isn’t relevant and to save everyone from having to sift through a bunch of stuff.

John_Betong · July 15, 2018, 11:59pm

Try enclosing $order2 variable inside htmlspecialchars.

http://php.net/manual/en/function.htmlspecialchars.php

If the hidden XML fields are shown then the string could be parsed using PHP to be more user friendly.

Mittineague · July 16, 2018, 12:08am

I’m interested in knowing what the result in the CLI is for

SHOW CREATE TABLE `orders`

SamA74 · July 16, 2018, 9:27am

Although the main reason given for using them is efficiency in repeated queries, it is also used as an effective means of preventing sql injections where variables are placed in a query.

John_Betong · July 16, 2018, 1:01pm

Try this:

<?php 
declare(strict_types=1);
ini_set('display_errors', 'TRUE');
error_reporting(-1);

  $raw = <<< ____TMP
    <postback>
      <mc_gross>-1.00</mc_gross>
      <invoice>bill-9BA7054514E7A16992FACB7D55F15B10</invoice>
      <protection_eligibility>Eligible</protection_eligibility>
      <payer_id>DEWPJGLH65RFS</payer_id>
      <address_street>7722 SE 16th Ave</address_street>
    </postback>
____TMP;
echo '<pre>';


echo '<b> #1  output raw </b>';
  echo $raw;  
  echo'<br><br>';


echo '<b> #2 Remove linefeeds and double spaces </b>';
  $ok2 = str_replace(["\n", '  '], [', ', ''], $raw);
  echo '<br>$ok2 ==> ' .$ok2;
  echo'<br><br><br>';


echo '<b> #3  Make into an Array </b>';
  $ok3 = explode(',', $ok2); 
  echo '<br>$ok3 ==> ';
  print_r($ok3);
  echo '<br><br>';


echo '<b> #4  Remove empty Array elements </b>';
  $ok3   = array_splice($ok3, 1, 5); 
  echo '<br>$ok3 ==> ';
  print_r($ok3);
  echo '<br><br>';


echo '<b> #5  Create array items and output </b>';
  $items = [
    'mc_gross', 
    'invoice', 
    'protection_eligibility', 
    'payer_id', 
    'address_street'];

  echo '<dl>';
    foreach($items as $id => $item):
      echo '<dt>' .$item .'</dt>';
      echo '<dd>' .$ok3[$id] .'</dd>';
    endforeach;  
  echo '</dl>';

echo '</pre>';

**Output:** [quote] #1 output raw -1.00 bill-9BA7054514E7A16992FACB7D55F15B10 Eligible DEWPJGLH65RFS 7722 SE 16th Ave

#2 Remove linefeeds and double spaces
$ok2 ==> , -1.00, bill-9BA7054514E7A16992FACB7D55F15B10, Eligible, DEWPJGLH65RFS, 7722 SE 16th Ave,

#3 Make into an Array
$ok3 ==> Array
(
[0] =>
[1] => -1.00
[2] => bill-9BA7054514E7A16992FACB7D55F15B10
[3] => Eligible
[4] => DEWPJGLH65RFS
[5] => 7722 SE 16th Ave
[6] =>
)

#4 Remove empty Array elements
$ok3 ==> Array
(
[0] => -1.00
[1] => bill-9BA7054514E7A16992FACB7D55F15B10
[2] => Eligible
[3] => DEWPJGLH65RFS
[4] => 7722 SE 16th Ave
)

#5 Create array items and output

mc_gross
-1.00
invoice
bill-9BA7054514E7A16992FACB7D55F15B10
protection_eligibility
Eligible
payer_id
DEWPJGLH65RFS
address_street
7722 SE 16th Ave
[/quote]

Mittineague · July 16, 2018, 8:26pm

Likely a negligible performance difference, but I’m thinking it might be better to trim() off the bookend commas and do away with the splice().

( string function vs. array function)

John_Betong · July 17, 2018, 12:11am

I was thinking making the script into a function because it would not only be more flexible but will also cater for other XML scripts.

I hope to try later and incorporate trim() instead of array_slice().

Many thanks for the suggestion.

John_Betong · July 17, 2018, 3:14am

I tried using trim(…) to remove the leading and trailing commas and could not get it to work

Here is the promised function:

<?php 
declare(strict_types=1);
ini_set('display_errors', 'TRUE');
error_reporting(-1);

  $raw = <<< ____TMP
    <postback>
      <mc_gross>-1.00</mc_gross>
      <invoice>bill-9BA7054514E7A16992FACB7D55F15B10</invoice>
      <protection_eligibility>Eligible</protection_eligibility>
      <payer_id>DEWPJGLH65RFS</payer_id>
      <address_street>7722 SE 16th Ave</address_street>
    </postback>
____TMP;
echo '<pre>';


# ===========================================================
function prettyXml(string $sRaw, array $aItems=[], int $iMode=0)
:string
{
# 1 return values  
  $result = 'PROBLEMS :(';

# 2 Remove linefeeds and double spaces 
  $sRaw = str_replace(["\n", '  '], [', ', ''], $sRaw);

# 3 Make into an Array
  $aRaw = explode(',', $sRaw); 

# 4 Remove empty Array elements
  $aRaw = array_splice($aRaw, 1, 5); 

# 5 Return $result
  switch($iMode):
    case 0:
      $result = '';
      foreach($aItems as $id => $item):
        $result .= '<b>' .$item .' = </b>';
        $result .= $aRaw[$id]   . ' | ';
      endforeach;  
      # echo 'Switch Default';
    break;  

    case 1 :
      $result = '<dl>';
        foreach($aItems as $id => $item):
          $result .= '<dt>' .$item .'</dt>';
          $result .= '<dd>' .$aRaw[$id] .'</dd>';
        endforeach;  
      $result .= '</dl>';    
    break;

    default:
      $result = 'Non-existent $iMode ???';
  endswitch;    

  return $result;
}//endFunc()

 $aItems = [
    'mc_gross', 
    'invoice', 
    'protection_eligibility', 
    'payer_id', 
    'address_street'];
  echo prettyXml($raw, $aItems, 0);

 $aItems = [
    'One', 
    'Two', 
    'Three', 
    'Four', 
    'Five'];
  echo prettyXml($raw, $aItems, 1);

Output:

NormsIM · July 17, 2018, 5:41pm

Thanks so much! I could never get htmlspecialchars() to work no matter how hard I tried, nor could I get htmlentities() to work. But your suggestion got me thinking so I did a str_replace on ["<",">"] to ["<",">"] and viola! The “<…>” tags appeared in the variable.

I was then able to run through doing some more str_replace so that I could explode the variable into an array with [0] having the value label and [1] having the value. Also, it is dynamic because I don’t know ahead of time what the pair labels are (they vary). Something like:

[0]
‘mc_gross’,
‘invoice’,
‘protection_eligibility’,
‘payer_id’,
‘address_street’];
[1]
-1.00
bill-9BA7054514E7A16992FACB7D55F15B10
Eligible
DEWPJGLH65RFS
7722 SE 16th Ave

Thanks so much for the hints!

Mittineague · July 18, 2018, 7:55pm

Have you given the MySQL XML ExtractValue function a try?

https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html#function_extractvalue

It looks promising as long as you can get the XPath expressions right.

NormsIM · July 18, 2018, 8:59pm

No, I haven’t tried that one. Thanks for making me aware of it. It looks like it might work, except for one caveat. Looking at the function, it seems to require that you know what the tag labels are in order to extract the value for the tag. In this specific case I don’t know what the tag labels are going to be. They change based on different criteria so I don’t really know what the tag label for the value will be.

John_Betong · July 19, 2018, 3:34am

Here we go, Version: 002

<?php 
declare(strict_types=1);
ini_set('display_errors', 'TRUE');
error_reporting(-1);

// BEWARE: USING PHP HEREDOC
// FOLLOWING LINE MUST END WITH ONLY WITH A CARRIAGE RETURN
  $raw2 = <<< ____TMP
    <postback>
      <mc_gross>-1.00 </mc_gross>
      <invoice> bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
      <protection_eligibility> Eligible </protection_eligibility>
      <payer_id> DEWPJGLH65RFS </payer_id>
      <address_street> 7722 SE 16th Ave </address_street>
      <XXX-mc_gross> YYY--1.00 </mc_gross>
      <XXX-invoice> YYY-bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
      <XXX-protection_eligibility> YYY-Eligible </protection_eligibility>
      <XXX-payer_id> YYY-DEWPJGLH65RFS </payer_id>
      <XXX-address_street> YYY-7722 SE 16th Ave </address_street>
    </postback>
____TMP;
// ABOVE LINE MUST END ONLY WITH A SEMI-COLON

echo '<pre>';
  evenMorePrettyXml($raw2, $iMode=0);
echo '</pre>';


function evenMorePrettyXml(string $sRaw, int $iMode=0)
{
$tmp = htmlspecialchars($sRaw);

echo '<br><br> #1 ADD LINEFEEDS';
  $tmp = str_replace("\n", "\n", $tmp);
  echo '<br>', $tmp;
echo '<hr>';

echo '<br><br> #2 REMOVE &lt;postback&gt; and &lt;/postback&gt;';
  $tmp = str_replace(['&lt;postback&gt;','&lt;/postback&gt;'], '', $tmp);
  echo $tmp;

echo '<br><br> #3 CREATE ARRAY $aTmp[]';
  $aTmp = explode( "\n", $tmp);
  echo '<br>'; print_r($aTmp);

echo '<br><br> #4 REMOVE EMPTY ARRAY $aTmp[] ELEMENTS';
  $aTmp = array_splice($aTmp, 1, count($aTmp)-2 );
  echo '<br>'; print_r($aTmp);

echo '<br><br> #5 GET ARRAY $aNames[]';
  $aNames = [];
  foreach($aTmp as $id => $item):
    # REMOVE LEADING &lt;
    $item     = str_replace('&lt;', '', $item);
    $aNames[] = strstr($item, '&gt;', TRUE);
  endforeach;  
  echo '<br>'; print_r($aNames);

echo '<br><br><b> #6 Remove linefeeds and double spaces </b>';
  $tmp = str_replace(["\n", '  '], [', ', ''], $sRaw);
  echo '<br>$tmp ==> ' .$tmp;

echo '<br><br><b> #7  Make into an Array $aValues[] </b>';
  $aValues = explode(',', $tmp); 
  echo '<br>$aValues ==> ';
  print_r($aValues);

echo '<br><br><b> #8 REMOVE EMPTY ARRAY $aValues[] ELEMENTS </b>';
  $aValues = array_splice($aValues, 1, count($aValues)-2 );
  echo '<br>'; print_r($aValues);

echo '<br><br><b> #9 DISPLAY RESULTS </b>';
  echo '<dl>';
  for($i2=0; $i2<count($aNames); $i2++):
    echo '<dt><b>' 
          .$aNames[$i2]   .'</b></dt><dd>' 
          .$aValues[$i2] .'</dd>';
  endfor;  
  echo '</dl>';
}//endfunc

Output:

SamA74 · July 19, 2018, 5:21pm

It looks like you may have to extract the data from the table as xml, then use the xml tools in php to convert that data into either arrays or php objects, ready for outputputting as you want.

NormsIM · July 19, 2018, 7:48pm

Awesome John, I’m definitely going to give this latest rendition a try. I might not be able to get to it for a week though as I’m heading out for some camping.

system · October 19, 2018, 10:59am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Mysql xml record Databases	14	2504	September 24, 2014
Extract value from XML using PHP HTML & CSS xml	3	7425	October 8, 2014
MySQLi prepared statements to XML output PHP	2	2134	October 8, 2014
Create xml from mysql/php need help PHP	1	469	May 31, 2011
MySQL Results PHP	1	212	February 21, 2011

How to perform a MySQL query and retrieve data from an xml formated field?

Related topics