Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • Create and Delete and Recreate Table

    @Warren12
    Recommendations Offline Message

    • 0
    • 0
    • 0
    • 1
    • 0
    • 0
    • 0
    • 1.32k
    Answer it

    Hi i have been asked to the following task.

     

    Your project must meet the following specifications:


    1. Create a table tbl_User in MySQL using the console or phpMyAdmin, consisting of the following column names:
    a. The table structure is as follows for tbl_User:
    ID (autoincrement - PK), FName (text), LName (text), Email (text), Password (Text);
    b. Create a text file userData.txt and populate the textfile with at least 30 fictitious entries, e.g. John Doe j.doe@abc.co.za 29ef52e7563626a96cea7f4b4085c124;
    c. Use the console or phpMyAdmin and load the text file manually into the table.

     

    I have managed to complete this question. 

     

    This question below is what I'm struggling with....

     

    2. Use the existing test database that is pre-installed with wamp or xamp.
    a. Create a connection within the test database using PHP and store the code which creates a connection in a file called DBConn.php.
    b. Create a script called createTable.php that will check if the table tbl_User exists and if it does, delete the table and (re)create the table and load the data into the table using the userData.txt file as source file.

    c. Embed the DBConn.php as an include file within the createTable.php script. Hint: You may include the connection code in the createTable.php directly and later refactor the code into an include file to modularize your code.
    d. Each time the script is run the table will be deleted if it exists and reloaded afresh with the data stored in the text file.

     

    My DBConn.php is below:

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Connect to DB</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
$DBName = "Test";
$DBConnect = mysqli_connect ("127.0.0.1","root","","test");

if($DBConnect === FALSE)
			echo "<p> Connection Failed /<p>\n";
		
		else {
			echo "<p> Successfully create the " . "\"$DBName\" database . </p>\n";
		}
			
?>
</body>

     

    Please make answer as simple as possible.

     

     

     

    php
    Answer it

 1 Answer(s)

  • @sukhminder.kaur
    Recommendations Offline Message

    over 8 years ago

    Hi Warren,

    Please find below the code packet as a solution to your problem I am attaching a sample CSV file also. Please try and let me know if you face any problem

    1. include 'dbconn.php';
    2. $drop_query = 'DROP TABLE IF EXISTS `test`.`tbl_user`';
    3. $create_query = 'CREATE TABLE `test`.`tbl_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(50) DEFAULT NULL, `lname` varchar(50) DEFAULT NULL, `email` varchar(50) DEFAULT NULL, `password` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ';
    4. $field_separator = '\'\t\''; //character which seperates the fields in csv file it can other than \t
    5. $line_separator = '\'\n\''; //character which seperates the records in csv file it can other than \n
    6. $csv_file = 'test.csv';
    7. $import_query = ' LOAD DATA LOCAL INFILE \''.$csv_file.'\' INTO TABLE `test`.`tbl_user` FIELDS TERMINATED BY '.$field_separator.' LINES TERMINATED BY '.$line_separator;
    8. // the import query should look like LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE `test`.`tbl_user` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
    9. if(!file_exists($csv_file)) {
    10. die("File not found. Make sure you specified the correct path.");
    11. }
    12. mysqli_query($DBConnect,$drop_query); // drop the table if it exists
    13. mysqli_query($DBConnect,$create_query); //create the table
    14. mysqli_query($DBConnect,$import_query); // import the csv file
    15. $rows_imported = mysqli_affected_rows($DBConnect); // returns how many rows are affected/inserted
    16. echo $rows_imported.' row(s)affected';
Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: