Milestone 2: create and populate a database table for baby names 6. Download the
ID: 3865641 • Letter: M
Question
Milestone 2: create and populate a database table for baby names 6. Download the file containing the 1000 most popular baby names in 2014, according to the Social Security Administration office: https://www.ssa.gov/oact/babynames/names.zip. 7. Expand the zip file and look for the file named yob2014.txt. 8. Upload that file to your area on the server. 9. Write a PHP script to: o Connect to the database o Create a table (‘BABYNAMES’) for storing the most popular baby names o Populate the table using the raw content from yob2014.txt. o Display the table’s contents on a web page.
Milestone 3: implement basic functionality 10. Write a PHP script that allows users to vote on their favorite boy/girl baby name. Basically it should: o Display a form containing: a way to select boy or girl, a text input for typing in the name, and a Submit button. o Once the Submit button is pressed, record the user’s input into a table (separate from ‘BABYNAMES’), which you use to keep track of the votes received so far. o Display the most popular names so far (in decreasing order by number of votes). § See https://www.ssa.gov/oact/babynames/ for an example of such table (limited to the top 10 baby names).
Milestone 4: implement enhanced functionality 11. Once the app is fully functional, add AJAX capabilities to it, namely: implement a method to autosuggest / autocomplete popular baby names based on the keystrokes typed by the user so far.
db_connect.php
// Do not change the following two lines.
$teamURL = dirname($_SERVER['PHP_SELF']) . DIRECTORY_SEPARATOR;
$server_root = dirname($_SERVER['PHP_SELF']);
// You will need to require this file on EVERY php file that uses the database.
// Be sure to use $db->close(); at the end of each php file that includes this!
$dbhost = 'localhost'; // Most likely will not need to be changed
$dbname = '';
$dbuser = '';
$dbpass = '';
$db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($db->connect_errno > 0) {
die('Unable to connect to database [' . $db->connect_error . ']');
}
index.php
require_once './php/db_connect.php';
?>
DB Table Test
Database Table Test
// Create table with two columns: id and value
$createStmt = 'CREATE TABLE `TEST` (' . PHP_EOL
. ' `id` int(11) NOT NULL AUTO_INCREMENT,' . PHP_EOL
. ' `value` varchar(50) DEFAULT NULL,' . PHP_EOL
. ' PRIMARY KEY (`id`)' . PHP_EOL
. ') ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;';
?>
Step One Creating the table
if($db->query($createStmt)) {
echo '
Table creation successful.
' . PHP_EOL;
} else {
echo '
Table creation failed: (' . $db->errno . ') ' . $db->error . '
' . PHP_EOL;
exit(); // Prevents the rest of the file from running
}
?>
// Add two rows to the table
$insertStmt = 'INSERT INTO `TEST` (`id`, `value`)' . PHP_EOL
. ' VALUES (NULL, 'Test 1'),' . PHP_EOL
. ' (NULL, 'Lorem Ipsum');';
?>
Step Two Inserting into the table
if($db->query($insertStmt)) {
echo '
Values inserted successfully.
' . PHP_EOL;
} else {
echo '
Value insertion failed: (' . $db->errno . ') ' . $db->error . '
' . PHP_EOL;
exit();
}
?>
// Get the rows from the table
$selectStmt = 'SELECT * FROM `TEST`;';
?>
Step Three Retrieving the rows
$result = $db->query($selectStmt);
if($result->num_rows > 0) {
echo '' . PHP_EOL;
while($row = $result->fetch_assoc()) {
echo '
id: ' . $row["id"] . ' - value: ' . $row["value"] . '
' . PHP_EOL;
}
echo '' . PHP_EOL;
} else {
echo '
No Results
' . PHP_EOL;
}
?>
// Drop the TEST table now that we're done with it
$dropStmt = 'DROP TABLE `TEST`;';
?>
Step Four Dropping the table
if($db->query($dropStmt)) {
echo '
Table drop successful.
' . PHP_EOL;
} else {
echo '
Table drop failed: (' . $db->errno . ') ' . $db->error . '
' . PHP_EOL;
exit();
}
?>
Explanation / Answer
Create new database-
<?php
echo "creating database ";
try {
$dbh = new PDO('sqlite:voting.db');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$dbh->exec('
CREATE TABLE tally (
QID varchar(32) NOT NULL,
AID integer NOT NULL,
votes integer NOT NULL,
PRIMARY KEY (QID,AID))
');
}
catch(PDOException $e) {
echo "ERROR!!: $e";
exit;
}
echo "db created successfully.";
?>
Then design view of the poll-
<form class="webPoll" method="post" action="test.php">
<h4>What question would you like to ask?</h4>
<ul>
<li>Answer Here</li>
<li>Another Answer Here</li>
</ul>
</form>
<
form class="webPoll" method="post" action="test.php">
<h4>What question would you like to ask?</h4>
<ul>
<li>
<label class='poll_active'>
<input type='radio' name='AID' value='0'>
First Answer Here
</label>
</li>
</ul>
</form>
Add fieldset tag to open up some styling options, and of course we need a submit button!
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
<form class="webPoll" method="post" action="/poll/test.php">
<h4>What question would you like to ask?</h4>
<fieldset>
<ul>
<li>
<label class='poll_active'>
<input type='radio' name='AID' value='0'>
First Answer Here
</label>
</li>
</ul>
</fieldset>
<p class="buttons">
<button type="submit" class="vote">Vote!</button>
</p>
</form>
Include one answer view-
<li>
<div class='result'> </div>
<label class='poll_results'>
10%: First Answer Here
</label>
</li>
Then Style the Form Tag
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
form.webPoll {
background:#ededed;
behavior:url(PIE.php);
border:1px solid #bebebe;
-moz-border-radius:8px;
-webkit-border-radius:8px;
border-radius:8px;
-moz-box-shadow:#666 0 2px 3px;
-webkit-box-shadow:#666 0 2px 3px;
box-shadow:#666 0 2px 3px;
margin:10px 0 10px 8px;
padding:6px;
position:relative;
width:246px;
}
<?php
echo "creating database ";
try {
$dbh = new PDO('sqlite:voting.db');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$dbh->exec('
CREATE TABLE tally (
QID varchar(32) NOT NULL,
AID integer NOT NULL,
votes integer NOT NULL,
PRIMARY KEY (QID,AID))
');
}
catch(PDOException $e) {
echo "ERROR!!: $e";
exit;
}
echo "db created successfully.";
?>