Along the way, I have needed to connect through a script to a database and didn’t have any of the bridges or scripting additions installed that I had often read about. At first, I use to think that I absolutely had to have them before I could connect. But as usual, after spending long hours of searching and trial by error, I discovered that the path to implementing a database within my XCode projects was far easier than I imagined. So, to give something back to the community, I’ve created this cheat sheet to help those that need to know how to do it the first time. It is a fairly simple and complete way to access your tables and perform the basics (add, delete, update, and retrieve). I hope it helps some of you.
APPLESCRIPT (mySQL database) Retrieve, modify, delete, add
Visit http://dev.mysql.com/doc/world-setup/en/world-setup.html to download and install the example database & data or use your own.
NOTE: Since I am using phpMyAdmin on a remote server (where my site is stored) I created the database ‘world’ and added a new user called ‘uworld’ with a password of ‘worldu’. I then imported the file from the above site (world.sql) to create the three tables containing the example data. So far, I now have a new database called ‘world’ that is populated with three tables and plenty of example data that is accessible to the new user called ‘uworld’. Don’t forget to move the created PHP files to your server and check that the path is the correct one or change to suit.
RETRIEVAL OF DATA:
Next, I need to create the PHP file to “retrieve” the data that I want to display from my Applescript file.
PHP CODE: retrieve.php
Now I need to create the script that will call the PHP and then retrieve and display the data from the database.
APPLESCRIPT CODE for retrieve.php
set cityname to "Kabul"
set myURL to "http://YOURSERVERNAME/test/retrieve.php?cname=" & cityname & ""
set checkit to (do shell script "curl " & quoted form of myURL)
set wordcount to count words of checkit
if wordcount is equal to 3 then
set countrycode to {word 1} of checkit as string
set district to {word 2} of checkit as string
set population to {word 3} of checkit as integer
else
set countrycode to {word 1} of checkit as string
set district1 to {word 2} of checkit as string
set district2 to {word 3} of checkit as string
set district to district1 & "-" & district2
set population to {word 4} of checkit as integer
end if
display dialog "The city of " & cityname & " has a population of " & population & " and is located in the district of " & district & ". The country's short code is " & countrycode & ""
MODIFY DATA:
Since I now want to modify the data within a record of the table of the database, I will need to write a different PHP file to call.
PHP CODE: modify.php
And the script to update the data would be:
APPLESCRIPT CODE for modify.php
set cityname to "Kabul"
set popnumber to 1759990
set myURL to "http://YOURSERVERNAME/test/modify.php?nameofcity=" & cityname & "&newnumber=" & popnumber & ""
set urlMy to (do shell script "curl " & quoted form of myURL)
DELETE DATA:
Deleting is just as easy
PHP CODE: delete.php
APPLESCRIPT CODE for delete.php
set cityname to "Kabul"
set myURL to "http://YOURSERVERNAME/test/delete.php?nameofcity=" & cityname & ""
set urlMy to (do shell script "curl " & quoted form of myURL)
ADD DATA:
Easily add data records as well
PHP CODE: add.php
APPLESCRIPT CODE for add.php
set cname to "Kabul"
set ccode to "AFG"
set cdist to "Kabol"
set npop to "1780000"
set myURL to "http://YOURSERVERNAME/test/add.php?cityname=" & cname & "&countrycode=" & ccode & "&district=" & cdist & "&population=" & npop & ""
set checkit to (do shell script "curl " & quoted form of myURL)
Figuring out the rest should be fairly easy from here.
Chuck
Model: Dual 2.5 G5
Browser: Safari 525.20.1
Operating System: Mac OS X (10.5)