Archive

Archive for the ‘mysql’ Category

Some notes on MySQL

Log in to MySQL as administrator:

mysql -u root -p

Show databases:

mysql> show databases;

Show tables in the database mysql:

mysql> use mysql;
mysql> show tables;

Administration program for the mysqld daemon: mysqladmin.

Accessing phpmyadmin: http://localhost/phpmyadmin.

Another GUI administration program: mysql-admin (note the ‘-‘).

Create a database called “finance”:

$ mysqladmin -u root -p create finance

Create a user who can work on the previously created finance database:

mysql> GRANT select, insert, update, delete, create, drop, index, alter,
create temporary tables, lock tables
ON finance.*
TO 'myuser'@'localhost'
IDENTIFIED BY 'mypass';

Now we have a user called “myuser” with the password “mypass” who can work on the “finance” database from localhost only.

Log in with the newly created user:

$ mysql -u myuser -p
mysql> use finance;
mysql> show tables;
Categories: mysql Tags: ,

Multiple inserts with MySQL/PHP

January 13, 2011 Leave a comment

Problem

You want to insert multiple records in a MySQL table (in a loop, for instance). How to improve the performance?

Solution #1

One way is to regroup all the inserts in one SQL command:

INSERT INTO x (a,b)
VALUES
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

Credits go here.

Solution #2

Another way is to regroup the inserts in a transaction:

mysql_query("START TRANSACTION");
mysql_query("INSERT ...");
mysql_query("INSERT ...");
mysql_query("INSERT ...");
mysql_query("COMMIT");   // or "ROLLBACK" if you changed your mind

Credits go here.

Categories: mysql, php Tags: , ,

MySQL timestamp to Unix timestamp [PHP]

December 6, 2010 Leave a comment

Problem

In MySQL, you have a field with timestamp type. Timestamp is used to track the elapsed time since ’1970-01-01 00:00:00 UTC’. However, if you read it from the database, you get a date (2010-12-06 18:27:07) instead of its integer equivalent (1291678027). How to convert the date format to Unix timestamp?

Use case: I created a table with cached values. In each record, I have a “last_update” field of type timestamp. In my PHP script, I compare the current time with this last_update field. If the difference is more than X minutes, then I need to update the cache table. For calculating the time difference, first I convert the MySQL timestamp to Unix timestamp.

Solution

// from MySQL to UNIX timestamp
function convert_datetime($str)
{
    list($date, $time) = explode(' ', $str);
    list($year, $month, $day) = explode('-', $date);
    list($hour, $minute, $second) = explode(':', $time);

    $timestamp = mktime($hour, $minute, $second, $month, $day, $year);

    return $timestamp;	// return type: int
}

Usage:

$time_from_db = convert_datetime($tmp);
$time_now = time();
$time_diff = $time_now - $time_from_db;

Credits

I found this solution here.

Categories: mysql, php Tags: , ,

Foreign keys with phpMyAdmin [MySQL]

November 25, 2010 Leave a comment

Problem

You are using MySQL and you want to set foreign keys with phpMyAdmin.

Solution

By default, phpMyAdmin uses the storage engine MyISAM. If you want foreign keys, you must use the engine InnoDB instead. Steps:

  • choose the table you want to modify
  • choose the tab Operations
  • here, change the Storage Engine to “InnoDB” (do this with all the tables in the current database)
  • choose the tab Structure, then follow the link Relation view
  • now you can set the foreign keys and decide what to do on delete/update
Categories: mysql Tags: ,
Follow

Get every new post delivered to your Inbox.

Join 62 other followers