working with MYSQL data
Answers
Answer:
Inserting, updating, deleting, and retrieving data all revolve around the use of the mysql_query() function to execute the basic SQL queries. For INSERT, UPDATE, and DELETE, no additional scripting is required after the query has been executed because you're not displaying any results (unless you want to). For SELECT, you have a few options for displaying the data retrieved by your query. Let's start with the basics and insert some data, so you'll have something to retrieve later on.
Inserting Data with PHP
The easiest method for inserting data is to simply hard-code the INSERT statement, as shown in Listing 8.6.
Listing 8.6 A Script to Insert a Record
1: <?php
2: // open the connection
3: $conn = mysql_connect("localhost", "joeuser", "somepass");
4: // pick the database to use
5: mysql_select_db("testDB",$conn);
6: // create the SQL statement
7: $sql = "INSERT INTO testTable values ('', 'some value')";
8: // execute the SQL statement
9: $result = mysql_query($sql, $conn) or die(mysql_error());
10: // echo the result identifier
11: echo $result;
12: ?>
You might wonder why you need to echo the result identifier if you're just inserting data. Well, you don't have to; it's just there for kicks. You can clean this script up a bit by replacing the query execution line so that it simply executes and prints a relevant statement if successful, as shown in Listing 8.7.
Listing 8.7 The Modified Insert Script
1: <?php
2: // open the connection
3: $conn = mysql_connect("localhost", "joeuser", "somepass");
4: // pick the database to use
5: mysql_select_db("testDB",$conn);
6: // create the SQL statement
7: $sql = "INSERT INTO testTable values ('', 'some value')";
8: // execute the SQL statement
9: if (mysql_query($sql, $conn)) {
10: echo "record added!";
11: } else {
12: echo "something went wrong";
13: }
14: ?>
Running this script will result in the addition of a row to the testTable table. To enter more records than just the one shown in the script, you can either make a long list of hard-coded SQL statements and use mysql_query() multiple times to execute these statements, or you can create a form-based interface to the record addition script.
To create the form for this script, you really only need one field because the id field can automatically increment. The action of the form is the name of the record-addition script; let's call it insert.php. Your HTML form might look something like Listing 8.8.
Listing 8.8 An Insert Form
1: <HTML>
2: <HEAD>
3: <TITLE>Insert Form</TITLE>
4: </HEAD>
5: <BODY>
6: <FORM ACTION="insert.php" METHOD=POST>
7: <P>Text to add:<br>
8: <input type=text name="testField" size=30>
9: <p><input type=submit name="submit" value="Insert Record"></p>
10: </FORM>
11: </BODY>
12: </HTML>
Save this file as insert_form.html, and put it in the document root of your Web server. Next, create the insert.php script shown in Listing 8.9. The value entered in the form will replace the hard-coded values in the SQL query with a variable called $_POST[testField].
Listing 8.9 An Insert Script Used with the Form
1: <?php
2: // open the connection
3: $conn = mysql_connect("localhost", "joeuser", "somepass");
4: // pick the database to use
5: mysql_select_db("testDB",$conn);
6: // create the SQL statement
7: $sql = "INSERT INTO testTable values ('', '$_POST[testField]')";
8: // execute the SQL statement
9: if (mysql_query($sql, $conn)) {
10: echo "record added!";
11: } else {
12: echo "something went wrong";
13: }
14: ?>
Save the script as insert.php, and put it in the document root of your Web server. In your Web browser, access the HTML form that you created. It should look something like Figure 8.1.
Figure 8.1. The HTML form for adding a record.
graphics/08FIG01.gif
Explanation:
please mark as brain list
Answer:
Inserting, updating, deleting, and retrieving data all revolve around the use of the mysql_query() function to execute the basic SQL queries. For INSERT, UPDATE, and DELETE, no additional scripting is required after the query has been executed because you're not displaying any results (unless you want to). For SELECT, you have a few options for displaying the data retrieved by your query. Let's start with the basics and insert some data, so you'll have something to retrieve later on.