Odd behaviour on webhost server

[jhw]

I am fairly new to MySQL. I have written code on a local machine using PHP to access MySQL, and it works fine, but when I upload it to my web hoster's server, it behaves oddly.

 

This is the code:

 

 <?php

 

    include 'includes/mysql_connect.php';

       

    mysqli_set_charset($con, "utf8");

 

    $sql = "select n.content content " .

           "from notices n " . 

           "where n.enddate >= curdate() " .

           "order by n.startdate desc";

    $result = mysqli_query($con,$sql);

 

    foreach ($result as $row) {

      echo $row[content];

      echo '<hr>';

    }   

 

    include "includes/mysql_close.php";

    

  ?>

 

File includes/mysql_connect.php contains the following (I have changed the password and db name)

 

<?php $con = mysqli_connect("localhost","webuser","password","db"); ?>

 

and includes/mysql_close.php contains

 

<?php mysqli_close($con); ?>

 

On a local PC, this returns a single item followed by a horizontal line. This is correct. On the web host server it just returns five horizontal lines and no data. The same thing happens with any page which retrieves data from the database - I get five blank lines regardless of how many items are recovered. If I output the row count it is as I would expect, and if I run the SQL directly in MySQL it gives the correct output.

 

The local machine is running PHP 5.4, the web host PHP 5.3.

Edited October 14, 2013 by jhw

[mac_gyver]

from the mysqli documentation -

 

The mysqli_result class

Changelog

Version Description
5.4.0      Iterator support was added, as mysqli_result now implements Traversable.

 

you can only iterate over the result set like that in php5.4 or higher.

 

it's best not to use the latests php features for a while. just use a traditional while(){} loop with a mysqli_fetch_xxxxxx() statement.

 

edit: your code when running under php5.3 is looping over the 5 properties of a mysqli_result object - 

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(5) ["type"]=> int(0) }

Edited October 14, 2013 by mac_gyver

[AbraCadaver]

Might be able to do:

$result = mysqli_fetch_all(mysqli_query($con, $sql));

[jhw]

Problem solved - thank you very much.

[mac_gyver]

or if you want code that would work for any php5 version/mysql driver -

// dynamically produce function to return mysqli result/data, as quickly as possible, that can be iterated over using a foreach(){} loop
// if php5.4.0 or above, mysqli_result object is traversable and can be used directly
// if php5.3.0 or above w/mysqlnd driver mysqli_fetch_all() exists
// else return array of fetched data

// run this code once to dynamically define the appropriate function code, the resulting function name is in $get_mysqli_result
if (version_compare(PHP_VERSION, '5.4.0') >= 0) {
    // result object is already traversable, just return it
    $get_mysqli_result = create_function('$a', 'return $a;');
} elseif (function_exists('mysqli_fetch_all')){
    // fetch_all function exists, return array with all data in it
    $get_mysqli_result = create_function('$a', 'return $a->fetch_all(MYSQLI_ASSOC);');
} else {
    // none of the above, 'manually' fetch the data and return it as an array
    $get_mysqli_result = create_function('$a', '$b = array(); while($r=$a->fetch_assoc()){$b[] = $r;} return $b;');
}



$mysqli = new mysqli( .... );

$query = 'select id from users'; // some query
$result = $mysqli->query($query); // run query

// loop over result
foreach($get_mysqli_result($result) as $row){
    echo "{$row['id']}<br>";
}