SQL, , ,

What a bizarre tutorial – you are probably thinking – he’s meant to be showing me php and mysql and I haven’t even had them mentioned yet. This is true, and yet this will almost always be the case. Database application development follows fairly similar routes and stages, almost always you will need to have some basic structures in place in the flash file before you start to think about connecting the two. As you get more experienced you may be able to get some old code and hack through it, but, you will benefit from setting up locally by not having to test every aspec of your interface on a live database, and time is everything.

This section of the tutorial will concentrate on setting up your database environment. Yey! And also to let you create the skeleton PHP files.

In order for this to work you will obviously need access to a server with MYSQL on it and have access to a database as well as PHP. If you haven’t got these then the rest of the MYSQL / PHP tutorial will be tricky for you and probably a bit pointless. So looking on the positive side I am going to guess you have access to these things.

Every database will have a name, it will need a user (with privelidges to read/write/delete) and the user will have a password. There is also the host of the database, localHost simply means this server. These details should all be obvious as well as how to find your PHPMYADMIN tool. This tool is a database development environment allowing you to fully interact with databases, their tables and just about anything you need to do with them. Its very good and intuitive. Usually these basic connection details are kept in a simple file on your server and included into all of your scripts. Give the file a name like include300368.php something memorable but impossible to guess. Justa quick note on security here, php is mostly a safe language, anybody trying to access the php inside will not be able to as it is parsed by the server prior to being served to a visitors browser. In the case below, these are temporary php variables and are not served to the visitor ever. Saying that, things change, right now there could be an 11 year old git who has figured how to get access to the raw code.

This is the building block of your PHP files and will give you the necessary login access to your database.

Next we need to set up a table for our work. Go to your phpMyAdmin page, you may have to log in, and access the database you want to work with. If you only have 1 database then this will be preselected otherwise look on the far left column. When the database start page is open there will be an option to”Create new table on database DATABASENAME” call your new tavle phpexampleData. Or don’t. You can call it purpleElephants if you want to but typically its good to hold to logical naming standards.

For this simple example we need an ID, a title and a position value, so name your columns and select INT, STRING, INT for the types and remember to click AUTO_INCREMENT for the id, do not end your column names with numbers, why will become apparent later. The ID is your primary index and auto incrementing it means it will always be a unique value.

Now we are going to do an insert on the table. In fact we are going to action all of the tasks we need our PHP to use later and copy them. Open up notepad for somewhere to store the information. Click the insert table and insert a row use 1,2,3

Clicking go executes the request and the screen should have the SQL statement on the screen, copy this and paste it into notepad then change it to look like the one below:

$sql = "INSERT INTO `mkbatman_sms`.`phpExampleData` (`x`, `y`, `z`) VALUES (NULL, '$x1', '$x2');";

notice I have changed the values to be $x1 and $x2 these are PHP variables. Also I changed the value going into the id field to a NULL, this says have a look at the highest current ID and autoincrement that. The double quotes in PHP is special and in this case it will look inside the double quoted string for any PHP variables and it will replace them with their values.

Next lets update the row we just created, click the pencil icon on the new row on screen and update the title field and click go. You should see the SQL statement, change it to the one below

$sql = "UPDATE`mkbatman_sms`.`xxxx`SET `y`='$x1' WHERE `x`='$x2';"; again the actual change and the id have been replaced by php variables.

Next the get, we will be wanting to select a sorted data set, so very quickly insert another row of test data and then when it returns click on the title of the third column, here its z, because there are 2 rows in there this becomes clickable to sort. Clicking on it will execute a get and a sort, grab the code and change it to the one below

$sql = "SELECT * FROM `xxxx` ORDER BY `z`ASC;";

and now the final statement delete, should look like:

$sql = "DELETE FROM `mkbatman_sms`.`xxxx`WHERE`xxxx`.`x`='$x1';";

and again I have replaced the actual ID by the php variable.

Great, we are now ready to create some PHP files. In this instance we are going to use HTTP GET variables which allows us to test the scripts directly on the browser and not have to wait for our integration with the flash. Let’s start with the add a row script.

looks pretty simple, we include out credentials file, we look at the url to get our arguments to be inserted, we make a p-connection to the databse (p = persistant) and we choose the database we want to work with. Next we create the query string SQL using the arguments, action the SQL and if it inserted we get a good, otherwise we get a bad, this is then echoed to the screen.

Note also the end of the status statement has bull = 1, this is a dummy end argument and I use it from the old days to ensure that there isn’t any extra items tagged to the end of the returning statement. It can happen. The other reason is that one of the variables I usually send to the script is bull=0. By checking on the return that bull =1 I can confirm the script was ran.

Save this text as insertNew.php and send it to the server. next enter the URL of the file into the browser and add at the end “?x1=test&x2=5” which will populate our GET variables. Hit return to send it and you should see “msg=good&bull=1” on the screen. If there are errors double check each statement has a “;” terminating it and just look around for an error, its a simple script so should be easy to find.

Next to confirm the action go back to phpMyAdmin, select the table and confirm the addition.

The other three actions are very similar and should be tested in the same format. Once you have confirmed they all work change the GET to a POST in the script and leave them. They should look like the following:

1. GET does the select and order and then parses the results and sends them back along with the total number of results

2. DELETE

3. UPDATE ROW

4. REORDER THE LIST – need to do this when we re-order the list in flash

that’s more than enough for one session. We have set up the database table, actioned the main tasks within phpMyAdmin and using those created our primary PHP files which we tested using HTTP GET variables directly on the browser.

After fully testing the scripts and checking the table amends were correct we can convert the variables back to HTTP POST for the integration.

As always the teaser is still there on the server if you want reminded of what we are aiming at