Module 03 Course Project - Developer - Building the Website
iliasmalak2x
untitledfolder.zipuntitled folder/.DS_Store
__MACOSX/untitled folder/._.DS_Store
untitled folder/epalka_accounts.pdf
structure of table
Database: test_data, Table: data, Purpose: Table structure
Column Type Null Default CommentsColumn Type Null Default Comments CUST_ID int(11) No FNAME varchar(150) No LNAME varchar(150) No ADDRESS text No CITY varchar(150) No ZIP varchar(150) No COUNTRY varchar(200) No LOGIN varchar(100) No PASSWORD varchar(200) No CC int(11) No CVV int(11) No EXPIRATION datetime No EMAIL varchar(100) No
Page number: 1/3 Oct 18, 2017 at 07:48 AM
structure of table
Database: test_data, Table: data, Purpose: Dumping data
CUST_I D
FNAME LNAME ADDRESS CITY ZIP COUNTRY LOGIN PASSWORD CC CV V
EXPIRATION EMAILCUST_I D
FNAME LNAME ADDRESS CITY ZIP COUNTRY LOGIN PASSWORD CC CV V
EXPIRATION EMAIL
208 Deanna Molina Ap #690-8758 Morbi St. Fort Wayne 56954 United States Molina175 GQC74MUE1SA 647989 175 2016-06-04 18:52:14 [email protected] 207 Xerxes Solis P.O. Box 274, 5915 Amet Road Northumberland L3T 6Y7 Canada Solis402 RAF72TEE1PV 2147483647 402 2016-01-27 01:58:49 [email protected] 206 Roanna Hartman 5203 Sapien. Rd. Jacksonville 91644 United States Hartman423 YEN75ETJ0KK 2147483647 423 2015-11-17 12:50:12 [email protected] 205 McKenzie Murray Ap #241-5962 Pede, Rd. Racine 34707 United States Murray683 MNY00SJS4LP 645412 683 2017-10-06 03:47:40 [email protected] 204 Anne Barrett P.O. Box 897, 7182 Nunc Rd. Lang M0R 2C0 Canada Barrett931 KTD35ELE7DH 519 931 2014-04-29 07:38:18 [email protected] 203 Melodie Travis P.O. Box 710, 1541 Facilisis Ave Charlottetown B8R 3E2 Canada Travis281 ZBK62PDV6MU 552 281 2016-10-20 10:52:45 [email protected] 202 Melissa Humphrey 170 In, St. Sacramento 77367 United States Humphrey829 BDP38ZRJ1UB 4929 829 2014-05-03 00:15:37 [email protected] 201 Lysandra Pace 924-9284 Odio, Road Tallahassee 32589 United States Pace564 WII15AYT3KC 3474 564 2015-08-25 21:43:05 [email protected] 200 Micah Riley 706-4848 Vel Road West Jordan 67860 United States Riley496 TUW91AMJ0PV 402 496 2015-01-15 21:36:58 [email protected] 199 Jessamine Hatfield 282-4274 Molestie Rd. Rockford 72683 United States Hatfield529 VTV86HZJ0NX 4485 529 2016-08-05 18:38:39 [email protected] 198 Declan Nguyen 9685 Quis Avenue Regina R0N 1E0 Canada Nguyen130 FPC28LBF2DH 539308 130 2014-09-25 11:23:56 [email protected] 197 Sean Mcfadden P.O. Box 976, 8730 Quisque Ave Winnipeg V6X 6P9 Canada Mcfadden709 MMJ47EBC8AH 6468 709 2014-08-31 12:35:00 [email protected] 196 Rajah Talley 394-3613 Senectus Avenue Burin T4K 3X9 Canada Talley413 JYV08KQO5AW 6498 413 2016-02-25 03:29:01 [email protected] 195 Carla Pugh Ap #204-730 Tristique St. Gibbons N0B 9N3 Canada Pugh788 SSV69GBF3JK 644 788 2014-10-22 03:00:11 [email protected] 194 Blythe Martin P.O. Box 170, 3582 Convallis Avenue Fortune T2H 3H2 Canada Martin338 GBJ50FYG1SA 648802 338 2016-04-23 17:44:55 [email protected] 193 Christian Weaver Ap #980-2677 Taciti Avenue Yorkton C2N 6W4 Canada Weaver466 UUC38URQ8LD 2147483647 466 2016-06-30 04:24:45 [email protected] 192 Fritz Harris P.O. Box 802, 5540 Ornare, St. Racine 71932 United States Harris572 ZFI78XMH1QC 2147483647 572 2015-12-02 11:14:41 [email protected] 191 Drew Mckay Ap #449-9078 Nec Av. Cincinnati 45789 United States Mckay758 CPH79GOG7IG 3455 758 2016-01-09 11:59:59 [email protected] 190 Amena Nielsen 103-790 Magnis Street Fort McPherson K2S 2G4 Canada Nielsen245 GWA76ROW6OM 3781 245 2015-10-13 04:49:56 [email protected] 189 Wang Crosby P.O. Box 989, 9144 Ante, Rd. Orilla M7Z 5B4 Canada Crosby712 BEI75STT0TM 2147483647 712 2017-04-27 19:02:27 [email protected] 188 Dean Patel Ap #601-7607 Adipiscing Rd. Quispamsis V9B 3J5 Canada Patel263 VYH55SQF0MJ 2147483647 263 2015-04-16 22:58:33 [email protected] 187 Darryl Mccarthy 742-2036 Quisque Ave Tulita X1Y 6E3 Canada Mccarthy470 DUQ96UNQ6FR 2147483647 470 2015-11-08 20:42:21 [email protected] 186 Nigel Cooper 9790 Orci Rd. Ketchikan 46227 United States Cooper223 OHB48WKI3GD 2147483647 223 2014-07-04 18:39:46 [email protected] 185 Gary Francis 1083 Cubilia St. Ajax Y7X 2K2 Canada Francis570 ZWD26MUZ1UV 644 570 2017-02-01 23:16:07 [email protected] 184 Brett Wallace P.O. Box 757, 5399 Inceptos Street Watson Lake H9W 0P9 Canada Wallace443 YYJ92PGD0CT 3468 443 2017-05-13 15:19:29 [email protected] 183 Jackson Sandoval 212-5259 Eu, Ave Memphis 82008 United States Sandoval603 KVK96QYW9WP 556902 603 2017-10-18 00:34:16 [email protected] 182 Blake Hunter Ap #131-6380 Tempus, Av. Glovertown T2R 5C2 Canada Hunter489 TVK77WAT7JG 2147483647 489 2015-11-12 19:17:16 [email protected] 181 Gretchen Cervantes P.O. Box 491, 6482 Mauris St. Colchester 51824 United States Cervantes624 ION26EZB4YW 2147483647 624 2017-12-16 07:12:08 [email protected] 180 Signe Doyle 402-8947 Libero Rd. Windsor V9X 1Y6 Canada Doyle470 IFE80DAG2ZN 527900 470 2014-12-18 09:58:21 [email protected] 179 Rebecca Garrison Ap #537-8136 Montes, St. Lourdes T9G 6S1 Canada Garrison425 RHZ69ZSR8AE 2147483647 425 2014-03-31 08:05:33 [email protected] 178 Ariel Nash 636-5187 Molestie Rd. Hamilton J6A 6P3 Canada Nash970 NPO70DWJ4XN 649014 970 2014-12-02 05:35:49 [email protected] 177 Petra Mcgee P.O. Box 532, 1164 Ornare Road Colorado Springs 91303 United States Mcgee317 SFA11NGV2RD 540 317 2017-02-23 11:45:38 [email protected] 176 Carson Baldwin P.O. Box 996, 9393 Euismod Avenue Nakusp G3E 2A1 Canada Baldwin906 RVS16DDU8ST 4716 906 2017-05-20 08:01:05 [email protected] 175 Amery Hicks 119-7903 Metus. Ave Deline M5T 9J3 Canada Hicks213 ELV90BCE0TA 3744 213 2016-09-10 14:05:27 [email protected] 174 Kelly Hogan 965-5317 Ut St. Flint 12919 United States Hogan825 JTD55SJU1HW 2147483647 825 2016-06-03 04:42:27 [email protected] 173 Isaac Cline 7850 Magna Road Minitonas N3Z 0L2 Canada Cline172 YRT02GZD6NE 2147483647 172 2014-07-30 10:21:48 [email protected] 172 Madonna Conway 3488 Lorem. St. Prince Albert H0M 4L3 Canada Conway757 CPI70DEM3CQ 3485 757 2015-02-21 19:04:06 [email protected] 171 Taylor Potter 935-4951 A Avenue Paradise R3Y 4B4 Canada Potter964 KBF80KSD0BV 2147483647 964 2014-04-29 04:12:21 [email protected] 170 Emmanuel Mcclure P.O. Box 412, 7923 Orci. St. C�te-Saint-Luc T5S 1R2 Canada Mcclure542 NBX86AHX4GG 5266 542 2016-03-14 22:19:04 [email protected] 169 Alec Stark 320-4618 Vel, Road Cariboo Regional District R7A 2M2 Canada Stark868 TYM95KDN8JM 512 868 2015-06-10 22:57:22 [email protected] 168 Tucker Hampton P.O. Box 314, 5427 Integer St. Indianapolis 63758 United States Hampton711 KWA91GQJ9BK 558 711 2017-08-21 14:56:32 [email protected] 167 Zahir Kemp Ap #847-9102 Maecenas Rd. South Portland 42583 United States Kemp970 WWT12AHK0VR 647225 970 2016-10-28 04:35:35 [email protected] 166 Wyatt Harding 979-8283 Proin St. McCallum H6J 2H7 Canada Harding697 FAQ51OEP1EW 553 697 2015-10-03 05:23:42 [email protected] 165 Ruth Olsen Ap #926-7669 Gravida St. Kaneohe 38443 United States Olsen396 HSA74NOD2OJ 4539 396 2015-02-22 05:09:48 [email protected] 164 Zena Mclean P.O. Box 908, 7900 Luctus Rd. Springfield 13663 United States Mclean847 UXF24KKW9NA 5515 847 2017-05-15 14:41:01 [email protected] 163 Joel Mullen 8664 Sociis Street Virginia Beach 62809 United States Mullen907 XVO91FOS4OO 2147483647 907 2017-03-31 03:28:28 [email protected] 162 Amethyst Booker 869-4281 Elit Avenue Sparwood G2K 2R8 Canada Booker788 DAL85SEY2EF 5180 788 2015-06-25 05:36:28 [email protected] 161 Quail Griffin 541-9852 Commodo Street Estevan X6T 2L8 Canada Griffin816 TMQ27BTU5WS 513249 816 2015-12-27 05:45:59 [email protected] 160 Jescie Jennings Ap #812-1731 Magna. Street Leamington Y8A 9A7 Canada Jennings582 GOW71NKZ6JP 548719 582 2016-12-25 18:23:40 [email protected] 159 Tanek Cochran P.O. Box 192, 2606 Sed St. Oklahoma City 10786 United States Cochran752 FCI13WYW0OD 3408 752 2017-10-19 20:00:41 [email protected] 158 Pascale Bryan P.O. Box 726, 6529 Morbi Rd. Portland 73307 United States Bryan117 OKT85HPO5QS 529 117 2016-10-03 23:08:51 [email protected] 157 Chaim Summers P.O. Box 113, 236 Arcu. Avenue Clarksville 72767 United States Summers318 HTS53JDY8NM 2147483647 318 2017-04-03 20:47:00 [email protected] 156 Herman Rocha 417-8267 A, Av. Rochester 64235 United States Rocha605 GXV91NTD0YH 552 605 2015-07-02 13:50:35 [email protected] 155 Armando Malone 8730 Mus. Ave Billings 23249 United States Malone877 GFS47NIJ6NH 3775 877 2017-01-01 02:00:08 [email protected] 154 Maya Cardenas Ap #910-1754 Eu Avenue Philadelphia 74958 United States Cardenas374 XGK68MDW7VD 5177 374 2015-09-26 13:15:54 [email protected] 153 Quynn Compton P.O. Box 631, 5325 Aenean Rd. Augusta 93467 United States Compton695 CYC00TFA8AT 2147483647 695 2015-02-19 00:36:42 [email protected] 152 Cecilia Carter 8503 Nec, Rd. Fort St. John B6G 5B9 Canada Carter899 MGB56KJC4RJ 3437 899 2017-01-17 09:04:26 [email protected] 151 Marah Cross 621-1847 Est St. Toronto J8S 6R7 Canada Cross152 BPG52PWZ1KB 3755 152 2015-07-03 00:02:13 [email protected] 150 Colton Pate 216-2401 Quis, Ave Kapolei 35070 United States Pate281 LBF53UEJ3VC 5596 281 2014-09-18 18:55:59 [email protected] 149 Yardley Hanson 5666 Etiam Rd. Chicago 70633 United States Hanson587 KZJ52JSK5OC 6498 587 2016-05-18 03:02:25 [email protected] 148 Sonia Meyers P.O. Box 983, 8334 Cras Ave Sandy 12218 United States Meyers265 ETA13OLQ9TW 2147483647 265 2015-10-08 20:55:47 [email protected] 147 Wyoming Woodard 310-7254 Vitae, Rd. Evansville 39038 United States Woodard306 DQL57KZR6WQ 2147483647 306 2015-09-25 05:05:40 dignissim.Maecenas.ornare@imperdietdictummagna
.net 146 Erich Fletcher Ap #630-7911 Donec Ave Paradise B2P 0J3 Canada Fletcher509 TWS75IAE8LU 2147483647 509 2016-02-17 15:24:09 [email protected] 145 Marshall Fitzgerald 5626 Vestibulum. Av. Winnipeg X0L 8T6 Canada Fitzgerald743 SHP43LQZ0TD 3777 743 2014-11-28 17:54:45 [email protected] 144 Ciaran Hawkins 2945 Dolor. Street Arviat X2P 3J0 Canada Hawkins829 EWK77DMW3RP 6474 829 2016-10-15 00:21:33 [email protected] 143 Carol Bond 494-4670 Phasellus St. Gresham 48831 United States Bond889 VBL09EWF9NV 5489 889 2017-07-30 21:22:15 [email protected] 142 Athena Mercer 247-4809 Eu Ave Portland 77825 United States Mercer776 SID61YQA4WY 2147483647 776 2015-07-05 00:59:54 [email protected] 141 Nissim Barron Ap #513-6132 Quisque St. Flin Flon R0M 2Y0 Canada Barron147 DEN61KFC1HR 2147483647 147 2015-11-18 21:21:37 [email protected] number: 2/3 Oct 18, 2017 at 07:48 AM
structure of table
Database: test_data, Table: data, Purpose: Dumping data
CUST_I D
FNAME LNAME ADDRESS CITY ZIP COUNTRY LOGIN PASSWORD CC CV V
EXPIRATION EMAILCUST_I D
FNAME LNAME ADDRESS CITY ZIP COUNTRY LOGIN PASSWORD CC CV V
EXPIRATION EMAIL
140 Jin Haney 8711 Scelerisque Avenue Fresno 56347 United States Haney650 YEK98MNL6NX 2147483647 650 2015-09-06 00:16:49 [email protected] 139 Chaney Roberson P.O. Box 261, 4587 Fringilla Road Watson Lake J8L 7E1 Canada Roberson644 XYL26GWS4YK 2147483647 644 2017-01-25 01:11:56 [email protected] 138 Eugenia Parsons 631-8101 Ut Rd. Phoenix 26150 United States Parsons975 FVK81CXS8CP 655094 975 2014-11-24 21:58:16 [email protected] 137 Aaron Callahan Ap #927-5488 Lorem Rd. Fort Good Hope X5T 1R4 Canada Callahan803 SXN22BOP2OW 3776 803 2014-11-10 17:15:33 [email protected] 136 Veronica Day 1577 Ante Road Colorado Springs 28218 United States Day743 AMT80ZZZ9NT 2147483647 743 2014-10-20 16:17:28 [email protected] 135 Beck Brennan P.O. Box 359, 806 Ante. Ave Green Bay 11006 United States Brennan901 WCH31CDJ4RI 5545 901 2017-09-14 13:19:07 [email protected] 134 Beau Mcintyre 620-4523 Ipsum Rd. Columbia 35861 United States Mcintyre476 XXN60PYS7MS 2147483647 476 2015-10-31 14:08:19 [email protected] 133 Harrison Mayer P.O. Box 609, 3382 Fusce Rd. New Maryland A1K 6N6 Canada Mayer460 MFZ06APJ7WJ 2147483647 460 2014-04-01 06:19:56 [email protected] 132 Stella Fischer 6859 Dis St. Town of Yarmouth J1Y 8Z7 Canada Fischer482 JJA54ZKJ5EM 550951 482 2016-03-06 10:31:18 [email protected] 131 Blythe Hampton 249-6542 Nam Avenue Watson Lake X8Z 3L1 Canada Hampton642 ZXE41SAV2YH 2147483647 642 2016-12-21 17:06:31 [email protected] 130 Demetrius Manning P.O. Box 197, 6050 Dolor Road Iowa City 19386 United States Manning853 XOL18DVX2ZG 2147483647 853 2017-05-05 03:54:14 [email protected] 129 Thaddeus Vega P.O. Box 283, 5770 Pellentesque Rd. Dieppe P6A 3G5 Canada Vega665 RSE00PJM4DB 521044 665 2015-07-30 17:59:14 [email protected] 128 Cameron Gomez 3619 Pede. Road Chelsea M5S 6P2 Canada Gomez126 HYY77HXO0KI 3456 126 2015-01-31 04:35:07 [email protected] 127 Bree Richmond P.O. Box 744, 9818 Vestibulum Road Cedar Rapids 85731 United States Richmond479 CFX90ZPT4KL 527859 479 2017-01-14 06:55:42 [email protected] 126 Hunter Ayala 1380 Class Road Lafayette 45749 United States Ayala332 LCH33QZY3KG 5418 332 2017-07-09 07:34:26 [email protected] 125 Ifeoma Kemp 9161 Semper St. Cambridge Bay T8C 7P5 Canada Kemp150 SIM95JAG7MR 548551 150 2015-10-13 14:43:59 [email protected] 124 Hyatt Orr Ap #731-2636 Enim Rd. Paradise A5A 2C8 Canada Orr732 VFW36PNN3VS 2147483647 732 2016-10-11 19:13:44 [email protected] 123 Noel Andrews 759-8693 Orci. Street Winnipeg X3C 1M4 Canada Andrews760 FGR45JCR8ZN 6011 760 2015-08-23 15:03:47 [email protected] 122 Doris Hoffman 230-4136 Integer Av. Rutland 94827 United States Hoffman801 JBW42ZPK4FP 2147483647 801 2016-09-10 12:55:59 [email protected] 121 Patricia Montoya Ap #117-4391 Nisi Road Whitehorse N2M 6P1 Canada Montoya598 ZSW17IFX6ZG 2147483647 598 2016-05-05 19:26:25 [email protected] 120 Rhonda Benton 775-8576 Ut, Av. Pangnirtung T1M 3K8 Canada Benton930 DVI75CFE1LT 601 930 2014-08-17 11:06:15 [email protected] 119 Connor Silva P.O. Box 187, 8311 Non St. Whitehorse G0N 6G8 Canada Silva656 CPY28CWL9VN 2147483647 656 2014-12-30 22:54:56 [email protected] 118 Karyn Rojas 625-9806 Mi Av. Frederick 60688 United States Rojas849 MNU05DDO9CO 2147483647 849 2015-04-07 06:10:44 [email protected] 117 Byron Sykes Ap #753-7214 Aenean Street Cape Breton Island H9C 0C4 Canada Sykes491 UMV71FHD8DA 2147483647 491 2014-10-16 15:30:50 [email protected] 116 Knox Christensen Ap #200-261 Arcu St. Pugwash P9C 0P9 Canada Christensen692 QUQ30VMW3CO 645475 692 2014-04-25 20:25:11 [email protected] 115 Rana Mathis Ap #374-6576 Ipsum. St. New Maryland J5W 4J1 Canada Mathis381 UDX36TIT3XP 541 381 2017-09-19 03:08:00 [email protected] 114 Bree Mcbride P.O. Box 611, 2151 Massa. Av. South Burlington 85800 United States Mcbride528 JWK34TJF8FP 2147483647 528 2016-11-22 11:24:38 [email protected] 113 Rajah Alvarado P.O. Box 442, 5875 Augue, Avenue West Valley City 53305 United States Alvarado935 WAP18BWZ8HC 4024 935 2015-01-09 05:25:12 [email protected] 112 Danielle Swanson 206-169 Erat. Rd. Assiniboia R7K 5G7 Canada Swanson802 AJP63KGB7LG 2147483647 802 2017-12-21 14:32:34 [email protected] 111 Rogan Strong Ap #378-3567 Dui. Street Hartford 10209 United States Strong939 DIB20ROH7ZL 646 939 2015-01-28 18:26:47 [email protected] 13 Kaye Armstrong 7618 Tempor Avenue Fort Laird J3W 0L9 Canada Armstrong743 BXT58SGO9FU 645747 743 2017-08-25 13:50:25 [email protected] 1 Samuel Wright 657-1272 Phasellus Av. Shippagan C6B 9A6 Canada Wright606 DJL64VYQ2AZ 2147483647 606 2015-08-22 19:54:55 [email protected]
Powered by TCPDF (www.tcpdf.org) Page number: 3/3 Oct 18, 2017 at 07:48 AM
__MACOSX/untitled folder/._epalka_accounts.pdf
untitled folder/Form.png
__MACOSX/untitled folder/._Form.png
untitled folder/index.php
Search Your name with Sur Name
SurName: connect($host, $user, $password); if($objdB->connect_errno){ echo("Failed to connect"); } if($objdB->connect_error){ echo("An Error Find."); } $objdB->select_db($database); if($objdB->errno){ echo("Failed to select."); } if(isset($_GET['search'])){ $search_Value= mysqli_real_escape_string($objdB,$_GET['search']); $query_select="SELECT * FROM data WHERE LNAME='$search_Value'"; $result=$objdB->query($query_select); if(mysqli_num_rows($result)==0){ echo("No record found."); } if(!$result){ die('Invalid Query:'. mysqli_error()); } while($row=mysqli_fetch_array($result)){ $FNAME=$row['FNAME']; $LNAME=$row['LNAME']; $ADDRESS=$row['ADDRESS']; $CITY=$row['CITY']; $ZIP=$row['ZIP']; $COUNTRY=$row['COUNTRY']; $LOGIN=$row['LOGIN']; $CC=$row['CC']; $CVV=$row['CVV']; $EXPIRATION=$row['EXPIRATION']; $EMAIL=$row['EMAIL']; echo "| First Name | Last Name | Address | City | Zip | Country | Login |
|---|
| $FNAME | $LNAME | $ADDRESS | $CITY | $ZIP | $COUNTRY | $LOGIN |
__MACOSX/untitled folder/._index.php
untitled folder/Module_03_Lab01_Worksheet_1_Updated-4.docx
Module 03 Lab Worksheet
Web Development Using LAMP
Lab Activities:
· Create a database in MySQL
· Import data into MySQL
· Access a MySQL database using SQL
· Connect to a MySQL database using PHP
· Integrate SQL query results into a Web page
· Run an SQL query from a Web form
Introduction
Download the sample data file TestData.csv.zip and unzip it.
|
Note that the file is in plain ASCII, with Unix line endings and uses the pipe symbol (|) as a field delimiter. You’ll need to know this when you import it into your database.
Over the course of this worksheet I’ll be asking you questions about MySQL commands. You can find documentation at http://dev.mysql.com . I encourage you to use this to answer the worksheet questions. You can also get some of your answers from PHPMyAdmin itself as we work through the exercise but this will help you get more familiar with the SQL language.
|
Evaluate Your Data
Open the sample data file in a spreadsheet program and examine the data. In the following table, list the names, description and data types for each field.
Create Database
Before you import unformatted data into MySQL, you have to have a place to put it.
You can import data in three ways:
· Use a compatible table of an existing database
· Create a new table in an existing database either manually or by importing.
· Create a new database with a table that fits the incoming data.
Log into PHPMyAdmin.
Create a new database called dbtest. What SQL command would you use?
Since it’s not a good idea to use the MySQL administrator account root for everything, we’ll create a new MySQL user that will be managing this new database.
Create a new user with a user name consisting of your first initial followed by your last name. For example, Ellie Palka would create a user named epalka.
This will be a user for localhost only and has no access to any databases. The password will be the same as the username (we can change it later).
In other words, the full user name for Edith Palka would look like epalka@localhost with a password of epalka. (Remember to substitute your own first initial/last name for the user ID.)
What is your user name?
What was the SQL command you would use to create this user?
|
A user with an easily-guessed password is insecure but this is only for testing purposes and we can change the password later. |
Now give the user you just created administrative access to the database dbtest. That is, they should have full control over the database dbtest and no others. What SQL command would do that?
Confirm that your user has full access to dbtest. How would you show this with an SQL command without logging in as that user?
Run that command, if you haven’t already. What was the output?
Log out and log back in as your new user to confirm that they can administer dbtest and nothing else. If this works, continue with this lab as your new user.
Importing Data
Sometimes the data file you are importing is formatted in a way that isn’t quite right for your project. This happens quite frequently when you are getting your data from a third party. The process of fixing it is called data cleansing.
Data cleansing can be a complex job and there are commercial applications designed for just that purpose. In fact, there are entire companies that provide data cleansing services.
In our case we have to
· replace the pipe (|) delimiters in our file with commas
· enclose our data fields with quotes
· add a column to use as our primary key.
This can all be done by hand, of course but we’d prefer to have the computer do the grunt work for us.
Our cleaning job is pretty simple so we might be able to do it with the tools we already have.
Cleaning Our Data
Upload TestData.csv to your workspace.
Using Find->Replace, replace the pipe symbols (|) with a comma enclosed in double quotes. (“,”)
That’s most of the work. Now we just have to put a double quote (“) on the beginning and end of each line and add a field for our data key. You can do it by hand but it’s better to automate the job.
The text editor isn’t flexible enough to let us do this but we have other tools at our disposal.
At the bottom of your workspace is a terminal window with a command prompt. This lets you access many powerful text-processing tools that will make your work easier.
|
Every modern operating system has a set of command line utilities that allow you to do things that are difficult or even impossible to do in the GUI. If you are an IT professional, or plan to become one, you should familiarize yourself with these tools. |
Click your mouse into the window to make it active.
|
If the window is not available, right-click on your DevBox and select SSH Terminal, then click your mouse inside the window. |
Type ls at the prompt and press <Enter> to see a list of the files in your workspace.
To add a double quote to the beginning and end of each line, we will use a utility called sed, which is short for ‘stream editor’. It’s very useful for performing monotonous tasks like this.
At the command prompt, type:
sed 's/^.*$/"&"/g' TestData.csv > TestData01.csv
and press <Enter>.
|
Normally sed will print its output to the screen. To save it to a file instead (like TestData01.csv) we need to use the > symbol to redirect the output. |
Right-click on your DevBox and select Refresh.
A new file, TestData01.csv should now be in your File Explorer. Note that the original file is still available so if we mess up we can easily go back and re-do it.
Click on TestData01.csv to open it and make sure that it’s properly formatted before continuing.
Adding a Primary Key Field
|
Remember, a primary key has two major properties: 1. It must be unique 2. It cannot be null (it should always have a value).
When choosing a primary key, you should consider: · It should be simple and familiar. · The value shouldn’t change. · For efficiency, an integer value is preferable. · It is usually a single column but you can use multiple columns. In any case, use as few columns as possible.
A popular strategy is to create a new column with an unrelated, unique number and auto-increment it with each new record. That’s what we are going to do. |
We're going to create a column labelled CUST_ID that consists of 1, 2, 3 and so on for each row.
.
First we need to figure out how many records we actually have. At the command line, enter:
wc -l TestData01.csv
The wc command is normally used to get a word count. The -l option tells wc to count lines instead.
(We can also count them in our text editor by scrolling down and looking at the last line number.)
What number is returned by this command?
Given that the first line consists of our field labels, how many records do we actually have?
We're going to do this in two steps:
1. Create a text file with the column data we need.
2. Join this file to TestData01.csv.
Create the Column Data
In your workspace, create a new file called ids.txt.
Select the file to open it in your text editor.
In the file add the single line at the top:
"CUST_ID",
Don't forget the end comma and press <Enter> at the end of the line.
Save your changes before continuing.
Adding our Number Data
Now we need to add the numbers. We know that we will need 1 through 100 and they will have to be double-quoted with a comma on the end. In other words, each line will look something like this:
"2",
|
A good way to get a computer to do something repetitive is to use a loop. Normally these are found in program code but we can also run them at the command line. |
At the terminal, run the following command:
for ((a=1;a <= 100;a++)); do echo \"$a\", ; done >> ids.txt
|
This looks complicated but let's break it down. a is the label for the number that we're going to print and we want it to start at 1:
a=1
and end at 100. So we test to see if a is still less than or equal to 100 each time we run our loop:
a <= 100
Each time we run the loop, we add 1 to a:
a++
During our loop we run one command:
echo \"$a\",
The $ tells echo that we don't want the letter 'a', we want the current number with the label a.
A double-quote usually means something special to the echo command and they're not normally printed out. The backslashes (\) tell echo to treat the double-quotes like any other character and just print them.
Normally echo prints to the screen but we want to add the output to our file ids.txt instead.
>> ids.txt
The double-arrows (>>) tell our terminal to append the output to the end of our file instead of replacing the contents of the file. |
Close and reopen the ids.txt file in your text editor and you should see the changes.
Putting It All Together
The last thing we need to do is to append ids.txt to TestData01.csv.
This is actually pretty easy. In the terminal, run the command:
paste -d '' ids.txt TestData01.csv > TestData02.csv
Now when you refresh your workspace, you'll see the TestData02.csv file.
Open it in your editor to make sure that all of the changes we need have been completed.
Download TestData02.csv and rename it accounts.csv.
NOTE: You could do all of this by hand or in a spreadsheet program.
BUT:
|
Sometimes the spreadsheet will modify your data when you open the file because it wrongly guesses the datatype. For example, it might convert a credit card number into scientific notation.
MS Excel in particular is notorious for this.
This can corrupt your data so make sure to double-check before importing into your database!
I’ve experimented with several spreadsheet programs and so far have found that LibreOffice (free for Windows, Linux and Mac OS X) and Numbers (free for Mac OS X) preserve the formatting of the text fields.
Since this test data is just 100 records, it can be edited by hand.
However, the project data file is 1000 records so it’s worth the effort to figure out what tools work to automate the process. |
Now we can finally import our data.
Log into PHPMyAdmin and click on the dbtest database you created.
Click on the Import tab.
In File to Import, click on the Browse button and select accounts.csv.
Since we want the first line (the header columns) to be the names of our fields, click on the check box labelled “The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)”.
Click on Go.
If you get an error, read it carefully to see what part of the data file was a problem. Once you fix it, try to import it again.
If everything goes well, you should receive a success message.
How many records were added?
What SQL command would you use to import your data file?
Click on the new table name and examine it to make sure it has all of the data we need.
We need to tell MySQL that CUST_ID is our primary key.
In the table view, select the Structure tab.
In the row containing CUST_ID, click on Primary (the golden key icon).
Confirm that you want to make CUST_ID your primary key. (The key icon should change from gold to grey.)
Note that the table has the default name of TABLE 1. We should rename it to make it less generic.
With the table still selected, click on the More tab and select Operations.
In the Table operations page, go to the Table options section and rename the table to accounts. Click the Go button to submit your change.
Access Database Using SQL and PHP
Now we have our database and have run some SQL statements against it in PHPMyAdmin. But our real goal is to integrate our database with our Web page. We can do this with any programming language understood by our Web server but since we have PHP installed we’ll use that.
Let’s break this task down into pieces:
· Crafting the SQL query.
· Passing the query via PHP and sending the output to a Web page.
· Create a Web page with a button linked to our query.
Crafting our Query
It’s important to make sure your query works as expected before you plug it into a PHP statement. This minimizes the work required to debug your code. PHPMyAdmin allows you to enter SQL directly and will advise you if you make a mistake. Let’s keep things simple for now with a query that will search for the first and last names of all of the customers in our table.
SELECT FNAME,LNAME FROM `accounts`;
Go ahead and run this statement in the SQL tab of PHPMyAdmin and make sure it works as expected. If it does, we can move on to the next phase.
Passing Our Query to PHP
First we need to tell PHP how to open a connection to our database before it can send a query. Create a new folder in your workspace called test to hold your test scripts. Add a file to this folder called php_dbtest.php with the following content in the body:
<!doctype html>
<html lang="en">
<head>
<title>PHP Data Connection Test</title>
</head>
<body>
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
'$conn = new mysqli($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
</body>
</html>
(Don't forget to save your changes before continuing.)
|
Remember to change username and password to the user and password you created for your dbtest database before saving. |
Open a new page in your Web browser and point it to your DevBox home page. Click on the link for the file you just created.
You should get the message ‘Connected successfully’. If you didn’t, check your code and try again until you do before continuing.
Your connection to the database will remain open as long as the script is running. If you want to close it beforehand, simply use the expression:
$conn->close();
So now we know how to connect to our server, let’s connect specifically to our database dbtest. Edit your PHP test page and add the following after the code that opens the database connection:
In the section where we set the server and user information, add the line:
$dbname = "dbtest";
In the section where we create our connection, modify the statement
$conn = new mysqli($servername, $username, $password);
to read
$conn = new mysqli($servername, $username, $password, $dbname);
|
The original connection was to the database server. The new connection is to a specific database on that server. |
Save the changes, reload the Web page and make sure you get the success message before continuing.
Now that we can connect to a specific database on our server, it’s time to send a query.
Edit your Web page to add the following to the section after we create our connection:
$sql = "SELECT FNAME,LNAME FROM `accounts`";
$result = $conn->query($sql);
This loads our query into the variable sql which then is sent to the server via the connection we just opened. The output of our query is stored in the object named $result.
We’re not done yet. Now that we have a result, let’s do something with it. First let’s just see how many records were returned.
if ($result->num_rows > 0) {
echo "<br>$result->num_rows results returned.";
} else {
echo "0 results";
}
This if statement will be very, very useful for us. Basically it says “If more than one record matched the query, do something with it, otherwise tell me that nothing happened.” All we have to do is figure out what that “something” is. The <br> tag just puts in a line break between the successful connection message and our output.
|
Remember any printed output from this script will be interpreted as HTML so remember to include appropriate tags to format your text. |
Save your changes and refresh the Web page.
How many results were returned?
Take a few moments to experiment with other HTML tags on your output before continuing, like changing the size and style.
Now that we know our query was passed and executed successfully, let’s print out the results. This time instead of echo, we’ll use the printf ( formatted print) function. In addition, we’d like to print out the name in standard <last name>,<first name> format.
Modify your if statement to read:
if ($result->num_rows > 0) {
//output data of each row
while ($row = $result->fetch_assoc()) {
printf ("%s, %s<br>", $row["LNAME"], $row["FNAME"]);
}
if ($result->num_rows = 0) {
echo "0 results <br";
}
}
Let’s break down what’s happening here, line-by-line.
while ($row = $result->fetch_assoc()) {
Remember, result is an object that contains the...well, results of our query. The method fetch_assoc() dumps the records into an associative array called row. The while loop goes through each record until there are no more records to process.
|
Unlike a traditional array, which uses integers as indexes (ex. a[0],a[1]...), an associative array uses strings. In this case the indexes are the field names (row[FNAME], row[LNAME], row[ZIP], etc.)
Be aware that field names are case-sensitive. |
printf ("%s, %s<br>", $row["LNAME"], $row["NAME"]);
The printf function has two parts - the first describes the desired print format and what data types are going to be printed and the second part lists the variables to be printed. Some common data type notations you can use:
|
%s |
String |
|
%c |
Character |
|
%d |
decimal (base 10) |
|
%o |
octal (base 8) |
|
%x |
hexadecimal (base 16 |
|
%f |
floating point |
Think of it this way -- you’re sending printf a string of 1’s and 0’s and the data type tells it how to interpret them.
So now we can use PHP to run a query on a database and output the results to a Web page. For our last step, let’s set up a button on our Web page to run that query when clicked. In other words, we’re going to create an HTML form.
For what we need to do, creating a page with a form is pretty simple. All we need is a page with a single button which, when pressed, runs our query and prints the results.
Create a new HTML page called dbform_test.html with the following content and save it in your test folder:
<body>
<form action="php_dbtest.php">
<button type="submit">Run SQL Query</button>
</form>
</body>
</html>
Button, Button, Where’s that Darn Button?
Go back to your DevBox home page and click the link for dbform_test.html. (You may have to refresh the page.)
What do you see?
Click the button. What happens?
Final Task: Create a Web Search Page
By now you should know enough to create a simple search form to run a query on a database.
· Create a form that will let the user input a surname and when the search runs, it will output all users with that surname from the accounts table.
· Submit your HTML and PHP code files with this worksheet.
· Export the accounts table as a PDF, name it <userid>_accounts.pdf and submit it with this worksheet. For example, if your username is epalka, then you would submit a PDF named epalka_accounts.pdf.
You can submit all of these files as a single Zip file if you prefer.
2
__MACOSX/untitled folder/._Module_03_Lab01_Worksheet_1_Updated-4.docx
untitled folder/PHP_Project-2/.DS_Store
__MACOSX/untitled folder/PHP_Project-2/._.DS_Store
untitled folder/PHP_Project-2/DataBase/test_data.sql
-- phpMyAdmin SQL Dump -- version 4.5.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Oct 18, 2017 at 07:56 AM -- Server version: 10.1.13-MariaDB -- PHP Version: 5.6.21 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `test_data` -- -- -------------------------------------------------------- -- -- Table structure for table `data` -- CREATE TABLE `data` ( `CUST_ID` int(11) UNSIGNED NOT NULL, `FNAME` varchar(150) NOT NULL, `LNAME` varchar(150) NOT NULL, `ADDRESS` text NOT NULL, `CITY` varchar(150) NOT NULL, `ZIP` varchar(150) NOT NULL, `COUNTRY` varchar(200) NOT NULL, `LOGIN` varchar(100) NOT NULL, `PASSWORD` varchar(200) NOT NULL, `CC` int(11) NOT NULL, `CVV` int(11) NOT NULL, `EXPIRATION` datetime NOT NULL, `EMAIL` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `data` -- INSERT INTO `data` (`CUST_ID`, `FNAME`, `LNAME`, `ADDRESS`, `CITY`, `ZIP`, `COUNTRY`, `LOGIN`, `PASSWORD`, `CC`, `CVV`, `EXPIRATION`, `EMAIL`) VALUES (1, 'Samuel', 'Wright', '657-1272 Phasellus Av.', 'Shippagan', 'C6B 9A6', 'Canada', 'Wright606', 'DJL64VYQ2AZ', 2147483647, 606, '2015-08-22 19:54:55', '[email protected]'), (13, 'Kaye', 'Armstrong', '7618 Tempor Avenue', 'Fort Laird', 'J3W 0L9', 'Canada', 'Armstrong743', 'BXT58SGO9FU', 645747, 743, '2017-08-25 13:50:25', '[email protected]'), (111, 'Rogan', 'Strong', 'Ap #378-3567 Dui. Street', 'Hartford', '10209', 'United States', 'Strong939', 'DIB20ROH7ZL', 646, 939, '2015-01-28 18:26:47', '[email protected]'), (112, 'Danielle', 'Swanson', '206-169 Erat. Rd.', 'Assiniboia', 'R7K 5G7', 'Canada', 'Swanson802', 'AJP63KGB7LG', 2147483647, 802, '2017-12-21 14:32:34', '[email protected]'), (113, 'Rajah', 'Alvarado', 'P.O. Box 442, 5875 Augue, Avenue', 'West Valley City', '53305', 'United States', 'Alvarado935', 'WAP18BWZ8HC', 4024, 935, '2015-01-09 05:25:12', '[email protected]'), (114, 'Bree', 'Mcbride', 'P.O. Box 611, 2151 Massa. Av.', 'South Burlington', '85800', 'United States', 'Mcbride528', 'JWK34TJF8FP', 2147483647, 528, '2016-11-22 11:24:38', '[email protected]'), (115, 'Rana', 'Mathis', 'Ap #374-6576 Ipsum. St.', 'New Maryland', 'J5W 4J1', 'Canada', 'Mathis381', 'UDX36TIT3XP', 541, 381, '2017-09-19 03:08:00', '[email protected]'), (116, 'Knox', 'Christensen', 'Ap #200-261 Arcu St.', 'Pugwash', 'P9C 0P9', 'Canada', 'Christensen692', 'QUQ30VMW3CO', 645475, 692, '2014-04-25 20:25:11', '[email protected]'), (117, 'Byron', 'Sykes', 'Ap #753-7214 Aenean Street', 'Cape Breton Island', 'H9C 0C4', 'Canada', 'Sykes491', 'UMV71FHD8DA', 2147483647, 491, '2014-10-16 15:30:50', '[email protected]'), (118, 'Karyn', 'Rojas', '625-9806 Mi Av.', 'Frederick', '60688', 'United States', 'Rojas849', 'MNU05DDO9CO', 2147483647, 849, '2015-04-07 06:10:44', '[email protected]'), (119, 'Connor', 'Silva', 'P.O. Box 187, 8311 Non St.', 'Whitehorse', 'G0N 6G8', 'Canada', 'Silva656', 'CPY28CWL9VN', 2147483647, 656, '2014-12-30 22:54:56', '[email protected]'), (120, 'Rhonda', 'Benton', '775-8576 Ut, Av.', 'Pangnirtung', 'T1M 3K8', 'Canada', 'Benton930', 'DVI75CFE1LT', 601, 930, '2014-08-17 11:06:15', '[email protected]'), (121, 'Patricia', 'Montoya', 'Ap #117-4391 Nisi Road', 'Whitehorse', 'N2M 6P1', 'Canada', 'Montoya598', 'ZSW17IFX6ZG', 2147483647, 598, '2016-05-05 19:26:25', '[email protected]'), (122, 'Doris', 'Hoffman', '230-4136 Integer Av.', 'Rutland', '94827', 'United States', 'Hoffman801', 'JBW42ZPK4FP', 2147483647, 801, '2016-09-10 12:55:59', '[email protected]'), (123, 'Noel', 'Andrews', '759-8693 Orci. Street', 'Winnipeg', 'X3C 1M4', 'Canada', 'Andrews760', 'FGR45JCR8ZN', 6011, 760, '2015-08-23 15:03:47', '[email protected]'), (124, 'Hyatt', 'Orr', 'Ap #731-2636 Enim Rd.', 'Paradise', 'A5A 2C8', 'Canada', 'Orr732', 'VFW36PNN3VS', 2147483647, 732, '2016-10-11 19:13:44', '[email protected]'), (125, 'Ifeoma', 'Kemp', '9161 Semper St.', 'Cambridge Bay', 'T8C 7P5', 'Canada', 'Kemp150', 'SIM95JAG7MR', 548551, 150, '2015-10-13 14:43:59', '[email protected]'), (126, 'Hunter', 'Ayala', '1380 Class Road', 'Lafayette', '45749', 'United States', 'Ayala332', 'LCH33QZY3KG', 5418, 332, '2017-07-09 07:34:26', '[email protected]'), (127, 'Bree', 'Richmond', 'P.O. Box 744, 9818 Vestibulum Road', 'Cedar Rapids', '85731', 'United States', 'Richmond479', 'CFX90ZPT4KL', 527859, 479, '2017-01-14 06:55:42', '[email protected]'), (128, 'Cameron', 'Gomez', '3619 Pede. Road', 'Chelsea', 'M5S 6P2', 'Canada', 'Gomez126', 'HYY77HXO0KI', 3456, 126, '2015-01-31 04:35:07', '[email protected]'), (129, 'Thaddeus', 'Vega', 'P.O. Box 283, 5770 Pellentesque Rd.', 'Dieppe', 'P6A 3G5', 'Canada', 'Vega665', 'RSE00PJM4DB', 521044, 665, '2015-07-30 17:59:14', '[email protected]'), (130, 'Demetrius', 'Manning', 'P.O. Box 197, 6050 Dolor Road', 'Iowa City', '19386', 'United States', 'Manning853', 'XOL18DVX2ZG', 2147483647, 853, '2017-05-05 03:54:14', '[email protected]'), (131, 'Blythe', 'Hampton', '249-6542 Nam Avenue', 'Watson Lake', 'X8Z 3L1', 'Canada', 'Hampton642', 'ZXE41SAV2YH', 2147483647, 642, '2016-12-21 17:06:31', '[email protected]'), (132, 'Stella', 'Fischer', '6859 Dis St.', 'Town of Yarmouth', 'J1Y 8Z7', 'Canada', 'Fischer482', 'JJA54ZKJ5EM', 550951, 482, '2016-03-06 10:31:18', '[email protected]'), (133, 'Harrison', 'Mayer', 'P.O. Box 609, 3382 Fusce Rd.', 'New Maryland', 'A1K 6N6', 'Canada', 'Mayer460', 'MFZ06APJ7WJ', 2147483647, 460, '2014-04-01 06:19:56', '[email protected]'), (134, 'Beau', 'Mcintyre', '620-4523 Ipsum Rd.', 'Columbia', '35861', 'United States', 'Mcintyre476', 'XXN60PYS7MS', 2147483647, 476, '2015-10-31 14:08:19', '[email protected]'), (135, 'Beck', 'Brennan', 'P.O. Box 359, 806 Ante. Ave', 'Green Bay', '11006', 'United States', 'Brennan901', 'WCH31CDJ4RI', 5545, 901, '2017-09-14 13:19:07', '[email protected]'), (136, 'Veronica', 'Day', '1577 Ante Road', 'Colorado Springs', '28218', 'United States', 'Day743', 'AMT80ZZZ9NT', 2147483647, 743, '2014-10-20 16:17:28', '[email protected]'), (137, 'Aaron', 'Callahan', 'Ap #927-5488 Lorem Rd.', 'Fort Good Hope', 'X5T 1R4', 'Canada', 'Callahan803', 'SXN22BOP2OW', 3776, 803, '2014-11-10 17:15:33', '[email protected]'), (138, 'Eugenia', 'Parsons', '631-8101 Ut Rd.', 'Phoenix', '26150', 'United States', 'Parsons975', 'FVK81CXS8CP', 655094, 975, '2014-11-24 21:58:16', '[email protected]'), (139, 'Chaney', 'Roberson', 'P.O. Box 261, 4587 Fringilla Road', 'Watson Lake', 'J8L 7E1', 'Canada', 'Roberson644', 'XYL26GWS4YK', 2147483647, 644, '2017-01-25 01:11:56', '[email protected]'), (140, 'Jin', 'Haney', '8711 Scelerisque Avenue', 'Fresno', '56347', 'United States', 'Haney650', 'YEK98MNL6NX', 2147483647, 650, '2015-09-06 00:16:49', '[email protected]'), (141, 'Nissim', 'Barron', 'Ap #513-6132 Quisque St.', 'Flin Flon', 'R0M 2Y0', 'Canada', 'Barron147', 'DEN61KFC1HR', 2147483647, 147, '2015-11-18 21:21:37', '[email protected]'), (142, 'Athena', 'Mercer', '247-4809 Eu Ave', 'Portland', '77825', 'United States', 'Mercer776', 'SID61YQA4WY', 2147483647, 776, '2015-07-05 00:59:54', '[email protected]'), (143, 'Carol', 'Bond', '494-4670 Phasellus St.', 'Gresham', '48831', 'United States', 'Bond889', 'VBL09EWF9NV', 5489, 889, '2017-07-30 21:22:15', '[email protected]'), (144, 'Ciaran', 'Hawkins', '2945 Dolor. Street', 'Arviat', 'X2P 3J0', 'Canada', 'Hawkins829', 'EWK77DMW3RP', 6474, 829, '2016-10-15 00:21:33', '[email protected]'), (145, 'Marshall', 'Fitzgerald', '5626 Vestibulum. Av.', 'Winnipeg', 'X0L 8T6', 'Canada', 'Fitzgerald743', 'SHP43LQZ0TD', 3777, 743, '2014-11-28 17:54:45', '[email protected]'), (146, 'Erich', 'Fletcher', 'Ap #630-7911 Donec Ave', 'Paradise', 'B2P 0J3', 'Canada', 'Fletcher509', 'TWS75IAE8LU', 2147483647, 509, '2016-02-17 15:24:09', '[email protected]'), (147, 'Wyoming', 'Woodard', '310-7254 Vitae, Rd.', 'Evansville', '39038', 'United States', 'Woodard306', 'DQL57KZR6WQ', 2147483647, 306, '2015-09-25 05:05:40', '[email protected]'), (148, 'Sonia', 'Meyers', 'P.O. Box 983, 8334 Cras Ave', 'Sandy', '12218', 'United States', 'Meyers265', 'ETA13OLQ9TW', 2147483647, 265, '2015-10-08 20:55:47', '[email protected]'), (149, 'Yardley', 'Hanson', '5666 Etiam Rd.', 'Chicago', '70633', 'United States', 'Hanson587', 'KZJ52JSK5OC', 6498, 587, '2016-05-18 03:02:25', '[email protected]'), (150, 'Colton', 'Pate', '216-2401 Quis, Ave', 'Kapolei', '35070', 'United States', 'Pate281', 'LBF53UEJ3VC', 5596, 281, '2014-09-18 18:55:59', '[email protected]'), (151, 'Marah', 'Cross', '621-1847 Est St.', 'Toronto', 'J8S 6R7', 'Canada', 'Cross152', 'BPG52PWZ1KB', 3755, 152, '2015-07-03 00:02:13', '[email protected]'), (152, 'Cecilia', 'Carter', '8503 Nec, Rd.', 'Fort St. John', 'B6G 5B9', 'Canada', 'Carter899', 'MGB56KJC4RJ', 3437, 899, '2017-01-17 09:04:26', '[email protected]'), (153, 'Quynn', 'Compton', 'P.O. Box 631, 5325 Aenean Rd.', 'Augusta', '93467', 'United States', 'Compton695', 'CYC00TFA8AT', 2147483647, 695, '2015-02-19 00:36:42', '[email protected]'), (154, 'Maya', 'Cardenas', 'Ap #910-1754 Eu Avenue', 'Philadelphia', '74958', 'United States', 'Cardenas374', 'XGK68MDW7VD', 5177, 374, '2015-09-26 13:15:54', '[email protected]'), (155, 'Armando', 'Malone', '8730 Mus. Ave', 'Billings', '23249', 'United States', 'Malone877', 'GFS47NIJ6NH', 3775, 877, '2017-01-01 02:00:08', '[email protected]'), (156, 'Herman', 'Rocha', '417-8267 A, Av.', 'Rochester', '64235', 'United States', 'Rocha605', 'GXV91NTD0YH', 552, 605, '2015-07-02 13:50:35', '[email protected]'), (157, 'Chaim', 'Summers', 'P.O. Box 113, 236 Arcu. Avenue', 'Clarksville', '72767', 'United States', 'Summers318', 'HTS53JDY8NM', 2147483647, 318, '2017-04-03 20:47:00', '[email protected]'), (158, 'Pascale', 'Bryan', 'P.O. Box 726, 6529 Morbi Rd.', 'Portland', '73307', 'United States', 'Bryan117', 'OKT85HPO5QS', 529, 117, '2016-10-03 23:08:51', '[email protected]'), (159, 'Tanek', 'Cochran', 'P.O. Box 192, 2606 Sed St.', 'Oklahoma City', '10786', 'United States', 'Cochran752', 'FCI13WYW0OD', 3408, 752, '2017-10-19 20:00:41', '[email protected]'), (160, 'Jescie', 'Jennings', 'Ap #812-1731 Magna. Street', 'Leamington', 'Y8A 9A7', 'Canada', 'Jennings582', 'GOW71NKZ6JP', 548719, 582, '2016-12-25 18:23:40', '[email protected]'), (161, 'Quail', 'Griffin', '541-9852 Commodo Street', 'Estevan', 'X6T 2L8', 'Canada', 'Griffin816', 'TMQ27BTU5WS', 513249, 816, '2015-12-27 05:45:59', '[email protected]'), (162, 'Amethyst', 'Booker', '869-4281 Elit Avenue', 'Sparwood', 'G2K 2R8', 'Canada', 'Booker788', 'DAL85SEY2EF', 5180, 788, '2015-06-25 05:36:28', '[email protected]'), (163, 'Joel', 'Mullen', '8664 Sociis Street', 'Virginia Beach', '62809', 'United States', 'Mullen907', 'XVO91FOS4OO', 2147483647, 907, '2017-03-31 03:28:28', '[email protected]'), (164, 'Zena', 'Mclean', 'P.O. Box 908, 7900 Luctus Rd.', 'Springfield', '13663', 'United States', 'Mclean847', 'UXF24KKW9NA', 5515, 847, '2017-05-15 14:41:01', '[email protected]'), (165, 'Ruth', 'Olsen', 'Ap #926-7669 Gravida St.', 'Kaneohe', '38443', 'United States', 'Olsen396', 'HSA74NOD2OJ', 4539, 396, '2015-02-22 05:09:48', '[email protected]'), (166, 'Wyatt', 'Harding', '979-8283 Proin St.', 'McCallum', 'H6J 2H7', 'Canada', 'Harding697', 'FAQ51OEP1EW', 553, 697, '2015-10-03 05:23:42', '[email protected]'), (167, 'Zahir', 'Kemp', 'Ap #847-9102 Maecenas Rd.', 'South Portland', '42583', 'United States', 'Kemp970', 'WWT12AHK0VR', 647225, 970, '2016-10-28 04:35:35', '[email protected]'), (168, 'Tucker', 'Hampton', 'P.O. Box 314, 5427 Integer St.', 'Indianapolis', '63758', 'United States', 'Hampton711', 'KWA91GQJ9BK', 558, 711, '2017-08-21 14:56:32', '[email protected]'), (169, 'Alec', 'Stark', '320-4618 Vel, Road', 'Cariboo Regional District', 'R7A 2M2', 'Canada', 'Stark868', 'TYM95KDN8JM', 512, 868, '2015-06-10 22:57:22', '[email protected]'), (170, 'Emmanuel', 'Mcclure', 'P.O. Box 412, 7923 Orci. St.', 'C�te-Saint-Luc', 'T5S 1R2', 'Canada', 'Mcclure542', 'NBX86AHX4GG', 5266, 542, '2016-03-14 22:19:04', '[email protected]'), (171, 'Taylor', 'Potter', '935-4951 A Avenue', 'Paradise', 'R3Y 4B4', 'Canada', 'Potter964', 'KBF80KSD0BV', 2147483647, 964, '2014-04-29 04:12:21', '[email protected]'), (172, 'Madonna', 'Conway', '3488 Lorem. St.', 'Prince Albert', 'H0M 4L3', 'Canada', 'Conway757', 'CPI70DEM3CQ', 3485, 757, '2015-02-21 19:04:06', '[email protected]'), (173, 'Isaac', 'Cline', '7850 Magna Road', 'Minitonas', 'N3Z 0L2', 'Canada', 'Cline172', 'YRT02GZD6NE', 2147483647, 172, '2014-07-30 10:21:48', '[email protected]'), (174, 'Kelly', 'Hogan', '965-5317 Ut St.', 'Flint', '12919', 'United States', 'Hogan825', 'JTD55SJU1HW', 2147483647, 825, '2016-06-03 04:42:27', '[email protected]'), (175, 'Amery', 'Hicks', '119-7903 Metus. Ave', 'Deline', 'M5T 9J3', 'Canada', 'Hicks213', 'ELV90BCE0TA', 3744, 213, '2016-09-10 14:05:27', '[email protected]'), (176, 'Carson', 'Baldwin', 'P.O. Box 996, 9393 Euismod Avenue', 'Nakusp', 'G3E 2A1', 'Canada', 'Baldwin906', 'RVS16DDU8ST', 4716, 906, '2017-05-20 08:01:05', '[email protected]'), (177, 'Petra', 'Mcgee', 'P.O. Box 532, 1164 Ornare Road', 'Colorado Springs', '91303', 'United States', 'Mcgee317', 'SFA11NGV2RD', 540, 317, '2017-02-23 11:45:38', '[email protected]'), (178, 'Ariel', 'Nash', '636-5187 Molestie Rd.', 'Hamilton', 'J6A 6P3', 'Canada', 'Nash970', 'NPO70DWJ4XN', 649014, 970, '2014-12-02 05:35:49', '[email protected]'), (179, 'Rebecca', 'Garrison', 'Ap #537-8136 Montes, St.', 'Lourdes', 'T9G 6S1', 'Canada', 'Garrison425', 'RHZ69ZSR8AE', 2147483647, 425, '2014-03-31 08:05:33', '[email protected]'), (180, 'Signe', 'Doyle', '402-8947 Libero Rd.', 'Windsor', 'V9X 1Y6', 'Canada', 'Doyle470', 'IFE80DAG2ZN', 527900, 470, '2014-12-18 09:58:21', '[email protected]'), (181, 'Gretchen', 'Cervantes', 'P.O. Box 491, 6482 Mauris St.', 'Colchester', '51824', 'United States', 'Cervantes624', 'ION26EZB4YW', 2147483647, 624, '2017-12-16 07:12:08', '[email protected]'), (182, 'Blake', 'Hunter', 'Ap #131-6380 Tempus, Av.', 'Glovertown', 'T2R 5C2', 'Canada', 'Hunter489', 'TVK77WAT7JG', 2147483647, 489, '2015-11-12 19:17:16', '[email protected]'), (183, 'Jackson', 'Sandoval', '212-5259 Eu, Ave', 'Memphis', '82008', 'United States', 'Sandoval603', 'KVK96QYW9WP', 556902, 603, '2017-10-18 00:34:16', '[email protected]'), (184, 'Brett', 'Wallace', 'P.O. Box 757, 5399 Inceptos Street', 'Watson Lake', 'H9W 0P9', 'Canada', 'Wallace443', 'YYJ92PGD0CT', 3468, 443, '2017-05-13 15:19:29', '[email protected]'), (185, 'Gary', 'Francis', '1083 Cubilia St.', 'Ajax', 'Y7X 2K2', 'Canada', 'Francis570', 'ZWD26MUZ1UV', 644, 570, '2017-02-01 23:16:07', '[email protected]'), (186, 'Nigel', 'Cooper', '9790 Orci Rd.', 'Ketchikan', '46227', 'United States', 'Cooper223', 'OHB48WKI3GD', 2147483647, 223, '2014-07-04 18:39:46', '[email protected]'), (187, 'Darryl', 'Mccarthy', '742-2036 Quisque Ave', 'Tulita', 'X1Y 6E3', 'Canada', 'Mccarthy470', 'DUQ96UNQ6FR', 2147483647, 470, '2015-11-08 20:42:21', '[email protected]'), (188, 'Dean', 'Patel', 'Ap #601-7607 Adipiscing Rd.', 'Quispamsis', 'V9B 3J5', 'Canada', 'Patel263', 'VYH55SQF0MJ', 2147483647, 263, '2015-04-16 22:58:33', '[email protected]'), (189, 'Wang', 'Crosby', 'P.O. Box 989, 9144 Ante, Rd.', 'Orilla', 'M7Z 5B4', 'Canada', 'Crosby712', 'BEI75STT0TM', 2147483647, 712, '2017-04-27 19:02:27', '[email protected]'), (190, 'Amena', 'Nielsen', '103-790 Magnis Street', 'Fort McPherson', 'K2S 2G4', 'Canada', 'Nielsen245', 'GWA76ROW6OM', 3781, 245, '2015-10-13 04:49:56', '[email protected]'), (191, 'Drew', 'Mckay', 'Ap #449-9078 Nec Av.', 'Cincinnati', '45789', 'United States', 'Mckay758', 'CPH79GOG7IG', 3455, 758, '2016-01-09 11:59:59', '[email protected]'), (192, 'Fritz', 'Harris', 'P.O. Box 802, 5540 Ornare, St.', 'Racine', '71932', 'United States', 'Harris572', 'ZFI78XMH1QC', 2147483647, 572, '2015-12-02 11:14:41', '[email protected]'), (193, 'Christian', 'Weaver', 'Ap #980-2677 Taciti Avenue', 'Yorkton', 'C2N 6W4', 'Canada', 'Weaver466', 'UUC38URQ8LD', 2147483647, 466, '2016-06-30 04:24:45', '[email protected]'), (194, 'Blythe', 'Martin', 'P.O. Box 170, 3582 Convallis Avenue', 'Fortune', 'T2H 3H2', 'Canada', 'Martin338', 'GBJ50FYG1SA', 648802, 338, '2016-04-23 17:44:55', '[email protected]'), (195, 'Carla', 'Pugh', 'Ap #204-730 Tristique St.', 'Gibbons', 'N0B 9N3', 'Canada', 'Pugh788', 'SSV69GBF3JK', 644, 788, '2014-10-22 03:00:11', '[email protected]'), (196, 'Rajah', 'Talley', '394-3613 Senectus Avenue', 'Burin', 'T4K 3X9', 'Canada', 'Talley413', 'JYV08KQO5AW', 6498, 413, '2016-02-25 03:29:01', '[email protected]'), (197, 'Sean', 'Mcfadden', 'P.O. Box 976, 8730 Quisque Ave', 'Winnipeg', 'V6X 6P9', 'Canada', 'Mcfadden709', 'MMJ47EBC8AH', 6468, 709, '2014-08-31 12:35:00', '[email protected]'), (198, 'Declan', 'Nguyen', '9685 Quis Avenue', 'Regina', 'R0N 1E0', 'Canada', 'Nguyen130', 'FPC28LBF2DH', 539308, 130, '2014-09-25 11:23:56', '[email protected]'), (199, 'Jessamine', 'Hatfield', '282-4274 Molestie Rd.', 'Rockford', '72683', 'United States', 'Hatfield529', 'VTV86HZJ0NX', 4485, 529, '2016-08-05 18:38:39', '[email protected]'), (200, 'Micah', 'Riley', '706-4848 Vel Road', 'West Jordan', '67860', 'United States', 'Riley496', 'TUW91AMJ0PV', 402, 496, '2015-01-15 21:36:58', '[email protected]'), (201, 'Lysandra', 'Pace', '924-9284 Odio, Road', 'Tallahassee', '32589', 'United States', 'Pace564', 'WII15AYT3KC', 3474, 564, '2015-08-25 21:43:05', '[email protected]'), (202, 'Melissa', 'Humphrey', '170 In, St.', 'Sacramento', '77367', 'United States', 'Humphrey829', 'BDP38ZRJ1UB', 4929, 829, '2014-05-03 00:15:37', '[email protected]'), (203, 'Melodie', 'Travis', 'P.O. Box 710, 1541 Facilisis Ave', 'Charlottetown', 'B8R 3E2', 'Canada', 'Travis281', 'ZBK62PDV6MU', 552, 281, '2016-10-20 10:52:45', '[email protected]'), (204, 'Anne', 'Barrett', 'P.O. Box 897, 7182 Nunc Rd.', 'Lang', 'M0R 2C0', 'Canada', 'Barrett931', 'KTD35ELE7DH', 519, 931, '2014-04-29 07:38:18', '[email protected]'), (205, 'McKenzie', 'Murray', 'Ap #241-5962 Pede, Rd.', 'Racine', '34707', 'United States', 'Murray683', 'MNY00SJS4LP', 645412, 683, '2017-10-06 03:47:40', '[email protected]'), (206, 'Roanna', 'Hartman', '5203 Sapien. Rd.', 'Jacksonville', '91644', 'United States', 'Hartman423', 'YEN75ETJ0KK', 2147483647, 423, '2015-11-17 12:50:12', '[email protected]'), (207, 'Xerxes', 'Solis', 'P.O. Box 274, 5915 Amet Road', 'Northumberland', 'L3T 6Y7', 'Canada', 'Solis402', 'RAF72TEE1PV', 2147483647, 402, '2016-01-27 01:58:49', '[email protected]'), (208, 'Deanna', 'Molina', 'Ap #690-8758 Morbi St.', 'Fort Wayne', '56954', 'United States', 'Molina175', 'GQC74MUE1SA', 647989, 175, '2016-06-04 18:52:14', '[email protected]'); -- -- Indexes for dumped tables -- -- -- Indexes for table `data` -- ALTER TABLE `data` ADD PRIMARY KEY (`CUST_ID`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `data` -- ALTER TABLE `data` MODIFY `CUST_ID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=209; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
__MACOSX/untitled folder/PHP_Project-2/DataBase/._test_data.sql
__MACOSX/untitled folder/PHP_Project-2/._DataBase
__MACOSX/untitled folder/PHP_Project-2/._epalka_accounts.pdf
untitled folder/PHP_Project-2/Form.png
__MACOSX/untitled folder/PHP_Project-2/._Form.png
untitled folder/PHP_Project-2/Module_03_Lab01_Worksheet_1_Updated-3.docx
Module 03 Lab Worksheet
Web Development Using LAMP
Lab Activities:
· Create a database in MySQL
· Import data into MySQL
· Access a MySQL database using SQL
· Connect to a MySQL database using PHP
· Integrate SQL query results into a Web page
· Run an SQL query from a Web form
Introduction
Download the sample data file TestData.csv.zip and unzip it.
|
Note that the file is in plain ASCII, with Unix line endings and uses the pipe symbol (|) as a field delimiter. You’ll need to know this when you import it into your database.
Over the course of this worksheet I’ll be asking you questions about MySQL commands. You can find documentation at http://dev.mysql.com . I encourage you to use this to answer the worksheet questions. You can also get some of your answers from PHPMyAdmin itself as we work through the exercise but this will help you get more familiar with the SQL language.
|
Evaluate Your Data
Open the sample data file in a spreadsheet program and examine the data. In the following table, list the names, description and data types for each field.
|
Field Name (from file) |
Description |
Data Type |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Create Database
Before you import unformatted data into MySQL, you have to have a place to put it.
You can import data in three ways:
· Use a compatible table of an existing database
· Create a new table in an existing database either manually or by importing.
· Create a new database with a table that fits the incoming data.
Log into PHPMyAdmin.
Create a new database called dbtest. What SQL command would you use?
Since it’s not a good idea to use the MySQL administrator account root for everything, we’ll create a new MySQL user that will be managing this new database.
Create a new user with a user name consisting of your first initial followed by your last name. For example, Ellie Palka would create a user named epalka.
This will be a user for localhost only and has no access to any databases. The password will be the same as the username (we can change it later).
In other words, the full user name for Edith Palka would look like epalka@localhost with a password of epalka. (Remember to substitute your own first initial/last name for the user ID.)
What is your user name?
What was the SQL command you would use to create this user?
|
A user with an easily-guessed password is insecure but this is only for testing purposes and we can change the password later. |
Now give the user you just created administrative access to the database dbtest. That is, they should have full control over the database dbtest and no others. What SQL command would do that?
Confirm that your user has full access to dbtest. How would you show this with an SQL command without logging in as that user?
Run that command, if you haven’t already. What was the output?
Log out and log back in as your new user to confirm that they can administer dbtest and nothing else. If this works, continue with this lab as your new user.
Importing Data
Sometimes the data file you are importing is formatted in a way that isn’t quite right for your project. This happens quite frequently when you are getting your data from a third party. The process of fixing it is called data cleansing.
Data cleansing can be a complex job and there are commercial applications designed for just that purpose. In fact, there are entire companies that provide data cleansing services.
In our case we have to
· replace the pipe (|) delimiters in our file with commas
· enclose our data fields with quotes
· add a column to use as our primary key.
This can all be done by hand, of course but we’d prefer to have the computer do the grunt work for us.
Our cleaning job is pretty simple so we might be able to do it with the tools we already have.
Cleaning Our Data
Upload TestData.csv to your workspace.
Using Find->Replace, replace the pipe symbols (|) with a comma enclosed in double quotes. (“,”)
That’s most of the work. Now we just have to put a double quote (“) on the beginning and end of each line and add a field for our data key. You can do it by hand but it’s better to automate the job.
The text editor isn’t flexible enough to let us do this but we have other tools at our disposal.
At the bottom of your workspace is a terminal window with a command prompt. This lets you access many powerful text-processing tools that will make your work easier.
|
Every modern operating system has a set of command line utilities that allow you to do things that are difficult or even impossible to do in the GUI. If you are an IT professional, or plan to become one, you should familiarize yourself with these tools. |
Click your mouse into the window to make it active.
|
If the window is not available, right-click on your DevBox and select SSH Terminal, then click your mouse inside the window. |
Type ls at the prompt and press <Enter> to see a list of the files in your workspace.
To add a double quote to the beginning and end of each line, we will use a utility called sed, which is short for ‘stream editor’. It’s very useful for performing monotonous tasks like this.
At the command prompt, type:
sed 's/^.*$/"&"/g' TestData.csv > TestData01.csv
and press <Enter>.
|
Normally sed will print its output to the screen. To save it to a file instead (like TestData01.csv) we need to use the > symbol to redirect the output. |
Right-click on your DevBox and select Refresh.
A new file, TestData01.csv should now be in your File Explorer. Note that the original file is still available so if we mess up we can easily go back and re-do it.
Click on TestData01.csv to open it and make sure that it’s properly formatted before continuing.
Adding a Primary Key Field
|
Remember, a primary key has two major properties: 1. It must be unique 2. It cannot be null (it should always have a value).
When choosing a primary key, you should consider: · It should be simple and familiar. · The value shouldn’t change. · For efficiency, an integer value is preferable. · It is usually a single column but you can use multiple columns. In any case, use as few columns as possible.
A popular strategy is to create a new column with an unrelated, unique number and auto-increment it with each new record. That’s what we are going to do. |
We're going to create a column labelled CUST_ID that consists of 1, 2, 3 and so on for each row.
.
First we need to figure out how many records we actually have. At the command line, enter:
wc -l TestData01.csv
The wc command is normally used to get a word count. The -l option tells wc to count lines instead.
(We can also count them in our text editor by scrolling down and looking at the last line number.)
What number is returned by this command?
Given that the first line consists of our field labels, how many records do we actually have?
We're going to do this in two steps:
1. Create a text file with the column data we need.
2. Join this file to TestData01.csv.
Create the Column Data
In your workspace, create a new file called ids.txt.
Select the file to open it in your text editor.
In the file add the single line at the top:
"CUST_ID",
Don't forget the end comma and press <Enter> at the end of the line.
Save your changes before continuing.
Adding our Number Data
Now we need to add the numbers. We know that we will need 1 through 100 and they will have to be double-quoted with a comma on the end. In other words, each line will look something like this:
"2",
|
A good way to get a computer to do something repetitive is to use a loop. Normally these are found in program code but we can also run them at the command line. |
At the terminal, run the following command:
for ((a=1;a <= 100;a++)); do echo \"$a\", ; done >> ids.txt
|
This looks complicated but let's break it down. a is the label for the number that we're going to print and we want it to start at 1:
a=1
and end at 100. So we test to see if a is still less than or equal to 100 each time we run our loop:
a <= 100
Each time we run the loop, we add 1 to a:
a++
During our loop we run one command:
echo \"$a\",
The $ tells echo that we don't want the letter 'a', we want the current number with the label a.
A double-quote usually means something special to the echo command and they're not normally printed out. The backslashes (\) tell echo to treat the double-quotes like any other character and just print them.
Normally echo prints to the screen but we want to add the output to our file ids.txt instead.
>> ids.txt
The double-arrows (>>) tell our terminal to append the output to the end of our file instead of replacing the contents of the file. |
Close and reopen the ids.txt file in your text editor and you should see the changes.
Putting It All Together
The last thing we need to do is to append ids.txt to TestData01.csv.
This is actually pretty easy. In the terminal, run the command:
paste -d '' ids.txt TestData01.csv > TestData02.csv
Now when you refresh your workspace, you'll see the TestData02.csv file.
Open it in your editor to make sure that all of the changes we need have been completed.
Download TestData02.csv and rename it accounts.csv.
NOTE: You could do all of this by hand or in a spreadsheet program.
BUT:
|
Sometimes the spreadsheet will modify your data when you open the file because it wrongly guesses the datatype. For example, it might convert a credit card number into scientific notation.
MS Excel in particular is notorious for this.
This can corrupt your data so make sure to double-check before importing into your database!
I’ve experimented with several spreadsheet programs and so far have found that LibreOffice (free for Windows, Linux and Mac OS X) and Numbers (free for Mac OS X) preserve the formatting of the text fields.
Since this test data is just 100 records, it can be edited by hand.
However, the project data file is 1000 records so it’s worth the effort to figure out what tools work to automate the process. |
Now we can finally import our data.
Log into PHPMyAdmin and click on the dbtest database you created.
Click on the Import tab.
In File to Import, click on the Browse button and select accounts.csv.
Since we want the first line (the header columns) to be the names of our fields, click on the check box labelled “The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)”.
Click on Go.
If you get an error, read it carefully to see what part of the data file was a problem. Once you fix it, try to import it again.
If everything goes well, you should receive a success message.
How many records were added?
What SQL command would you use to import your data file?
Click on the new table name and examine it to make sure it has all of the data we need.
We need to tell MySQL that CUST_ID is our primary key.
In the table view, select the Structure tab.
In the row containing CUST_ID, click on Primary (the golden key icon).
Confirm that you want to make CUST_ID your primary key. (The key icon should change from gold to grey.)
Note that the table has the default name of TABLE 1. We should rename it to make it less generic.
With the table still selected, click on the More tab and select Operations.
In the Table operations page, go to the Table options section and rename the table to accounts. Click the Go button to submit your change.
Access Database Using SQL and PHP
Now we have our database and have run some SQL statements against it in PHPMyAdmin. But our real goal is to integrate our database with our Web page. We can do this with any programming language understood by our Web server but since we have PHP installed we’ll use that.
Let’s break this task down into pieces:
· Crafting the SQL query.
· Passing the query via PHP and sending the output to a Web page.
· Create a Web page with a button linked to our query.
Crafting our Query
It’s important to make sure your query works as expected before you plug it into a PHP statement. This minimizes the work required to debug your code. PHPMyAdmin allows you to enter SQL directly and will advise you if you make a mistake. Let’s keep things simple for now with a query that will search for the first and last names of all of the customers in our table.
SELECT FNAME,LNAME FROM `accounts`;
Go ahead and run this statement in the SQL tab of PHPMyAdmin and make sure it works as expected. If it does, we can move on to the next phase.
Passing Our Query to PHP
First we need to tell PHP how to open a connection to our database before it can send a query. Create a new folder in your workspace called test to hold your test scripts. Add a file to this folder called php_dbtest.php with the following content in the body:
<!doctype html>
<html lang="en">
<head>
<title>PHP Data Connection Test</title>
</head>
<body>
<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
'$conn = new mysqli($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
</body>
</html>
(Don't forget to save your changes before continuing.)
|
Remember to change username and password to the user and password you created for your dbtest database before saving. |
Open a new page in your Web browser and point it to your DevBox home page. Click on the link for the file you just created.
You should get the message ‘Connected successfully’. If you didn’t, check your code and try again until you do before continuing.
Your connection to the database will remain open as long as the script is running. If you want to close it beforehand, simply use the expression:
$conn->close();
So now we know how to connect to our server, let’s connect specifically to our database dbtest. Edit your PHP test page and add the following after the code that opens the database connection:
In the section where we set the server and user information, add the line:
$dbname = "dbtest";
In the section where we create our connection, modify the statement
$conn = new mysqli($servername, $username, $password);
to read
$conn = new mysqli($servername, $username, $password, $dbname);
|
The original connection was to the database server. The new connection is to a specific database on that server. |
Save the changes, reload the Web page and make sure you get the success message before continuing.
Now that we can connect to a specific database on our server, it’s time to send a query.
Edit your Web page to add the following to the section after we create our connection:
$sql = "SELECT FNAME,LNAME FROM `accounts`";
$result = $conn->query($sql);
This loads our query into the variable sql which then is sent to the server via the connection we just opened. The output of our query is stored in the object named $result.
We’re not done yet. Now that we have a result, let’s do something with it. First let’s just see how many records were returned.
if ($result->num_rows > 0) {
echo "<br>$result->num_rows results returned.";
} else {
echo "0 results";
}
This if statement will be very, very useful for us. Basically it says “If more than one record matched the query, do something with it, otherwise tell me that nothing happened.” All we have to do is figure out what that “something” is. The <br> tag just puts in a line break between the successful connection message and our output.
|
Remember any printed output from this script will be interpreted as HTML so remember to include appropriate tags to format your text. |
Save your changes and refresh the Web page.
How many results were returned?
Take a few moments to experiment with other HTML tags on your output before continuing, like changing the size and style.
Now that we know our query was passed and executed successfully, let’s print out the results. This time instead of echo, we’ll use the printf ( formatted print) function. In addition, we’d like to print out the name in standard <last name>,<first name> format.
Modify your if statement to read:
if ($result->num_rows > 0) {
//output data of each row
while ($row = $result->fetch_assoc()) {
printf ("%s, %s<br>", $row["LNAME"], $row["FNAME"]);
}
if ($result->num_rows = 0) {
echo "0 results <br";
}
}
Let’s break down what’s happening here, line-by-line.
while ($row = $result->fetch_assoc()) {
Remember, result is an object that contains the...well, results of our query. The method fetch_assoc() dumps the records into an associative array called row. The while loop goes through each record until there are no more records to process.
|
Unlike a traditional array, which uses integers as indexes (ex. a[0],a[1]...), an associative array uses strings. In this case the indexes are the field names (row[FNAME], row[LNAME], row[ZIP], etc.)
Be aware that field names are case-sensitive. |
printf ("%s, %s<br>", $row["LNAME"], $row["NAME"]);
The printf function has two parts - the first describes the desired print format and what data types are going to be printed and the second part lists the variables to be printed. Some common data type notations you can use:
|
%s |
string |
|
%c |
character |
|
%d |
decimal (base 10) |
|
%o |
octal (base 8) |
|
%x |
hexadecimal (base 16 |
|
%f |
floating point |
Think of it this way -- you’re sending printf a string of 1’s and 0’s and the data type tells it how to interpret them.
So now we can use PHP to run a query on a database and output the results to a Web page. For our last step, let’s set up a button on our Web page to run that query when clicked. In other words, we’re going to create an HTML form.
For what we need to do, creating a page with a form is pretty simple. All we need is a page with a single button which, when pressed, runs our query and prints the results.
Create a new HTML page called dbform_test.html with the following content and save it in your test folder:
<body>
<form action="php_dbtest.php">
<button type="submit">Run SQL Query</button>
</form>
</body>
</html>
Button, Button, Where’s that Darn Button?
Go back to your DevBox home page and click the link for dbform_test.html. (You may have to refresh the page.)
What do you see?
Click the button. What happens?
Final Task: Create a Web Search Page
By now you should know enough to create a simple search form to run a query on a database.
· Create a form that will let the user input a surname and when the search runs, it will output all users with that surname from the accounts table.
· Submit your HTML and PHP code files with this worksheet.
· Export the accounts table as a PDF, name it <userid>_accounts.pdf and submit it with this worksheet. For example, if your username is epalka, then you would submit a PDF named epalka_accounts.pdf.
You can submit all of these files as a single Zip file if you prefer.
14
__MACOSX/untitled folder/PHP_Project-2/._Module_03_Lab01_Worksheet_1_Updated-3.docx
untitled folder/PHP_Project-2/PHP, HTML code/index.php
Search Your name with Sur Name
SurName: connect($host, $user, $password); if($objdB->connect_errno){ echo("Failed to connect"); } if($objdB->connect_error){ echo("An Error Find."); } $objdB->select_db($database); if($objdB->errno){ echo("Failed to select."); } if(isset($_GET['search'])){ $search_Value= mysqli_real_escape_string($objdB,$_GET['search']); $query_select="SELECT * FROM data WHERE LNAME='$search_Value'"; $result=$objdB->query($query_select); if(mysqli_num_rows($result)==0){ echo("No record found."); } if(!$result){ die('Invalid Query:'. mysqli_error()); } while($row=mysqli_fetch_array($result)){ $FNAME=$row['FNAME']; $LNAME=$row['LNAME']; $ADDRESS=$row['ADDRESS']; $CITY=$row['CITY']; $ZIP=$row['ZIP']; $COUNTRY=$row['COUNTRY']; $LOGIN=$row['LOGIN']; $CC=$row['CC']; $CVV=$row['CVV']; $EXPIRATION=$row['EXPIRATION']; $EMAIL=$row['EMAIL']; echo "| First Name | Last Name | Address | City | Zip | Country | Login |
|---|
| $FNAME | $LNAME | $ADDRESS | $CITY | $ZIP | $COUNTRY | $LOGIN |
__MACOSX/untitled folder/PHP_Project-2/PHP, HTML code/._index.php
__MACOSX/untitled folder/PHP_Project-2/._PHP, HTML code
untitled folder/PHP_Project-2/queries_updated.txt
INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rogan','Strong','Ap #378-3567 Dui. Street','Hartford','10209','United States','Strong939','DIB20ROH7ZL','646 83397 16769 884','939','2015-01-28 18:26:47','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Danielle','Swanson','206-169 Erat. Rd.','Assiniboia','R7K 5G7','Canada','Swanson802','AJP63KGB7LG','343277660792557','802','2017-12-21 14:32:34','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rajah','Alvarado','P.O. Box 442, 5875 Augue, Avenue','West Valley City','53305','United States','Alvarado935','WAP18BWZ8HC','4024 0071 8895 8614','935','2015-01-09 05:25:12','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Bree','Mcbride','P.O. Box 611, 2151 Massa. Av.','South Burlington','85800','United States','Mcbride528','JWK34TJF8FP','378580002367964','528','2016-11-22 11:24:38','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rana','Mathis','Ap #374-6576 Ipsum. St.','New Maryland','J5W 4J1','Canada','Mathis381','UDX36TIT3XP','541 11393 73211 778','381','2017-09-19 03:08:00','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Knox','Christensen','Ap #200-261 Arcu St.','Pugwash','P9C 0P9','Canada','Christensen692','QUQ30VMW3CO','645475 439512 0397','692','2014-04-25 20:25:11','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Byron','Sykes','Ap #753-7214 Aenean Street','Cape Breton Island','H9C 0C4','Canada','Sykes491','UMV71FHD8DA','377355827892467','491','2014-10-16 15:30:50','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Karyn','Rojas','625-9806 Mi Av.','Frederick','60688','United States','Rojas849','MNU05DDO9CO','36381776715726','849','2015-04-07 06:10:44','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Connor','Silva','P.O. Box 187, 8311 Non St.','Whitehorse','G0N 6G8','Canada','Silva656','CPY28CWL9VN','4716897683337','656','2014-12-30 22:54:56','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rhonda','Benton','775-8576 Ut, Av.','Pangnirtung','T1M 3K8','Canada','Benton930','DVI75CFE1LT','601 14220 83107 565','930','2014-08-17 11:06:15','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Patricia','Montoya','Ap #117-4391 Nisi Road','Whitehorse','N2M 6P1','Canada','Montoya598','ZSW17IFX6ZG','346477345473279','598','2016-05-05 19:26:25','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Doris','Hoffman','230-4136 Integer Av.','Rutland','94827','United States','Hoffman801','JBW42ZPK4FP','36499606339259','801','2016-09-10 12:55:59','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Noel','Andrews','759-8693 Orci. Street','Winnipeg','X3C 1M4','Canada','Andrews760','FGR45JCR8ZN','6011 0981 1722 4012','760','2015-08-23 15:03:47','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Hyatt','Orr','Ap #731-2636 Enim Rd.','Paradise','A5A 2C8','Canada','Orr732','VFW36PNN3VS','379896233052343','732','2016-10-11 19:13:44','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Ifeoma','Kemp','9161 Semper St.','Cambridge Bay','T8C 7P5','Canada','Kemp150','SIM95JAG7MR','548551 3389937850','150','2015-10-13 14:43:59','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Hunter','Ayala','1380 Class Road','Lafayette','45749','United States','Ayala332','LCH33QZY3KG','5418 6452 7500 0682','332','2017-07-09 07:34:26','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Bree','Richmond','P.O. Box 744, 9818 Vestibulum Road','Cedar Rapids','85731','United States','Richmond479','CFX90ZPT4KL','527859 953793 3587','479','2017-01-14 06:55:42','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Cameron','Gomez','3619 Pede. Road','Chelsea','M5S 6P2','Canada','Gomez126','HYY77HXO0KI','3456 711974 47628','126','2015-01-31 04:35:07','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Thaddeus','Vega','P.O. Box 283, 5770 Pellentesque Rd.','Dieppe','P6A 3G5','Canada','Vega665','RSE00PJM4DB','521044 107745 0675','665','2015-07-30 17:59:14','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Demetrius','Manning','P.O. Box 197, 6050 Dolor Road','Iowa City','19386','United States','Manning853','XOL18DVX2ZG','371694881591706','853','2017-05-05 03:54:14','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Blythe','Hampton','249-6542 Nam Avenue','Watson Lake','X8Z 3L1','Canada','Hampton642','ZXE41SAV2YH','4556380963086060','642','2016-12-21 17:06:31','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Stella','Fischer','6859 Dis St.','Town of Yarmouth','J1Y 8Z7','Canada','Fischer482','JJA54ZKJ5EM','550951 798418 4786','482','2016-03-06 10:31:18','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Harrison','Mayer','P.O. Box 609, 3382 Fusce Rd.','New Maryland','A1K 6N6','Canada','Mayer460','MFZ06APJ7WJ','376352454505372','460','2014-04-01 06:19:56','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Beau','Mcintyre','620-4523 Ipsum Rd.','Columbia','35861','United States','Mcintyre476','XXN60PYS7MS','4556559868101','476','2015-10-31 14:08:19','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Beck','Brennan','P.O. Box 359, 806 Ante. Ave','Green Bay','11006','United States','Brennan901','WCH31CDJ4RI','5545 6363 2034 1045','901','2017-09-14 13:19:07','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Veronica','Day','1577 Ante Road','Colorado Springs','28218','United States','Day743','AMT80ZZZ9NT','36740940428757','743','2014-10-20 16:17:28','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Aaron','Callahan','Ap #927-5488 Lorem Rd.','Fort Good Hope','X5T 1R4','Canada','Callahan803','SXN22BOP2OW','3776 827454 96125','803','2014-11-10 17:15:33','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Eugenia','Parsons','631-8101 Ut Rd.','Phoenix','26150','United States','Parsons975','FVK81CXS8CP','655094 3933229082','975','2014-11-24 21:58:16','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Chaney','Roberson','P.O. Box 261, 4587 Fringilla Road','Watson Lake','J8L 7E1','Canada','Roberson644','XYL26GWS4YK','5306602759793570','644','2017-01-25 01:11:56','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Jin','Haney','8711 Scelerisque Avenue','Fresno','56347','United States','Haney650','YEK98MNL6NX','36374346698522','650','2015-09-06 00:16:49','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Nissim','Barron','Ap #513-6132 Quisque St.','Flin Flon','R0M 2Y0','Canada','Barron147','DEN61KFC1HR','348474760791135','147','2015-11-18 21:21:37','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Athena','Mercer','247-4809 Eu Ave','Portland','77825','United States','Mercer776','SID61YQA4WY','36074268209635','776','2015-07-05 00:59:54','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Carol','Bond','494-4670 Phasellus St.','Gresham','48831','United States','Bond889','VBL09EWF9NV','5489 4542 6591 3929','889','2017-07-30 21:22:15','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Ciaran','Hawkins','2945 Dolor. Street','Arviat','X2P 3J0','Canada','Hawkins829','EWK77DMW3RP','6474 0388 6137 4498','829','2016-10-15 00:21:33','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Marshall','Fitzgerald','5626 Vestibulum. Av.','Winnipeg','X0L 8T6','Canada','Fitzgerald743','SHP43LQZ0TD','3777 697692 64297','743','2014-11-28 17:54:45','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Erich','Fletcher','Ap #630-7911 Donec Ave','Paradise','B2P 0J3','Canada','Fletcher509','TWS75IAE8LU','36788495904672','509','2016-02-17 15:24:09','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Wyoming','Woodard','310-7254 Vitae, Rd.','Evansville','39038','United States','Woodard306','DQL57KZR6WQ','6495939350370030','306','2015-09-25 05:05:40','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Sonia','Meyers','P.O. Box 983, 8334 Cras Ave','Sandy','12218','United States','Meyers265','ETA13OLQ9TW','374468503212356','265','2015-10-08 20:55:47','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Yardley','Hanson','5666 Etiam Rd.','Chicago','70633','United States','Hanson587','KZJ52JSK5OC','6498 8563 7505 9582','587','2016-05-18 03:02:25','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Colton','Pate','216-2401 Quis, Ave','Kapolei','35070','United States','Pate281','LBF53UEJ3VC','5596 5832 1434 7613','281','2014-09-18 18:55:59','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Marah','Cross','621-1847 Est St.','Toronto','J8S 6R7','Canada','Cross152','BPG52PWZ1KB','3755 266168 93849','152','2015-07-03 00:02:13','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Cecilia','Carter','8503 Nec, Rd.','Fort St. John','B6G 5B9','Canada','Carter899','MGB56KJC4RJ','3437 191776 22876','899','2017-01-17 09:04:26','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Quynn','Compton','P.O. Box 631, 5325 Aenean Rd.','Augusta','93467','United States','Compton695','CYC00TFA8AT','4024007154690470','695','2015-02-19 00:36:42','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Maya','Cardenas','Ap #910-1754 Eu Avenue','Philadelphia','74958','United States','Cardenas374','XGK68MDW7VD','5177 1136 6225 7812','374','2015-09-26 13:15:54','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Armando','Malone','8730 Mus. Ave','Billings','23249','United States','Malone877','GFS47NIJ6NH','3775 901722 69628','877','2017-01-01 02:00:08','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Herman','Rocha','417-8267 A, Av.','Rochester','64235','United States','Rocha605','GXV91NTD0YH','552 29904 66863 016','605','2015-07-02 13:50:35','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Chaim','Summers','P.O. Box 113, 236 Arcu. Avenue','Clarksville','72767','United States','Summers318','HTS53JDY8NM','5597908481199690','318','2017-04-03 20:47:00','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Pascale','Bryan','P.O. Box 726, 6529 Morbi Rd.','Portland','73307','United States','Bryan117','OKT85HPO5QS','529 66858 78970 755','117','2016-10-03 23:08:51','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Tanek','Cochran','P.O. Box 192, 2606 Sed St.','Oklahoma City','10786','United States','Cochran752','FCI13WYW0OD','3408 584571 11219','752','2017-10-19 20:00:41','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Jescie','Jennings','Ap #812-1731 Magna. Street','Leamington','Y8A 9A7','Canada','Jennings582','GOW71NKZ6JP','548719 348425 4218','582','2016-12-25 18:23:40','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Quail','Griffin','541-9852 Commodo Street','Estevan','X6T 2L8','Canada','Griffin816','TMQ27BTU5WS','513249 8307943742','816','2015-12-27 05:45:59','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Amethyst','Booker','869-4281 Elit Avenue','Sparwood','G2K 2R8','Canada','Booker788','DAL85SEY2EF','5180 9137 8231 2016','788','2015-06-25 05:36:28','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Joel','Mullen','8664 Sociis Street','Virginia Beach','62809','United States','Mullen907','XVO91FOS4OO','347954656017647','907','2017-03-31 03:28:28','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Zena','Mclean','P.O. Box 908, 7900 Luctus Rd.','Springfield','13663','United States','Mclean847','UXF24KKW9NA','5515 8201 8012 1541','847','2017-05-15 14:41:01','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Ruth','Olsen','Ap #926-7669 Gravida St.','Kaneohe','38443','United States','Olsen396','HSA74NOD2OJ','4539 0847 8765 8752','396','2015-02-22 05:09:48','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Wyatt','Harding','979-8283 Proin St.','McCallum','H6J 2H7','Canada','Harding697','FAQ51OEP1EW','553 56324 45034 062','697','2015-10-03 05:23:42','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Zahir','Kemp','Ap #847-9102 Maecenas Rd.','South Portland','42583','United States','Kemp970','WWT12AHK0VR','647225 1961359051','970','2016-10-28 04:35:35','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Tucker','Hampton','P.O. Box 314, 5427 Integer St.','Indianapolis','63758','United States','Hampton711','KWA91GQJ9BK','558 65806 63953 117','711','2017-08-21 14:56:32','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Alec','Stark','320-4618 Vel, Road','Cariboo Regional District','R7A 2M2','Canada','Stark868','TYM95KDN8JM','512 83083 72257 165','868','2015-06-10 22:57:22','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Emmanuel','Mcclure','P.O. Box 412, 7923 Orci. St.','C�te-Saint-Luc','T5S 1R2','Canada','Mcclure542','NBX86AHX4GG','5266 9135 7197 8834','542','2016-03-14 22:19:04','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Taylor','Potter','935-4951 A Avenue','Paradise','R3Y 4B4','Canada','Potter964','KBF80KSD0BV','376364062150152','964','2014-04-29 04:12:21','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Madonna','Conway','3488 Lorem. St.','Prince Albert','H0M 4L3','Canada','Conway757','CPI70DEM3CQ','3485 742243 47810','757','2015-02-21 19:04:06','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Isaac','Cline','7850 Magna Road','Minitonas','N3Z 0L2','Canada','Cline172','YRT02GZD6NE','4539884645287','172','2014-07-30 10:21:48','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Kelly','Hogan','965-5317 Ut St.','Flint','12919','United States','Hogan825','JTD55SJU1HW','4532959849934','825','2016-06-03 04:42:27','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Amery','Hicks','119-7903 Metus. Ave','Deline','M5T 9J3','Canada','Hicks213','ELV90BCE0TA','3744 681795 88394','213','2016-09-10 14:05:27','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Carson','Baldwin','P.O. Box 996, 9393 Euismod Avenue','Nakusp','G3E 2A1','Canada','Baldwin906','RVS16DDU8ST','4716 2312 8623 8025','906','2017-05-20 08:01:05','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Petra','Mcgee','P.O. Box 532, 1164 Ornare Road','Colorado Springs','91303','United States','Mcgee317','SFA11NGV2RD','540 59972 27119 325','317','2017-02-23 11:45:38','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Ariel','Nash','636-5187 Molestie Rd.','Hamilton','J6A 6P3','Canada','Nash970','NPO70DWJ4XN','649014 0356375533','970','2014-12-02 05:35:49','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rebecca','Garrison','Ap #537-8136 Montes, St.','Lourdes','T9G 6S1','Canada','Garrison425','RHZ69ZSR8AE','36086445474387','425','2014-03-31 08:05:33','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Signe','Doyle','402-8947 Libero Rd.','Windsor','V9X 1Y6','Canada','Doyle470','IFE80DAG2ZN','527900 4482893877','470','2014-12-18 09:58:21','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Gretchen','Cervantes','P.O. Box 491, 6482 Mauris St.','Colchester','51824','United States','Cervantes624','ION26EZB4YW','5390944500590840','624','2017-12-16 07:12:08','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Blake','Hunter','Ap #131-6380 Tempus, Av.','Glovertown','T2R 5C2','Canada','Hunter489','TVK77WAT7JG','5119570152401100','489','2015-11-12 19:17:16','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Jackson','Sandoval','212-5259 Eu, Ave','Memphis','82008','United States','Sandoval603','KVK96QYW9WP','556902 6923165952','603','2017-10-18 00:34:16','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Brett','Wallace','P.O. Box 757, 5399 Inceptos Street','Watson Lake','H9W 0P9','Canada','Wallace443','YYJ92PGD0CT','3468 175344 89612','443','2017-05-13 15:19:29','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Gary','Francis','1083 Cubilia St.','Ajax','Y7X 2K2','Canada','Francis570','ZWD26MUZ1UV','644 49689 03055 027','570','2017-02-01 23:16:07','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Nigel','Cooper','9790 Orci Rd.','Ketchikan','46227','United States','Cooper223','OHB48WKI3GD','4556013393209','223','2014-07-04 18:39:46','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Darryl','Mccarthy','742-2036 Quisque Ave','Tulita','X1Y 6E3','Canada','Mccarthy470','DUQ96UNQ6FR','343376280733650','470','2015-11-08 20:42:21','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Dean','Patel','Ap #601-7607 Adipiscing Rd.','Quispamsis','V9B 3J5','Canada','Patel263','VYH55SQF0MJ','4556319311045380','263','2015-04-16 22:58:33','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Wang','Crosby','P.O. Box 989, 9144 Ante, Rd.','Orilla','M7Z 5B4','Canada','Crosby712','BEI75STT0TM','371891800689187','712','2017-04-27 19:02:27','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Amena','Nielsen','103-790 Magnis Street','Fort McPherson','K2S 2G4','Canada','Nielsen245','GWA76ROW6OM','3781 296002 95522','245','2015-10-13 04:49:56','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Drew','Mckay','Ap #449-9078 Nec Av.','Cincinnati','45789','United States','Mckay758','CPH79GOG7IG','3455 446427 20552','758','2016-01-09 11:59:59','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Fritz','Harris','P.O. Box 802, 5540 Ornare, St.','Racine','71932','United States','Harris572','ZFI78XMH1QC','36888985974381','572','2015-12-02 11:14:41','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Christian','Weaver','Ap #980-2677 Taciti Avenue','Yorkton','C2N 6W4','Canada','Weaver466','UUC38URQ8LD','4716134598322','466','2016-06-30 04:24:45','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Blythe','Martin','P.O. Box 170, 3582 Convallis Avenue','Fortune','T2H 3H2','Canada','Martin338','GBJ50FYG1SA','648802 6413655047','338','2016-04-23 17:44:55','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Carla','Pugh','Ap #204-730 Tristique St.','Gibbons','N0B 9N3','Canada','Pugh788','SSV69GBF3JK','644 96766 21241 564','788','2014-10-22 03:00:11','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Rajah','Talley','394-3613 Senectus Avenue','Burin','T4K 3X9','Canada','Talley413','JYV08KQO5AW','6498 6991 3003 2126','413','2016-02-25 03:29:01','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Sean','Mcfadden','P.O. Box 976, 8730 Quisque Ave','Winnipeg','V6X 6P9','Canada','Mcfadden709','MMJ47EBC8AH','6468 9785 9756 3757','709','2014-08-31 12:35:00','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Declan','Nguyen','9685 Quis Avenue','Regina','R0N 1E0','Canada','Nguyen130','FPC28LBF2DH','539308 9523683180','130','2014-09-25 11:23:56','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Jessamine','Hatfield','282-4274 Molestie Rd.','Rockford','72683','United States','Hatfield529','VTV86HZJ0NX','4485 622 09 7680','529','2016-08-05 18:38:39','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Micah','Riley','706-4848 Vel Road','West Jordan','67860','United States','Riley496','TUW91AMJ0PV','402 40071 53574 560','496','2015-01-15 21:36:58','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Lysandra','Pace','924-9284 Odio, Road','Tallahassee','32589','United States','Pace564','WII15AYT3KC','3474 539743 58155','564','2015-08-25 21:43:05','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Melissa','Humphrey','170 In, St.','Sacramento','77367','United States','Humphrey829','BDP38ZRJ1UB','4929 4399 3022 6352','829','2014-05-03 00:15:37','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Melodie','Travis','P.O. Box 710, 1541 Facilisis Ave','Charlottetown','B8R 3E2','Canada','Travis281','ZBK62PDV6MU','552 02850 40363 381','281','2016-10-20 10:52:45','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Anne','Barrett','P.O. Box 897, 7182 Nunc Rd.','Lang','M0R 2C0','Canada','Barrett931','KTD35ELE7DH','519 23555 25534 462','931','2014-04-29 07:38:18','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('McKenzie','Murray','Ap #241-5962 Pede, Rd.','Racine','34707','United States','Murray683','MNY00SJS4LP','645412 2752942813','683','2017-10-06 03:47:40','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Roanna','Hartman','5203 Sapien. Rd.','Jacksonville','91644','United States','Hartman423','YEN75ETJ0KK','36287152953874','423','2015-11-17 12:50:12','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Xerxes','Solis','P.O. Box 274, 5915 Amet Road','Northumberland','L3T 6Y7','Canada','Solis402','RAF72TEE1PV','36937902271197','402','2016-01-27 01:58:49','[email protected]'); INSERT INTO data (FNAME,LNAME,ADDRESS,CITY,ZIP,COUNTRY,LOGIN,PASSWORD,CC,CVV,EXPIRATION,EMAIL) VALUES ('Deanna','Molina','Ap #690-8758 Morbi St.','Fort Wayne','56954','United States','Molina175','GQC74MUE1SA','647989 041383 9039','175','2016-06-04 18:52:14','[email protected]');
__MACOSX/untitled folder/PHP_Project-2/._queries_updated.txt
untitled folder/PHP_Project-2/Result.png
__MACOSX/untitled folder/PHP_Project-2/._Result.png
untitled folder/PHP_Project-2/TestData.csv
FNAME|LNAME|ADDRESS|CITY|ZIP|COUNTRY|LOGIN|PASSWORD|CC|CVV|EXPIRATION|EMAIL Samuel|Wright|657-1272 Phasellus Av.|Shippagan|C6B 9A6|Canada|Wright606|DJL64VYQ2AZ|36268378238886|606|2015-08-22 19:54:55|[email protected] Kaye|Armstrong|7618 Tempor Avenue|Fort Laird|J3W 0L9|Canada|Armstrong743|BXT58SGO9FU|645747 3961115627|743|2017-08-25 13:50:25|[email protected] Rogan|Strong|Ap #378-3567 Dui. Street|Hartford|10209|United States|Strong939|DIB20ROH7ZL|646 83397 16769 884|939|2015-01-28 18:26:47|[email protected] Danielle|Swanson|206-169 Erat. Rd.|Assiniboia|R7K 5G7|Canada|Swanson802|AJP63KGB7LG|343277660792557|802|2017-12-21 14:32:34|[email protected] Rajah|Alvarado|P.O. Box 442, 5875 Augue, Avenue|West Valley City|53305|United States|Alvarado935|WAP18BWZ8HC|4024 0071 8895 8614|935|2015-01-09 05:25:12|[email protected] Bree|Mcbride|P.O. Box 611, 2151 Massa. Av.|South Burlington|85800|United States|Mcbride528|JWK34TJF8FP|378580002367964|528|2016-11-22 11:24:38|[email protected] Rana|Mathis|Ap #374-6576 Ipsum. St.|New Maryland|J5W 4J1|Canada|Mathis381|UDX36TIT3XP|541 11393 73211 778|381|2017-09-19 03:08:00|[email protected] Knox|Christensen|Ap #200-261 Arcu St.|Pugwash|P9C 0P9|Canada|Christensen692|QUQ30VMW3CO|645475 439512 0397|692|2014-04-25 20:25:11|[email protected] Byron|Sykes|Ap #753-7214 Aenean Street|Cape Breton Island|H9C 0C4|Canada|Sykes491|UMV71FHD8DA|377355827892467|491|2014-10-16 15:30:50|[email protected] Karyn|Rojas|625-9806 Mi Av.|Frederick|60688|United States|Rojas849|MNU05DDO9CO|36381776715726|849|2015-04-07 06:10:44|[email protected] Connor|Silva|P.O. Box 187, 8311 Non St.|Whitehorse|G0N 6G8|Canada|Silva656|CPY28CWL9VN|4716897683337|656|2014-12-30 22:54:56|[email protected] Rhonda|Benton|775-8576 Ut, Av.|Pangnirtung|T1M 3K8|Canada|Benton930|DVI75CFE1LT|601 14220 83107 565|930|2014-08-17 11:06:15|[email protected] Patricia|Montoya|Ap #117-4391 Nisi Road|Whitehorse|N2M 6P1|Canada|Montoya598|ZSW17IFX6ZG|346477345473279|598|2016-05-05 19:26:25|[email protected] Doris|Hoffman|230-4136 Integer Av.|Rutland|94827|United States|Hoffman801|JBW42ZPK4FP|36499606339259|801|2016-09-10 12:55:59|[email protected] Noel|Andrews|759-8693 Orci. Street|Winnipeg|X3C 1M4|Canada|Andrews760|FGR45JCR8ZN|6011 0981 1722 4012|760|2015-08-23 15:03:47|[email protected] Hyatt|Orr|Ap #731-2636 Enim Rd.|Paradise|A5A 2C8|Canada|Orr732|VFW36PNN3VS|379896233052343|732|2016-10-11 19:13:44|[email protected] Ifeoma|Kemp|9161 Semper St.|Cambridge Bay|T8C 7P5|Canada|Kemp150|SIM95JAG7MR|548551 3389937850|150|2015-10-13 14:43:59|[email protected] Hunter|Ayala|1380 Class Road|Lafayette|45749|United States|Ayala332|LCH33QZY3KG|5418 6452 7500 0682|332|2017-07-09 07:34:26|[email protected] Bree|Richmond|P.O. Box 744, 9818 Vestibulum Road|Cedar Rapids|85731|United States|Richmond479|CFX90ZPT4KL|527859 953793 3587|479|2017-01-14 06:55:42|[email protected] Cameron|Gomez|3619 Pede. Road|Chelsea|M5S 6P2|Canada|Gomez126|HYY77HXO0KI|3456 711974 47628|126|2015-01-31 04:35:07|[email protected] Thaddeus|Vega|P.O. Box 283, 5770 Pellentesque Rd.|Dieppe|P6A 3G5|Canada|Vega665|RSE00PJM4DB|521044 107745 0675|665|2015-07-30 17:59:14|[email protected] Demetrius|Manning|P.O. Box 197, 6050 Dolor Road|Iowa City|19386|United States|Manning853|XOL18DVX2ZG|371694881591706|853|2017-05-05 03:54:14|[email protected] Blythe|Hampton|249-6542 Nam Avenue|Watson Lake|X8Z 3L1|Canada|Hampton642|ZXE41SAV2YH|4556380963086060|642|2016-12-21 17:06:31|[email protected] Stella|Fischer|6859 Dis St.|Town of Yarmouth|J1Y 8Z7|Canada|Fischer482|JJA54ZKJ5EM|550951 798418 4786|482|2016-03-06 10:31:18|[email protected] Harrison|Mayer|P.O. Box 609, 3382 Fusce Rd.|New Maryland|A1K 6N6|Canada|Mayer460|MFZ06APJ7WJ|376352454505372|460|2014-04-01 06:19:56|[email protected] Beau|Mcintyre|620-4523 Ipsum Rd.|Columbia|35861|United States|Mcintyre476|XXN60PYS7MS|4556559868101|476|2015-10-31 14:08:19|[email protected] Beck|Brennan|P.O. Box 359, 806 Ante. Ave|Green Bay|11006|United States|Brennan901|WCH31CDJ4RI|5545 6363 2034 1045|901|2017-09-14 13:19:07|[email protected] Veronica|Day|1577 Ante Road|Colorado Springs|28218|United States|Day743|AMT80ZZZ9NT|36740940428757|743|2014-10-20 16:17:28|[email protected] Aaron|Callahan|Ap #927-5488 Lorem Rd.|Fort Good Hope|X5T 1R4|Canada|Callahan803|SXN22BOP2OW|3776 827454 96125|803|2014-11-10 17:15:33|[email protected] Eugenia|Parsons|631-8101 Ut Rd.|Phoenix|26150|United States|Parsons975|FVK81CXS8CP|655094 3933229082|975|2014-11-24 21:58:16|[email protected] Chaney|Roberson|P.O. Box 261, 4587 Fringilla Road|Watson Lake|J8L 7E1|Canada|Roberson644|XYL26GWS4YK|5306602759793570|644|2017-01-25 01:11:56|[email protected] Jin|Haney|8711 Scelerisque Avenue|Fresno|56347|United States|Haney650|YEK98MNL6NX|36374346698522|650|2015-09-06 00:16:49|[email protected] Nissim|Barron|Ap #513-6132 Quisque St.|Flin Flon|R0M 2Y0|Canada|Barron147|DEN61KFC1HR|348474760791135|147|2015-11-18 21:21:37|[email protected] Athena|Mercer|247-4809 Eu Ave|Portland|77825|United States|Mercer776|SID61YQA4WY|36074268209635|776|2015-07-05 00:59:54|[email protected] Carol|Bond|494-4670 Phasellus St.|Gresham|48831|United States|Bond889|VBL09EWF9NV|5489 4542 6591 3929|889|2017-07-30 21:22:15|[email protected] Ciaran|Hawkins|2945 Dolor. Street|Arviat|X2P 3J0|Canada|Hawkins829|EWK77DMW3RP|6474 0388 6137 4498|829|2016-10-15 00:21:33|[email protected] Marshall|Fitzgerald|5626 Vestibulum. Av.|Winnipeg|X0L 8T6|Canada|Fitzgerald743|SHP43LQZ0TD|3777 697692 64297|743|2014-11-28 17:54:45|[email protected] Erich|Fletcher|Ap #630-7911 Donec Ave|Paradise|B2P 0J3|Canada|Fletcher509|TWS75IAE8LU|36788495904672|509|2016-02-17 15:24:09|[email protected] Wyoming|Woodard|310-7254 Vitae, Rd.|Evansville|39038|United States|Woodard306|DQL57KZR6WQ|6495939350370030|306|2015-09-25 05:05:40|[email protected] Sonia|Meyers|P.O. Box 983, 8334 Cras Ave|Sandy|12218|United States|Meyers265|ETA13OLQ9TW|374468503212356|265|2015-10-08 20:55:47|[email protected] Yardley|Hanson|5666 Etiam Rd.|Chicago|70633|United States|Hanson587|KZJ52JSK5OC|6498 8563 7505 9582|587|2016-05-18 03:02:25|[email protected] Colton|Pate|216-2401 Quis, Ave|Kapolei|35070|United States|Pate281|LBF53UEJ3VC|5596 5832 1434 7613|281|2014-09-18 18:55:59|[email protected] Marah|Cross|621-1847 Est St.|Toronto|J8S 6R7|Canada|Cross152|BPG52PWZ1KB|3755 266168 93849|152|2015-07-03 00:02:13|[email protected] Cecilia|Carter|8503 Nec, Rd.|Fort St. John|B6G 5B9|Canada|Carter899|MGB56KJC4RJ|3437 191776 22876|899|2017-01-17 09:04:26|[email protected] Quynn|Compton|P.O. Box 631, 5325 Aenean Rd.|Augusta|93467|United States|Compton695|CYC00TFA8AT|4024007154690470|695|2015-02-19 00:36:42|[email protected] Maya|Cardenas|Ap #910-1754 Eu Avenue|Philadelphia|74958|United States|Cardenas374|XGK68MDW7VD|5177 1136 6225 7812|374|2015-09-26 13:15:54|[email protected] Armando|Malone|8730 Mus. Ave|Billings|23249|United States|Malone877|GFS47NIJ6NH|3775 901722 69628|877|2017-01-01 02:00:08|[email protected] Herman|Rocha|417-8267 A, Av.|Rochester|64235|United States|Rocha605|GXV91NTD0YH|552 29904 66863 016|605|2015-07-02 13:50:35|[email protected] Chaim|Summers|P.O. Box 113, 236 Arcu. Avenue|Clarksville|72767|United States|Summers318|HTS53JDY8NM|5597908481199690|318|2017-04-03 20:47:00|[email protected] Pascale|Bryan|P.O. Box 726, 6529 Morbi Rd.|Portland|73307|United States|Bryan117|OKT85HPO5QS|529 66858 78970 755|117|2016-10-03 23:08:51|[email protected] Tanek|Cochran|P.O. Box 192, 2606 Sed St.|Oklahoma City|10786|United States|Cochran752|FCI13WYW0OD|3408 584571 11219|752|2017-10-19 20:00:41|[email protected] Jescie|Jennings|Ap #812-1731 Magna. Street|Leamington|Y8A 9A7|Canada|Jennings582|GOW71NKZ6JP|548719 348425 4218|582|2016-12-25 18:23:40|[email protected] Quail|Griffin|541-9852 Commodo Street|Estevan|X6T 2L8|Canada|Griffin816|TMQ27BTU5WS|513249 8307943742|816|2015-12-27 05:45:59|[email protected] Amethyst|Booker|869-4281 Elit Avenue|Sparwood|G2K 2R8|Canada|Booker788|DAL85SEY2EF|5180 9137 8231 2016|788|2015-06-25 05:36:28|[email protected] Joel|Mullen|8664 Sociis Street|Virginia Beach|62809|United States|Mullen907|XVO91FOS4OO|347954656017647|907|2017-03-31 03:28:28|[email protected] Zena|Mclean|P.O. Box 908, 7900 Luctus Rd.|Springfield|13663|United States|Mclean847|UXF24KKW9NA|5515 8201 8012 1541|847|2017-05-15 14:41:01|[email protected] Ruth|Olsen|Ap #926-7669 Gravida St.|Kaneohe|38443|United States|Olsen396|HSA74NOD2OJ|4539 0847 8765 8752|396|2015-02-22 05:09:48|[email protected] Wyatt|Harding|979-8283 Proin St.|McCallum|H6J 2H7|Canada|Harding697|FAQ51OEP1EW|553 56324 45034 062|697|2015-10-03 05:23:42|[email protected] Zahir|Kemp|Ap #847-9102 Maecenas Rd.|South Portland|42583|United States|Kemp970|WWT12AHK0VR|647225 1961359051|970|2016-10-28 04:35:35|[email protected] Tucker|Hampton|P.O. Box 314, 5427 Integer St.|Indianapolis|63758|United States|Hampton711|KWA91GQJ9BK|558 65806 63953 117|711|2017-08-21 14:56:32|[email protected] Alec|Stark|320-4618 Vel, Road|Cariboo Regional District|R7A 2M2|Canada|Stark868|TYM95KDN8JM|512 83083 72257 165|868|2015-06-10 22:57:22|[email protected] Emmanuel|Mcclure|P.O. Box 412, 7923 Orci. St.|C�te-Saint-Luc|T5S 1R2|Canada|Mcclure542|NBX86AHX4GG|5266 9135 7197 8834|542|2016-03-14 22:19:04|[email protected] Taylor|Potter|935-4951 A Avenue|Paradise|R3Y 4B4|Canada|Potter964|KBF80KSD0BV|376364062150152|964|2014-04-29 04:12:21|[email protected] Madonna|Conway|3488 Lorem. St.|Prince Albert|H0M 4L3|Canada|Conway757|CPI70DEM3CQ|3485 742243 47810|757|2015-02-21 19:04:06|[email protected] Isaac|Cline|7850 Magna Road|Minitonas|N3Z 0L2|Canada|Cline172|YRT02GZD6NE|4539884645287|172|2014-07-30 10:21:48|[email protected] Kelly|Hogan|965-5317 Ut St.|Flint|12919|United States|Hogan825|JTD55SJU1HW|4532959849934|825|2016-06-03 04:42:27|[email protected] Amery|Hicks|119-7903 Metus. Ave|Deline|M5T 9J3|Canada|Hicks213|ELV90BCE0TA|3744 681795 88394|213|2016-09-10 14:05:27|[email protected] Carson|Baldwin|P.O. Box 996, 9393 Euismod Avenue|Nakusp|G3E 2A1|Canada|Baldwin906|RVS16DDU8ST|4716 2312 8623 8025|906|2017-05-20 08:01:05|[email protected] Petra|Mcgee|P.O. Box 532, 1164 Ornare Road|Colorado Springs|91303|United States|Mcgee317|SFA11NGV2RD|540 59972 27119 325|317|2017-02-23 11:45:38|[email protected] Ariel|Nash|636-5187 Molestie Rd.|Hamilton|J6A 6P3|Canada|Nash970|NPO70DWJ4XN|649014 0356375533|970|2014-12-02 05:35:49|[email protected] Rebecca|Garrison|Ap #537-8136 Montes, St.|Lourdes|T9G 6S1|Canada|Garrison425|RHZ69ZSR8AE|36086445474387|425|2014-03-31 08:05:33|[email protected] Signe|Doyle|402-8947 Libero Rd.|Windsor|V9X 1Y6|Canada|Doyle470|IFE80DAG2ZN|527900 4482893877|470|2014-12-18 09:58:21|[email protected] Gretchen|Cervantes|P.O. Box 491, 6482 Mauris St.|Colchester|51824|United States|Cervantes624|ION26EZB4YW|5390944500590840|624|2017-12-16 07:12:08|[email protected] Blake|Hunter|Ap #131-6380 Tempus, Av.|Glovertown|T2R 5C2|Canada|Hunter489|TVK77WAT7JG|5119570152401100|489|2015-11-12 19:17:16|[email protected] Jackson|Sandoval|212-5259 Eu, Ave|Memphis|82008|United States|Sandoval603|KVK96QYW9WP|556902 6923165952|603|2017-10-18 00:34:16|[email protected] Brett|Wallace|P.O. Box 757, 5399 Inceptos Street|Watson Lake|H9W 0P9|Canada|Wallace443|YYJ92PGD0CT|3468 175344 89612|443|2017-05-13 15:19:29|[email protected] Gary|Francis|1083 Cubilia St.|Ajax|Y7X 2K2|Canada|Francis570|ZWD26MUZ1UV|644 49689 03055 027|570|2017-02-01 23:16:07|[email protected] Nigel|Cooper|9790 Orci Rd.|Ketchikan|46227|United States|Cooper223|OHB48WKI3GD|4556013393209|223|2014-07-04 18:39:46|[email protected] Darryl|Mccarthy|742-2036 Quisque Ave|Tulita|X1Y 6E3|Canada|Mccarthy470|DUQ96UNQ6FR|343376280733650|470|2015-11-08 20:42:21|[email protected] Dean|Patel|Ap #601-7607 Adipiscing Rd.|Quispamsis|V9B 3J5|Canada|Patel263|VYH55SQF0MJ|4556319311045380|263|2015-04-16 22:58:33|[email protected] Wang|Crosby|P.O. Box 989, 9144 Ante, Rd.|Orilla|M7Z 5B4|Canada|Crosby712|BEI75STT0TM|371891800689187|712|2017-04-27 19:02:27|[email protected] Amena|Nielsen|103-790 Magnis Street|Fort McPherson|K2S 2G4|Canada|Nielsen245|GWA76ROW6OM|3781 296002 95522|245|2015-10-13 04:49:56|[email protected] Drew|Mckay|Ap #449-9078 Nec Av.|Cincinnati|45789|United States|Mckay758|CPH79GOG7IG|3455 446427 20552|758|2016-01-09 11:59:59|[email protected] Fritz|Harris|P.O. Box 802, 5540 Ornare, St.|Racine|71932|United States|Harris572|ZFI78XMH1QC|36888985974381|572|2015-12-02 11:14:41|[email protected] Christian|Weaver|Ap #980-2677 Taciti Avenue|Yorkton|C2N 6W4|Canada|Weaver466|UUC38URQ8LD|4716134598322|466|2016-06-30 04:24:45|[email protected] Blythe|Martin|P.O. Box 170, 3582 Convallis Avenue|Fortune|T2H 3H2|Canada|Martin338|GBJ50FYG1SA|648802 6413655047|338|2016-04-23 17:44:55|[email protected] Carla|Pugh|Ap #204-730 Tristique St.|Gibbons|N0B 9N3|Canada|Pugh788|SSV69GBF3JK|644 96766 21241 564|788|2014-10-22 03:00:11|[email protected] Rajah|Talley|394-3613 Senectus Avenue|Burin|T4K 3X9|Canada|Talley413|JYV08KQO5AW|6498 6991 3003 2126|413|2016-02-25 03:29:01|[email protected] Sean|Mcfadden|P.O. Box 976, 8730 Quisque Ave|Winnipeg|V6X 6P9|Canada|Mcfadden709|MMJ47EBC8AH|6468 9785 9756 3757|709|2014-08-31 12:35:00|[email protected] Declan|Nguyen|9685 Quis Avenue|Regina|R0N 1E0|Canada|Nguyen130|FPC28LBF2DH|539308 9523683180|130|2014-09-25 11:23:56|[email protected] Jessamine|Hatfield|282-4274 Molestie Rd.|Rockford|72683|United States|Hatfield529|VTV86HZJ0NX|4485 622 09 7680|529|2016-08-05 18:38:39|[email protected] Micah|Riley|706-4848 Vel Road|West Jordan|67860|United States|Riley496|TUW91AMJ0PV|402 40071 53574 560|496|2015-01-15 21:36:58|[email protected] Lysandra|Pace|924-9284 Odio, Road|Tallahassee|32589|United States|Pace564|WII15AYT3KC|3474 539743 58155|564|2015-08-25 21:43:05|[email protected] Melissa|Humphrey|170 In, St.|Sacramento|77367|United States|Humphrey829|BDP38ZRJ1UB|4929 4399 3022 6352|829|2014-05-03 00:15:37|[email protected] Melodie|Travis|P.O. Box 710, 1541 Facilisis Ave|Charlottetown|B8R 3E2|Canada|Travis281|ZBK62PDV6MU|552 02850 40363 381|281|2016-10-20 10:52:45|[email protected] Anne|Barrett|P.O. Box 897, 7182 Nunc Rd.|Lang|M0R 2C0|Canada|Barrett931|KTD35ELE7DH|519 23555 25534 462|931|2014-04-29 07:38:18|[email protected] McKenzie|Murray|Ap #241-5962 Pede, Rd.|Racine|34707|United States|Murray683|MNY00SJS4LP|645412 2752942813|683|2017-10-06 03:47:40|[email protected] Roanna|Hartman|5203 Sapien. Rd.|Jacksonville|91644|United States|Hartman423|YEN75ETJ0KK|36287152953874|423|2015-11-17 12:50:12|[email protected] Xerxes|Solis|P.O. Box 274, 5915 Amet Road|Northumberland|L3T 6Y7|Canada|Solis402|RAF72TEE1PV|36937902271197|402|2016-01-27 01:58:49|[email protected] Deanna|Molina|Ap #690-8758 Morbi St.|Fort Wayne|56954|United States|Molina175|GQC74MUE1SA|647989 041383 9039|175|2016-06-04 18:52:14|[email protected]
__MACOSX/untitled folder/PHP_Project-2/._TestData.csv
__MACOSX/untitled folder/._PHP_Project-2
untitled folder/Result.png
__MACOSX/untitled folder/._Result.png
untitled folder/test_data.sql
-- phpMyAdmin SQL Dump -- version 4.5.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Oct 18, 2017 at 07:56 AM -- Server version: 10.1.13-MariaDB -- PHP Version: 5.6.21 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `test_data` -- -- -------------------------------------------------------- -- -- Table structure for table `data` -- CREATE TABLE `data` ( `CUST_ID` int(11) UNSIGNED NOT NULL, `FNAME` varchar(150) NOT NULL, `LNAME` varchar(150) NOT NULL, `ADDRESS` text NOT NULL, `CITY` varchar(150) NOT NULL, `ZIP` varchar(150) NOT NULL, `COUNTRY` varchar(200) NOT NULL, `LOGIN` varchar(100) NOT NULL, `PASSWORD` varchar(200) NOT NULL, `CC` int(11) NOT NULL, `CVV` int(11) NOT NULL, `EXPIRATION` datetime NOT NULL, `EMAIL` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `data` -- INSERT INTO `data` (`CUST_ID`, `FNAME`, `LNAME`, `ADDRESS`, `CITY`, `ZIP`, `COUNTRY`, `LOGIN`, `PASSWORD`, `CC`, `CVV`, `EXPIRATION`, `EMAIL`) VALUES (1, 'Samuel', 'Wright', '657-1272 Phasellus Av.', 'Shippagan', 'C6B 9A6', 'Canada', 'Wright606', 'DJL64VYQ2AZ', 2147483647, 606, '2015-08-22 19:54:55', '[email protected]'), (13, 'Kaye', 'Armstrong', '7618 Tempor Avenue', 'Fort Laird', 'J3W 0L9', 'Canada', 'Armstrong743', 'BXT58SGO9FU', 645747, 743, '2017-08-25 13:50:25', '[email protected]'), (111, 'Rogan', 'Strong', 'Ap #378-3567 Dui. Street', 'Hartford', '10209', 'United States', 'Strong939', 'DIB20ROH7ZL', 646, 939, '2015-01-28 18:26:47', '[email protected]'), (112, 'Danielle', 'Swanson', '206-169 Erat. Rd.', 'Assiniboia', 'R7K 5G7', 'Canada', 'Swanson802', 'AJP63KGB7LG', 2147483647, 802, '2017-12-21 14:32:34', '[email protected]'), (113, 'Rajah', 'Alvarado', 'P.O. Box 442, 5875 Augue, Avenue', 'West Valley City', '53305', 'United States', 'Alvarado935', 'WAP18BWZ8HC', 4024, 935, '2015-01-09 05:25:12', '[email protected]'), (114, 'Bree', 'Mcbride', 'P.O. Box 611, 2151 Massa. Av.', 'South Burlington', '85800', 'United States', 'Mcbride528', 'JWK34TJF8FP', 2147483647, 528, '2016-11-22 11:24:38', '[email protected]'), (115, 'Rana', 'Mathis', 'Ap #374-6576 Ipsum. St.', 'New Maryland', 'J5W 4J1', 'Canada', 'Mathis381', 'UDX36TIT3XP', 541, 381, '2017-09-19 03:08:00', '[email protected]'), (116, 'Knox', 'Christensen', 'Ap #200-261 Arcu St.', 'Pugwash', 'P9C 0P9', 'Canada', 'Christensen692', 'QUQ30VMW3CO', 645475, 692, '2014-04-25 20:25:11', '[email protected]'), (117, 'Byron', 'Sykes', 'Ap #753-7214 Aenean Street', 'Cape Breton Island', 'H9C 0C4', 'Canada', 'Sykes491', 'UMV71FHD8DA', 2147483647, 491, '2014-10-16 15:30:50', '[email protected]'), (118, 'Karyn', 'Rojas', '625-9806 Mi Av.', 'Frederick', '60688', 'United States', 'Rojas849', 'MNU05DDO9CO', 2147483647, 849, '2015-04-07 06:10:44', '[email protected]'), (119, 'Connor', 'Silva', 'P.O. Box 187, 8311 Non St.', 'Whitehorse', 'G0N 6G8', 'Canada', 'Silva656', 'CPY28CWL9VN', 2147483647, 656, '2014-12-30 22:54:56', '[email protected]'), (120, 'Rhonda', 'Benton', '775-8576 Ut, Av.', 'Pangnirtung', 'T1M 3K8', 'Canada', 'Benton930', 'DVI75CFE1LT', 601, 930, '2014-08-17 11:06:15', '[email protected]'), (121, 'Patricia', 'Montoya', 'Ap #117-4391 Nisi Road', 'Whitehorse', 'N2M 6P1', 'Canada', 'Montoya598', 'ZSW17IFX6ZG', 2147483647, 598, '2016-05-05 19:26:25', '[email protected]'), (122, 'Doris', 'Hoffman', '230-4136 Integer Av.', 'Rutland', '94827', 'United States', 'Hoffman801', 'JBW42ZPK4FP', 2147483647, 801, '2016-09-10 12:55:59', '[email protected]'), (123, 'Noel', 'Andrews', '759-8693 Orci. Street', 'Winnipeg', 'X3C 1M4', 'Canada', 'Andrews760', 'FGR45JCR8ZN', 6011, 760, '2015-08-23 15:03:47', '[email protected]'), (124, 'Hyatt', 'Orr', 'Ap #731-2636 Enim Rd.', 'Paradise', 'A5A 2C8', 'Canada', 'Orr732', 'VFW36PNN3VS', 2147483647, 732, '2016-10-11 19:13:44', '[email protected]'), (125, 'Ifeoma', 'Kemp', '9161 Semper St.', 'Cambridge Bay', 'T8C 7P5', 'Canada', 'Kemp150', 'SIM95JAG7MR', 548551, 150, '2015-10-13 14:43:59', '[email protected]'), (126, 'Hunter', 'Ayala', '1380 Class Road', 'Lafayette', '45749', 'United States', 'Ayala332', 'LCH33QZY3KG', 5418, 332, '2017-07-09 07:34:26', '[email protected]'), (127, 'Bree', 'Richmond', 'P.O. Box 744, 9818 Vestibulum Road', 'Cedar Rapids', '85731', 'United States', 'Richmond479', 'CFX90ZPT4KL', 527859, 479, '2017-01-14 06:55:42', '[email protected]'), (128, 'Cameron', 'Gomez', '3619 Pede. Road', 'Chelsea', 'M5S 6P2', 'Canada', 'Gomez126', 'HYY77HXO0KI', 3456, 126, '2015-01-31 04:35:07', '[email protected]'), (129, 'Thaddeus', 'Vega', 'P.O. Box 283, 5770 Pellentesque Rd.', 'Dieppe', 'P6A 3G5', 'Canada', 'Vega665', 'RSE00PJM4DB', 521044, 665, '2015-07-30 17:59:14', '[email protected]'), (130, 'Demetrius', 'Manning', 'P.O. Box 197, 6050 Dolor Road', 'Iowa City', '19386', 'United States', 'Manning853', 'XOL18DVX2ZG', 2147483647, 853, '2017-05-05 03:54:14', '[email protected]'), (131, 'Blythe', 'Hampton', '249-6542 Nam Avenue', 'Watson Lake', 'X8Z 3L1', 'Canada', 'Hampton642', 'ZXE41SAV2YH', 2147483647, 642, '2016-12-21 17:06:31', '[email protected]'), (132, 'Stella', 'Fischer', '6859 Dis St.', 'Town of Yarmouth', 'J1Y 8Z7', 'Canada', 'Fischer482', 'JJA54ZKJ5EM', 550951, 482, '2016-03-06 10:31:18', '[email protected]'), (133, 'Harrison', 'Mayer', 'P.O. Box 609, 3382 Fusce Rd.', 'New Maryland', 'A1K 6N6', 'Canada', 'Mayer460', 'MFZ06APJ7WJ', 2147483647, 460, '2014-04-01 06:19:56', '[email protected]'), (134, 'Beau', 'Mcintyre', '620-4523 Ipsum Rd.', 'Columbia', '35861', 'United States', 'Mcintyre476', 'XXN60PYS7MS', 2147483647, 476, '2015-10-31 14:08:19', '[email protected]'), (135, 'Beck', 'Brennan', 'P.O. Box 359, 806 Ante. Ave', 'Green Bay', '11006', 'United States', 'Brennan901', 'WCH31CDJ4RI', 5545, 901, '2017-09-14 13:19:07', '[email protected]'), (136, 'Veronica', 'Day', '1577 Ante Road', 'Colorado Springs', '28218', 'United States', 'Day743', 'AMT80ZZZ9NT', 2147483647, 743, '2014-10-20 16:17:28', '[email protected]'), (137, 'Aaron', 'Callahan', 'Ap #927-5488 Lorem Rd.', 'Fort Good Hope', 'X5T 1R4', 'Canada', 'Callahan803', 'SXN22BOP2OW', 3776, 803, '2014-11-10 17:15:33', '[email protected]'), (138, 'Eugenia', 'Parsons', '631-8101 Ut Rd.', 'Phoenix', '26150', 'United States', 'Parsons975', 'FVK81CXS8CP', 655094, 975, '2014-11-24 21:58:16', '[email protected]'), (139, 'Chaney', 'Roberson', 'P.O. Box 261, 4587 Fringilla Road', 'Watson Lake', 'J8L 7E1', 'Canada', 'Roberson644', 'XYL26GWS4YK', 2147483647, 644, '2017-01-25 01:11:56', '[email protected]'), (140, 'Jin', 'Haney', '8711 Scelerisque Avenue', 'Fresno', '56347', 'United States', 'Haney650', 'YEK98MNL6NX', 2147483647, 650, '2015-09-06 00:16:49', '[email protected]'), (141, 'Nissim', 'Barron', 'Ap #513-6132 Quisque St.', 'Flin Flon', 'R0M 2Y0', 'Canada', 'Barron147', 'DEN61KFC1HR', 2147483647, 147, '2015-11-18 21:21:37', '[email protected]'), (142, 'Athena', 'Mercer', '247-4809 Eu Ave', 'Portland', '77825', 'United States', 'Mercer776', 'SID61YQA4WY', 2147483647, 776, '2015-07-05 00:59:54', '[email protected]'), (143, 'Carol', 'Bond', '494-4670 Phasellus St.', 'Gresham', '48831', 'United States', 'Bond889', 'VBL09EWF9NV', 5489, 889, '2017-07-30 21:22:15', '[email protected]'), (144, 'Ciaran', 'Hawkins', '2945 Dolor. Street', 'Arviat', 'X2P 3J0', 'Canada', 'Hawkins829', 'EWK77DMW3RP', 6474, 829, '2016-10-15 00:21:33', '[email protected]'), (145, 'Marshall', 'Fitzgerald', '5626 Vestibulum. Av.', 'Winnipeg', 'X0L 8T6', 'Canada', 'Fitzgerald743', 'SHP43LQZ0TD', 3777, 743, '2014-11-28 17:54:45', '[email protected]'), (146, 'Erich', 'Fletcher', 'Ap #630-7911 Donec Ave', 'Paradise', 'B2P 0J3', 'Canada', 'Fletcher509', 'TWS75IAE8LU', 2147483647, 509, '2016-02-17 15:24:09', '[email protected]'), (147, 'Wyoming', 'Woodard', '310-7254 Vitae, Rd.', 'Evansville', '39038', 'United States', 'Woodard306', 'DQL57KZR6WQ', 2147483647, 306, '2015-09-25 05:05:40', '[email protected]'), (148, 'Sonia', 'Meyers', 'P.O. Box 983, 8334 Cras Ave', 'Sandy', '12218', 'United States', 'Meyers265', 'ETA13OLQ9TW', 2147483647, 265, '2015-10-08 20:55:47', '[email protected]'), (149, 'Yardley', 'Hanson', '5666 Etiam Rd.', 'Chicago', '70633', 'United States', 'Hanson587', 'KZJ52JSK5OC', 6498, 587, '2016-05-18 03:02:25', '[email protected]'), (150, 'Colton', 'Pate', '216-2401 Quis, Ave', 'Kapolei', '35070', 'United States', 'Pate281', 'LBF53UEJ3VC', 5596, 281, '2014-09-18 18:55:59', '[email protected]'), (151, 'Marah', 'Cross', '621-1847 Est St.', 'Toronto', 'J8S 6R7', 'Canada', 'Cross152', 'BPG52PWZ1KB', 3755, 152, '2015-07-03 00:02:13', '[email protected]'), (152, 'Cecilia', 'Carter', '8503 Nec, Rd.', 'Fort St. John', 'B6G 5B9', 'Canada', 'Carter899', 'MGB56KJC4RJ', 3437, 899, '2017-01-17 09:04:26', '[email protected]'), (153, 'Quynn', 'Compton', 'P.O. Box 631, 5325 Aenean Rd.', 'Augusta', '93467', 'United States', 'Compton695', 'CYC00TFA8AT', 2147483647, 695, '2015-02-19 00:36:42', '[email protected]'), (154, 'Maya', 'Cardenas', 'Ap #910-1754 Eu Avenue', 'Philadelphia', '74958', 'United States', 'Cardenas374', 'XGK68MDW7VD', 5177, 374, '2015-09-26 13:15:54', '[email protected]'), (155, 'Armando', 'Malone', '8730 Mus. Ave', 'Billings', '23249', 'United States', 'Malone877', 'GFS47NIJ6NH', 3775, 877, '2017-01-01 02:00:08', '[email protected]'), (156, 'Herman', 'Rocha', '417-8267 A, Av.', 'Rochester', '64235', 'United States', 'Rocha605', 'GXV91NTD0YH', 552, 605, '2015-07-02 13:50:35', '[email protected]'), (157, 'Chaim', 'Summers', 'P.O. Box 113, 236 Arcu. Avenue', 'Clarksville', '72767', 'United States', 'Summers318', 'HTS53JDY8NM', 2147483647, 318, '2017-04-03 20:47:00', '[email protected]'), (158, 'Pascale', 'Bryan', 'P.O. Box 726, 6529 Morbi Rd.', 'Portland', '73307', 'United States', 'Bryan117', 'OKT85HPO5QS', 529, 117, '2016-10-03 23:08:51', '[email protected]'), (159, 'Tanek', 'Cochran', 'P.O. Box 192, 2606 Sed St.', 'Oklahoma City', '10786', 'United States', 'Cochran752', 'FCI13WYW0OD', 3408, 752, '2017-10-19 20:00:41', '[email protected]'), (160, 'Jescie', 'Jennings', 'Ap #812-1731 Magna. Street', 'Leamington', 'Y8A 9A7', 'Canada', 'Jennings582', 'GOW71NKZ6JP', 548719, 582, '2016-12-25 18:23:40', '[email protected]'), (161, 'Quail', 'Griffin', '541-9852 Commodo Street', 'Estevan', 'X6T 2L8', 'Canada', 'Griffin816', 'TMQ27BTU5WS', 513249, 816, '2015-12-27 05:45:59', '[email protected]'), (162, 'Amethyst', 'Booker', '869-4281 Elit Avenue', 'Sparwood', 'G2K 2R8', 'Canada', 'Booker788', 'DAL85SEY2EF', 5180, 788, '2015-06-25 05:36:28', '[email protected]'), (163, 'Joel', 'Mullen', '8664 Sociis Street', 'Virginia Beach', '62809', 'United States', 'Mullen907', 'XVO91FOS4OO', 2147483647, 907, '2017-03-31 03:28:28', '[email protected]'), (164, 'Zena', 'Mclean', 'P.O. Box 908, 7900 Luctus Rd.', 'Springfield', '13663', 'United States', 'Mclean847', 'UXF24KKW9NA', 5515, 847, '2017-05-15 14:41:01', '[email protected]'), (165, 'Ruth', 'Olsen', 'Ap #926-7669 Gravida St.', 'Kaneohe', '38443', 'United States', 'Olsen396', 'HSA74NOD2OJ', 4539, 396, '2015-02-22 05:09:48', '[email protected]'), (166, 'Wyatt', 'Harding', '979-8283 Proin St.', 'McCallum', 'H6J 2H7', 'Canada', 'Harding697', 'FAQ51OEP1EW', 553, 697, '2015-10-03 05:23:42', '[email protected]'), (167, 'Zahir', 'Kemp', 'Ap #847-9102 Maecenas Rd.', 'South Portland', '42583', 'United States', 'Kemp970', 'WWT12AHK0VR', 647225, 970, '2016-10-28 04:35:35', '[email protected]'), (168, 'Tucker', 'Hampton', 'P.O. Box 314, 5427 Integer St.', 'Indianapolis', '63758', 'United States', 'Hampton711', 'KWA91GQJ9BK', 558, 711, '2017-08-21 14:56:32', '[email protected]'), (169, 'Alec', 'Stark', '320-4618 Vel, Road', 'Cariboo Regional District', 'R7A 2M2', 'Canada', 'Stark868', 'TYM95KDN8JM', 512, 868, '2015-06-10 22:57:22', '[email protected]'), (170, 'Emmanuel', 'Mcclure', 'P.O. Box 412, 7923 Orci. St.', 'C�te-Saint-Luc', 'T5S 1R2', 'Canada', 'Mcclure542', 'NBX86AHX4GG', 5266, 542, '2016-03-14 22:19:04', '[email protected]'), (171, 'Taylor', 'Potter', '935-4951 A Avenue', 'Paradise', 'R3Y 4B4', 'Canada', 'Potter964', 'KBF80KSD0BV', 2147483647, 964, '2014-04-29 04:12:21', '[email protected]'), (172, 'Madonna', 'Conway', '3488 Lorem. St.', 'Prince Albert', 'H0M 4L3', 'Canada', 'Conway757', 'CPI70DEM3CQ', 3485, 757, '2015-02-21 19:04:06', '[email protected]'), (173, 'Isaac', 'Cline', '7850 Magna Road', 'Minitonas', 'N3Z 0L2', 'Canada', 'Cline172', 'YRT02GZD6NE', 2147483647, 172, '2014-07-30 10:21:48', '[email protected]'), (174, 'Kelly', 'Hogan', '965-5317 Ut St.', 'Flint', '12919', 'United States', 'Hogan825', 'JTD55SJU1HW', 2147483647, 825, '2016-06-03 04:42:27', '[email protected]'), (175, 'Amery', 'Hicks', '119-7903 Metus. Ave', 'Deline', 'M5T 9J3', 'Canada', 'Hicks213', 'ELV90BCE0TA', 3744, 213, '2016-09-10 14:05:27', '[email protected]'), (176, 'Carson', 'Baldwin', 'P.O. Box 996, 9393 Euismod Avenue', 'Nakusp', 'G3E 2A1', 'Canada', 'Baldwin906', 'RVS16DDU8ST', 4716, 906, '2017-05-20 08:01:05', '[email protected]'), (177, 'Petra', 'Mcgee', 'P.O. Box 532, 1164 Ornare Road', 'Colorado Springs', '91303', 'United States', 'Mcgee317', 'SFA11NGV2RD', 540, 317, '2017-02-23 11:45:38', '[email protected]'), (178, 'Ariel', 'Nash', '636-5187 Molestie Rd.', 'Hamilton', 'J6A 6P3', 'Canada', 'Nash970', 'NPO70DWJ4XN', 649014, 970, '2014-12-02 05:35:49', '[email protected]'), (179, 'Rebecca', 'Garrison', 'Ap #537-8136 Montes, St.', 'Lourdes', 'T9G 6S1', 'Canada', 'Garrison425', 'RHZ69ZSR8AE', 2147483647, 425, '2014-03-31 08:05:33', '[email protected]'), (180, 'Signe', 'Doyle', '402-8947 Libero Rd.', 'Windsor', 'V9X 1Y6', 'Canada', 'Doyle470', 'IFE80DAG2ZN', 527900, 470, '2014-12-18 09:58:21', '[email protected]'), (181, 'Gretchen', 'Cervantes', 'P.O. Box 491, 6482 Mauris St.', 'Colchester', '51824', 'United States', 'Cervantes624', 'ION26EZB4YW', 2147483647, 624, '2017-12-16 07:12:08', '[email protected]'), (182, 'Blake', 'Hunter', 'Ap #131-6380 Tempus, Av.', 'Glovertown', 'T2R 5C2', 'Canada', 'Hunter489', 'TVK77WAT7JG', 2147483647, 489, '2015-11-12 19:17:16', '[email protected]'), (183, 'Jackson', 'Sandoval', '212-5259 Eu, Ave', 'Memphis', '82008', 'United States', 'Sandoval603', 'KVK96QYW9WP', 556902, 603, '2017-10-18 00:34:16', '[email protected]'), (184, 'Brett', 'Wallace', 'P.O. Box 757, 5399 Inceptos Street', 'Watson Lake', 'H9W 0P9', 'Canada', 'Wallace443', 'YYJ92PGD0CT', 3468, 443, '2017-05-13 15:19:29', '[email protected]'), (185, 'Gary', 'Francis', '1083 Cubilia St.', 'Ajax', 'Y7X 2K2', 'Canada', 'Francis570', 'ZWD26MUZ1UV', 644, 570, '2017-02-01 23:16:07', '[email protected]'), (186, 'Nigel', 'Cooper', '9790 Orci Rd.', 'Ketchikan', '46227', 'United States', 'Cooper223', 'OHB48WKI3GD', 2147483647, 223, '2014-07-04 18:39:46', '[email protected]'), (187, 'Darryl', 'Mccarthy', '742-2036 Quisque Ave', 'Tulita', 'X1Y 6E3', 'Canada', 'Mccarthy470', 'DUQ96UNQ6FR', 2147483647, 470, '2015-11-08 20:42:21', '[email protected]'), (188, 'Dean', 'Patel', 'Ap #601-7607 Adipiscing Rd.', 'Quispamsis', 'V9B 3J5', 'Canada', 'Patel263', 'VYH55SQF0MJ', 2147483647, 263, '2015-04-16 22:58:33', '[email protected]'), (189, 'Wang', 'Crosby', 'P.O. Box 989, 9144 Ante, Rd.', 'Orilla', 'M7Z 5B4', 'Canada', 'Crosby712', 'BEI75STT0TM', 2147483647, 712, '2017-04-27 19:02:27', '[email protected]'), (190, 'Amena', 'Nielsen', '103-790 Magnis Street', 'Fort McPherson', 'K2S 2G4', 'Canada', 'Nielsen245', 'GWA76ROW6OM', 3781, 245, '2015-10-13 04:49:56', '[email protected]'), (191, 'Drew', 'Mckay', 'Ap #449-9078 Nec Av.', 'Cincinnati', '45789', 'United States', 'Mckay758', 'CPH79GOG7IG', 3455, 758, '2016-01-09 11:59:59', '[email protected]'), (192, 'Fritz', 'Harris', 'P.O. Box 802, 5540 Ornare, St.', 'Racine', '71932', 'United States', 'Harris572', 'ZFI78XMH1QC', 2147483647, 572, '2015-12-02 11:14:41', '[email protected]'), (193, 'Christian', 'Weaver', 'Ap #980-2677 Taciti Avenue', 'Yorkton', 'C2N 6W4', 'Canada', 'Weaver466', 'UUC38URQ8LD', 2147483647, 466, '2016-06-30 04:24:45', '[email protected]'), (194, 'Blythe', 'Martin', 'P.O. Box 170, 3582 Convallis Avenue', 'Fortune', 'T2H 3H2', 'Canada', 'Martin338', 'GBJ50FYG1SA', 648802, 338, '2016-04-23 17:44:55', '[email protected]'), (195, 'Carla', 'Pugh', 'Ap #204-730 Tristique St.', 'Gibbons', 'N0B 9N3', 'Canada', 'Pugh788', 'SSV69GBF3JK', 644, 788, '2014-10-22 03:00:11', '[email protected]'), (196, 'Rajah', 'Talley', '394-3613 Senectus Avenue', 'Burin', 'T4K 3X9', 'Canada', 'Talley413', 'JYV08KQO5AW', 6498, 413, '2016-02-25 03:29:01', '[email protected]'), (197, 'Sean', 'Mcfadden', 'P.O. Box 976, 8730 Quisque Ave', 'Winnipeg', 'V6X 6P9', 'Canada', 'Mcfadden709', 'MMJ47EBC8AH', 6468, 709, '2014-08-31 12:35:00', '[email protected]'), (198, 'Declan', 'Nguyen', '9685 Quis Avenue', 'Regina', 'R0N 1E0', 'Canada', 'Nguyen130', 'FPC28LBF2DH', 539308, 130, '2014-09-25 11:23:56', '[email protected]'), (199, 'Jessamine', 'Hatfield', '282-4274 Molestie Rd.', 'Rockford', '72683', 'United States', 'Hatfield529', 'VTV86HZJ0NX', 4485, 529, '2016-08-05 18:38:39', '[email protected]'), (200, 'Micah', 'Riley', '706-4848 Vel Road', 'West Jordan', '67860', 'United States', 'Riley496', 'TUW91AMJ0PV', 402, 496, '2015-01-15 21:36:58', '[email protected]'), (201, 'Lysandra', 'Pace', '924-9284 Odio, Road', 'Tallahassee', '32589', 'United States', 'Pace564', 'WII15AYT3KC', 3474, 564, '2015-08-25 21:43:05', '[email protected]'), (202, 'Melissa', 'Humphrey', '170 In, St.', 'Sacramento', '77367', 'United States', 'Humphrey829', 'BDP38ZRJ1UB', 4929, 829, '2014-05-03 00:15:37', '[email protected]'), (203, 'Melodie', 'Travis', 'P.O. Box 710, 1541 Facilisis Ave', 'Charlottetown', 'B8R 3E2', 'Canada', 'Travis281', 'ZBK62PDV6MU', 552, 281, '2016-10-20 10:52:45', '[email protected]'), (204, 'Anne', 'Barrett', 'P.O. Box 897, 7182 Nunc Rd.', 'Lang', 'M0R 2C0', 'Canada', 'Barrett931', 'KTD35ELE7DH', 519, 931, '2014-04-29 07:38:18', '[email protected]'), (205, 'McKenzie', 'Murray', 'Ap #241-5962 Pede, Rd.', 'Racine', '34707', 'United States', 'Murray683', 'MNY00SJS4LP', 645412, 683, '2017-10-06 03:47:40', '[email protected]'), (206, 'Roanna', 'Hartman', '5203 Sapien. Rd.', 'Jacksonville', '91644', 'United States', 'Hartman423', 'YEN75ETJ0KK', 2147483647, 423, '2015-11-17 12:50:12', '[email protected]'), (207, 'Xerxes', 'Solis', 'P.O. Box 274, 5915 Amet Road', 'Northumberland', 'L3T 6Y7', 'Canada', 'Solis402', 'RAF72TEE1PV', 2147483647, 402, '2016-01-27 01:58:49', '[email protected]'), (208, 'Deanna', 'Molina', 'Ap #690-8758 Morbi St.', 'Fort Wayne', '56954', 'United States', 'Molina175', 'GQC74MUE1SA', 647989, 175, '2016-06-04 18:52:14', '[email protected]'); -- -- Indexes for dumped tables -- -- -- Indexes for table `data` -- ALTER TABLE `data` ADD PRIMARY KEY (`CUST_ID`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `data` -- ALTER TABLE `data` MODIFY `CUST_ID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=209; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
