Asked  7 Months ago    Answers:  5   Viewed   39 times

I'm working on streamlining a bit our db helpers and utilities and I see that each of our functions such as for example findAllUsers(){....} or findCustomerById($id) {...} have their own connection details for example :

function findAllUsers() {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        die("Connection to DB failed: " . $con->connect_error);
    } else {
        sql = "SELECT * FROM customers..."
        .....
        .....
    }

}

and so on for each helper/function. SO I thought about using a function that returns the connection object such as :

function dbConnection ($env = null) {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        return false;
    } else {
        return $con;
    }
}

Then I could just do

function findAllUsers() {
    $con = dbConnection();
    if ($con === false) {
        echo "db connection error";
    } else {
        $sql = "SELECT ....
        ...
    }

Is there any advantages at using a function like this compared to a Class system such as $con = new dbConnection() ?

 Answers

72

You should open the connection only once. Once you realize that you only need to open the connection once, your function dbConnection becomes useless. You can instantiate the mysqli class at the start of your script and then pass it as an argument to all your functions/classes.

The connection is always the same three lines:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli($srv, $usr, $pwd, $db, $port);
$con->set_charset('utf8mb4');

Then simply pass it as an argument and do not perform any more checks with if statements.

function findAllUsers(mysqli $con) {
    $sql = "SELECT ....";
    $stmt = $con->prepare($sql);
    /* ... */
}

It looks like your code was some sort of spaghetti code. I would therefore strongly recommend to rewrite it and use OOP with PSR-4.

Wednesday, March 31, 2021
 
axiomer
answered 7 Months ago
78

You cannot mix & match between mysql_* functions and mysqli_* functions. Either you use ALL mysql_* (deprecated) or you use ALL mysqli_* functions. You changed the connection, apparently, but did not yet change, for instance, mysqli_query to mysqli_query and etc.

Do note that there is not necessarily a 1-to-1 correspondence between the 2 APIs. In other words, you can't just add an "i" and hope it will always work - there are some conceptual and syntactic and etc changes that you will need to take into account.

In any event, look at every occurrence of "mysql_" in your PHP code and convert it to use "mysqli_*" functions and that should solve your problem.

Wednesday, March 31, 2021
 
Puneet
answered 7 Months ago
96

The first big question when diving in to this is "how do you want to store changesets"?

  1. Diffs?
  2. Whole record copies?

My personal approach would be to store diffs. Because the display of these diffs is really a special action, I would put the diffs in a different "history" collection.

I would use the different collection to save memory space. You generally don't want a full history for a simple query. So by keeping the history out of the object you can also keep it out of the commonly accessed memory when that data is queried.

To make my life easy, I would make a history document contain a dictionary of time-stamped diffs. Something like this:

{
    _id : "id of address book record",
    changes : { 
                1234567 : { "city" : "Omaha", "state" : "Nebraska" },
                1234568 : { "city" : "Kansas City", "state" : "Missouri" }
               }
}

To make my life really easy, I would make this part of my DataObjects (EntityWrapper, whatever) that I use to access my data. Generally these objects have some form of history, so that you can easily override the save() method to make this change at the same time.

UPDATE: 2015-10

It looks like there is now a spec for handling JSON diffs. This seems like a more robust way to store the diffs / changes.

Monday, June 7, 2021
 
linjuming
answered 5 Months ago
38

As some users have suggested (and is the best way), return the mysqli instance

function getConnected($host,$user,$pass,$db) {

   $mysqli = new mysqli($host, $user, $pass, $db);

   if($mysqli->connect_error) 
     die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

   return $mysqli;
}

Example:

$mysqli = getConnected('localhost','user','password','database');
Wednesday, July 7, 2021
 
mdevils
answered 4 Months ago
72

Have you looked at SqlMetal? It's officially supported, although not promoted too much. You can use it to build dbmls from the commandline - we've used it as part of a db's continous integration updates (make sure you have really good code separation if you do this though - partial classes are a saviour - as the dbml will get overwritten).

If I recall correctly it doesn't have quite the same features as the model designer in Visual Studio (I think it handles pluralisation differently). There a good post about it on Ben Hall's blog.

Monday, July 12, 2021
 
tplaner
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :