In this lesson, we look at how you can insert data into the database directly from your PHP scripts.
Insert data using SQL
You use SQL to insert data in a database in the same way that you can use SQL to create databases and tables. The syntax of the SQL query is:
INSERT INTO TableName(column1, column2, ...) VALUES(value1, value2, ...)
As you can see, you can update multiple columns in the SQL statement by specifying them in a comma-separated list. But of course, it is also possible to specify just one column and one value. The columns that are not mentioned in the SQL statement will just be empty.
Example: Insert a new person in the table
In this example we use the database from lesson 18. Let's say we want to insert a person into the database. It could be the person Gus Goose with the phone number 99887766 and 1964-04-20 as the date of birth.
The SQL statement would then look like this:
$strSQL = "INSERT INTO people(FirstName,LastName,Phone,BirthDate) VALUES('Gus','Goose','99887766 ','1964-04-20')"; mysql_query($strSQL) or die(mysql_error());
As you can see, SQL statements can get quite long, and you can easily lose track. Therefore, it can be an advantage to write the SQL statement in a slightly different way:
strSQL = "INSERT INTO people("; strSQL = strSQL . "FirstName, "; strSQL = strSQL . "LastName, " strSQL = strSQL . "Phone, "; strSQL = strSQL . "birth) "; strSQL = strSQL . "VALUES ("; strSQL = strSQL . "'Gus', "; strSQL = strSQL . "'Goose', "; strSQL = strSQL . "'99887766', "; strSQL = strSQL . "'1964-04-20')"; mysql_query($strSQL) or die(mysql_error());
This way, the SQL statement is built up by splitting the sentence into small parts and then putting those parts together in the variable $strSQL.
In practice, it makes no difference which method you choose, but once you start working with larger tables, it's crucial that you always keep track, so choose the method you find most convenient.
Try running the following code to insert Gus Goose into the database:
<html> <head> <title>Insert data into database</title> </head> <body> <?php // Connect to database server mysql_connect("mysql.myhost.com", "user", "sesame") or die (mysql_error ()); // Select database mysql_select_db("mydatabase") or die(mysql_error()); // The SQL statement is built $strSQL = "INSERT INTO people("; $strSQL = $strSQL . "FirstName, "; $strSQL = $strSQL . "LastName, "; $strSQL = $strSQL . "Phone, "; $strSQL = $strSQL . "BirthDate) "; $strSQL = $strSQL . "VALUES("; $strSQL = $strSQL . "'Gus', "; $strSQL = $strSQL . "'Goose', "; $strSQL = $strSQL . "'99887766', "; $strSQL = $strSQL . "'1964-04-20')"; // The SQL statement is executed mysql_query($strSQL) or die (mysql_error()); // Close the database connection mysql_close(); ?> <h1>The database is updated!</h1> </body> </html>
No comments:
Post a Comment