BIOSTALL

Executing a MySQL DELETE query that contains a JOIN… This is something that I always forget how to do so I wanted to write about it as a personal reference, and to help others looking to do the same. First lets take a look at a standard DELETE query containing a single table:

DELETE FROM
	`my_table`
WHERE
	`my_value`='FooBar';

Easy right? Now, the next part is where I was going wrong and I’m guessing is the same in your case. The next statement shows how you might first try to perform the DELETE with a JOIN based on other MySQL syntax:

DELETE FROM
	`my_table`
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE
	`my_value`='FooBar';

By running the above you’ll get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column` WHERE `my_va' ne 1

The Solution

Sticking with the query above we can get it to work with a simple amendment. All we need to do is put the name of the table we’re deleting from between the words ‘DELETE’ and ‘FROM’ like so:

DELETE `my_table` FROM
	`my_table`
INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column`
WHERE
	`my_value`='FooBar';

Whilst recently carrying out a security audit, one of the tasks on my list was to refine the permissions that MySQL users have. One of the users in the list was called ‘admin’. It had full permissions to all databases which struck me as a bit unsafe so, seeing as I wasn’t using this MySQL user on any of my sites, I struck (maybe a bit too hastily) and deleted it. From here on in things went downhill…

It’s probably worth mentioning at this point that I use the Plesk control panel to manage various aspects of my server; from database access to setting up cronjobs. I know this could all be done via the command line but by doing it through the Plesk GUI it makes carrying out tasks generally quicker.

It was at the moment I deleted the MySQL admin user that I started to receive the following error when trying to access Plesk:

The service is not available now.
Some Container resources are overused. Click to see the details.

The error was definitely misleading and didn’t relate in anyway to the deleted MySQL user. Regardless, I was certain the two were linked so my first point of call was to reinstate the admin user I had just deleted. But how do you add a MySQL user with full permissions when you a) don’t have any other MySQL users available or b) don’t have another MySQL user available to log in as with high enough privileges to create more users.

The Solution

After lots of digging around I found the solution to add the admin user to MySQL and managed to get access back into Plesk by following the steps below:

1. Log onto the Linux command line using a tool like Putty.

2. Navigate to your MySQL configuration file and open it in an editor. For me this was located at /etc/my.cnf:

$ nano /etc/my.cnf

3. Add the following line to my.cnf on a blank line:

skip-grant-tables

By adding ‘skip-grant-tables’ we are telling MySQL that permissions are out of the window and anyone can get access to everything. It goes without saying that we’ll need to remember to remove this later on.

4. Save and close my.cnf and restart MySQL so the updated configuration comes into effect:

$ /etc/init.d/mysqld restart

5. Once the MySQL service has restarted the next step is to add our ‘admin’ user back in. First, lets log onto the MySQL command line like so:

$ mysql

Notice how we don’t need to add any user credentials? This is the configuration option we added above in effect.

6. Next lets change to the ‘mysql’ database:

mysql> use mysql;

The ‘mysql’ database is where MySQL stores it’s configuration settings, as well as user information and permissions.

7. Now it’s time to add the ‘admin’ user back in where it belongs. We can achieve this by running the following query:

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_plesk_password';

8. Good. Now it’s time to grant this new user some privileges:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

9. And finally, to bring our reinstated user and all it’s permission into effect we need to flush the privileges:

mysql> FLUSH PRIVILEGES;

At this point in the process you should now be able to log into Plesk successfully. If you’re already logged in I recommend you log out and then log back in again. Fingers crossed it should all be back to normal.

Remember…

The last thing to remember to do is to undo what we did in step 3 above. Simply repeat steps 1-4 and remove or comment out the option.

When selecting fields in a MySQL statement you can perform certain conditional statements to get the values you require based on other values. One of these is the Switch Case conditional statement. If you’re familiar with other programming languages you probably know how to do this already but allow me to demonstrate how this can be done within a MySQL query:

SELECT
	CASE `field`
		WHEN 'value1' THEN 'output_value1'
		WHEN 'value2' THEN 'output_value2'
		WHEN 'value3' THEN 'output_value3'
	END AS fieldAlias
FROM
	`table`

Heres an another example using the Switch Case conditional statement that will output a users active state based on a number in the database:

SELECT
	CASE `userActive`
		WHEN '1' THEN 'Active'
		WHEN '2' THEN 'Pending'
		WHEN '3' THEN 'Deleted'
	END AS userActive
FROM
	`user`

I was recently assigned the task of getting the first word from a field in a MySQL table. In my case it was getting the first part of a customers postcode. Following a quick look through the MySQL reference manual I was guided towards the SUBSTRING_INDEX() function. By using this function I was able to select the first part of the string by using an SQL query like so:

SELECT SUBSTRING_INDEX(`postcode`, ' ', 1)) AS postcodeOutward FROM `customers`

Here I am passing the function three parameters; the table column name in question, the delimiter (in our case a space is being used), and the count of delimiters from the start of the string at which to stop at.

Should you want to loop through or use this information in PHP you can then do something like so:

$query = "SELECT SUBSTRING_INDEX(`postcode`, ' ', 1)) AS postcodeOutward FROM `customers`";
$result = mysql_query($query) or die(mysql_error());
while ($row=mysql_fetch_assoc($result)) {
	echo $row['postcodeOutward']."\n";
}

We can insert a single record into a MySQL table using the following format:

INSERT INTO `table_name`
	(`column1`, `column2`, ...)
VALUES
	('value1', 'value2', ...)

But how do we go about inserting more than one record at the same time? The answer is simple and involves us duplicating the last part of the query the required amount of times. Let me show you an example:

INSERT INTO `table_name`
	(`column1`, `column2`, ...)
VALUES
	('value1', 'value2', ...),
	('value1', 'value2', ...),
	('value1', 'value2', ...)

By simply separating the sets of values with a comma we can now insert multiple records in one go. Much more efficient and cleaner than executing a separate INSERT command for each new record.

If you’re building a website using PHP then at some you’ll no doubt want to read and write data to a MySQL database. In order to do this we first need to establish a connection to the database. Presuming the database has already been setup we can do this by placing just a few lines of code before any queries take place, normally at the top of the page:

$db_host = "localhost";
$db_user = "mysql_user";
$db_pass = "mysql_pass";
$db_name = "my_database";

mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);

The Code Explained

Lets break down the above code and look at what each line means:

Line 1: Here we set the MySQL server. If the script making the connection is on the same server as MySQL then this will more than likely be “localhost”, however if hosted elsewhere this will be the hostname of the external server.

Line 2: MySQL users are setup in order to specify different privileges to different databases and tables. I won’t go into too much detail on users in this post but to connect you will need a username and password. Default MySQL installations already have a user setup with the username ‘root’ and no password, however I recommend that you contact your web hoster if you are unsure on these details.

Line 3: As above, like the username, you will need the password of a valid MySQL user in order to successfully connect.

Line 4: This is the name of the database that you wish to perform actions on. If your script has trouble connecting to the database I would firstly ensure that the database is setup and spelt correctly (note that the name is case-sensitive) and that the MySQL user you are connecting as (Lines 1 and 2) has permission to access the database in question.

Line 6: Now that we have our variables setup we are ready to connect to the MySQL server. The php function mysql_connect() allows us to do this.

Line 7: If your script has got this far without showing an error you’re doing well. We’re connected and all that’s left is to select the relevant database. As with connecting we can do this by using the built in PHP function mysql_select_db().

Well done! You’re connected to MySQL enabling you to run queries on the database you specified.

The Icing on the Cake

What we’ve done above allows us to connect and select a database in a very primitive form. That’s great, but what if you’re getting an error, nothings happening at all or we want to make our script a bit more future-proof were something to go wrong in the future. The snippet below is similar to above but adds error checking meaning if something is wrong with your setup you’ll get a much better idea of what and where it’s going wrong:

$db_host = "localhost";
$db_user = "mysql_user";
$db_pass = "mysql_pass";
$db_name = "my_database";

mysql_connect($db_host, $db_user, $db_pass) or die('Could not connect: ' . mysql_error());
mysql_select_db($db_name) or die('Could not select database: ' . mysql_error());

Note the additional die() commands? By adding these along with the mysql_error() function you’ll get an explanation of exactly what’s wrong should an error occur.

If you haven’t heard of the MySQL REPLACE() function before then add it to your toolkit because I’m pretty confident you’ll need it again at some point. It’s saved my a** numerous times now and saved me hours of manual work.

For those of you reading this that are familiar with programming it’s the MySQL equivalent of PHP’s str_replace() and JavaScript’s replace() functions and it’s format goes something like this:

UPDATE
    `[table_name]`
SET
    `[dest_field_name]` = replace(`[source_field_name]`, '[string_to_find]', '[string_to_replace]');

When inserting records into a MySQL table that contains an auto-incrementing field you might at some point start receiving the following error:

“Duplicate entry ’127′ for key 1″

If this is the case it means the datatype of the field in question is set as ‘tinyint’. The tinyint datatype only accepts integers up to 127 (or 255 if set as unsigned) and therefore on the 128th insertion attempt you will receive the above error.

To get around this issue, try changing the datatype of this field to a type that supports larger numbers such as a smallint, mediumint or int.

A recent e-commerce project I was working on required me to generate a unique order reference number containing both letters and numbers for every new order placed. Generating a unique reference was easy, but what if the generated output already existed in the database? Wouldn’t be very unique now would it!

After a few minutes deliberating over the best way to go about this I came up with the below:

// The length we want the unique reference number to be
$unique_ref_length = 8;

// A true/false variable that lets us know if we've
// found a unique reference number or not
$unique_ref_found = false;

// Define possible characters.
// Notice how characters that may be confused such
// as the letter 'O' and the number zero don't exist
$possible_chars = "23456789BCDFGHJKMNPQRSTVWXYZ";

// Until we find a unique reference, keep generating new ones
while (!$unique_ref_found) {

	// Start with a blank reference number
	$unique_ref = "";

	// Set up a counter to keep track of how many characters have
	// currently been added
	$i = 0;

	// Add random characters from $possible_chars to $unique_ref
	// until $unique_ref_length is reached
	while ($i < $unique_ref_length) {

		// Pick a random character from the $possible_chars list
		$char = substr($possible_chars, mt_rand(0, strlen($possible_chars)-1), 1);

		$unique_ref .= $char;

		$i++;

	}

	// Our new unique reference number is generated.
	// Lets check if it exists or not
	$query = "SELECT `order_ref_no` FROM `orders`
		      WHERE `order_ref_no`='".$unique_ref."'";
	$result = mysql_query($query) or die(mysql_error().' '.$query);
	if (mysql_num_rows($result)==0) {

		// We've found a unique number. Lets set the $unique_ref_found
		// variable to true and exit the while loop
		$unique_ref_found = true;

	}

}

echo 'Our unique reference number is: '.$unique_ref;

The above code will generate a unique reference number and check it against the database to ensure it doesn’t already exist.  If it does repeat the sequence and try again or, if it is indeed unique, continue with the script.