prepared statement method.. confused

Question

I don't know what's missing or why it isn't displaying data. My code is working if I'm not using prepared statements. When I used prepared statements, it seems that code is not working anymore.

db.php

Class Database{


public $mysqli;
public function __construct($db_host, $db_user, $db_password, $db_name){

    $this->con = new mysqli($db_host, $db_user, $db_password, $db_name);

}

    public function selectUserInfo($id){

        $stmt = $this->con->prepare("SELECT * FROM users WHERE os_id = ?");
        $stmt->bind_param("s", $id);
        if($stmt->execute() == FALSE){
            trigger_error($stmt->error, E_USER_ERROR);
        }else{
            $data = array();
            while($row = $stmt->fetch()){
                $data[] = $row;
            }
            return $data;
        }

}
}

config.php

define("DBHOST","somehost");
define("DBUSER","someroot");
define("DBPASS","somepassword");
define("DB","my_database");

this is how I would displayed it at my page.

require 'global/db.php';
require_once 'config.php';
$db = new Database(DBHOST, DBUSER, DBPASS, DB);
$data = $db->selectUserInfo($_GET['name']);
foreach ($data as $key) {
    # code...
    echo $key['os_fname'];
}

No error messages? Are you passing a GET parameter named `name`? If so, are there rows in your DB with `osd_id` equal to that parameter? — Phil, Aug 29 '13 at 02:53
it does not display error message.. I would like to display a value of 1 in my $_GET['name'] — eaponz, Aug 29 '13 at 02:57
@Edgar So when you open the *page*, do you have `?name=1` in the URL? — Phil, Aug 29 '13 at 03:00
@Edgar ... and do you have rows in your database `users` table with `os_id` equal to 1? — Phil, Aug 29 '13 at 03:17
Change `if($stmt->execute() == FALSE){` to `if(!$stmt->execute()){` and `$stmt->bind_param("s", $id);` to `$stmt->bind_param("i", $id);` — Prix, Aug 29 '13 at 03:53
already changed to what you said.. still it doesn't display data O.o — eaponz, Aug 29 '13 at 04:02
@Edgar check my code and lets see if u get any error. also if you are currently getting any error kindly post the message. — Prix, Aug 29 '13 at 04:03

Prix · Accepted Answer · 2013-08-29T05:18:18.517

0

As we have defined, that the issue was with your foreach.

What is wrong is with how you're reading it, fetch does not have associative properties so need to use the bind_result.

Here is a hack that is also suggested at the fetch manual:

public function selectUserInfo($id)
{
    $stmt = $this->con->prepare("SELECT * FROM users WHERE os_id=?");
    $stmt->bind_param('i', $id);
    if(!$stmt->execute())
    {
        trigger_error($stmt->error, E_USER_ERROR);
    }
    else
    {
        $bindVarArray = array();
        $data = array();
        $result;
        $meta = $stmt->result_metadata();
        while ($column = $meta->fetch_field())
        {
            $columnName = str_replace(' ', '_', $column->name);
            $bindVarArray[] = &$result[$columnName];
        }
        call_user_func_array(array($stmt, 'bind_result'), $bindVarArray);

        $index = 0;
        while ($stmt->fetch() != null)
        {
            foreach ($result as $k => $v)
            {
                $data[$index][$k] = $v;
            }
            $index++;
        }
        return $data;
    }
}

Then you can use your foreach to read it like this:

foreach ($data as $result)
{
    echo $result['os_fname'], ' => ', $result['os_lname'], "\n";
}

And you can always use print_r to see how your resulting array is:

print_r($data);

edited Aug 29 '13 at 05:18

answered Aug 29 '13 at 04:03

Prix

19,417
15
73
132

@Edgar Then look at the error_log file you probably forgot to close a variable. Or post the content of `global/db.php` and `config.php` removing any sensitive data such as password, username, ip etc. – Prix Aug 29 '13 at 04:09
i edited my post above.. it displayed Array ( [0] => Edgar ) when I removed my foreach and changed it to print_r – eaponz Aug 29 '13 at 04:17
@Edgar ;) there you go, your problem is that you're not associating your array hence it will not have `$key['os_fname']` you have to use `$stmt->bind_result($fname);` and declared on your query all the fields you want to read like I have on my example or call the variable by the index. `foreach ($data as $value) echo $value;` or `echo $data[0], .... rest of columns ..., $data[1], "\n";` – Prix Aug 29 '13 at 04:20
so code would be like this ? am I correct ? if ($stmt->execute()) { $data = array(); $result = $stmt->bind_result($fname, $lname, $email); while($stmt->fetch()) { $data[] = $result; } return $data; } – eaponz Aug 29 '13 at 04:27
1

@Edgar see update its a hack to automatically read the fields. – Prix Aug 29 '13 at 05:18

leo wong · Answer 2 · 2013-08-29T04:27:16.813

your od_id type in DB is string or integer? if a integer

public function selectUserInfo($id){

    $stmt = $this->con->prepare("SELECT * FROM users WHERE os_id = ?");
    $stmt->bind_param("i", $id);//use 'i' instead of 's'

    if($stmt->execute() == FALSE){
        trigger_error($stmt->error, E_USER_ERROR);
    }else{
        $data = array();
        while($row = $stmt->fetch()){
            $data[] = $row;
        }
        return $data;
    }

}

prepared statement method.. confused

2 Answers2

Linked