MySQL

Killing MySQL Queries that exceed a timeout using PDO and PHP

Here is an easy way to kill queries using PDO connection method with MySQL. Note that you can NOT kill queries using a "prepared" string. It must be done using "query".

The first step is to grab the PROCESSLIST.

PDO Connection Basics and Examples using PHP

Here are some PDO connection method basics with examples.

You will often commonly see the connection method being referred to as $dbh and $sth. These simply stand for "database handler" and "statement handler".

Connecting to MySQL with PDO Object

Return only the difference from MySQL results

The below example shows how to return the difference in tables from a MySQL query

Problem: A user has the ability to assign labels to records. When a user is to add a new label to a record only query the labels the user has access to and don't display the labels already assigned.

MySQL - Display BLOB and INSERT INTO BLOB

Inserting into a BLOB is just as easy as inserting into any field.

Here is the example query that we can use to change a "field" to "Insert Value":

UPDATE table SET field = 's:12:"Insert Value"' WHERE id = 'uniqueid';

Here is how we can display info from BLOB into text.

Update MySQL PDO Function

Below is a PDO update function that I wrote to save me time from
constantly constructing the PDO update query whenever I need it. Now I
simply pass my values and fields to the function and all is taken care
of.

Insert to MySQL PDO Function

Below is a PDO insert function that I wrote to save me time from
constantly constructing the insert query whenever I need it. Now I
simply pass my values and fields to the function and all is taken care
of.

Connect to MySQL database using PDO

Below is how to set up a PDO connection to a MySQL database.

MySQL, UPDATE data into a database using CONCAT()

To insert data into a MySQL database field while preserving the existing data in that field, simply concatenate the new data to the end of the existing data.

Below is an example of how to do this using the field "changelog".

UPDATE table1 SET changelog = CONCAT(changelog, "new data" ) WHERE id = 'idnumber';

If you would like the data that you are entering to appear at the beginning of the existing data, simply flip the concatenation, example:

MySQL INSERT with a Max() + 1 SubQuery

This was a pain in my ass to figure out so I figured I would post it here in case anyone else needs to insert a record using a Max() + 1 SubQuery.

I only use the Max()+1 SubQuery when the value I want to increase is not the primary key.

INSERT INTO table (row1, row2, row3) SELECT 1 + COALESCE((SELECT MAX(row1) FROM table), 0), 'value2', 'value3';

MySQL DELETE with a Max() Subquery

Here is a nifty way to use a SubQuery when deleting a record from your MySQL table.

The following MySQL code will delete a record from "table1" where the slot is equal to the MAX() value of slot from "table2".

DELETE FROM table1 WHERE slot = (SELECT MAX(slot) FROM table2);

Pages

Subscribe to RSS - MySQL