PHP MySQLi Library Pt2

In my previous post Part 1, I talked about the basics of the PHP MySQL library that I wrote. In this post, I'm going to talk about the different queries you can run. I am going to assume that you are operating within your own database class that you extended with Php_Db.

A couple notes. By default, function calls will return the SQL that was built from the parameters that were passed in. If you change the $autorun static variable to true, it will automatically execute any calls and return the result of the query. So you would want to store the return in a variable or check it against an if() block. If $autorun remains false it returns the string SQL, but it also stores it (until another function is called). If you want to run it, call $this->execute().

select

Returns

`stdClass` or `array:stdClass`

Definition

select($strTableName, $fieldList = [], $whereClauses = [], $flags = [])

So a select statement obviously is what most people do. Just like the previous post showed, you need one parameter, with 3 more being optional. So a call to $this->select("users"); will pull ALL fields and ALL records from the table users. If you only want to pull the id and name fields for example, you would run $this->select("users", ['id', 'name']); Again, this would return the id and name fields for ALL records. So now, the real power! You want to pull a specific list of all users with the name containing 'George'. That would look like...

$where = new DBWhere('name', '%George%', DBWhere::LIKE);
// You have to set escape equal to false so that it doesn't escape the wildcards
$where->escape = false;

$this->select("users", ['id', 'name'], $where);

SELECT 'id', 'name' FROM users WHERE name LIKE '%George%'

Again, this can be expanded or refined further with the 'flags' parameter.

selectCount

Definition

selectCount($strTableName, $whereClauses, $flags)

Returns

number

A selectCount query will just return the number of rows that satisfy the WHERE clause. The big different is that there is no 'field list' in this call. To return the number of all users whose names contain the string 'George' you would call...

$where = new DBWhere('name', '%George%', DBWhere::LIKE);
$where->escape = false;

$this->selectCount("users", $where);

SELECT COUNT(1) FROM users WHERE name LIKE '%George%'

insert

Definition

insert($strTableName, $params, $blnIgnore = false)

Returns

number of affected rows

The insert query will create a single insert statement that allows you to insert a single row to a table.

$this->insert("users", [
    'id' => 1,
    'name' => 'George Jetson',
    'email' => 'george.jetson@spacelysprockets.com'
]);

INSERT INTO users ('id', 'name', 'email') VALUES ('1', 'George Jetson', 'george.jetson@spacelysprockets.com')

This also stores the insert id from the previous query, so you can retrieve it by accessing $this->_insertId Optionally, you can also specify a third boolean parameter if you want to add an "IGNORE" clause to the query

INSERT IGNORE INTO...

extendedInsert

Definition

extendedInsert($strTableName, $arrFields, $params, $blnToIgnore = false)

Returns

number of affected rows

This query will create a multi-insert statement that allows you to insert multiple rows at a time. This query is a little different in that it requires the addition of the field list for the second parameter and an array of arrays with the list of items to put in each field. The order must be the same!

$this->extendedInsert("users", ['id', 'name', 'email'], [
    ['1', 'George Jetson', 'george.jetson@spacelysprockets.com'],
    ['2', 'Fred Flintsone', null]
], true);

INSERT IGNORE INTO users ('id', 'name', 'email') VALUES
('1', 'George Jetson', 'george.jetson@spacelysprockets.com'),
('2', 'Fred Flintstone', NULL)

NOTE: As you see if null is the value of a field in most cases it will translate to NULL in MySQL.

update

Definition

update($strTableName, $params, $whereClauses = [], $flags = [])

Returns

number of affected rows

This query will create an update query. This is very similar to an insert query, but the 3rd parameter is a where clause. You can also use the flags to effect change in joined tables.

$this->update("users", ['email' => null], new DBWhere('id', 1));

UPDATE users SET email = NULL WHERE id = 1

extendedUpdate

Definition

extendedUpdate($strTableToUpdate, $strOriginalTable, $strLinkField, $fieldsToUpdate)

Returns

number of affected rows

The extended update query creates an update query that updates one table using another. So the best option is to create a TEMPORARY table that you insert updated data in. Then using this query to update permanent table data with the data from the temporary table.

The first parameter is the table to update. The second parameter is the source table for the update. The third parameter is the field that is common between the two table and used as the to decide what records to update. The fourth parameter can either be a string or array and is a list or particular field you want updated. So in this case only the name and email fields will be updated.

$this->extendedUpdate('users', 'tmp_users', 'id', ['name', 'email']);

UPDATE users tbu INNER JOIN tmp_users o USING ('id') SET tbu.name = o.name, tbu.email = o.email

replace

Definition

replace($strTableName, $params)

Returns

number of affected rows

The replace function is built exactly like the insert function, except as a replace function which runs a delete statement first, then an insert

extendedReplace

Definition

extendedReplace($strTableName, $fieldList, $param)

Returns

number of affected rows

The extendedReplace function is built exactly like the extendedInsert function

delete

returns number of affected rows

The delete function creates a delete query to remove records from the table. The second parameter is an array list of fields you want to remove. This is provided so that you can remove rows from a specific table and not a joined table

// true and false will automatically be converted to '1' and '0', respectfully
$where = new DBWhere('um.active', false);
// the backticks parameter must be set to false to not surround the field with backticks
// e.g. `um.active`, which is not valid
$where->backticks = false;

$this->delete('users u', ['u.*'], $where, [
    'joins' => [
        "JOIN user_meta um ON um.user_id = u.id"
   ]
]);

DELETE u.* FROM users u JOIN user_meta um ON um.user_id = u.id WHERE um.active = '0'

This covers most of the main queries that Php_Db can run. There are a few other DDL queries that it can also build, but I will go into those in the next part.