Member Avatar for dawbin

I'm an experienced ASP/MSSQL programmer trying to make the move to PHP/MYSQL and I've been able to wrangle my way up to now. I have a simple SQL query that works great in mymcadmin but gets lost in PHP. I've simplified it a bit here but this is the actual code. Any help would be appreciated!

CODE
<?php
    ini_set('display_errors','1'); 

    //connect to database
    $con=mysqli_connect("000.00.00.00","0000000000","0000000000","000000000");
    if (mysqli_connect_errno()) {
        echo '<font color=red>' . mysqli_connect_error() . '</font>';
    }   

    $xml='';
    $sql = "SELECT zip FROM tax LIMIT 0 , 10";
    $result = mysqli_query($con,$sql);
    while ($row = mysqli_fetch_array($result)) {
        $xml+='<row>';
        $xml+='<zip>' . $row['zip'] . '</zip>';
        $xml+='</row>';
    }

    mysqli_close($con);

    echo '<?xml version="1.0"?>';
    echo '<root>' . $xml . '</root>';

?>
EXPECTED OUTPUT

<?xml version="1.0"?>
<root>
<row>00000</row>
...
</root>

ACTUAl OUTPUT

<?xml version="1.0"?>
<root>0</root>

  • 5 Contributors
  • 8 Replies
  • 317 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by Member #46692

Dani AI

Generated

Short diagnosis and immediate fixes:

The single-character symptom ("<root>0</root>") is classic PHP type-juggling: using the numeric addition operator to try to append strings causes PHP to convert the strings to numbers (resulting in 0). The separate warning you saw comes from mixing extensions (the old mysql_* functions versus mysqli/PDO). correctly flagged the concatenation bug; replies from and were on the right track about associative fetches but one suggested the deprecated mysql extension. Use one DB API consistently and avoid manual string fiddling for XML.

Quick troubleshooting checklist:

  • Confirm the variable type and value while debugging (for example with var_dump or print_r) so you can see whether the XML variable is a string or an integer.
  • Do not mix mysql* and mysqli/PDO calls. Pick mysqli or PDO and enable exceptions/error reporting so SQL problems surface.
  • Build XML with an XML API (DOMDocument or SimpleXML) instead of hand-concatenating pieces; it automatically escapes data and prevents malformed output.
  • Ensure the connection uses the correct charset (utf8/utf8mb4) and send the proper Content-Type header for XML.

Example (robust pattern using PDO + DOMDocument):

<?php
$pdo = new PDO('mysql:host=HOST;dbname=DB;charset=utf8mb4','USER','PASS',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->query('SELECT zip FROM tax LIMIT 10');

$doc = new DOMDocument('1.0','UTF-8');
$root = $doc->createElement('root');
$doc->appendChild($root);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row = $doc->createElement('row');
    $row->appendChild($doc->createElement('zip', $r['zip']));
    $root->appendChild($row);
}

header('Content-Type: application/xml; charset=utf-8');
echo $doc->saveXML();

Notes: using an XML builder prevents the common escaping/formatting traps; prepared statements and proper charset settings avoid garbled output in production.

Recommended Answers

All 8 Replies

Member Avatar for Ajay Gokhale

Hi,

Try to change mysqli_fetch_array($result) with mysql_fetch_assoc($result). I think you will get required output.

Please check and let me know.
Thanks,
Ajay

Member Avatar for ryantroop

I believe, since you did mysqli_fetch_array() you are getting a 0 index result. If you want to use the column name, use mysqli_fetch_assoc() instead, or change this line:

$xml+='<zip>' . $row['zip'] . '</zip>';

to

$xml+='<zip>' . $row[0] . '</zip>';

and that should work.

Member Avatar for Member #120589

Neither of the above are correct as mysqli_fetch_array() gives both types of array (indexed and associative) as default. Since you did not specify a resulttype, both will be returned. However, if you do not need an indexed array, you can use either the mysqli_fetch_assoc() mentioned by ryantroop (NOT mysql_fetch_assoc!!), or you can use:

mysqli_fetch_array($result, MYSQLI_ASSOC)

Member Avatar for Member #120589

$xml+= is used for incrementing in php and concatenation in js.

Use $xml.=

Member Avatar for dawbin

Thank you for the replies. I corrected some syntax and simplified some more but now I am getting:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, object given in /home/DB1746/f1drop.com/php/test.php on line 8

The query works fine in mymcadmin but this code does not work... any thoughs apprecaited.

<?php
    //error handling    
    ini_set('display_errors','1'); 
    //connect
    $con=mysqli_connect("","","","");
    //select
    $result = mysqli_query($con,'SELECT zip FROM tax LIMIT 0 , 10');
    //output
    while ($row = mysql_fetch_assoc($result)) {
        echo '<p>Output: ' . $row['zip'] . '</p>';
    }
    //close
    mysqli_close($con);
?>
Member Avatar for Member #120589

Is this because you took Ajay's advice and used the mysql_* function isntead of mysqli_* ? Use:

while ($row = mysqli_fetch_assoc($result)) {
Member Avatar for Member #46692

mysqli there is an i at the end!

Member Avatar for Member #46692

Additionally, I can see some further issues down the line.

That result is practically unuseable as an xml file. So look up simpleXML() and figure out how to use it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.