240 likes | 402 Views
PHP+SQL: View and Adding,. OBJECTIVES Learn how to view data using SQL and PHP Learn how to add new data using SQL and PHP. Mechanism for HTLM PHP and SQL. HTML FORM with textboxes, radio buttons, etc. PHP gets important values, and executes SQL commands. SUBMIT BUTTON.
E N D
PHP+SQL: View and Adding, OBJECTIVES Learn how to view data using SQL and PHP Learn how to add new data using SQL and PHP
Mechanism for HTLM PHP and SQL HTML FORM with textboxes, radio buttons, etc. PHP gets important values, and executes SQL commands SUBMIT BUTTON
Searching and viewing data • Check database connection • Get the value string from the previous form • Process data: (in this process, we use the select function) • Give feedback to user.
Viewing Search Data We catch the data from the HTML’s textbox using either $_POST / $_GET This HTML form has a textbox where the user enters a search string, and a button to send data Use that data as our value for the search String. SUBMIT BUTTON Use the SELECT function to view data Execute it using PHP
Remember the SELECT command in SQL>? SELECT SELECT * FROM table_name WHERE [Conditions] Eg. SELECT * INTO friends WHERE Lastname = ‘Gener’;
Remember how we catch data? Html_form.html Result_form.php <html> <body> <form name="input" action=“result_form.php" method="post"> message: <input type="text" name= “lastname" > <input type ="submit" value ="Submit"> </form> </body> </html> <?php $input = $_POST[‘lastname’]; echo “You said: <i>$input</i>”; ?> source destination
Remember the SQL/DB Connector? • <?php • $connection = mysql_connect(‘localhost’,’root’,’password’); • mysql_select_db('friends') ; • $query = 'Select * FROM names'; • $result = mysql_query($query); • echo "<ol>"; • if(mysql_num_rows($result) > 0) • { • while($row = mysql_fetch_row($result)) • { • echo "<li> <b>$row[1]</b>, $row[2] </li>"; • } • } • echo "</ol>"; • mysql_free_result($result); • mysql_close($connection); • ?>
What to do? • We need to integrate the Catching of Data and SQL/DB to PHP Connection Mechanism together.
Html_form.html • <html> • <body> • <form name="input" action=“result_form.php" method="post"> • message: <input type="text" name= “lastname" > • <input type ="submit" value ="Submit"> • </form> • </body> • </html>
Result_form.php • <?php • $connection = mysql_connect(‘localhost’,’root’,’’); • mysql_select_db('friends') ; • $query = 'Select * FROM names'; • $result = mysql_query($query); • echo "<ol>"; • if(mysql_num_rows($result) > 0) • { • while($row = mysql_fetch_row($result)) • { • echo "<li> <b>$row[1]</b>, $row[2] </li>"; • } • } • echo "</ol>"; • mysql_free_result($result); • mysql_close($connection); • ?>
Result_form.php improvement • <?php • $search_value = $_POST[‘lastname’]; • $connection = mysql_connect(‘localhost’,’root’,’’); • mysql_select_db('friends') ; • $query = “Select * FROM names where lastname LIKE ‘$search_value%’”; • $result = mysql_query($query); • echo "<ol>"; • if(mysql_num_rows($result) > 0) • { • while($row = mysql_fetch_row($result)) • { • echo "<li> <b>$row[1]</b>, $row[2] </li>"; • } • } • else • { • echo “Record Not found!”; • } • echo "</ol>"; • mysql_free_result($result); • mysql_close($connection); • ?>
Step 2: Schema 1st Page: The Site will Ask for the Lastname Does the record exist? Error Page: The Site give a feedback False True 2nd Page: The Site will Show the Record using a loop
Inserting Data Check database connection Get the data from the previous form Process data: (in this process, we use the insert function) Give feedback to user.
Viewing Search Data We catch the data from the HTML’s textbox using either $_POST / $_GET This HTML form with all necessary form elements . Use the INSERT function (SQL) to Insert Data to the Database SUBMIT BUTTON Execute it using PHP
View, Add, Edit and Delete INSERT INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...) Eg. INSERT INTO friends (firstname,lastname) VALUES (‘Pancho’,’Trinidad’);
Html_form.html • <html> • <body> • <form name="input" action=“result_form.php" method="post"> • lastname: <input type="text" name= “lastname" > <br> • firstname: <input type="text" name= “firstname" > <br> • midname: <input type="text" name= “midname" > <br> • age: <input type="text" name= “age" > • gender: <input type="radio" name="sex" value="male" /> Male <br /> <input type="radio" name="sex" value="female" /> Female • <input type ="submit" value ="Submit"> • </form> • </body> • </html>
Result_form.php //PART 1: Check Database and connect it to php <?php $search_value = $_POST[‘lastname’]; $connection = mysql_connect(‘localhost’,’root’,’’); mysql_select_db('friends') ; . . .
Result_form.php (cont) . . . // get Data $firstname = $_POST[‘firstname’]; $lastname = $_POST[‘lastname’]; $midname = $_POST[‘midname’]; $age = $_POST[‘age’]; $gender = $_POST[‘sex’]; . . .
Result_form.php (cont) // process data and run Sql $query = “INSERT into friends (lastname,firstname,midname,age,gender) values (‘$firstname’,’$lastname’,’$midname’,’age’, ‘gender’)”; $result = mysql_query($query); // give feedback Echo “Database Saved”; mysql_free_result($result); mysql_close($connection); ?>
Step 2: Schema 1st Page: The page will Ask for the data using form elements 2nd Page: The page will Insert records using SQL commands The Page give a feedback
Assignment # 2 • Make a Telephone Catalogue • Firstname • Lastname • Address_House_Number • Address_Street • Address_Barangay • Address_City • Telephone_number
Step 2: Schema Telephone Directory Menu Add Record Look List Search Record Insert Record Show all list Get Search Key The Page give a feedback The Page give a feedback Search Record If record exist? The Page give a feedback Show Record