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 stackoverflow.com/questions/5313258/…. Please change this Question's title so it better reflects what you are asking. — Gordon
– Gordon, Commented Mar 18, 2011 at 10:38
possible duplicate of mysql_fetch_array() expects parameter 1 to be resource, boolean given in select — GDP
– GDP, Commented Jul 30, 2012 at 18:47
possible duplicate of Warning: mysql_fetch_* expects parameter 1 to be resource, boolean given error — John Conde
– John Conde, Commented Aug 2, 2012 at 12:55

Starx · Accepted Answer · 2011-03-18 11:53:11Z

1

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

edited Mar 18, 2011 at 11:53

answered Mar 18, 2011 at 10:28

Starx

79.3k50 gold badges187 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user643160 Over a year ago

@starx - i changed my code around to look like this according to your code and this is what it lloks like now

Starx Over a year ago

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 Over a year ago

Please, Update your question, dont create an answer which is a question.

user643160 Over a year ago

Ya that was my error.i changed the way i connected to th db and it worked.thanks

Collectives™ on Stack Overflow

Changing database records to xml file using php

UPDATE

1 Answer 1

UPDATE (after question Update)

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

UPDATE (after question Update)

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related