Source Reference - MySQL

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. This is particularly useful if information contained in the array is likely to change.

TABLE OF CONTENTS:

By: Alex Ressi

Poor Best

Rating:

/ 53

January 04, 2000

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Plug this in place of the JavaScript array in the source code of the refering page and go! PHP can be inbeded in JavaScript tags.


<?php
$db = mysql_connect("localhost", "root", "");
// This establishes a link to MySQL
mysql_select_db("extranet",$db);  // The database is specified
$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);
$type = "";
$number2 = "0";
while ($myrow = mysql_fetch_row($result)) {
if ($myrow[3] != $type) {
if ($number2 != NULL) { 
$newnumber2 = ($number2 + "1");
print ("ar[$number2] = new Array();\n");
$number2 = $newnumber2;
$type = $myrow[3];
$number = "0";
}
}
print "ar[" . ($number2 - "1") . "]";
if ($number != NULL) {
$newnumber = ($number + "1");
print ("[$number]");
$number = $newnumber;
}
print (" = new makeOption(\"$myrow[2]\", \"$myrow[1]$myrow[3]\");\n");
}
?>

The drop down menu with skills is also database driven so that new skills can easily be added to the database. Here is the code that was used to generate it.

 <SELECT NAME="industry" onChange="relate(this.form)">
<?
$db = mysql_connect("localhost", "root", "");  
mysql_select_db("extranet",$db);
$sql2 = "SELECT DISTINCT
s.skill_id,
p.skill_id,
skill_name ";
$sql2 .= "FROM
skill s,
person_skill p
WHERE
s.skill_id = p.skill_id
ORDER BY
s.skill_id";
$result2 = mysql_query($sql2);
while ($myrow2 = mysql_fetch_row($result2)) {
print ("

The following is the code to build and populate the the tables that are used in this module. It can be cut out of the web page and then pasted into a text file on your database server where it can then be imported by MySQL using the mysqlimport command.

#
# Table structure for table 'personnel'
#
CREATE TABLE personnel (
person_id int(11) DEFAULT '0' NOT NULL auto_increment,
first_name varchar(15),
last_name varchar(15),
company varchar(30),
PRIMARY KEY (person_id)
);
#
# Dumping data for table 'personnel'
#
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (34,'Turok','Steve','1');
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (32,'Berman','Randal','1');
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (30,' Vi
jaya','Narayanas','1');
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (27,' Jo
han','Lindgren','1');
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (22,'Christiansen','Steve','1');
INSERT INTO personnel (person_id, last_name, first_name,
company) VALUES (15,'Crown','Tom','1');
INSERT INTO personnel (person_id, first_name, last_name,
company) VALUES (36,'Cider','Eric','1');
INSERT INTO personnel (person_id, first_name, last_name,
company) VALUES (42,'Bolton','Liz','1');
INSERT INTO personnel (person_id, first_name, last_name,
company) VALUES (43,'Tuti','Berna','1');
INSERT INTO personnel (person_id, first_name, last_name,
company) VALUES (44,'Dong','Enormai','1');
#
# Table structure for table 'person_skill'
#
CREATE TABLE person_skill (
person_id int(11) DEFAULT '0' NOT NULL,
skill_id tinyint(2),
level tinyint(1)
);
#
# Dumping data for table 'person_skill'
#
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (15,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (15,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (22,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (22,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (27,3,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (30,6,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (32,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (32,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (34,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (34,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (34,7,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (36,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (36,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (42,1,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (42,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (42,7,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (43,4,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (43,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (43,3,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (44,2,NULL);
INSERT INTO person_skill (person_id, skill_id, level)
VALUES (44,3,NULL);
#
# Table structure for table 'skill'
#
CREATE TABLE skill (
skill_id int(11) DEFAULT '0' NOT NULL auto_increment,
skill_name varchar(20),
skill_desc varchar(250),
PRIMARY KEY (skill_id)
);
#
# Dumping data for table 'skill'
#
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (6,'Oracle',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (5,'ASP',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (4,'Cold Fusion',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (3,'Vignette',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (2,'JavaScript',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (1,'HTML',NULL);
INSERT INTO skill (skill_id, skill_name, skill_desc)
VALUES (7,'MySQL',NULL);