|
PHP
MYSQL with XAMP
The following scripts allow the programmer to add a form to a database , dispay the database, delete data and edit data. 1. Connect the database. 2. Create the MYSQL Database and populate the tables 3. Create the Formail. 4. Write the Formail script. 5. Display data to the screen 6.Create delete data script 7. Create update data Form 8.
Create update Form script
|
| base.php Connects to the
database($dbname = "testdb")
I dont use a password when working on local host. A function that is
used often
|
|
$servername = "localhost"; $username = "root"; $password = ""; $dbname = "testdb"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully "; ?> |
| index.php Function as 2
purposes
1. Input data to the database with a form and send to another script to process into the database. 2. Second part of the script displays the output of the database in a table format with further HTML links to delete and edit scripts |
|
<body> <center> <div> <h1>Register</h1> Please enter your details below to register. <br><form method="post" action="registerindian.php" name="registerform" id="register"><fieldset><label for="fname">fname:</label><input type="text" name="fname" id="fname" /> <br><label for="lname">lname:</label><input type="lname" name="lname" id="lname" /> <br><input type="radio" value="male" name="gender">MALE<input type="radio" value="female" name="gender">FEMALE<br /> <input type="checkbox" name="a[]" value="ENGLISH">ENGLISH <br /> <input type="checkbox" name="a[]" value="PHYSICS ">PHYSICS <br /> <input type="checkbox" name="a[]" value="MATHS">MATHS <br /> <input type="checkbox" name="a[]" value="GEOGRAPHY">GEOGRAPHY <br /> <br><input type="submit" name="register" id="register" value="Register" /></fieldset></form> </div> <br> <div> <table border="1"> <thead> <th>ID</th> <th>Firstname</th> <th>Lastname</th> <th>gender</th> <th>subjects</th> <th></th> </thead> <tbody> <?php $query=mysqli_query($conn,"select * from `contact`"); while($row=mysqli_fetch_array($query)){ ?> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['fname']; ?></td> <td><?php echo $row['lname']; ?></td> <td><?php echo $row['gender']; ?></td> <td><?php echo $row['a']; ?></td> <td> <a href="edit.php?id=<?php echo $row['id']; ?>">Edit</a> <a href="delete.php?id=<?php echo $row['id']; ?>">Delete</a> </td> </tr> <?php } ?> </tbody> </table> </div> </center> </body> |
| registerindian.php Script that inserts
the form into the database, names, dropdown gender and click
boxes.
|
|
<?php include "base.php"; ?> <?php if ($_POST["register"] == "Register") { $fname = $_POST["fname"]; $lname = $_POST["lname"]; $gender = $_POST["gender"]; // Get the selected checkbox values $a = $_POST['a']; } // Convert the array to a string $a_string = implode(",", $a); $sql = "INSERT INTO contact (fname, lname, gender, a) VALUES ('$fname', '$lname', '$gender', '$a_string' )"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); header('location:campcodes.php'); ?> |
| delete.php A simple script that deletes
items from a database
|
|
<?php $id=$_GET['id']; mysqli_query($conn,"delete from `contact` where id='$id'"); header('location:campcodes.php'); ?> |
| edit.php A script that will load up a
seperate form to allow changes to be made to the form when
resubmited .
Dont forget the id needs to be included $id = $row["id"]; on top of the other data otherwise it will not be passed thro. The clickbox array needs to be reformed as below $arra = array("ENGLISH","PHYSICS ","MATHS","GEOGRAPHY"); This is because the implode command will convert the array into a string containg only the boxes that are checked. and insert into the database (go into my phpMyadmin and take a look) |
|
<?php echo " <center><h1> CHANGE VALUES OF DATABASE </h1> "; $sql = "select * from contact where id='$id'"; $result = $conn->query($sql); $arra = array("ENGLISH","PHYSICS ","MATHS","GEOGRAPHY"); if ($result->num_rows > 0){ $row = $result->fetch_assoc(); $id = $row["id"]; $fname = $row["fname"]; $lname = $row["lname"]; $gender = $row["gender"]; } //close of $sql select ?> <html> <body> <CENTER> <form action='edit_process.php' method='post'> Person ID: <input type='text' name='id' value="<?php echo $row['id']?>"><br><br> First Name: <input type='text' name='fname' value="<?php echo $row['fname']?>"><br><br> Surname: <input type='text' name='lname' value="<?php echo $row['lname']?>"><br><br > Gender: <select name='gender'> <option value="<?php echo $row['gender']?>" selected><?php echo $row['gender']?> </option> <option value='male'>male</option> <option value='femal'>femal</option> </select> <br> <br> </body> </html> <?php $dba = explode( ",", $row['a']); foreach ($arra as $aa) { if(in_array($aa,$dba)){ echo "<input type = 'checkbox' name = 'a[]' value ='".$aa."' checked />".$aa; }else{ echo "<input type = 'checkbox' name = 'a[]' value ='".$aa."' />".$aa; } } ?> <br> <br> <br> <input type ='submit'> <br> <br> </form> </CENTER> |
| edit_process.php The script to process the
editing form that resubmits data to the database when it as been edited
|
|
<?php $id = $_POST["id"]; $fname = $_POST["fname"]; $lname = $_POST["lname"]; $gender = $_POST["gender"]; $a = implode(",", $_POST["a"]); echo "<pre>"; print_r($_POST); echo "</pre>"; $sql = "update contact set fname='$fname', lname='$lname', gender='$gender', a='$a' where id='$id'"; if ($conn->query($sql) === TRUE) { echo "Records updated: ".$id."-".$fname."-".$lname."-".$gender."-".$a; } else { echo "Error: ".$sql."<br>".$conn->error; } $conn->close(); header('location:campcodes.php'); ?> |