Browsing articles tagged with

mysql

Export table to csv in mysql

It is possible to export the contents of a table directly to csv format from within mysql. It is useful when migrating large tables. CSV export import is much faster for large tables compared to mysqldump/mysql command. To generate csv from the table use the following command SELECT id,name INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ...

Quick Tip: Getting the Next and Previous Items or Posts

It is a common requirement in lots of projects to fetch the next and previous items or posts and display a link to them on every item or post page. Getting the previous and next item can be quite tricky especially if they need to be user-specific, although it is very easy. I'll show you how - function nextItem($item_id) { ...

Disqus Comments Importer Script in PHP

Disqus is a great commenting platform that does it's job really well. Many people use it these days because writing your own commenting system is a tedious and hard work whereas a solution like Disqus or Livefyre makes the entire commenting process a breeze with features like social logins, many levels of nesting, easy replying, quick and realtime commenting, easy ...

Mysql autoincrement column that resets every month

There are situations when we need an autoincrement field to reset every month or so. Example : Invoice Numbers : Month of october 2011-10-01 2011-10-02 ... ... ... 2011-10-800 Month of november 2011-11-01 2011-11-02 ... ... and so on. Groupwise autoincrement P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 ... Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement ...

Set mysql timezone at runtime via PHP

To set the timezone of mysql at runtime via php use the following code : $c = mysqli_connect($host, $username, $password); mysqli_query($c , "SET `time_zone` = '".date('P')."'"); This can be executed when the connection is established and it will set the timezone of mysql to whatever timezone php is set to. Fast and effective. ...

List foreign keys in mysql

information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where referenced_table_name is not null; The output is a clean table listing out all foreign keys ...

Create foreign key using Phpmyadmin

The innodb storage engine supports foreign keys in Mysql. To create foreign keys in phpmyadmin : 1. Convert both tables into innodb, if they are not already. 2. View the structure of the table which will have a foreign key. Make the referencing field an INDEX. 3. Now come back to structure view and click Relation view. In the Relation view page the field (which ...

Import mysql dump files with view definer and sql security

Views and mysqldump A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error : #1449 - There is no 'projects'@'localhost' registered The problem is with a security feature ...

Enable unicode utf-8 characters in wordpress blog title

Wordpress and unicode Sometime back I was trying to put unicode characters(utf-8) in the title of a blog from wp-admin > settings > general. After saving it became like ?????????. All pages had utf-8 in their meta tags so the issue was somewhere else. A working solution was found : 1. Go to phpmyadmin and select the database of this blog 2. Select table ...

PHP Database Class to access Mysql

This is a simple class that evolved out of the php code I wrote so far. It has the following functions : 1. dbms() - The constructor to do the initialisation like connecting to the database etc. 2. query($query) - The method to take a sql string and perform the query. 3. close() - Close the database connection 4. backup() - Create a backup of ...

Pages:12»


Connect with us