Update an existing record

UPDATE tbl_name SET col_name = value WHERE what_record_to_update;

Delete an existing record

DELETE FROM tbl_name WHERE which_records_to_delete;

Create users

CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

CREATE USER 'admin'@'localhost';

Grant access

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host.

One account has a user name of admin and no password. This account can be used only by connecting from the local host.

To check the privileges for an account, use SHOW GRANTS:

SHOW GRANTS FOR 'admin'@'localhost';

To check the MySQL server's listen address use netstat as root:

netstat -tlnp

To make MySQL listen on all available IP addresses, edit /etc/mysql/my.cnf and comment out the line:

#bind-address = 127.0.0.1

Accessing mysql from the shell, returning results to the shell

me@my:~ $ x="1"; while read a; do echo $x - "$a"; let x++; done < <(mysql -se 'show databases')
1 - information_schema
2 - mysql
me@my:~ $

The query string is based on having a ~/.my.cnf file so that variables like host, user, and password do not have to be included.

The silent option (-s) turn off column headings.
The -e option means execute the query and quit. The query string should be enclosed in single quotes to protect it from the shell.
Multiple queries, separated by semicolons are allowed.
The < <(command) shell construct lets you redirect the command output into the while block.
The space between the two <s is mandatory.

To simply run a command from the shell as if you were at a mysql prompt:

mysql -e 'show databases'

If you want output similar to using MySQL interactively use the tables (-t) option

me@my:~ $ mysql -te 'show databases'
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| xmainstreetanswers-1 |
+----------------------+
me@my:~ $

See also Bash and MySQL

Accessing mysql from PHP, returning results to PHP

Connect to the mysql server:

$dbh = @mysql_connect($msq_host,$msq_user,$msq_pass) OR die("Connect failed");

If an error occurs, this function produces an error message which appears in the output sent to the client browser which may not be what you want. The @ operator supresses the error message.

Select the database:

mysql_select_db($msq_db,$dbh) OR die(MYSQL_ERROR());

Disconnect from the mysql server:

mysql_close($dbh);

There are two types of queries; those that don't return a result set (i.e. DELETE, INSERT, REPLACE, UPDATE, etc) and those that do.

For queries that don't return a result set

$result_id = mysql_query("DELETE FROM tbl WHERE fld='value'");
if (!$result_id)
  print "Query failed\n";
elseif (mysql_affected_rows() < 1)
  print "No records were deleted\n";
else
  print mysql_affected_rows()." records were deleted\n";

For queries that do return a result set

There are two types of these. Those that return only one row, and those that return multiple rows.

$result_id = mysql_query("SELECT COUNT(*) FROM tbl");
if (!$result_id || !($row = mysql_fetch_row ($result_id)))
  print "Query failed\n";
else
  print ("The table has $row[0] records\n");

$result_id = mysql_query("SELECT * FROM tbl");
if (!$result_id)
  print ("Query failed\n");
else {
  printf ("Number of records: %d\n", mysql_num_rows ($result_id));
  # Fetch each row in result set
  while ($row = mysql_fetch_row ($result_id)) {
    for ($i = 0; $i < mysql_num_fields ($result_id); $i++) {
      if ($i > 0)
        print(",");
      print ($row($i));
    }
    print ("\n");
  }
  mysql_free_result ($result_id);
}

Or, if you prefer to die:

$result_id = mysql_query("SELECT * FROM tbl") OR die ("Query failed");
while ($row = mysql_fetch_row ($result_id)) {
  for ($i = 0; $i < mysql_num_fields ($result_id); $i++) {
    if ($i > 0)
      print(",");
    print ($row($i));
  }
  print ("\n");
)
mysql_free_result ($result_id);

Or if you don't like for loops, and want to list:

$query = "SELECT * FROM tbl";
$result_id = mysql_query($query) OR die ("Query failed");
while (list ($fld1,$fld2,...) = mysql_fetch_row ($result_id))
  printf ("$s $s ...\n", $fld1, $fld2,...);
mysql_free_result ($result_id);

Row-Fetching Functions

• mysql_fetch_row() - indexed numerically starting with 0
• mysql_fetch_assoc() - indexed associatively keyed by column names
• mysql_fetch_array() - indexed both numerically and associatively
• mysql_fetch_object() - returns the row as an object

Testing for NULL

NULL values in PHP are unset values.

$query = "SELECT * FROM tbl";
$result_id = mysql_query($query) OR die ("Query failed");
while (list ($fld1,$fld2,...) = mysql_fetch_row ($result_id))
  if (isset($fld1))
    printf ("%s %s ...\n", $fld1, $fld2,...);
mysql_free_result ($result_id);

Handling Quoting Issues

First create a function to select one of several possible mechanisms:

function quote_value ($str) {
  if (!isset ($str))
    return ("NULL");
  if (function_exists ("mysql_real_escape_string"))
    return ("'" . mysql_real_escape_string ($str) . "'");
  if (function_exists ("mysql__escape_string"))
    return ("'" . mysql_escape_string ($str) . "'");
  return ("'" . addslashes ("str") . "'");
}

NOTE: If the quotes above are hard to read they are a single quote inside double quotes (" ' " with no spaces).

Now insert directly into the query without quotes:

$last = quote_value ("O'Malley");
$first = quote_value ("Brian");
$exp = quote_value ("2010-04-13");
$query = "INSERT INTO tbl (last, first, expiration) VALUES ($last, $first, $exp)";

Send mail to the Webmaster

	This site best viewed with a browser
	Warning: This is a Debian centric site and MAY contain peanuts.
	Many thanks to Debra Lynn and Ian Murdock for making Debian possible
	First created Dec 14, 2008 ~ Last revised September 28, 2010