Bind Statements - printing several elements from DB (OOP)

PHP
1

Hi everyone,

I’m building a test page to print a table with two rows in PHP using OOP.

I currently have this on my test.php file:


define("value", 1);

require_once 'admin/classes_php/Queries.class.php';
$query = new Queries();

$test = $query->myQuery(value);
echo $test;

on my Queries.class.php I have this:


require_once 'Populate.class.php';

public function myQuery($value) {
    $populate = new Populate();
		
    $query = "SELECT name
    FROM users
    WHERE id_type= ?";
			
    return $populate->pullTest($query, $value);	
}

on my Populate.class.php I have this:


function pullTest($query, $value) {
		$result = $this->db->prepare($query);
		$result->bind_param('i', $value);
		$result->execute();
		$result->store_result();
		$result->bind_result($users);
		
		if($result->fetch()){
			return $users;
			$result->free_result($users);
		}
		else
			return 'Content not found';
	}

The above works and prints just one row. However, I would like to print both rows on my sql.
I was testing like below, but I know something is wrong (for instance I don’t how to use $users and it also gives me error with fetch_array.


function pullTest($query, $value){
		$result = $this->db->prepare($query);
		$result->bind_param('i', $value);
		$result->execute();
		$result->store_result();
		$result->bind_result($users);
		
		if($result){
			$rows = array();
			while($row = $result->fetch_array()){
				$rows[] = $row;
			}
			return $rows;
		}
		else
			return false;
	}

Any ideas on how to print both or more rows?

Thanks in advance.

2

Thank you, Guido. It works now :slight_smile:

One last question!

Let’s say that I now want to select every element in my table and not just the name.


$query = "SELECT *
    FROM users
    WHERE id_type= ?";

How should I do it?

3

$users should contain the result of the fetch. Try this:


function pullTest($query, $value) {
  $result = $this->db->prepare($query);
  $result->bind_param('i', $value);
  $result->execute();
  $result->store_result();
  $result->bind_result($users);
		
  $rows = array();
  while ($result->fetch_array()) {
    $rows[] = $users;
  }
  if (count($rows) == 0) {
    return false
  } else {
    return $rows;
  }
}
4

Hi Guido,

Thank you for your answer.
It makes more sense but still doesn’t work. I’m getting this error:

Call to undefined method mysqli_stmt::fetch_array()

I’m trying to google it and I believe it has something to do with mysqli extension, but I really don’t find a good answer for this.

5

Ah yes, forgot to change that.
Use $result->fetch() instead of $result->fetch_array().

6
  1. Don’t use the * instead name each column you want to extract in the SELECT clause.
  2. Do a bind_result for each column (see the manual for some examples)
  3. Modify the rest of the code to use these new variables.
7

I’m going to try that… Thanks! :slight_smile:

8

Hi,

Sorry to answer so late.

I’m having troubles using the way you’ve told me.

Now I have this:


function pullTest($query, $value) {
  $result = $this->db->prepare($query);
  $result->bind_param('i', $value);
  $result->execute();
  $result->store_result();
  $result->bind_result($users, $logins);
        
  $rows = array();
  while ($result->fetch()) {
    $rows[] = $users;
  }
  if (count($rows) == 0) {
    return false
  } else {
    return $rows;
  }
}

As you can see I have the line that states:

 $rows[] = $users;

But I want two values, not one. I do I equal both values into the array and return it?

9

Do you want to store the two values in the $rows array? What must the $rows array look like after the while loop?

10

I want to store anyway possible, actually I just want to be able to pass my database values to my index page to echo every element. I have something like this currently on my index page:

$test = $query->myQuery(value);
    if(is_array($test)){
        foreach($test as $tests){
            echo $tests['users'];
	    echo $tests['logins];
    }
}
11

Ok then try


while ($result->fetch()) {
   $rows[]['users'] = $users;
   $rows[]['logins'] = $logins;
 }
12

Hmm, that (see post above) might not work.
Maybe this is better


while ($result->fetch()) {  
   $newrow = array();
   $newrow['users'] = $users;
   $newrow['logins'] = $logins; 
   $rows[] = $newrow;
 }
13

Worked like a charm! :slight_smile:

Thank you, Guido. For all your help.

You are the man.