Editing Rows in MySQL

on Sat Jul 05 08:33:17 GMT 2008 in PHP and viewed 7109 times

• 
• 

This tutorial teaches the very basics of editing rows in MySQL databases, very useful for mistakes made.

1. Okay, you need a simple table. Run this query. Don’t worry, I’ll also show you how to delete tables at the end of this tutorial. Create a simple page called create.php and put this in it:

  <?php
  $connection = mysql_connect("localhost",
  "username",
  "password");
  mysql_select_db("database", $connection);
  $create = "CREATE TABLE test (id int AUTO_INCREMENT PRIMARY KEY,
  testfield text)";
  mysql_query($create);
?>

Now run it.

2. Now insert random data. So put this query in a file and run it:

  $connection = mysql_connect("localhost",
  "username",
  "password");
  mysql_select_db("database", $connection);
  $insert = "INSERT INTO test VALUES ('', 'somerandomtext')";
  mysql_query($insert);
  ?>

3. Now you need to edit it, right? Well that is simple with the UPDATE command. Put this again, in a file and run it.

  <?php
  $connection = mysql_connect("localhost",
  "username",
  "password");
  mysql_select_db("database", $connection);
  $update = "UPDATE test SET testfield = 'something different' WHERE id = 1";
  mysql_query($update);
  ?>

4. Now I’ll just explain the update briefly. Basically the command is

UPDATE [table] SET [row]=[newvalue], [another row] = [another value] WHERE [field]=[fieldvalue]

Everything within [] is changeable. Basically replace [table] with the table you want to change, [row] with the row you want to change, and [newvalue] with the new value of it. To edit more than one row, just separate the [row]=[newvalue] with commas. Make sure to put single quotes (‘) or escaped double quotes (“) around strings, and leave integers (numbers) un-quoted. Now for the WHERE clause, that’s different. [field] is the field within a row that you’re searching for. [fieldvalue] is the value of the field you’re looking for. So in my example, I looked for the first row, id = 1. And then I changed the other field within that row to something else.

5. And to delete a table:

  <?php
  $drop = "DROP TABLE test";
  mysql_query=($drop);
  ?>