Lecture 3 – Data Storage with XML+AJAX and MySQL+socket.io

1. Lecture 3 – Data Storage with XML+AJAX and MySQL+socket.io Written by Matthew Shelley for Professor Wei Shi

2. Data Storage

3. Data Storage • It is often necessary to store game information, which may or may not change • In a single-player game, one file is often sufficient • For instance, we might read settings from an XML file • For an online social game, however, each user may have their own information, which could be shared • A database such as MySQL can fulfill this need

4. XML and AJAX

5. XML • XML serves the purposes of describing data • Typically in a hierarchical fashion • While XML and HTML are structurally very similar, XML does not do anything with the data it describes • A basic tag / element: • <tagname attr1=“value1” ... /> • A more complicated tag / element: • <tagname attr1=“value1” ...>...</tagname>

6. XML • All XML files should begin with • <?xml version="1.0" encoding="ISO-8859-1"?> • Comments are written just like in HTML • <!-- COMMENT --> • Complex data using < and & should be wrapped with <![CDATA[ and ]]>, e.g. • <![CDATA[<notatag>&&&&</notatag>]]>

7. XML Example – Multiple Choice Quiz <?xml version="1.0" encoding="ISO-8859-1"?> <quiz> <question q=“Is true false?” correctvalue=“2”> <answer value=“1”>True</answer> <answer value=“2”>False</answer> </question> <question …> … </question> ... </quiz>

8. Parsing XML with jQuery • With an XML file written, we need to be able to read the data within and then use it • To do so, we first load the file with AJAX and then parse it with jQuery similar to HTML • $.ajax(…) • $(xml).find(‘tag’) • $(xml).children(‘tag’) • $(xml).attr(‘attribute’) • $(xml).text()

9. AJAX • “Asynchronous JavaScript and XML” is used to communicate between the client and the server in the background, similar to WebSockets • Data is sent through either GET or POST methods • GET: data is visible in the URL • POST: data is not visible in the URL • Refer to “type” in the upcoming example • Essentially, AJAX requests a URL, possibly with data sent along, and returns the server’s response

10. AJAX + XML Example • The example provided reads in an XML file containing three users, and then displays the results to a table on the web page • Please refer to • ajax_example.htm • ajax_example.xml • js/ajax_example.js

11. Useful Links • XML Introduction • http://www.w3schools.com/xml/xml_whatis.asp • CDATA • http://www.w3.org/TR/REC-xml/#syntax • AJAX via jQuery • http://api.jquery.com/jQuery.ajax/

12. XAMPP

13. XAMPP • XAMPP allows you to treat your computer like a web server, which is helpful for when you want to develop outside of a live website • Installing XAMPP gains access to: • Apache • MySQL • PHP • phpMyAdmin • And a few others... • Be sure to turn on Apache when you use phpmyAdmin

14. Download and Install XAMPP • To download XAMPP, select your version from: • http://www.apachefriends.org/en/xampp.html • Launch the installer file • It may be necessary to install XAMPP through “Run as Administrator” on Windows • Unless you know what to exclude, get everything • Installation will take 15 – 20 minutes

15. MySQL Databases

16. What is a database? • A database is a collection of tables, where a table is a collection of entries (rows) with associated data (columns) • We perform queries on these tables • Select • Insert • Update • Delete

17. Example Tables: ‘users’ + ‘friends’

18. What Are Queries? • Queries describe ‘what we want to find’ or ‘what we want to happen’ rather than ‘how we will find it’ or ‘how we will make it happen’ • For example, “SELECT userid FROM users” selects all user ids from the user tables • But, we are not specifying how this operation occurs! • In fact, queries even tend to optimize themselves • These queries are performed on the server-side using languages such as PHP or extensions like node-mysql (as will be our case)

19. Break – 15 minutes

20. MySQLQueries

21. MySQL SELECT – Example 1 • SELECT * FROM users • Returns all rows from ‘users’ with all columns

