Loading JavaScript Arrays with MySQL Data
(Page 1 of 2 )
We have all seen pages that use JavaScript for better or for worse. In many cases JavaScript can improve a site's functionality and ease of use. Unfortunately administrating some of the complicated arrays that JavaScript depends on for things like heirarchichal menus and dynamic forms can be a pain in the rear. That's why were going to turn the task over to PHP and MySQL. We can use this combination to load data into the JavaScript for us. This is particularly useful if information contained in the array is likely to change.
In this exercise we will build a selection component for a resource management system. The component will tie people and project together based on staffing needs and employee skill. It will also illustrate how PHP and MySQL can be used to dynamically build JavaScript. The static component code is below.
This component uses two popular JavaScripts which are readily avialable on the web. I grabbed the JavaScript for the 'menu swapper' from
www.javascriptsource.com, and I picked up a script to handle the drop down menu change from
www.webreference.com.. With a little time, I managed to get the two scripts to work together as planned. View the source to see the resulting code. One of the first things you will notice is the following JavaScript array.
var ar = new Array();
ar[0] = new Array();
ar[0][0] = new makeOption("Crown, Tom", "151");
ar[0][1] = new makeOption("Christiansen, Steve", "221");
ar[0][2] = new makeOption("Berman, Randal", "321");
ar[0][3] = new makeOption("Turok, Steve", "341");
ar[0][4] = new makeOption("Cider, Eric", "361");
ar[0][5] = new makeOption("Bolton, Liz", "421");
ar[1] = new Array();
ar[1][0] = new makeOption("Crown, Tom", "152");
ar[1][1] = new makeOption("Christiansen, Steve", "222");
ar[1][2] = new makeOption("Berman, Randal", "322");
ar[1][3] = new makeOption("Turok, Steve", "342");
ar[1][4] = new makeOption("Cider, Eric", "362");
ar[1][5] = new makeOption("Bolton, Liz", "422");
ar[1][6] = new makeOption("Tuti, Berna", "432");
ar[1][7] = new makeOption("Dong, Enormai ", "442");
ar[2] = new Array();
ar[2][0] = new makeOption("Lindberg, John", "273");
ar[2][1] = new makeOption("Tuti, Berna ", "433");
ar[2][2] = new makeOption("Dong, Enormai", "443");
ar[3] = new Array();
ar[3][0] = new makeOption("Tuti, Berna ", "434");
ar[4] = new Array();
ar[4][0] = new makeOption("Narsysus, Thelma", "306");
ar[5] = new Array();
ar[5][0] = new makeOption("Turok, Steve ", "347");
ar[5][1] = new makeOption("Bolton, Liz ", "427");
The above code will serve as a model while we write our PHP
code. Let's take a quick look at the anatomy of an array. The first set of brackets, ar[x], in this multi-dimentional array refers to the skill. The second set of brackets ar[x][x] is the array index of the item, which will always begin by default with 0. The item in this case is the employee. This array will be replaced by PHP code which will dynamcally build it. Now that we have played around with the component and had a look at the source code, it would be a good idea to build and populate that database.
Once the database has been built and populated, we need to do the following things to make our JavaScript dynamic. Note: The only portion of the source code that will be dynamic is the array, the rest of the JavaScript will remain static.
- The database needs to be queried for employee names, and employee skills (two separate tables). The results need to be ordered by skill.
- We will then need to loop through the skills printing the employee names associated with the skill
- A mechanism then needs to be built to pass the employee id, skill id and project id to the form processing component.
Let's begin with the query. Have a look at the database schema (see page 2) to see how the information is stored. There are 3 tables involved in this component. Personnel, Skill, and person_skill.
$db = mysql_connect("localhost", "root", "");
mysql_select_db("extranet",$db);
A link to the database server is established, and the
database is selected.
$sql = "SELECT
p.person_id,
s.person_id,
CONCAT(last_name,', ',first_name) AS name,
skill_id";
$sql .= "FROM
personnel p,
person_skill s
WHERE
p.person_id = s.person_id
ORDER BY
skill_id, name";
$result = mysql_query($sql);
The SQL statement is pretty straightforward. If you are
unsure about what is going on here, you can always go to the
MySQL site where there are numerous tutorials. The important thing to note in this query is the ORDER BY clause, which will properly setup the arrangement of the resulting data. After performing our SQL we then initialize two variables:
$type = "";
$number2 = "0";
We then will perform the while loop which will actually build
the JavaScript array.
while ($myrow = mysql_fetch_row($result)) {
A series of "If then" statements will control the proper
formation of the array.
if ($myrow[3] != $type) {
The first if statement checks to see if the variable
$myrow[3] which is the skill_id from our SQL statement, is NOT equal to the variable $type. $type was set outside of the loop to nothing. The two values are not equal, so the next expression will be evaluated.
if ($number2 != NULL) {
$number2, which was initialized outside of the loop with a
value of 0 is not equal to NULL, so the code within the curly braces gets run.
$newnumber2 = ($number2 + "1");
print ("ar[$number2] = new Array();\n");
$number2 = $newnumber2;
$type = $myrow[3];
$number = "0";
}
}
We have a new variable to start with, $newnumber2 which is
given a value of 1. (0 + 1 = 1) The first line of the JavaScript array is then printed. ar[0] = new Array();
$number2 which was initially set to 0, now takes on the value of $newnumber2 which is 1. $type now is given a value. Initally set with no value and now $type has the value of $myrow[3] which is 0.
print "ar[" . ($number2 - "1") . "]";
if ($number != NULL) {
$newnumber = ($number + "1");
print ("[$number]");
$number = $newnumber;
}
From this code block we get the first part of the next line,
namely ar[0][0]. The first '[0]' refers to the skill, so it will be repeated for each person that is associated with that particular skill. The next '[0]' refers to an individual possessing the skill. There is an "if statement." that increments the number in the second set of square brackets for each row in the database.
print (" = new makeOption(\"$myrow[2]\", \"$myrow[1]$myrow[3]\");\n");
}
Before closing the while loop, we are going to append "= new
makeOption("Crown, Tom", "151");" to the "ar[0][0]", thus completing one pass through the loop. The loop will be run for each row in the database query, which is in this case is 21. You can view the entire unbroken source code on the next page. The next challenge will be passing multiple values to the form processing script. This will be done using a combination of JavaScript and PHP, and will be the focus of a seperate upcoming article.
In addition to building JavaScript arrays, this code can be hacked up for a number of other uses . What this code essentially does is print the first row of a given field, where rows in the database have fields with common values. In this example, we initialize an array which represents a given skill, for instance ar[0] = new Array();. Under that we print all the people in this case that relate to that skill. This can easlily be applied to something like a classified system. Let's say you are selling automobiles and you would like to print as a header, the make of the car before listing all the models that fall under it. You could use this code to do the same. There are many uses for this code. Don't be afraid to try things out. The world is now your oyster, enjoy.
Next: Source Reference >>
More MySQL Articles
More By Alex Ressi
/ 29 
