Insert, select, and delete from mysql with PHP

Edit: Please read through the tutorial again if you already have. I have added mysql_real_escape_string(); to the variables to protect against SQL injection.

This tutorial will cover insert select and delete from a mysql database using php. there will be 3 files that we will be making. A file that lists the data and shows the forms for inserting and deleting data, an insert process form, and a delete process form.

Create a database called tutorial with username root and no password. Inside 'tutorial' put a table called 'tutorial'. inside 'tutorial' make 2 fields, 'id' and 'data'. make ID "auto increment".

I will post all three scripts before i start going through them step by step.

the first one will be the forms and listing page.

forms and listing


";

echo " ID Data ";

// get the stuff

while($row = mysql_fetch_array($result)) {

	// put the data we received into a table

	echo ""; 

	echo $row['id'];

	echo ""; 

	echo $row['data'];

	echo ""; 

} 


echo "";

?>



insert



Data: 








delete



ID to delete:

that was the listing/form page. i won't go over it yet, i'll just wait till all 3 pages are done.

insert page

the delete page is very similar
delete page

now time to go over the first page



starts php



mysql_connect("localhost", "root", "") or die(mysql_error());


connects to "localhost" using username "root" and no password.



mysql_select_db("tutorial") or die(mysql_error());


selects database "tutorial"



$result = mysql_query("SELECT * FROM tutorial") or die(mysql_error());  


retrieves all data from table "tutorial" and puts it in variable $result



echo "";

html code for a table

echo "
";

html code for table headers ID and Data

while($row = mysql_fetch_array($result)) {


mysql_fetch_array() makes an array out of the data we SELECTed

echo "
"; 

table stuff :P

}


ends the while() loop that we started

echo "


 ID  Data  



"; 

more HTML table



echo $row['id'];


since we made it into an array with mysql_fetch_array() we can select data like this



echo " "; 

table stuff



echo $row['data'];


same idea as echo $row['id'];, we used mysql_fetch_array() to make an array out of the data



echo "



";


ends the table



?>



closes the PHP script and does 2 line breaks



insert


shows the text "insert" in bold





starts a form that goes to insert.php using the method POST (different than GET)



Data: 


displays the text Data: and then a text box with the name data. also does a line break.





Submit button







ends the form, does 2 line breaks




delete



ID to delete: 








basically same thing, but points to delete.php, and text box is named "id".



Okay, we've ended that first one. now on to the one that processes the insert data form.





starts php



mysql_connect("localhost", "root", "") or die(mysql_error());

mysql_select_db("tutorial") or die(mysql_error());


connects to mysql and selects DB tutorial



$data = $_POST['data'];


gets the value from the text box named "data" and puts it into the $data variable



$data = mysql_real_escape_string($data);


protects against sql injection



$sql = "INSERT INTO tutorial (data) VALUES ('$data')";


puts the sql query to insert the $data variable into the table inside the $sql variable



mysql_query($sql) or die(mysql_error());


executes $sql variable, and if that doesnt work display a mysql error



echo "data inserted";


say "Data inserted"



?>


ends PHP



now on to the "delete" process form





starts php (you should know that by now :P)




mysql_connect("localhost", "root", "") or die(mysql_error());

mysql_select_db("tutorial") or die(mysql_error());



connect to mysql and select database tutorial



$id = $_POST['id'];


puts the value from the form element "id" into the $id variable



$id = mysql_real_escape_string($id);


protects against SQL injection. you can overwrite a variable like this.



$sql = "DELETE FROM tutorial WHERE id='$id'";


SQL query to delete from table tutorial, where the row ID is equal to the data entered to $id text box. buts that query inside $sql variable



mysql_query($sql) or die(mysql_error());


executes the $sql variable



echo "data deleted";


says "Data Deleted"



?>


ends php



Okay, i did my best. these should work. my host wasnt up when i wrote this tutorial so i couldn't test the scripts.



if i made a mistake in this tutorial, please let me know! thanks!



~andrew :woot: :woot: :woot: