Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - Managing Tables and Running Queries with PHP Scripts

By: FYIcenter.com

Part:   1  2  3  4   5  6  7  8 

(Continued from previous part...)

How To Update Existing Rows in a Table?

Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:

<?php
  include "mysql_connection.php";

  $sql = "UPDATE fyi_links SET notes='Nice site.', counts=8"
    . " WHERE id = 102";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows updated.\n");
  } else {
    print("SQL statement failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, you will get something like this:

1 rows updated.

How To Delete Existing Rows in a Table?

If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:

<?php
  include "mysql_connection.php";

  $sql = "DELETE FROM fyi_links WHERE id = 1102";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows deleted.\n");
  } else {
    print("SQL statement failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, you will get something like this:

1 rows deleted.

How To Quote Text Values in SQL Statements?

Text values in SQL statements should be quoted with single quotes ('). If the text value contains a single quote ('), it should be protected by replacing it with two single quotes (''). In SQL language syntax, two single quotes represents one single quote in string literals. The tutorial exercise below shows you two INSERT statements. The first one will fail, because it has an un-protected single quote. The second one will be ok, because a str_replace() is used to replace (') with (''):

<?php
  include "mysql_connection.php";

  $notes = "It's a search engine!";
  $sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
      . " 201, 'www.google.com', '".$notes."')";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows inserted.\n");
  } else {
    print("SQL statement failed.\n");
  }

  $notes = "It's another search engine!";
  $notes = str_replace("'", "''", $notes);
  $sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
      . " 202, 'www.yahoo.com', '".$notes."')";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows inserted.\n");
  } else {
    print("SQL statement failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, you will get something like this:

SQL statement failed.
1 rows inserted.

(Continued on next part...)

Part:   1  2  3  4   5  6  7  8