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 Fix the INSERT Command Denied Error?

The reason for getting the "1142: INSERT command denied" error is that your user accound does not have the INSERT privilege on the table or database. To resolve this error, you need to ask your MySQL DBA to grant you the right privilege.

Your DBA will need to run commands like these to grant you enough privileges :

   GRANT INSERT ON fyi.* TO dev;
   GRANT UPDATE ON fyi.* TO dev;
   GRANT DELETE ON fyi.* TO dev;
   GRANT SELECT ON fyi.* TO dev;
   GRANT CREATE ON fyi.* TO dev;
   GRANT DROP ON fyi.* TO dev;

Once you got the right privileges, run the following tutorial scrip again:

<?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");
  }

  $sql = "INSERT INTO fyi_links (id, url) VALUES ("
      . " 102, 'dba.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); 
?>

The script should return this:

1 rows inserted.
1 rows inserted.

How To Insert Multiple Rows with a SELECT Statement?

If want to insert rows into a table based on data rows from other tables, you can use a sub-query inside the INSERT statement as shown in the following script example:

<?php
  include "mysql_connection.php";

  $sql = "INSERT INTO fyi_links"
      . " SELECT id+1000, url, notes, counts, time"
      . " FROM fyi_links";
  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:

2 rows inserted.

(Continued on next part...)

Part:   1  2   3  4  5  6  7  8