Lecture 3 – Data Storage with XML+AJAX and MySQL+socket.ioWritten by Matthew Shelley for Professor Wei Shi
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
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>
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>]]>
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>
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()
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
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
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/
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
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
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
Example Tables: ‘users’ + ‘friends’user_id username password email
1 User_1 Pass_1 [email protected]
2 User_2 Pass_2 [email protected]
3 User_3 Pass_3 [email protected]
user_id friend_id
1 2
1 3
2 3
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)
MySQL SELECT – Example 1
•SELECT * FROM users▫Returns all rows from ‘users’ with all
columns
user_id username password email
1 User_1 Pass_1 [email protected]
2 User_2 Pass_2 [email protected]
3 User_3 Pass_3 [email protected]
MySQL SELECT – Example 2
•SELECT username FROM users▫Returns all usernames
username
User_1
User_2
User_3
MySQL SELECT – Example 3
•SELECT userid, username FROM users ORDER BY userid DESC LIMIT 1▫Retrieves the newest user’s userid and
username
user_id username
3 User_3
MySQL SELECT – Example 4
•SELECT * FROM users WHERE (email = ‘[email protected]’ 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”
MySQL INSERT - Examples
•INSERT INTO users (username, password, email) VALUES (‘User 1’, ‘password1’, ‘[email protected]’)
•INSERT INTO users SETusername = ‘User 1’,password = ‘password1’,email = ‘[email protected]’
MySQL UPDATE - Example
•UPDATE users SETusername = ‘some_user’,password = ‘some_password’
WHEREuserid = 2
LIMIT 1
MySQL DELETE - Examples
•DELETE FROM usersWHERE
userid = ‘1’LIMIT 1
•DELETE FROM some_table ORDER BY datetime_of_access DESC LIMIT 1
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
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
Create Database
•With phpMyAdmin open, click the “Databases” tag atop the page
•Name your database and then press “Create”
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
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
Modify a Table
•To modify a table go to its ‘structure’ tab▫Each column can be modified or removed
entirely▫You can also add new columns
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
Using node-mysql
•To install node-mysql via command prompt:▫npm install [email protected]
•Load the module in code with▫var mysql = require(‘mysql’);
•Refer to server-mysql.js and run it via node.js
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
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
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...