query is not selecting anything. help?

[Tenaciousmug]

I don't know why. I've been debugging this for the past hour. My query function I built will not return anything. It passes the parameters just fine, but when it gets to the bind_param, I personally don't think that part is working since it doesn't update the query string when I debug it and therefore gives me a null when I call for the $stmt->num_rows(); function.

 

Here is my query that is calling it:

<?php
include_once dirname(__FILE__).'/class/Database.php';
$DB = new Database();

echo  $DB->select('usr_username', 'user_usr', 'usr_id = ?', '', '', 'i', '2', 'username');
?>

 

Here is my Database class:

<?php
include_once '/../includes/constants.php';

class Database {
    private $cxn;
    private $numResult;
    private $stmt;
    private $row;

    function __construct() {
        $this->cxn = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME)
                or die('Could not connect to the database.');
    }

    public function select($rows, $table, $where='', $order='', $limit='', $type='', $input='', $result='') {
        $this->stmt = $this->cxn->stmt_init();
$query = 'SELECT '.$rows.' FROM '.$table;
if($where != '') {
	$query .= ' WHERE '.$where;
}
if($order != '') {
	$query .= ' ORDER BY '.$order;
}
if($limit != '') {
	$query .= ' LIMIT '.$limit;
}
        $this->stmt = $this->cxn->prepare($query);
if($where != '') {
	$this->stmt->bind_param($type, $input);
}
$this->stmt->execute();
$this->stmt->bind_result($result);
$this->numResult = $this->stmt->num_rows;
for ($i = 0; $i < $this->numResult; $i++) { // look at when you get up. returning 0 instead of 1!
	$this->row = $this->stmt->fetch_array(MYSQLI_ASSOC);
	return $this->row;
}
return false; //return an error
    }
}
?>

 

Thanks for any help! I wish the num_rows would return 1 result and not be null.

[RobertP]

i think your whole class will be needed for use to help 

[PFMaBiSmAd]

After you call the ->stmt_init() method, $this->stmt contains an object that you use with the rest of the statements.

 

Your ->prepare call should be -

 

$this->stmt->prepare($query);

[PFMaBiSmAd]

The next problem you will have is that you need to call the ->store_result() method before you can access the ->num_rows property. You also don't fetch rows directly from a prepared statement result set using a property called ->fetch_array(). You would use the ->fetch() property. (If you have a high enough version of php, you could use the ->get_result() property followed by a $row = $result->fetch_array(MYSQLI_ASSOC) statement.) You are also going to have a problem with your bind_result() statement because that expects to bind to an actual php variable that you would then reference in the code.

 

I would recommend that you get your mysqli prepared code to work first, before you attempt to write a class that uses it.

 

I'm also going to guess that you don't have php's error_reporting set to E_ALL (or ever better a -1) and display_errors set to ON in your master php.ini to get php to help you. You would have been getting php detected errors in your existing code that would have helped you find where the problems in it are at.

 

Edit: you also have a return statement inside your for(){} loop. That makes no sense. The code would return during the first iteration through the loop and you could never operate on a result set that contains more than one row. You should form an array of the value(s) and then return that array after the end of the loop. Your database class should be general purpose and it should work with any amount of data.

[Tenaciousmug]

Wow thank you sooo much for all your help.

My function now looks like this:

<?php
    public function select($rows, $table, $where='', $order='', $limit='', $type='', $input='', $result='') {
        $this->stmt = $this->cxn->stmt_init();
$query = 'SELECT '.$rows.' FROM '.$table;
if($where != '') {
	$query .= ' WHERE '.$where;
}
if($order != '') {
	$query .= ' ORDER BY '.$order;
}
if($limit != '') {
	$query .= ' LIMIT '.$limit;
}
        $this->stmt = $this->stmt->prepare($query);
if($where != '') {
	$this->stmt->bind_param($type, $input);
}
$this->stmt->execute();
$this->stmt->bind_result($result);
        $this->stmt->store_result();
$this->numResult = $this->stmt->num_rows;
for ($i = 0; $i < $this->numResult; $i++) { // look at when you get up. returning 0 instead of 1!
	$this->row = $this->stmt->fetch();
}
        return $this->row;
return false; //return an error
    }
?>

 

Does anyone know why the bind_param is not working. It is giving me this error message:

Fatal error: Call to a member function bind_param() on a non-object in C:\wamp\www\Elvonica\class\Database.php on line 51

 

I'm turning my errors on right now. Never knew there was an option like that in WAMP.

[Tenaciousmug]

Fatal error: Call to undefined function mysqli_connect() in C:\wamp\www\Elvonica\class\Database.php on line 11

 

Huh. I wonder why that is..

My constructor is fine under my Database class. Am I not connecting properly? I would think it would say "Could not connect to the database" since I have an or die() function.

[Tenaciousmug]

Well since I couldn't edit my last message, I'll have to post what I'm running into with this whole mysql connection.

 

<?php
    private $cxn;
    private $numResult;
    private $stmt;
    private $row;
    
    function __construct() { 
        $this->cxn = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
                //or die('Could not connect to the database.');
    }
?>

 

And this is the error I'm getting:

Fatal error: Class 'mysqli' not found in C:\wamp\www\Elvonica\class\Database.php on line 11

 

So theeeennn I changed the php.ini file to this:

;extension=php_mssql.dll
extension=php_mysql.dll
extension=php_mysqli.dll
;extension=php_oci8.dll      
;extension=php_oci8_11g.dll 
;extension=php_openssl.dll

 

I have both the .dll's enabled.

And then there is this crazy libmysqli.dll or something you're suppose to put somewhere.. I searched my whole C:/ directory for that file and it didn't find anything. I even searched for "lib" "mysql". Nothing. :/

Then they said phpinfo(); should have a MySqlI section and I can't find any so I have a feeling I don't even have mysqli extension or something. But when I look under the ext folder in the php folder of WAMP, both files are there and I have them enabled in the php.ini.

 

So what am I doing wrong? Thank you soooo much for anyone who can help me solve this!!

[Tenaciousmug]

Any help?