We have been working with the front-end (GUI), and the middle (creating and mani

Question

We have been working with the front-end (GUI), and the middle (creating and manipulating collections of objects), and now we will add on the back end. The persistent storage of data in your applications. This exercise is to get you comfortable with connecting to a DB, adding, deleting, retrieving data.   I encourage you to play with this one, do more than the minimum.

SQLite is a very small database. It is included by default in Android and iOS. It is surprisingly powerful for such a small footprint. It can be frustrating to see what’s going on – what is in the DB, did the query work correctly?     MySQL is often called a community database. It belongs to Oracle, but they allow anyone to use it for free.   The recent versions of the MySQL workbench that allows you to see what’s going on in your database are really very nice – starting to look like the Access front end.  

1.     Create a connection to a relational database using SQLite or MySQL.

2.     Create a single database table to hold information.

Let’s make a simple class called Person for this exercise.

Person

-       firstName (String)

-       lastName(String)

-       age (int)

-       ssn (long)

-       creditCard (long)

Note that once you have the DB created, you don’t want to do this again every time you run your test program. The easiest way to deal with this – for this assignment, is to comment out the code that creates the DB creation and the table creation while you experiment with the following.

(Aside: I choose ssn and credit card as fields here so that you might think about the persistent storage of sensitive data. There are some pretty strict laws governing the storage of some data.    Please don’t use any actual social security numbers or credit card numbers in this exercise.)

3.     Demonstrate the insertion of a record into the database    Insert several records.

4.     Write a method called insertPerson(Person person) that adds a person object to your database. Create another object of type Person, and demonstrate calling your method, passing the object to the method.

5.     Demonstrate the retrieval of information from the database. Use SQL Select statements, to retrieve a particular Person from the database.

6.     Write a method called selectPerson that returns a Person object. This method retrieves the data for a Person from the database. We also need to pass a parameter to identify what person. You can use ‘name’ if you like, or if you find it easier to use the database generated ID that’s fine too.

This method returns the object that represents that person. This will require that you extract the data that is returned from the database, and call the Person constructor. (Later you will understand that that this is the data-exchange between the relational database and the business layer. )

7.     Write a method called findAllPeople that returns an ArrayList<Person> of objects containing all the people in the database.   Demonstrate that it is working correctly.

8.     Write a method called deletePerson that removes a person from the database. The parameters will be first name and last name. Print out on the console the data from the record that is being deleted. Use your findAllPeople method to verify that that person has been removed from the database. Consider what this method should return. Suppose the person is not found, should the method return that information somehow?

Deliverables.

Include all of your code.   And the output from the console that verifies that it’s working.  

Explanation / Answer

Answers:
PART1:
Here the code for connection creation and checking it
<?php
$servername = "localhost or the server you may be working on";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
-----------------------------------------------------------
PART2:
//creating DB
$sql = "CREATE DATABASE Person";
//creating table to store values
$sql query = "CREATE TABLE Person (firstName STRIN(6), lastName STRING(30),age INT(6),ssn LONGINT(20),creditcardLONGINT(30),)";

here u can choose the length of the values accourding your need
-----------------------------------------------------------
PART3:
// Mysql query for insertion of persons.You can use this query to enter as many entries you want to make

$sqlquery = "INSERT INTO Person(firstName, lastName, age,ssn,creditcard)
VALUES ('John', 'Doe', '33','242424242','123123123123231')";

$sqlquery2 = "INSERT INTO Person(firstName, lastName, age,ssn,creditcard)
VALUES ('mona', 'lisa', '18','242424242','123123123123231')";

and so on as many data you want enter into your database.
-------------------------------------------------------------

PART4:
now the insertion of data is a different thing rather than inserting data through Objects
we have to create class so we can have objects and than that objects can be entered into the database

<?php
class Perason{
public $ firstName;
public $ lastName;
public $ age;
public $ ssn;
public $ creditcard;

function_connection(){use creating connection codees here ie. PART1}

setPerson function(){
$insertperson = mysqk_query("INSERT INTO Person values ('this->firstName', 'this->lastName', 'this->age','this->ssn','this->creditcard')");
}
}
?>

note: "this-> vlaues" of each entery you will get from the front end from where user is entring data*you have made the front end so just use POST function to pass the values the user enter to it can handle*
otherwise it show ERROR if you have made some error in front end to pass the values

-------------------------------------------------------------------------------------
PART5:
selecting one person using select statement

$sql = "SELECT firstName FROM Person";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of
while($row = mysqli_fetch_assoc($result)) {
echo " firstName: " . $row["firstName"]. "-lastName" . $row["lastName"]. ''age".$row["age"]. ''ssn".$row["ssn"]. ''creditcard".$row["creditcard"]<br>";
}
} else {
echo "0 results";
}

mysqli_close($conn);

//its very imp to close the connction every time you create one.

note2:your question have many sub parts and also due to time limit iam unable to answer all of them.

thankyou

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.