Inspired-Evolution.com : : Portfolio : : PHP/MySQL

In my various development positions, I have been tasked to create web applications which not only looked great, but performed even better and were user friendly while displaying information in a quick and efficient manner. With a combination of using PHP/MySQL,CSS,JQuery, XML and AJAX, I was able to get this accomplished. Here is a how-to on creating and displaying data drom several MySQL tables using a JOIN SQL statement, then displaying that info with PHP. Finally decorating the table with a little CSS/JQuery and lastly adding some AJAX to submit the info, without a page refresh.

let's start with SQL I use to create and populate, two tables. One I will call sports and the table will contain two columns. One, an ID column that is the primary key and auto increments. The other is the column which list the sports. The other table contains the players SQL info which consist of it's own primary key (also an ID). A foreign key which matches up with sports table. This is needed in order to do our JOIN which I will explain shortly.

-- Table structure for table `sports`
--
CREATE TABLE IF NOT EXISTS `sports` (
  `category_id` int(2) NOT NULL AUTO_INCREMENT,
  `sport` text NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

--
-- Dumping data for table `sports`
--
INSERT INTO `sports` (`category_id`, `sport`) VALUES
(1, 'Basketball'),
(2, 'Hockey'),
(3, 'Baseball'),
(4, 'Football');

-- Table structure for table `players`
--
CREATE TABLE IF NOT EXISTS `players` (
  `player_id` int(2) NOT NULL AUTO_INCREMENT,
  `category_id` int(2) NOT NULL,
  `sport` text NOT NULL,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

--
-- Dumping data for table `players`
--

INSERT INTO `players` (`player_id`, `category_id`, `sport`,
 `first_name`, `last_name`) VALUES
(1, 1, 'Basketball', 'Michael', 'Jordan'),
(2, 1, 'Basketball', 'LeBron', 'James'),
(3, 1, 'Basketball', 'Magic', 'Johnson'),
(4, 1, 'Basketball', 'Larry', 'Bird'),
(5, 1, 'Basketball', 'Julius', 'Erving'),
(6, 1, 'Basketball', 'Jerry', 'West'),
(7, 3, 'Baseball', 'Barry', 'Bonds'),
(8, 3, 'Baseball', 'Derek', 'Jeter'),
(9, 3, 'Baseball', 'Alex', 'Rodriguez'),
(10, 3, 'Baseball', 'Albert', 'Pujos'),
(11, 2, 'Hockey', 'Wayne', 'Gretsky'),
(12, 2, 'Hockey', 'Eric', 'Stahl'),
(13, 2, 'Hockey', 'Mario', 'LeMeux'),
(14, 2, 'Hockey', 'Sid', 'Crosby'),
(15, 4, 'Football', 'Dan', 'Marino'),
(16, 4, 'Football', 'Joe', 'Montana'),
(17, 4, 'Football', 'Peyton', 'Manning'),
(18, 4, 'Football', 'Barry', 'Sanders'),
(19, 4, 'Football', 'Tom', 'Brady'),
(20, 4, 'Football', 'Deon', 'Sanders');

Now let's take a look at the PHP code used to connect to a MySQL Database and display the information in a drop-down type form.

//this is the AJAX code that will display the data 
$(function(){
    $('select[name="category_id"]').change(function(){
        var id = $('select[name="category_id"] option:selected').val();
        
        $.ajax({
            url: 'Query.php?category_id=' + id,
            success: function(response){
                $('#DataDisplay').html(response);
            }
        });
    });
});


        <?php
#connect to MySQL
$conn = mysql_connect( "localhost","username","pw")
or die( "You did not successfully connect to the DB!" );

#select the specified database-

$db = mysql_SELECT_DB ("DBName", $conn )
or die ( "Error connecting to the database test!");
?>
<form name="sports" id="sports">
<legend>Select a Sport
<select name="category_id">
<option value="">--Select a Sport--</option>
<?php
$sql = "SELECT category_id, sport FROM sports ".
"ORDER BY sport";

$db = mysql_query($sql);

while($row = mysql_fetch_array($db))
{
  echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n  ";
}
?>
</select>

</form>
<br />
<div id="DataDisplay"></div>

Now, lets get on with the Query code that will search the database, and bring back the results when a particular sport is selected.

<?php
#get the id
$id=$_GET["category_id"];

#connect to MySQL
$conn = mysql_connect( "localhost","username","pw")
or die( "Error connecting to MySQL" );

#select the specified database
$db = mysql_SELECT_DB ("DBName", $conn )
or die ( "Could not select that particular Database");

#create the query
$sql ="SELECT *
FROM sports
INNER JOIN players ON sports.category_id = players.category_id
WHERE players.category_id = '".$id."'";


//echo $sql;

#execute the query
$db = mysql_query($sql,$conn);
#start the table code
#table striping
$c = 0;
echo "<table><tr><th>Sport</th><th>First Name</th><th>Last Name</th></tr>";
#write the data
while( $row = mysql_fetch_array( $db) )
{
echo ("<tr>");

echo ("<td>");
echo ($row["sport"]);
echo ("</td>");

echo ("<td>");
echo ($row["first_name"]);
echo ("</td>");

echo ("<td>");
echo ($row["last_name"]);
echo ("</td></tr>");
}
echo "</tr></table>";
mysql_close($conn);
?>

Now all we have to do is make it look purty. Here is the CSS I used to accomplish that.

/* CSS Form and AJAX Display styling */

h1{
color:#000;
text-shadow:1px 1px #ccc;
font-size:24px;
font-family:Arial, Helvetica, sans-serif;
}

p{
color:#666;
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
}

form#sports{
background-color:#e3e3e3;
border:1px solid #ccc;
-moz-border-radius-bottomright: 10px;
-k-webkit-border-radius: 20px;
-moz-border-radius: 20px;
border-radius: 20px;
-webkit-box-shadow: 5px 5px 15px 2px rgba(55, 45, 40, 1);
-moz-box-shadow: 5px 5px 15px 2px rgba(55, 45, 40, 1);
box-shadow: 5px 5px 15px 2px rgba(55, 45, 40, 1);
width:600px;

padding:20px;
height:auto;
}

form legend{
font-family:arial, helvetica, san-serif;
font-weight:bold;
font-size:25px;
color:#999;
text-shadow: 1px 2px 5px #000000;
filter: dropshadow(color=#000000, offx=1, offy=2);
margin-bottom:20px;
}

form select {
width:300px;
padding:10px;
background-color:#fff;
color:#000033;
font-weight:bold;
-webkit-box-shadow: inset 1px 1px 39px 2px rgba(55, 45, 40, 1);
-moz-box-shadow: inset 1px 1px 39px 2px rgba(55, 45, 40, 1);
box-shadow: inset 1px 1px 39px 2px rgba(55, 45, 40, 1);
}

form select option{
background-color:#666;
color:#ccc;
font-weight:bold;
margin:10px 0 10px 0;
}

form select option:focus{
background-color:#036;
color:#fff;
font-weight:bold;
}

/*table striping*/
.odd {background: #333; color:#e3e3e3;}

table{
border:1px solid #333;
background-color:#e3e3e3;
padding:10px;
font-family:arial,helvetica,sans-serif;
font-size:12px;
color:#333;
width:800px;
}

table tr{
border-top:1px solid #ccc;
border-bottom:1px solid #ccc;
border-collapse:collapse;
}

table tr th{
background-color:#666;
color:#ccc;
font-weight:bold;
font-family:arial,helvetica, sans-serif;
padding:20px;
}

table tr td{
padding:20px;
text-align:center;
}

And there you have it! It's quite a bit of code, but pretty simple once you get the hang of it. Check out the final result Here. You may also view and down the code at my Git Hub Account.