Download the PHP package simplon/mysql without Composer

     _                 _                                         _ 
 ___(_)_ __ ___  _ __ | | ___  _ __    _ __ ___  _   _ ___  __ _| |
/ __| | '_ ` _ \| '_ \| |/ _ \| '_ \  | '_ ` _ \| | | / __|/ _` | |
\__ \ | | | | | | |_) | | (_) | | | | | | | | | | |_| \__ \ (_| | |
|___/_|_| |_| |_| .__/|_|\___/|_| |_| |_| |_| |_|\__, |___/\__, |_|
                |_|                              |___/        |_|  

Simplon/Mysql

1. Installing
2. Direct vs. CRUD
3. Setup connection
4. Usage: Direct access
   4.1. Query
   4.2. Insert
   4.3. Update
   4.4. Replace
   4.5. Delete
   4.6. Execute
5. Usage: CRUD
   5.1. Setup store
   5.2. Setup model
   5.3. Connect to store
   5.4. Query
   5.5. Insert
   5.6. Update
   5.7. Delete
   5.8. Custom queries
6. IN() Clause Handling
   6.1. The issue
   6.2. The solution
7. Exceptions

Dependecies

• PHP >= 7.1
• PDO

1. Installing

Easy install via composer. Still no idea what composer is? Inform yourself here.

2. Direct vs. CRUD

I implemented two different ways of interacting with MySQL. The first option is the usual one which interacts directly with the database. Following a straight forward example to show you what I mean:

In constrast to the prior method CRUD is more structured with store and model. Further, it uses Builder Patterns to interact with the database. A quick example of how we would rewrite the above :

3. Setup connection

The library requires a config value object in order to instantiate a connection with MySQL. See how it's done:

4. Usage: Direct access

4.1. Query

FetchColumn

Returns a selected column from the first match. The example below returns or if nothing was found.

FetchColumnMany

Returns an array with the selected column from all matching datasets. In the example below an array with all will be returned or if nothing was found.

FetchColumnManyCursor

Returns one matching dataset at a time. It is resource efficient and therefore handy when your result has many data. In the example below you either iterate through the foreach loop in case you have matchings or nothing will happen.

FetchRow

Returns all selected columns from a matched dataset. The example below returns , for the matched dataset. If nothing got matched will be returned.

FetchRowMany

Returns all selected columns from all matched dataset. The example below returns for each matched dataset , . If nothing got matched will be returned.

FetchRowManyCursor

Same explanation as for except that we receive all selected columns.

4.2. Insert

Single data

Inserting data into the database is pretty straight forward. Follow the example below:

The result depends on the table. If the table holds an column you will receive the ID count for the inserted data. If the table does not hold such a field you will receive for a successful insert. If anything went bogus you will receive .

Many datasets

Follow the example for inserting many datasets at once:

The result depends on the table. If the table holds an column you will receive the ID count for the inserted data. If the table does not hold such a field you will receive for a successful insert. If anything went bogus you will receive .

4.3. Updating

Simple update statement

Same as for insert statements accounts for updates. Its easy to understand. If the update succeeded the response will be . If nothing has been updated you will receive .

Custom update conditions query

Same as for insert statements accounts for updates. Its easy to understand. If the update succeeded the response will be . If nothing has been updated you will receive .

4.4. Replace

As MySQL states it: works exactly like , except that if an old row in the table has the same value as a new row for a or a , the old row is deleted before the new row is inserted.

Replace a single datasets

As a result you will either receive the or in case something went wrong.

Replace multiple datasets

As a result you will either receive an array of or in case something went wrong.

4.5. Delete

Simple delete conditions

The following example demonstrates how to remove data. If the query succeeds we will receive else .

Custom delete conditions query

The following example demonstrates how to remove data with a custom conditions query. If the query succeeds we will receive else .

4.6. Execute

This method is ment for calls which do not require any parameters such as . If the call succeeds you will receive . If it fails an will be thrown.

5. Usage: CRUD

The following query examples will be a rewrite of the aforementioned examples. For this we need a Store and a related Model.

5.1. Setup store

5.2. Setup model

5.3. Connect to store

In order to interact with our store we need to create an instance. For the following points we will make use of this instance.

5.4. Query

Fetch one item

Returns a name model or NULL if nothing could be matched.

You can make use of operators from the addCondition:

Fetch many items

Returns an array of name models or NULL if nothing could be matched.

5.5. Insert

The following example shows how to create a new store entry.

5.6. Update

The following example shows how to update an existing store entry.

5.7. Delete

The following example shows how to delete an existing store entry.

5.8. Custom queries

You also have the possibility to write custom queries/handlings for your store. I added a method to the store which demonstrates on how to implement custom handlings.

6. IN() Clause Handling

6.1. The issue

There is no way using an clause via PDO. This functionality is simply not given. However, you could do something like the following:

Looks good at first sight - not sexy but probably does the job, right? Wrong. This approach only works with and it does not the user's input - the reason why we use in first place.

Just for the record here is a string example which would not work:

The only way how this would work is by wrapping each value like the following: . Way too much work.

6.2. The solution

To take advantage of the built in with escaping and type handling do the following for the direct access. CRUD will do the query building automatically for you.

7. Exceptions

For both access methods (direct, sqlmanager) occuring exceptions will be wrapped by a . All essential exception information will be summarised as within the .

Here is an example of how that might look like:

License

Simplon/Mysql is freely distributable under the terms of the MIT license.

Copyright (c) 2017 Tino Ehrich ([email protected])

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.