22. MySQL SELECT – Example 2 • SELECT username FROM users • Returns all usernames

23. MySQL SELECT – Example 3 • SELECT userid, username FROM users ORDER BY userid DESC LIMIT 1 • Retrieves the newest user’s userid and username

24. MySQL SELECT – Example 4 • SELECT * FROM users WHERE (email = ‘em1@host.com’ AND password = ‘password1’) LIMIT 1 • Such a query would be useful for verifying that the user has provided the correct login information • Of course, the password should be encrypted • Since user input is often inserted into queries, it is important to sanitize this input to avoid harmful “SQL injection”

25. MySQLINSERT - Examples • INSERT INTO users (username, password, email) VALUES (‘User 1’, ‘password1’, ‘email1@host.com’) • INSERT INTO users SET username = ‘User 1’, password = ‘password1’, email = ‘email1@host.com’

26. MySQLUPDATE - Example • UPDATE users SET username = ‘some_user’, password = ‘some_password’WHEREuserid = 2LIMIT 1

27. MySQLDELETE - Examples • DELETE FROM usersWHEREuserid= ‘1’LIMIT 1 • DELETE FROM some_tableORDER BY datetime_of_access DESC LIMIT 1

28. Useful Links • Select • http://dev.mysql.com/doc/refman/5.5/en/select.html • Insert • http://dev.mysql.com/doc/refman/5.5/en/insert.html • Update • http://dev.mysql.com/doc/refman/5.5/en/update.html • Delete • http://dev.mysql.com/doc/refman/5.5/en/delete.html

29. phpMyAdmin

30. phpMyAdmin • phpMyAdmin manages MySQL databases and users, which are assigned privileges • To access phpMyAdmin: • Start Apache via XAMPP • Start MySQL • Click ‘Admin’ for MySQL to open phpMyAdmin

31. Create Database • With phpMyAdmin open, click the “Databases” tag atop the page • Name your database and then press “Create”

32. Create a User • From the database section, go to “Privileges” • You can see users assigned to this database • You can modify privileges of such users • You can also create a new user

33. Create a User

34. Create a Table • Click the “Create Table” button on the left menu • Enter the table name and number of columns • A few notes… • “varchar 255” is a string of length 255 • The primary key uniquely identifies a row • auto_increment(A_I) can be given to only one column, providing it is a key

35. Create a Table

36. Modify a Table • To modify a table go to its ‘structure’ tab • Each column can be modifiedor removed entirely • You can also add new columns

37. Additional Table Operations • From the “database” structure, you can… • Browse – view rows in a table • Structure – view table structure • Search – perform a query on a table • Insert – add rows to a table • Empty – delete all rows in a table • Drop – delete the entire table

38. MySQLDemos

39. Using node-mysql • To install node-mysql via command prompt: • npm install mysql@2.0.0-alpha5 • Load the module in code with • varmysql = require(‘mysql’); • Refer to server-mysql.js and run it via node.js

40. Using Socket.io + MySQL • Socket.io is an event-driven API that greatly simplifies sending and receiving messages • socket.on(“eventname”, function(data) {…}) • Add event listener to any arbitrary event in order to receive data, e.g. an associative array • socket.emit(“eventname”, data) • Send along data for an arbitrary event

41. Useful Links • mysql-node • https://npmjs.org/package/mysql • Socket.io • http://socket.io/ • Advanced HTML5 JavaScript: Down 'n Dirty • http://youtu.be/Pm6Ch4qoNe8?t=26m

42. One last thing…

43. PHP • There are many server-side languages, though PHP is a common one • If you are using AJAX, you will most likely end up using PHP for your server-side scripts, as it is available with most web hosts • PHP is outside of the scope of this lecture, but here are a few examples...

44. Common MySQL Functions in PHP • These PHP functions are used with MySQL: • mysql_connect() • mysql_select_db() • mysql_query() • mysql_fetch_assoc() • mysql_error()