Changing database records to xml file using php

Question

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:\xampp\htdocs\siamsatire1.php on line 12

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

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\siamsatire1.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:\xampp\htdocs\siamsatire1.php on line 12

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

You asked another question with the same title in http://stackoverflow.com/questions/5313258/using-php-to-create-an-xml-file-from-a-mysql-db. Please change this Question's title so it better reflects what you are asking. — Gordon, Mar 18 '11 at 10:38
By clicking the [edit](http://stackoverflow.com/posts/5350617/edit) link below the tag buttons. — Gordon, Mar 18 '11 at 10:45
possible duplicate of [mysql_fetch_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — GDP, Jul 30 '12 at 18:47
possible duplicate of [Warning: mysql_fetch_* expects parameter 1 to be resource, boolean given error](http://stackoverflow.com/questions/11674312/warning-mysql-fetch-expects-parameter-1-to-be-resource-boolean-given-error) — John Conde, Aug 02 '12 at 12:55

Starx · Accepted Answer · 2011-03-18T11:53:11.563

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();
?>

@starx - i changed my code around to look like this according to your code and this is what it lloks like now — user643160, Mar 18 '11 at 11:23
I dont see any changes, besides, try the snippet i provided it works (tested). Later feel free to modify it to fit with your library. — Starx, Mar 18 '11 at 11:26
Please, Update your question, dont create an answer which is a question. — Starx, Mar 18 '11 at 11:32
Ya that was my error.i changed the way i connected to th db and it worked.thanks — user643160, Mar 18 '11 at 11:49

Changing database records to xml file using php

UPDATE

1 Answers1

UPDATE (after question Update)