March 18, 2011

Changing database records to xml file using php

Question by user643160

I am trying to pass items from a mysql database into an xml file using php. I have the php code that creates the xml file. But the values that are passed to it aren’t the ones from the mysql database. The database has 13 cols with 62 rows. I have five items in my foreach statement and when they displays on the web screen the values are output as follows:

Number of properties found : 62

1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 R R R R R E E E E E W W W W W A A A A A h h h h h 1 1 1 1 1 < < < < < 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1

There are 65 items in the line above which is the 5 items in my foreach statement times the 13 cols in my database. This I think has something to do with it.

The following is my code:

<?php

@$db = new mysqli('localhost', 'root', '', 'siamsatire');

if (mysqli_connect_errno()) {
    echo 'error connecting to db';
    exit;
}
$query = "SELECT * from events";
$result = $db->query($query);
$num_results = $result->num_rows;
echo 'Number of properties found : <strong>' . $num_results . '</strong><br><br>';

for ($i=0; $i < $num_results; $i++) { 

    $row = $result->fetch_object(); 

    $name = $row->name; 
    $subtitle = $row->sub_title; 
    $date = $row->display_date; 
    $description = $row->slug; 
    $photo= $row->photo; 
    $thumb= $row->thumb; 

    /*echo '<tr>';
    echo "<td>$name</td>";
    echo "<td>$subtitle</td>";
    echo "<td>$date</td>";
    echo "<td>$description</td>";
    echo "<td>$photo</td>";
    echo "<td>$thumb</td>";1+0
    echo '<tr>';*/

} 

$doc = new DOMDocument("1.0");
$doc->formatOutput = true;

$r = $doc->createElement("events");
$doc->appendChild( $r );

foreach($row as $fieldvalue)
{
    $b = $doc->createElement( "event" );

    $name1 = $doc->createElement( "title" );
    $name1->appendChild( $doc->createTextNode( $fieldvalue['title'] ));
    $b->appendChild( $name1 );

    $subtitle1 = $doc->createElement( "subtitle" );
    $subtitle1->appendChild($doc->createTextNode( $fieldvalue['subtitle'] ));
    $b->appendChild( $subtitle1 );

    $date1 = $doc->createElement( "display_date" );
    $date1->appendChild($doc->createTextNode( $fieldvalue['display_date'] ));
    $b->appendChild( $date1 );

    $description1 = $doc->createElement( "slug" );
    $description1->appendChild( $doc->createTextNode( $fieldvalue['slug'] ));
    $b->appendChild( $description1 );

    $photo1 = $doc->createElement( "photo" );
    $photo1->appendChild( $doc->createTextNode( $fieldvalue['photo'] ) );
    $b->appendChild( $photo1 );

    $thumb1 = $doc->createElement( "thumb" );
    $thumb1->appendChild( $doc->createTextNode( $fieldvalue['thumb'] ) );
    $b->appendChild( $thumb1 );

    $r->appendChild( $b );
}

echo $doc->saveXML();
$doc->save("write.xml");

$result->free();
$db->close();
?>

Does anyone have any ideas as to what I’m doing wrong?

UPDATE


@starx – I changed my code around to look like this according to your code and this is what it looks like now.

<?php

    @$db = new mysqli( 'localhost', 'root', '', 'siamsatire');

    if (mysqli_connect_errno()) {
    echo 'error connecting to db';
    exit;
    }

    $query = "SELECT * from events";

    $result = mysql_query($query);  

    if(mysql_num_rows($result)) {
    $doc = new DOMDocument("1.0");
    $doc->formatOutput = true;

    while($row = mysql_fetch_assoc($result)) {
        $r = $doc->createElement( "events" );
        foreach($row as $field=>$value) {
            $tChild = $doc->createElement( $field );
            $tChild->appendChild( $doc->createTextNode($value) );
            $r->appendChild( $tChild );     
        }
        $doc->appendChild($r);
    }
        $doc->appendChild( $r );
        echo $doc->saveXML();
        $doc->save("write.xml");
    }

    //$result->free();
        //$db->close();
    ?>

And these are the errors I got with it.’

Warning: mysql_query() [function.mysql-query]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:xampphtdocssiamsatire1.php on line 12

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in C:xampphtdocssiamsatire1.php on line 12

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:xampphtdocssiamsatire1.php on line 14'

Do you know why I got them?

I then changed mysql_query to mysqli_query which cut the errors down to:

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:xampphtdocssiamsatire1.php on line 12

Warning: mysql_num_rows() expects parameter 1 to be resource, null given in C:xampphtdocssiamsatire1.php on line 14

Answer by Starx

Here is a better and correct solution

$query = "SELECT * from events";
$result = mysql_query($query);
if(mysql_num_rows($result)) {
    $doc = new DOMDocument("1.0");
    $doc->formatOutput = true;

    while($row = mysql_fetch_assoc($result)) {
        $r = $doc->createElement( "events" );
        foreach($row as $field=>$value) {
            $tChild = $doc->createElement( $field );
            $tChild->appendChild( $doc->createTextNode($value) );
            $r->appendChild( $tChild );     
        }
        $doc->appendChild($r);
    }
    $doc->appendChild( $r );
    echo $doc->saveXML();
    $doc->save("write.xml");
}

You can integrate above code with your library if you want.

UPDATE (after question Update)


Here is your working solution using mysqli

<?
@$db = new mysqli( 'localhost', 'root', '', 'siamsatire');
if (mysqli_connect_errno()) {
    echo 'error connecting to db';
    exit;
}
$query = "SELECT * from events";
$result = mysqli_query($db,$query);  
if(mysqli_num_rows($result)) {
    $doc = new DOMDocument("1.0");
    $doc->formatOutput = true;

        while($row = mysqli_fetch_assoc($result)) {
            $r = $doc->createElement( "events" );
            foreach($row as $field=>$value) {
                $tChild = $doc->createElement( $field );
                $tChild->appendChild( $doc->createTextNode($value) );
                $r->appendChild( $tChild );     
            }
            $doc->appendChild($r);
        }
        $doc->appendChild( $r );
        echo $doc->saveXML();
        $doc->save("write.xml");
}

//$result->free();
//$db->close();
?>

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!