Appreciate help with PHP MySql Query problem

[AnthonySterling]

Like this?

<root>
    <product>
        <id>3</id>
        <size>medium</size>
    </product>
    <product>
        <id>3</id>
        <size>large</size>
    </product>
    <product>
        <id>4</id>
        <size>xxl</size>
    </product>
</root>

[ColinHughes]

<?xml version="1.0" encoding="UTF-8"?>
<root>

<product>
<id>3</id>
<size>medium</size>
<colour>red</colour>
<stock>no</stock>
</product>

<product>
<id>4</id>
<size>large</size>
<colour>red</colour>
<stock>pending</stock>
</product>

<product>
<id>5</id>
<size>xxl</size>
<colour>green</colour>
<stock>yes</stock>
</product>

</root>

Like this

[AnthonySterling]

But what would happen when there is more than one product returned with the same id? Your example doesn't cover this...

<?php
include('includes/conn.php'); 
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
while($product = mysql_fetch_assoc($result))
{
    $xml .= '<product>';
    foreach($product as $key => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s<%1$s>',
            $key,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';
header ('Content-Type: text/xml');
echo $xml;
exit;
?>

[ColinHughes]

NEARLY THERE!!

This is the output:

<?xml version="1.0" encoding="UTF-8"?>
<root>
<product><id>1<id><name>Colour<name><value>Blue<value></product>
<product><id>1<id><name>Size<name><value>Large<value></product>
<product><id>1<id><name>Stock<name><value>Yes<value></product>
</root>

XML Parsing Error: mismatched tag. Expected: </value>
<product><id>1<id><name>Colour<name><value>Blue<value></product>
--------------------------------------------------------^

It fails to add the closing tag for the field names

[AnthonySterling]

Whoops, my bad!

 <?php
include('includes/conn.php'); 
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
while($product = mysql_fetch_assoc($result))
{
    $xml .= '<product>';
    foreach($product as $key => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            $key,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';
header ('Content-Type: text/xml');
echo $xml;
exit;
?>

[ColinHughes]

Ok fixed the tag problem code now as below:

<?php 

header("Content-Type: text/xml; charset=UTF-8");

include('includes/conn.php');

         $xml = '<?xml version="1.0" encoding="UTF-8"?>';
         $xml .= '<root>' . "\n";
         
                          
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);


while($product = mysql_fetch_assoc($result))
{
    $xml .= '<product>';
    foreach($product as $key => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            $key,
            $value
        );
    }
    $xml .= '</product>' . "\n";
}
$xml .= '</root>' . "\n";

echo $xml;
exit;
?>

This produces following XML

<root>
−
<product>
<id>1</id>
<name>Colour</name>
<value>Blue</value>
</product>
−
<product>
<id>1</id>
<name>Size</name>
<value>Large</value>
</product>
−
<product>
<id>1</id>
<name>Stock</name>
<value>Yes</value>
</product>
</root>

This is not correct it needs to read:

<root>
−
<product>
<id>1</id>
<colour>Blue</colour>
<size>Large</size>
<stock>Yes</stock>
</product>
−
<product>
<<id>2</id>
<colour>Red</colour>
<size>Small</size>
<stock>Yes</stock>
</product>

−
<product>
<id>3</id>
<colour>Orange</colour>
<size>Medium</size>
<stock>No</stock>
</product>

</root>

Not as close as I first thought.

[AnthonySterling]

Hold up, where did 'name' and 'value' come from? They aren't in the schema image you supplied...

[ColinHughes]

OH NO! please tell me that you didn't just look at image2 this is how the DB looks http://www.sitepoint.com/forums/atta...9&d=1254314584

image2 was just me griping on about how it ought to have been in my opinion.

[AnthonySterling]

Bugger!

<?php
include('includes/conn.php');
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$products = array();
while($product = mysql_fetch_assoc($result))
{
    foreach($product as $attribute => $value)
    {
        if($attribute !== 'id')
        {
            $products[$product['id']][$attribute] = $value;
        }
    }
}

$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
foreach($products as $id => $attributes)
{
    $xml .= '<product>';
    $xml .= '<id>' . $id . '</id>';
    foreach($attributes as $name => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            $name,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';

header('Content-Type: text/xml; charset=UTF-8');
echo $xml;
exit;
?>

[ColinHughes]

No 2 products have the same id incidentaly

C

[AnthonySterling]

Did you try my last Colin?

[ColinHughes]

Now we get:

<root>
−
<product>
<id>1</id>
<name>Stock</name>
<value>Yes</value>
</product>
</root>

We need to lose the "name tag" and for the "value tag" to read Stock. I wonder if this can be done? I did start to consider recordsets when I was attempting this on my own but my expertise was not up to it.

[AnthonySterling]

Sorry, again, a copy and paste faux pas on my side, it's getting late here.

<?php
include('includes/conn.php');
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$products = array();
while($product = mysql_fetch_assoc($result))
{
    $products[$product['id']][$product['name']] = $product['value'];
}

$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
foreach($products as $id => $attributes)
{
    $xml .= '<product>';
    $xml .= '<id>' . $id . '</id>';
    foreach($attributes as $name => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            $name,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';

header('Content-Type: text/xml; charset=UTF-8');
echo $xml;
exit;
?>

[ColinHughes]

Now I have a perfect:

<?xml version="1.0" encoding="UTF-8"?>
<root>
<product>
<id>1</id>
<Colour>Blue</Colour>
<Size>Large</Size>
<Stock>Yes</Stock>
</product>

<product>
<id>3</id>
<Colour>Green</Colour>
<Size>Large</Size>
<Stock>Yes</Stock>
</product>

<product>
<id>2</id>
<Colour>Green</Colour>
<Stock>No</Stock>
<Size>Medium</Size>
</product>

<product>
<id>4</id>
<Colour>Orange</Colour>
<Size>One Size</Size>
<Stock>No</Stock>
</product>

<product>
<id>5</id>
<Colour>Purple</Colour>
<Size>Large</Size>
<Stock>No</Stock>
</product>

</root>
Thank you so much for your time and experience Anthony, I have been trying to figure this out for a couple of days. As usual the generosity of a member of this forum worked out a solution.

Oh and yeh its getting even later here!!

regards

Colin

[AnthonySterling]

Woohoo!

Just glad I can now get to bed, your satisfaction comes a close second though.

Night Colin.

Anthony.

[ColinHughes]

Hi All
It seems I am not finished with this yet. Some of the tag titles that the database returns are not compatible with the feed format I require.

For instance <colour> needs to be <color>, <stock> needs to be <stock_level> to name just a couple. As mentioned previously I cannot change anything in the DB.

I somehow need to grab the data, change the tags and output it in the correct format as XML.

Anyone?

Regards

Colin

[Kayarc]

Hi All
It seems I am not finished with this yet. Some of the tag titles that the database returns are not compatible with the feed format I require.

For instance <colour> needs to be <color>, <stock> needs to be <stock_level> to name just a couple. As mentioned previously I cannot change anything in the DB.

I somehow need to grab the data, change the tags and output it in the correct format as XML.

Anyone?

Regards

Colin

This would work:

<?php
foreach($tags as $tag){
  $xml_tags[] = alter_tag($tag);
}
?>

<?php
function alter_tag($tag) { 
$tags_to_format = array(
  'colour'    =>  'color',
  'stock'     =>  'stock_level'
)
while(list($key, $value) = each($tags_to_format){
  if($key === $tag){$tag = $value; return $tag;}
}
return $tag;
}
?>

edit: you could also use the alter_tag() function with Array_Walk - not sure about the efficiency of all of this but it should get the job done

[ColinHughes]

Thanks for the reply, I am sorry but I cannot figure how to include this in my code ?? Explanation and guidance appreciated.

C

[AnthonySterling]

<?php
include('includes/conn.php');
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$products = array();
while($product = mysql_fetch_assoc($result))
{
    $products[$product['id']][$product['name']] = $product['value'];
}
$replacement_tags = array(
    'colour'    => 'color',
    'stock'        => 'stock_level',
);
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
foreach($products as $id => $attributes)
{
    $xml .= '<product>';
    $xml .= '<id>' . $id . '</id>';
    foreach($attributes as $name => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            array_key_exists($name, $replacement_tags) ? $replacement_tags[$name] : $name,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';

header('Content-Type: text/xml; charset=UTF-8');
echo $xml;
exit;
?>

[Kayarc]

php

Do you know whether array_walk is faster than foreach/while statements?

[ColinHughes]

Hi Anthony

Thanks for your response but the tags remain the same??

<id>1</id>
<Colour>Blue</Colour>
<Size>Large</Size>
<Stock>Yes</Stock>

What does this question mark do:


array_key_exists($name, $replacement_tags) ? $replacement_tags[$name] : $name,

Colin

[AnthonySterling]

It's the php implementation of the ternary operator.

The keys are case-sensitive.

<?php
include('includes/conn.php');
$result = mysql_query(
    sprintf(
        'SELECT * FROM products WHERE id = %d',
        1
    )
);
$products = array();
while($product = mysql_fetch_assoc($result))
{
    $products[$product['id']][$product['name']] = $product['value'];
}
$replacement_tags = array(
    'Colour'    => 'color',
    'Stock'        => 'stock_level',
);
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml .= '<root>';
foreach($products as $id => $attributes)
{
    $xml .= '<product>';
    $xml .= '<id>' . $id . '</id>';
    foreach($attributes as $name => $value)
    {
        $xml .= sprintf(
            '<%1$s>%2$s</%1$s>',
            array_key_exists($name, $replacement_tags) ? $replacement_tags[$name] : $name,
            $value
        );
    }
    $xml .= '</product>';
}
$xml .= '</root>';

header('Content-Type: text/xml; charset=UTF-8');
echo $xml;
exit;
?>

[ColinHughes]

Thanks Anthony you have done it again

Colin

[ColinHughes]

Doom and Gloom

I have just discovered that my feed must comply with an import config for the site I am trying to use. Basically they need the feed to run in a particular order in order for it to be accepted.

Something like:

<product>
<id></id>
<date></date>
<ref></ref>
<price></price>
<currency></currency>

<category>
<ref></ref>
</category>

<color><color>
<size></size>
<stock_level></stock_level>
<desc></desc>
<features></features>

<image>
<image_id></image_id>
<image_url></image_url>
<alt></alt>
</image>

<product>

Sorry guys but nobody told me this before, I am not happy

This I feel could present a problem??

Colin

[AnthonySterling]

It shouldn't be too bad Colin, what should happen if a particular product doesn't have a value, would a default be provided?