Tutorials MySQL MySQL Inserting and Manipulating Rows

MySQL Inserting and Manipulating Rows

Hello and welcome back to PHP Trainee! This time you will learn how to insert, update and delete rows. This is the last time I'm going to say this but every MySQL tutorial from now on is going to require: a MySQL database, a table and the connection information. Read the previous tutorials on what this stuff is.

Insert a new row

To insert a row, you need to have all the data first. You can use PHP variables to fill in data. When you insert the data, the order has to be the same as each other.

$sql = 'INSERT INTO table (name,phone,email)
  VALUES ("Mike","1234567890","mike@mail.com")';
mysql_query($sql);

Note: If you don't assign a value to a column that is NOT NULL and is not AUTO_INCREMENT, MySQL will automatically assign a 0 to it.

Update an existing row

To update a row, you use the update function. Shocker eh?

$sql = 'UPDATE table
  SET name = "Tom", phone="0987654321", email="tom@mail.com"
  WHERE id = "10" LIMIT 1';
mysql_query($sql);

Delete an existing row

To delete a row, you have to use the.... delete function! Wow, you probably would have never guessed it.

$sql = 'DELETE FROM table WHERE id="10" LIMIT 1';
mysql_query($sql);

Filtering MySQL results

The WHERE clause is as important to MySQL as the IF function is to PHP. It compares the data. If you don't want to grab the whole table, you need to use WHERE. If you need to manipulate specific rows, you have to use WHERE to choose which ones. You might also want to add a limit to your query. Adding a limit will tell MySQL to stop after the quota has been reached. Unless you want a mass purge, you will want to refine your results.

That's all for today. Try to fill up your database. Next lesson will be how to grab information from your database.

Posted by on . Category: MySQL


Comments

No comments posted yet

You need to register or login to post new comments.