Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Managing Tables and Running Queries with PHP Scripts

By: FYIcenter.com

Part:   1   2  3  4  5  6  7  8 

A collection of 18 tips on managing tables and data rows with PHP scripts. Clear explanations and tutorial exercises are provided on creating and dropping tables; inserting, updating, and deleting rows, selecting and looping through data rows by pages; setting auto-increment IDs. Topics included in this collections:

1. How To Create a New Table?
2. How To Get the Number of Rows Selected or Affected by a SQL Statement?
3. How To Insert Data into an Existing Table?
4. How To Fix the INSERT Command Denied Error?
5. How To Insert Multiple Rows with a SELECT Statement?
6. What Is a Result Set Object?
7. How To Query Tables and Loop through the Returning Rows?
8. How To Break Output into Pages?
9. How To Update Existing Rows in a Table?
10. How To Delete Existing Rows in a Table?
11. How To Quote Text Values in SQL Statements?
12. How To Quote Date and Time Values in SQL Statements?
13. How To Display a Past Time in Days, Hours and Minutes?
14. How To Perform Key Word Search in Tables?
15. How To Build WHERE Criteria with Web Form Search Fields?
16. How To Query Multiple Tables Jointly?
17. How To Get the ID Column Auto-Incremented?
18. How To Get the Last ID Assigned by MySQL?

Please note that all answers and tutorials are based on MySQL 5.0 and PHP 5.0. Sometimes you may need to run previous tutorials in order to continue a later tutorial.

For questions on connecting to MySQL server with PHP scripts, see the previous FAQ collection.

How To Create a New Table?

If you want to create a table, you can run the CREATE TABLE statement as shown in the following sample script:

<?php
  include "mysql_connection.php";

  \$sql = "CREATE TABLE fyi_links ("
      . " id INTEGER NOT NULL" 
      . ", url VARCHAR(80) NOT NULL"
      . ", notes VARCHAR(1024)"
      . ", counts INTEGER"
      . ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
      . ")";
  if (mysql_query($sql, $con)) {
    print("Table fyi_links created.\\n");
  } else {
    print("Table creation failed with error:\n");
    print(mysql_errno($con).": ".mysql_error($con)."\n");
  }

  mysql_close($con); 
?>

Remember that mysql_query() returns TRUE/FALSE on CREATE statements. If you run this script, you will get something like this:

Table fyi_links created.

How To Get the Number of Rows Selected or Affected by a SQL Statement?

There are two functions you can use the get the number of rows selected or affected by a SQL statement:

• mysql_num_rows($res) - Returns the number of rows selected in a result set object returned from SELECT statement.
• mysql_affected_rows() - Returns the number of rows affected by the last INSERT, UPDATE or DELETE statement.

How To Insert Data into an Existing Table?

If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:

<?php
  include "mysql_connection.php";

  $sql = "INSERT INTO fyi_links (id, url) VALUES ("
      . " 101, 'dev.fyicenter.com')";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows inserted.\n");
  } else {
    print("SQL statement failed with error:\n");
    print(mysql_errno($con).": ".mysql_error($con)."\n");
  }

  mysql_close($con); 
?>

Remember that mysql_query() returns integer/FALSE on INSERT statements. If you run this script, you could get something like this:

SQL statement failed with error:
1142: INSERT command denied to user 'dev'@'localhost' 
   for table 'fyi_links'

(Continued on next part...)

Part:   1   2  3  4  5  6  7  8