MySQLi Linking Results and Fetching Multiple Rows

Question

I am trying to loop through the results using MySQLi and bind / fetch.

static function getConnection() { if (!isset(self::$db_conn)) { self::$db_conn = new mysqli(self::$DBSERVER,self::$DBUSER,self::$DBPASS, ModelBase::$DBNAME) or die(mysql_error(0)." Error handling database connection. "); } return self::$db_conn; } 

The above getConnection () function. It is in the ModelBase class, which this class inherits.

 $term = "%".$term."%"; $con = ModelBase::getConnection(); $sql = "SELECT name FROM cities WHERE name LIKE ? LIMIT ?"; $query = $con->prepare($sql) or die("Error preparing sql in City ".parent::$db_conn->error); $query->bind_param("si", $term, $limit) or die("Error binding params in City ".parent::$db_conn->error); $query->execute() or die("Error executing query in City"); $tmp = ""; $query->bind_result($tmp); while($query->fetch()); { error_log($tmp); echo($tmp."\n"); } $query->close(); 

However, all I get is the last result and only once. There should be more than one result, and when I check the next request in phpMyAdmin or at the prompt, I get several lines.

 SELECT name FROM cities WHERE name LIKE %d% LIMIT 150 

This problem does not appear if I change

$ query-> bind_result ($ TMP);

to

$ query-> bind_result (& $ TMP);

Is there something wrong with the syntax?

Is there an easier way to do this? Maybe a way to get an object?

Answer 1

You have a semicolon at the end of your while-statement:

 while($query->fetch()); 

(which, of course, does an assessment of $ query-> fetch () until it is true if the code between the curly braces for each circle is not executed)