These days, composer is the recommended method for loading and updating the libraries for your PHP project. If you have no idea what composer is, you owe it to yourself to learn about it before proceeding. Type this on the command line to add MeekroDB to your project:
composer require sergeytsalkov/meekrodb
If you're not using composer, you'll need to download the library yourself and drop it in your project folder. After that, you'll need to require it into your project like this:
require_once 'db.class.php';
Next, you'll need to configure the database connection at the top of your project. Remember that the library won't actually establish a database connection until you run your first query. If you need help with the DSN string, check our connection page. If you'd rather use a new MeekroDB() object, and not the global static methods, also check our connection page.
DB::$dsn = 'mysql:host=localhost;dbname=mydb';
DB::$user = 'my_database_user';
DB::$password = 'my_database_password';
You can use the query() method to run a query without any parameters.
DB::query("SELECT * FROM tbl");
You can specify string, integer, and decimal parameters by using %s, %i, and %d placeholders in the query, and attaching the parameters as shown below. The library will run all needed safety checks, such as escaping strings and making sure integers are really integers.
DB::query("SELECT * FROM tbl WHERE name=%s AND age > %i AND height <= %d",
$name, 15, 13.75);
Similarly, you can use the list placeholders %ls, %li, and %ld to substitute for a list of strings, a list of integers, and a list of decimal numbers, respectively. These are typically used with IN and NOT IN.
DB::query("SELECT * FROM tbl WHERE name IN %ls AND age NOT IN %li",
['John', 'Bob'], [12, 15]);
If you need to refer to a specific parameter, rather than just getting them in the order that they were passed in, you can put a number after the placeholder. The first parameter passed will have number 0.
DB::query("SELECT * FROM tbl WHERE name=%s2 AND age > %i0 AND height <= %d1",
15, 13.75, $name);
You can use the above trick to refer to the same parameter multiple times.
DB::query("SELECT * FROM tbl WHERE firstname=%s0 AND lastname=%s0", 'John');
Results are returned as an array of associative arrays.
$results = DB::query("SELECT name, age, height FROM tbl");
foreach ($results as $row) {
echo "Name: " . $row['name'] . "\n";
echo "Age: " . $row['age'] . "\n";
echo "Height: " . $row['height'] . "\n";
echo "-------------\n";
}
You can use queryFirstRow() to get just the first row of results. You should probably use LIMIT 1 in your query so you're not pointlessly transmitting data.
$row = DB::queryFirstRow("SELECT name, age, height FROM tbl WHERE name=%s LIMIT 1", 'Joe');
echo "Name: " . $row['name'] . "\n"; // will be Joe, obviously
echo "Age: " . $row['age'] . "\n";
echo "Height: " . $row['height'] . "\n";
You can use queryFirstField() to get just the first field of the first row.
$count = DB::queryFirstField("SELECT COUNT(*) FROM tbl WHERE age > %i", 18);
echo "We have " . $count . " users whose age is greater than 18!";
You can use queryFirstColumn() to get just the first column of results.
$names = DB::queryFirstColumn("SELECT DISTINCT name FROM tbl ORDER BY name ASC");
foreach ($names as $name) {
echo $name . "\n";
}
You can do an INSERT or REPLACE by using the MeekroDB insert() and replace() functions. With both functions, all you need is the table name and an assoc array to insert.
$name = 'Frank';
DB::insert('tbl', [
'name' => $name,
'age' => 23,
'height' => 10.75
]);
We do provide update() and delete() commands, though they don't add much over just using query(). Here it is done both ways.
DB::update('tbl', ['age' => 25, 'height' => 10.99], ['name' => $name]);
DB::query("UPDATE tbl SET age=%i, height=%d WHERE name=%s", 25, 10.99, $name);
When deleting rows, you can use delete() or query().
DB::delete('tbl', ['username' => $name]);
DB::query("DELETE FROM tbl WHERE username=%s", $name);
Just run the command below at the top of your script, and all SQL queries and errors will be logged to a file. This will make it easy to see what your script is doing and what went wrong.
DB::$logfile = '/home/username/querylog.txt';
We recommend the static class approach (as shown in the examples above) because most projects don't need multiple database connections, and it's annoying to pass $db objects around all the time. Still, MeekroDB works the same in object-oriented mode. First, you have to create a new MeekroDB instance. You'll need to pass the standard connection details as shown, and any that you don't pass will be taken from the static instance.
$mdb = new MeekroDB($dsn, $user, $pass);
Now you can use the $mdb anywhere that you saw DB:: in the examples above.
$row = $mdb->queryFirstRow("SELECT name, age FROM tbl WHERE name=%s LIMIT 1", 'Joe');
echo "Name: " . $row['name'] . "\n"; // will be Joe, obviously
$mdb->param_char = '##';
$row2 = $mdb->queryFirstRow("SELECT name, age FROM tbl WHERE name=##s LIMIT 1", 'Frank');
echo "Name: " . $row2['name'] . "\n"; // will be Frank, obviously