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


#1

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

#2

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

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


#3

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?


#4

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.


#5

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.


#6

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.


#7

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.


#8

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

SHOW CREATE TABLE `orders`

#9

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

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.


#10

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:


#11

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)


#12

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.


#13

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

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:


#14

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 ["&lt;","&gt;"] 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!


#15

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.


#16

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.


#17

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:



 #1 ADD LINEFEEDS
    <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>

 #2 REMOVE <postback> and </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>
    

 #3 CREATE ARRAY $aTmp[]
Array
(
    [0] =>     
    [1] =>       <mc_gross>-1.00 </mc_gross>
    [2] =>       <invoice> bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
    [3] =>       <protection_eligibility> Eligible </protection_eligibility>
    [4] =>       <payer_id> DEWPJGLH65RFS </payer_id>
    [5] =>       <address_street> 7722 SE 16th Ave </address_street>
    [6] =>       <XXX-mc_gross> YYY--1.00 </mc_gross>
    [7] =>       <XXX-invoice> YYY-bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
    [8] =>       <XXX-protection_eligibility> YYY-Eligible </protection_eligibility>
    [9] =>       <XXX-payer_id> YYY-DEWPJGLH65RFS </payer_id>
    [10] =>       <XXX-address_street> YYY-7722 SE 16th Ave </address_street>
    [11] =>     
)


 #4 REMOVE EMPTY ARRAY $aTmp[] ELEMENTS
Array
(
    [0] =>       <mc_gross>-1.00 </mc_gross>
    [1] =>       <invoice> bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
    [2] =>       <protection_eligibility> Eligible </protection_eligibility>
    [3] =>       <payer_id> DEWPJGLH65RFS </payer_id>
    [4] =>       <address_street> 7722 SE 16th Ave </address_street>
    [5] =>       <XXX-mc_gross> YYY--1.00 </mc_gross>
    [6] =>       <XXX-invoice> YYY-bill-9BA7054514E7A16992FACB7D55F15B10 </invoice>
    [7] =>       <XXX-protection_eligibility> YYY-Eligible </protection_eligibility>
    [8] =>       <XXX-payer_id> YYY-DEWPJGLH65RFS </payer_id>
    [9] =>       <XXX-address_street> YYY-7722 SE 16th Ave </address_street>
)


 #5 GET ARRAY $aNames[]
Array
(
    [0] =>       mc_gross
    [1] =>       invoice
    [2] =>       protection_eligibility
    [3] =>       payer_id
    [4] =>       address_street
    [5] =>       XXX-mc_gross
    [6] =>       XXX-invoice
    [7] =>       XXX-protection_eligibility
    [8] =>       XXX-payer_id
    [9] =>       XXX-address_street
)


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

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


 #8 REMOVE EMPTY ARRAY $aValues[] ELEMENTS 
Array
(
    [0] =>  -1.00 
    [1] =>   bill-9BA7054514E7A16992FACB7D55F15B10 
    [2] =>   Eligible 
    [3] =>   DEWPJGLH65RFS 
    [4] =>   7722 SE 16th Ave 
    [5] =>   YYY--1.00 
    [6] =>   YYY-bill-9BA7054514E7A16992FACB7D55F15B10 
    [7] =>   YYY-Eligible 
    [8] =>   YYY-DEWPJGLH65RFS 
    [9] =>   YYY-7722 SE 16th Ave 
)


 #9 DISPLAY RESULTS 

      mc_gross
     -1.00 
      invoice
      bill-9BA7054514E7A16992FACB7D55F15B10 
      protection_eligibility
      Eligible 
      payer_id
      DEWPJGLH65RFS 
      address_street
      7722 SE 16th Ave 
      XXX-mc_gross
      YYY--1.00 
      XXX-invoice
      YYY-bill-9BA7054514E7A16992FACB7D55F15B10 
      XXX-protection_eligibility
      YYY-Eligible 
      XXX-payer_id
      YYY-DEWPJGLH65RFS 
      XXX-address_street
      YYY-7722 SE 16th Ave 

#18

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.


#19

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 :frowning: as I’m heading out for some camping.