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>

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

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
diafol

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
diafol

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

Use $xml.=

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
diafol

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

while ($row = mysqli_fetch_assoc($result)) {

mysqli there is an i at the end!

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.