Using MySQL and PHP

By Neil Williams
2007-11-10


Adding and updating records

The SELECT * statement retrieves data from the table with each field listed in the same order as the table itself. When adding new records, values to be entered must be in the same sequence. You can enter a specific string or the value from a PHP variable. If you need to omit a field (e.g. an auto-increment field used for the index field) use NULL:

INSERT INTO $tablename VALUES ("NULL","field2value","$field3");

Remember to escape the " marks if you create an insert statement within a $sql="" statement in PHP:

$sql = "INSERT INTO $tablename 
VALUES (\"NULL\",\"field2value\",\"$field3\")";

The update statement requires more caution: If used alone, it will modify ALL records with one command. e.g. the following SQL statement, if put into a query, would set all firstnames to Fred:

UPDATE $tablename SET firstname = "Fred"

To set only Fred's record to firstname=Fred, you must specify that it only applies to his record. e.g. if the database contains a unique field called 'id' and the value for id in Fred's record is 84:

UPDATE $tablename SET firstname = "Fred" WHERE id=84

To update more than one field in Fred's record with one query, create a list as in the INSERT command above. If you need to specify Fred's record using his surname or other text string, use LIKE to match the surname.

UPDATE $tablename SET firstname = "Fred",status="sacked" 
WHERE surname LIKE "Flintstone"

Don't let LIKE fool you - used without modifiers it looks only for an exact match. Fred Stone won't be affected, neither would Fred Flintstoner. To match these records, the statement could be changed to:

UPDATE $tablename SET firstname = "Fred" WHERE surname LIKE "%stone_"

% matches any and all characters (like the * used in DOS/Windows), including nothing, and _ matches only a single character. %stone_ would therefore also match Betty Waterstoner and Billy Stoner. For this reason, always try to design your tables to include a unique number field like id - make it auto_increment and the 'primary key' (main indexed field for the table) to guarantee it remains unique.

Tutorial Pages:
» Creating dynamic pages from online databases
» Connecting to the database
» Getting and displaying results
» Adding and updating records


Copyright © Neil Williams

Related Tutorials: » Web Database Access from Desktop Applications » CubeCart 3.0 Installation and Configuration » PHP Site Search Made Easy » Installing and Configuring Drupal 6.1 » Desktop Application Development with PHP-GTK » Installing PHP on Windows