Converting MySQL to MySqli advice

[ApeShape]

Hi folks

I'm a bit new and I've sort of hit a brick with what I'm doing now. Looked at W3S, PhP.net etc but I can't quite relate it to what I'm trying to do. Here's some old code.

     function getResult($sql) {
        $result = mysql_query($sql, $this->conn);
        if ($result) {
            return $result;
        } else {
            die("SQL Retrieve Error: " . mysql_error());
        }
    }

Now, this is my MySqli attempt:

function getResult($sql){;

        $result = mysqli_query($this->conn ,"$sql" ); //line93
        if ($result) {
            return $result;
        } else {
            die("SQL Retrieve Error: " . mysqli_error($sql));//97
        }
    }

The error I am getting is the null one.

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\1\func.php on line 93

Warning: mysqli_error() expects parameter 1 to be mysqli, string given in C:\xampp\htdocs\1\func.php on line 97
SQL Retrieve Error:

Now, the web page that calls up this function uses the this code. I think it's OK though.

$db1 = new dbmember();
$db1->openDB();
$sql="SELECT * from member";
$result=$db1->getResult($sql);
echo "<table border='1'>";
echo "<tr><th>Member ID</th><th>Name</th><th>Address</th><th>Postcode</th><th>Photo</th></tr>";
while($row = mysqli_fetch_assoc($result))

Any help on what's going on would be fantastic - I have been pouring over this code for hours and it feels like I'm hitting my head against a wall.

[bradgrafelman]

For one, passing $sql to [man]mysqli_error/man doesn't make sense - check the manual again to see what that function expects.

Second, the first warning error message tells you exactly what went wrong; you were supposed to pass a mysqli resource, but instead you passed null. Thus, the best course of action would be to determine why $this->conn is null rather than a mysqli resource.

[Bonesnap]

Can you show us the section of your class where $this->conn as being assigned a value?

[ApeShape]

Thanks a lot for the very clear answers, much better than the downvotes and cryptic answers on SO. Really appreciated.

So yes, your suggestion is spot on BUT. I'm racking my brains over the null-error. So I need to figure out what I should do with private conn; because it seems like I'm passing it into itself for some reason.

Maybe I need a new function with the actual SQL code to pass this->'SQLcodeherevariable' into, rather than doing the SQL code in a localised fashion i.e on the page itself.

Conn is just a class variable by the way.
ATB

[bradgrafelman]

ApeShape;11031059 wrote:

I'm racking my brains over the null-error.

So are we, but then again that's only because you haven't done as Bonesnap suggested and post the relevant code that assigns something to the 'conn' property.

ApeShape;11031059 wrote:

So I need to figure out what I should do with private conn

Obvious choice would be: Use it to store a 'mysqli' resource.

[ApeShape]

Yes, Conn is empty but it's a class variable so it was used in some methods like insert, delete.

Looking back at line 94, forgive the syntax but would something alone the lines of

$result = mysqli_query(openDB(),"SELECT * from member" );

be more applicable?

OpenDB is my connection method. Then I am making the actual SQL query in this statement, finally this is stored as $result which is fed back to the page.

But then I have the

 while($row = mysqli_fetch_assoc($result)) 

but I need to get my head around the interaction with this and $result it seems.

Or am I completely barking up the wrong tree?

[dalecosp]

ApeShape;11031069 wrote:

Yes, Conn is empty but it's a class variable so it was used in some methods like insert, delete.

Looking back at line 94, forgive the syntax but would something alone the lines of

$result = mysqli_query(openDB(),"SELECT * from member" );

be more applicable?

Well, that would work, assuming that openDB() returns a mysqli resource record, but why don't you just make it so that the $conn variable is the connection as its name suggests?

class SomeClass {

   private $conn;

   private function openDB() {
      //mysqli connect code
      $conn = mysqli_connect($foo,$bar,$baz,$db);
      if (!$conn) {
         $this->error_msg = "Could not connect DB!";
         return false;
      }
      $this->conn = $conn;
      return true;
   }

   function getResult($sql) {

   $result = mysqli_query($this->conn ,"$sql" );
   if ($result) {
       return $result;
   } else {
       die("SQL Retrieve Error: " . mysqli_error($result));//97
   }
   }
}

[ApeShape]

dalecosp;11031091 wrote:

Well, that would work, assuming that openDB() returns a mysqli resource record, but why don't you just make it so that the $conn variable is the connection as its name suggests?

class SomeClass {

   private $conn;

