mysqli_fetch_array() keeps returning null

[qazplm114477]

Hi, I`ve started doing PHP and MYSQL for a few months now and I am working on a project for a friend. Basically I had the code written down in mysql format and decided to change my code too mysqli.

Everything was working fine except for one function. I keep getting this error Warning: mysqli_fetch_array() [function.mysqli-fetch-array]: Couldn't fetch mysqli_result .

Here`s my code

connect to database

//DEFINE etc...

function connect(){
		//global $dbc;
		$dbc = mysqli_connect(HOST, USERNAME, PASSWORD, DB)
			or die('Cannot connect to MySQL! '.mysqli_connect_error());
		
		return $dbc;
		}

update function

function update_emp_job_position($emp_id, $job_id)
{
	$dbc=connect();
	// Turn autocommit off.
	mysqli_autocommit($dbc, FALSE);
		
	$success = true;
	
	$query = "Update employee set
				job_position_id='".$job_id."'
				where id = '".$emp_id."'";

       if($result = mysqli_query($dbc, $query) or die(mysqli_error()))
	{
		
		if(mysqli_affected_rows($dbc)>0)
			{
			
				//update training_date by selecting all the employees
				$query_sub = "select id, employee_id, job_position_id, training_id, required 
							from training_date
							where employee_id = '".$emp_id."'";
				
				
				if($result_sub = mysqli_query($dbc, $query_sub) or die(mysqli_error()));
				
				if(mysqli_num_rows($result_sub)>0){
				
				while($row_training_date = mysqli_fetch_array($result_sub, MYSQLI_BOTH)) //this is where i get the error
				{
¨                                   ..............................
}

I don`t really know why this is happening. I did a var_dump on the mysqli_fetch_array but it just returned NULL.

I echo all my queries and ran them in mysql, and it worked fine.

mysqli_fetch_array() works in other functions on the same page and called on the same page.

this code worked when I used mysql instead of mysqli...
I`m not sure if I should upgrade my php version (5.3.0 using wamp2) because mysqli_fetch_array() works when called else where

thanks to anyone who can help.

[colweb]

connect to database

//DEFINE etc...

function connect(){
		//global $dbc;
		$dbc = mysqli_connect(HOST, USERNAME, PASSWORD, DB)
			or die('Cannot connect to MySQL! '.mysqli_connect_error());
		
		return $dbc;
		}

The function I use to connect to the database with mysqli is this:

function open_database() {
	global $hostname, $username, $password, $database, $db;
	$db = new mysqli($hostname, $username, $password, $database);
	if (mysqli_connect_error()) {
		echo "<p>Can't connect with database<br/>Error message: ".mysqli_connect_error()."</p>";
		echo "<p>Please contact the database administrator.</p>\n";
                exit();
	}
	else {
		set_charset_utf8();
	}
}

The line set_charset is used to communicate using utf-8. And to do a query I use this function:

function do_query($query) {
	global $db;
	$result = $db->query($query);
	if ($db->errno) {
		echo "<p>Error message: ".$db->errno."  ".$db->error."</p>";
		echo "<p>Please contact the database administrator.</p>\n";
		exit();
	}
	return $result;
}

So from your main php code you call these function like so:

open_database();
$result = do_query("SELECT * FROM atable WHERE acondition ='".$condition."'");
$num_results = $result->num_rows;
if ($num_result == 0) { 
   echo "<p>No results from query</p>";
}
else {
   // we have some data to display / process 
.....

I forgot, $hostname, username, password and database come from an include (db.php) that looks like this:

<?php
	$hostname = 'aserver';
	$database = 'yourdatabase';
	$username = 'you';
	$password = 'strongplease';
?>

[qazplm114477]

Thanks for the reply.

How can i tell if the database has connected properly?

I did a var_dump($db) and the result was

object(mysqli)#2 (17) { ["affected_rows"]=> int(90) ["client_info"]=> string(50) "mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $" ["client_version"]=> int(50005) ["connect_errno"]=> int(0) ["connect_error"]=> string(0) "" ["errno"]=> int(0) ["error"]=> string(0) "" ["field_count"]=> int(5) ["host_info"]=> string(37) "MySQL host info: localhost via TCP/IP" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(20) "5.1.36-community-log" ["server_version"]=> int(50136) ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(26325) ["warning_count"]=> int(0) }

the mysqli_num_rows($result_sub) returned a value of 90
so i know my query went through

I just don`t understand why mysqli_fetch_array() keeps on returning null, even if the query returns 0 rows. it should return false.

the 1st error appears here

while($row_training_date = mysqli_fetch_array($result_sub, MYSQLI_BOTH))

I really have no idea what to do...

EDIT**

I did an echo mysqli_errno($dbc);

Output was 0

var_dump(result_sub) outputs

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

[colweb]

If you take a second look at my code, you see that I don't use mysqli_fetch_array the way you do it.. If you want to use mysqli functions try to use them the Object Oriented way. That is what I'm doing and is working great.

// keep this line
$query_sub = "select id, employee_id, job_position_id, training_id, required 
			   from training_date
			   where employee_id = '".$emp_id."'";

// change these lines
if($result_sub = mysqli_query($dbc, $query_sub) or die(mysqli_error()));
if(mysqli_num_rows($result_sub)>0){
    while($row_training_date = mysqli_fetch_array($result_sub, MYSQLI_BOTH)) 

// into this
$result = $dbc->query($query_sub);
// if you want place error checking code here (see my function do_query)
while($row = $result->fetch_array()) {
    // display results
    echo "id ".$row['id'];
    echo "employee id ".$row['employee_id'];
    echo "job position id ".$row['job_position_id'];
    echo "training id ".$row['training_id']."\n";
}

[qazplm114477]

thanks for the reply col. I do have a couple more questions if you don't mind.

can I mix Object oriented with procedural programming in one function?

I not too familiar with oop but I have used them in visual basic, so i was wondering if you know of any resources that can help me get started.

***
I did some poking around the web and noticed that most people using mysqli statements are using oop. but I still can't find out why mysqli_fetch_array wont work. I changed my syntax over to mysqli_prepare and it was able to return a true or false value with the same $query and $dbc as I used before.

I going too leave this thread unsolved for now since I'm still curious to why this is happening. Even if my query fails, shouldn't mysqli_fetch_array return false and NOT null?

thanks again

[colweb]

qazplm114477, the code I posted does work if you use $dbc = new mysqli(...)

mysqli_fetch_array returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset, but never false.

Therefore to test if a query returned a result always use

$num_results = $result->num_rows;
if ($num_results == 0) {
    // no result from query
}
else {
    // we have a result do something with it

can I mix Object oriented with procedural programming in one function?

Don't think it is possible, never done or even tried it myself. And why should you want to do so?

i was wondering if you know of any resources that can help me get started.

you can go to http://www.onlinecomputerbooks.com/free-php-books.php to read / download books online.

I have stated this on this forum several times already (and no, I don't get any money for it) but I find the book "PHP and MySQL web development", third edition written by Luke Welling and Laura Thomson (ISBN 0-672-32672-8) very good. It shows you both ways of doing it, procedural and object-oriented. If you look at there code you know why to use the object-oriented methods.

[qazplm114477]

thanks for that col, I had a book by Larry Ullman, "php6 and mysql5" but it doesn't really say anything about OOP in php.

Therefore to test if a query returned a result always use

$num_results = $result->num_rows;
if ($num_results == 0) {
    // no result from query
}
else {
    // we have a result do something with it

the thing is, my mysqli_num_rows($result) returns 90 rows but when it gets to mysqli_fetch_array($result, MYSQLI_BOTH), it still returns null. Sorry about this, but I just find this very confusing.