Category Archives: Tutorials

MySQL database export/import

I always forget these 2, although I use them quite often. Doh!

Out:

mysqldump -u[user] -p[pass] [db] > [file]

and back in:

mysql -u[user] -p[pass] [db] < [file]
Tagged , ,

Creating MySQL users and granting permissions

It is a good idea to have separate users for each database, and to limit the hostname.

 

The default syntax:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

e.g.

CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';

The above will create user test, allow it to connect from localhost (connecting to a DB that is on the same machine, and has password test).

 

Now let’s grant all privileges on some database to the above user:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'hostname' WITH GRANT OPTION;

e.g.

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

The above will grant all privileges on database test to user test when he connects from localhost. You can fine grain the permissions depending on username/hostname.

 

To check the permissions for some username/hostname:

SHOW GRANTS FOR 'username'@'hostname';

 

I recommend the official guide, if you need better understanding of the above.

Tagged , , , , ,

Installing MSSQL driver for PHP on Ubuntu

The driver used is FreeTDS, TDS stands for Tabular Data Stream which is an application layer protocol, used to transfer data between a database server and a client. It was initially developed by Sybase Inc. (that’s where the name of the package comes from, for those curious) and later by Microsoft for their relational SQL databases.

In most cases this should work:

sudo apt-get install php5-sybase freetds-common libsybdb5

but if it doesn’t, you probably need to change the TDS version that is in use by the driver. The configuration file is named freetds.conf, and should be in your /etc/ folder.

There are 2 ways for this:

  • Change the global settings, uncomment the global version and set it to the desired one, e.g.
    • tds version = 8.0
  • Or make a new server and set the version explicitly
    • [exampleServer]
      host = server.com
      port = 1433 (this is default MSSQL port)
      tds version = 7.0
If you picked the latter way, then just use exampleServer as the host, e.g.
mssql_connect("exampleServer\\SQLEXPRESS", "user", "password");

Ow, if it still isn’t working, then you have probably forgotten to allow remote connections on your server. From the start menu find and open “SQL Server Surface Area Configuration” then “Surface Area Configuration for Services and Connections”. Find your instance, expand the “Database Engine” tab, select “Remote Connections” and choose the “Using both TCP/IP and Named Pipes” option. After applying, you must restart your instance for the settings to take effect.

For info on TDS versions used in specific MSSQL version you can go here, or just use the direct link and download [MS-TDS].pdf and go to Appendix A. (or just use the newest version, think it’s 8.0 at the moment, it should work fine)

Tagged , , , ,

How to setup LDAP Server on Ubuntu

LDAP (Lightweight Directory Access Protocol), in short, is an application protocol used to lookup for information on a server. As far as I figured, it is used mostly for user authorization or (e-mail) address books. The Wikipedia article on LDAP is quite good, so you can get basic info/ideas there.

Continue reading

Tagged , , , , ,

How to make an URL Shortener (with code in PHP and MySQL database)


Considering the number of URL Shortening services, I decided to make a short review of the subject. URL Shorteners were made popular by micro-blog services which used limited number of characters for a message e.g. Twitter.

So, here’s what’s covered:

 
Update (2011-07-29):
Reorganized code. It’s now divided into classes, easier for understanding and reuse. Using PDO for database connection instead of mysql methods. Added QR Code for generated shortlinks. This tutorial won’t change anymore, since it’s on the basic level it needs to be. For more information, you can follow the URL Shortening project I started, named BooRL.

Continue reading

Tagged , , ,