MySQL

AMFPHP Genie v0.2

Dec 13th

Posted by Danny Kopping in AIR

1 comment

AMFPHP Genie (0.2) is a simple tool to help you get shit done using Flex and AMFPHP.

Check out http://dannykopping.co.za/amfphp-genie/ for more information!

, ,

QuickTip: asSQL Connection Problem with non-localhost hostname

Dec 12th

Posted by Danny Kopping in AIR

1 comment

asSQL is just great. It’s an ActionScript 3.0 library for connecting AIR applications to MySQL databases and it works brilliantly – when it works! Recently i gave it a shot again after an utter failure the last time i tried it… The library seems to be a port of the Java implementation of connecting to MySQL (Connections, Statements, Fields, etc). It takes a little getting used to but it’s an excellent library and i highly recommend it.

Background

My development environment is Windows-based (against my wishes… not enough loot or motivation for a Mac, lack of Adobe software on Linux) but i make up for this by running a virtual Linux installation in my Windows environment and networking the two together. It actually works really well, and the process for setting this up can be found here. To cut a very long and boring story short, essentially i cannot use localhost as your server when using this architecture since i have my LAMPP stack running on my Linux environment, so my “local server” can only be accessed using the IP address of the virtual system.

This complicates things a bit when trying to use asSQL to connect to my MySQL installation, because MySQL gets all paranoid and won’t accept connections from a foreign IP address (the IP of my Windows machine on which my AIR app is running)… To get around this, all you have to do is create a new user in MySQL (i used phpMyAdmin), set the user’s hostname to the IP address of your remote system (i’ve explained how to do this below) and you’re ready to rock and roll!

Solution

The first thing you need to do is get the IP address of the remote system…

Windows: Open the command line, type ipconfig
Linux & Mac: Open the terminal, type ifconfig

In this example, my IP address is 192.168.56.1

I’ll be using phpMyAdmin to fix the problem.

  1. Open phpMyAdmin and click on the Privileges tab.
  2. Click Add a new User
  3. Enter any username you like, paste the IP address obtained in the Host field and put in a password
  4. Under the Database for user panel, leave it at None
  5. Under the Global privileges, you can Check All

That should sort it out… Remember, this is HIGHLY insecure and should only be used on development environments and NOT production environments.

, , , , ,
conditionals

Using Conditional Statements with MySQL

May 14th

Posted by Danny Kopping in MySQL

2 comments

While working on a project, i got lazy (as i always do…); I didn’t feel like fetching an array of rows from MySQL and filtering the data in PHP according to a condition… Too much of a ballache to be honest. I thought: ‘Fuck it… Let’s see if one can use conditional statements in MySQL‘.

After doing a little research, i came across this page (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html). After scouring through the normally indecipherable MySQL reference manual, i managed to get the hang of it…

Although the scenarios where the application of this functionality are few and far between, i felt it’d still be a cool idea to share this (seemingly) arbitrary tit-bit.

Consider the following scenario…
You have a table of users. In this table, you keep the following information about each user:

  • name
  • surname
  • gender

Now, depending on the gender of your user, you would like to return their details with a certain prefix, namely Mr for a male or Ms for a female. Here’s the database structure, and two rows of sample data:

CREATE TABLE IF NOT EXISTS `users`
(
  `name` VARCHAR(255) NOT NULL,
  `surname` VARCHAR(255) NOT NULL,
  `gender` enum('M','F') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
--
-- Sample data
--
 
INSERT INTO `users` (`name`, `surname`, `gender`) VALUES
('Joe', 'Shmo', 'M'),
('Jane', 'Shmane', 'F');

…and here’s the SQL query

SELECT *, CONCAT(IF(gender = "M", "Mr ", "Ms "), name, " ", surname)
AS fullName
FROM users

If that looks a little too indecipherable/complicated, here’s the previous query in a more simplified (albeit less efficient) way:

SELECT *, IF(gender = "M", CONCAT("Mr ", name, " ", surname),
			CONCAT("Ms ", name, " ", surname))
AS fullName
FROM users

conditionalsEssentially the IF() statement is structured like this:

IF(condition, true code, false code)

However, you can place an IF statement anywhere in a query (from what i can tell thus far), it gets executed in place and is replaced by either the true code or the false code.

Another (much simplified) example:

SELECT IF(1 > 0, "TRUE!", "FALSE!");

…and that’s conditionals with MySQL!

,

Using PHP to Backup and Save your MySQL Database

Apr 17th

Posted by Danny Kopping in MySQL

4 comments

While working on a recent project, i needed to set up a cron job that would run every X amount of hours and backup a database. I’ve done this a few times before, but only this time (while Googling for a better solution) i found David Walsh‘s blog post on how to Backup Your MySQL Database Using PHP. David’s done a great job and i definitely recommend you check his blog out in its entireity for some really neat web dev articles.

I decided to go and put David’s script into class form (my case of OCD is genuine :P ) and i added one or two things to it. Basically it allows you to backup one or many tables in a database, include or exclude the data from the backup and i also included Rick Ellis’ (of CodeIgniter fame) Zip class to allow you to zip up the backup and stash it somewhere.

Thanks to David Walsh and Rick Ellis!

Here’s the class: DatabaseDump.php

…and here’s how to use it:

$dump = new DatabaseDump("host", "user", "password", "database", "destination/");
$dump->backup();

Be sure to check out the DatabaseDump.php class for the documentation!

, , , , , , ,