   private function openDB() {
      //mysqli connect code
      $conn = mysqli_connect($foo,$bar,$baz,$db);
      if (!$conn) {
         $this->error_msg = "Could not connect DB!";
         return false;
      }
      $this->conn = $conn;
      return true;
   }

   function getResult($sql) {

   $result = mysqli_query($this->conn ,"$sql" );
   if ($result) {
       return $result;
   } else {
       die("SQL Retrieve Error: " . mysqli_error($result));//97
   }
   }
}

You sir, are a genius. Spot on. I'm going to sift through all my other functions and apply this logic.

I guess it goes with the territory but being relatively in-experienced I am still mentally trying to over-complicated things which tends constripate the mind. :xbones:

[sneakyimp]

ApeShape, it looks like you are wrestling with a couple of things here. One is that mysqli requires you to keep track of your connection object that you create. This is different than the old mysql function which, if you did not specify a connection, would just assume that you intended to use the last connection opened.

The other thing you are contending with is how object-oriented programming works -- i.e., how [man]class[/man] constructs work. It may be awhile before you have the full-blown OOP awakening (I'm not even sure I've had it yet) but the basic idea is that a class lets you bundle up a bunch of variables and functions within an object. We haven't seen your class definitions, but it sounds like you might call OpenDB() somewhere without keeping track of the resulting connection object. The neat thing about classes is that you can define "class variables" that can be used by all of the functions within a class without passing them as parameters to the function and without declaring the variables as global variables. Why this is advantageous may not be clear to you, but it has to do with variable scope and really helps to avoid variable name collisions (i.e., having one variable name in your global scope that is used differently by different parts of your code -- this will cause problems).

[dalecosp]

ApeShape;11031101 wrote:

You sir, are a genius. Spot on. I'm going to sift through all my other functions and apply this logic.

Thanks for the compliment! But, Nay, I am just a dweeb from Missouri who's spent a lot of time here, asking similar questions and getting similar responses. The genius around here has well over 21k posts and was handing out fishing rods for young anglers when I was still considering whether or not this IT thing could make me a living 😉 And that's not to discount many other very intelligent, well-spoken and highly talented professionals that tend to hang out here (I'd write an array of them but I'm afraid I'd forget someone and embarrass myself or anger them).

So, welcome to PHPBuilder, come back when you need to, ask good questions, have patience and follow the rules ... there's good help to be had here. 😉

[ApeShape]

Thanks again for the responses...Oh and yes I have been learning about the power of class variables, global variables and keeping them all away from getting stuck in the wrong methods. Very handy that. I love the idea of being able to manipulate 1 page or 10,000 pages with a few changes to a class file. (for good, or BAD I might add!!)

Hope I don't get flogged for going a bit OT, but TBH I just hope the logic, concepts and handling of PHP/SQL/blahblah just settles in ones head a bit like when learn to drive. That being particularly true with manual cars in cities. After you've been doing the same route for years, I'd love for it to become second nature like a drive to the mall.

Repetition repetition repetition. That's the hardest bit for me, keeping concentrated and practising this thing every day without getting bored.

[Strider64]

I know I'm late to the party, but.... 😃
Another way to go about do this would to have a dedicated Database class that would setup a connection, this could easily be converted over to using mysqli (That's what I did when I converted over to PDO).

Database.php

class Database {

private $_connection;
// Store the single instance.
private static $_instance;

// Get an instance of the Database.
// @return Database: 
public static function getInstance() {
    if (!self::$_instance) {
        self::$_instance = new self();
    }
    return self::$_instance;
}

// Constructor - Build the PDO Connection:
public function __construct() {
    $db_options = array(
        PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements)
        , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent)
        , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays)
    );
    $this->_connection = new PDO('mysql:host=localhost;dbname=cms;charset=utf8', 'username', 'password', $db_options);
}

// Empty clone magic method to prevent duplication:
private function __clone() {

}

// Get the PDO connection:    
public function getConnection() {
    return $this->_connection;
}

}

The you can have some sort of utility file that auto loads the class, for example

// Autoload classes from "classes" directory:
function class_loader($class) {
    require('classes/' . $class . '.php');
}

spl_autoload_register('class_loader');

The you could do this

class SomeClass {

   function getResult($sql) {

    $db = Database::getInstance();
    $conn = $db->getConnection(); // Yeah, I know one extra line, but I think it's more versatile. 

   $result = mysqli_query($conn ,"$sql" );
   if ($result) {
       return $result;
   } else {
       die("SQL Retrieve Error: " . mysqli_error($result));//97
   }
   }
}