Archive for the ‘Mysql Development’ Category

MySQL rand unix timestamp without additional PHP loop + subquery

Figured we could improve a PHP script to not require an additional loop around records and sub query today.

This query will insert a random unix timestamp between now and 86399 seconds time (or almost one day) for each row.

I’ve found it to be an improvement on sub queries.

UPDATE dummy_table SET time_field = UNIX_TIMESTAMP(NOW())+FLOOR(1 + (RAND() * 86399));

MAMP v2.0+ and Apache Virtual Host Set Up

A colleague of mine was having some trouble setting up virtual host’s with his new installation of MAMP recently. So I decided to sit down and have a little look into it, and here follows a small guide to getting vhosts working locally on your mac using MAMP v2.0 and it’s bundled apache.

I’ve chosen to use “mate” which is the terminal shorthand for textmate, which you can download here Macromates Textmate, but you can swap out my instance’s of this with a more familiar editor like “nano” or “vim”.

Firstly run the following command or open /Applications/MAMP/conf/apache/httpd.conf in your preferred editor.

dave$ mate /Applications/MAMP/conf/apache/httpd.conf

First thing I noticed was that MAMP now quietly includes a bunch of file’s rather than just having it all appended at the bottom of httpd.conf, however all the usual files

Somewhere in amongst all of these files you will find the following:

# Virtual hosts
# Include /Applications/MAMP/conf/apache/extra/httpd-vhosts.conf

Simply uncomment it like so;

# Virtual hosts
Include /Applications/MAMP/conf/apache/extra/httpd-vhosts.conf

I used a command from terminal like so:

dave$ mate /Applications/MAMP/conf/apache/httpd.conf

Next we need to set up a record in our /etc/hosts/ file, you will need to call on the power of “sudo” for this. Use the following command:

dave$ sudo mate /private/etc/hosts

You will of course be prompted for your password, which is your system password. Once open you should see something like this:

##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1	localhost
255.255.255.255	broadcasthost
::1             localhost
fe80::1%lo0	localhost

Now add this line to the bottom of this file, replacing “nameserver” with whatever you wish to call yours.

127.0.0.1 testrun

Save and Quit.

Now you need to, create your virtual host in the vhosts file. So remember that line we uncommented in the httpd.conf file? The file that points to is now where we’re heading so.

dave$ mate /Applications/MAMP/conf/apache/extra/httpd-vhosts.conf

Once open you should see something like this:

#
# Virtual Hosts
#
# If you want to maintain multiple domains/hostnames on your
# machine you can setup VirtualHost containers for them. Most configurations
# use only name-based virtual hosts so the server doesn't need to worry about
# IP addresses. This is indicated by the asterisks in the directives below.
#
# Please see the documentation at
#
# for further details before you try to setup virtual hosts.
#
# You may use the command line option '-S' to verify your virtual host
# configuration
#
# Use name-based virtual hosting.
NameVirtualHost *:80
# VirtualHost example:
# Almost any Apache directive may go into a VirtualHost container.
# The first VirtualHost section is used for all requests that do not
# match a ServerName or ServerAlias in any  block.
#
ServerAdmin webmaster@dummy-host.example.com
DocumentRoot "/Applications/MAMP/htdocs/playme/http/"
ServerName dummy-host.example.com
ServerAlias www.dummy-host.example.com
ErrorLog "logs/dummy-host.example.com-error_log"
CustomLog "logs/dummy-host.example.com-access_log" common
#
ServerAdmin webmaster@dummy-host2.example.com
DocumentRoot "/Applications/MAMP/htdocs/playmedeux/http/"
ServerName dummy-host2.example.com
ErrorLog "logs/dummy-host2.example.com-error_log"
CustomLog "logs/dummy-host2.example.com-access_log" common

You’ll notice that there are two uncommented dummy servers set up, if you want to reference them at a later date just simply comment them out using the “#” infront of text on every line, or you can simply delete them. If your commenting it should end up like this:

#
#   ServerAdmin webmaster@dummy-host.example.com
#   DocumentRoot "/Applications/MAMP/htdocs/playme/http/"
#   ServerName dummy-host.example.com
#   ServerAlias www.dummy-host.example.com
#   ErrorLog "logs/dummy-host.example.com-error_log"
#   CustomLog "logs/dummy-host.example.com-access_log" common
#

Right, now to adding our new one, called testrun, let’s copy the format from above and insert this code into the bottom of our file. Note that at this point you now need to decide where your document route is going to be, in most cases I would just create a folder inside htdocs inside my mamp install something like below:

dave$ mkdir /Applications/MAMP/htdocs/testrun/

Our document root in our VirtualHost setup should now reflect that when you add the following.

<virtualHost *>
ServerName testrun
DocumentRoot /Applications/MAMP/htdocs/testrun/
</virtualHost>

Notice how i’ve decided to make this available on a specific port but have just opted for any port, normal port numbers for apache are :8888 or :80 so you could specify either one of those if you so wished like so:

VirtualHost *:8888

As opposed to:

VirtualHost *

Onto the final leg, now we just need to reboot apache, so, in terminal you can do:

/Applications/MAMP/bin/apache2/bin/apachectl restart

Or simply if you prefer the mamp interface a stop and start of server’s should suffice.

One last thing, let’s quickly add a index.html file to our Document Root for testing purposes.

mate /Applications/MAMP/htdocs/nameserver/index.html

And enter “Welcome to the testrun vhost – Served by MAMP” to the file. Save and quit.

Finally type http://testrun:8888 into your favourite browser (of course with MAMP started) and you should see your index.html file served up nicely.

Opening Up For Advice

Over the last year I’ve been constantly if not more than constantly trying to ask myself, are we doing things the right way?

There are a million and one different ways to accomplish some of the tasks we undertake on a day to day basis.

Preferences are always going to change and perhaps there is not one best method of practise at all.

Im trying to open myself up here to ask anyone in the context of my company and I, what the next step could be.

We build lots of text based games, driven by simple php and mysql, php = mixture of functions and classes. I generally don’t like the idea of php frameworks for use with game design of this type, as games quickly grow code base hugely and you get the annoyingness of extra overheads in load time. However im very much open to new ideas as to how we could improve code. Maybe a new language, or maybe a different approach.

I wondered really if anyone out there has built a complex game on anything other than there own custom php code and mysql (obviously in the context of text based, browser based games and improving on php).

Also we do alot of client work, nice designs, baskets and checkouts all the usual stuff. Does anyone use a particular framework for similar things and found its really good? We spend far too much time repetitively coding things like checkouts and baskets for each customer.

Ideally its all about taking things up a notch and what could improve what we do.

I’d also like to know if anyone uses any particularly good checkout bundles or companies, to manage all there payments really easily. Ours is very disjointed atm. With payments for different sources ie mobile, card etc all being taken by different companies.

Connecting To Server With Putty Using SSH Keys & Port Forwarding

Quick and dirty tutorial to connecting to a remote server with ssh keys and putty.

Firstly you need to download putty and puttygen which can be found:-

Here – Putty Client
&
Here – PuTTYgen

Yes they are both the same page, you will find the download links for both here.

Once you have downloaded both the clients you will need to first generate yourself a key using the puttygen and then save it out to a file in a location you will easily be able to remember. Remember you need to generate a public and private key, but the private is the one of most use to you.

Im going to largely breeze over adding your ssh key to the server, as you should really know how to do this already before attempting to use putty with ssh keys and port forwarding.

Open up putty, enter the host as whatever it needs to be either an ip address or a host name like google.access.me.com and specify the port as 22 if it isn’t already.

Then head down on the left hand menu to the SSH list which will probably be shut, click the plus to expand the list and head to the AUTH tab. Here enter the user you want to auto login with and specify the path to your key using the browse file function.

If all is correct head back to the “session” tab and move to the right hand side, and save your settings under any name you like, this is to ensure that you can pick up where you left off when returning to the same connection again.

Now you have done that hit open and you should with any luck either be presented with a enter passphrase for key authorisation, enter that and youll be in.

Port Forwarding Mysql From Remote To Local

Should you like us sometimes want to dev locally to a remote database, Putty can be used to tunnel a mysql connection from a remote server to your local mysql port. To do this in most cases simply forward the following or well at least for me.

Navigate to the following.
Connection -> SSH -> Tunnels -> Options Controlling Port Forwarding

L 3307 port destination to
the remote database.
remote.db.name.net:3306

Add that in, head back to session save it and then open it and wollah you should now be able to dev locally to the remote database, by forwarding mysql to your local ports.

This is just a quick and dirty tutorial and not meant to be a fully comprehensive guide, however if theres something you want to know I may be able to help, so leave a comment.

Mysql Dump Ignore Tables And Rsync To Remote Server

Not having had huge experience working with mysql dumping and rsync. This afternoon I was faced with the task of taking a backup of one our busy, very database intensive games using mysqldump command, but I also needed to exclude some rather over sized tables from the dump. After which I wanted to pipe over the mysqldumps to our remote secure testing server using rsync which id also never used before.

So the first things first I let our users know the site is going down in 5 mins I then sabotage our db_connect functions so that users a directed to a page with no connection attempts or db calls on it.

I then write the command to dump data and tables ignoring a few tables which id already flagged as potentially large and time consuming to dump.

My command looked a little like this:-

mysqldump -u username -p --ignore-table=database.tableA, database.tableB -B database -K > /path/to/outputfile.sql

With quite a few more ignores hehe but hopefully you’ll get the gist.

I then just wanted to dump the structure of the troublesome tables.

So I did something like the below.

mysqldump -u username -p database table1 table2 -d > /path/to/outputfile.sql

Notice the -d, this is very important as it signifies no data just structure.

Now I have my two files I want to sync them accross the our test area located on another server, this time using a rsync

I used ssh with rysnc and my final command looked like this.

rsync -a -e ssh backups/ username@server:/home/google/backups

Which simply took my backups directory on the server i was connected to already and synced it with the credentials entered here. Seemlessly and with all permissions etc carried over.

Now thus I had achieved what I needed to and all that was left was to load the files into the database for the test area. By simply:

mysql -u username -p database < /path/to/inputfile.sql

Mysql Dumping / Uploading a file or files via terminal and SSH

Just something I found myself looking up today so that I can do everything from the comfort of my little terminal window.

I wanted to take a backup from a table in one database and up it to another db.

Firstly I dumped the tables on my remote server to my sites webroot.

mysqldump -u root -p database_name table_1 table_2 table_3 > /path/to/outputfile/outputfile.sql

This will prompt you for your password, enter it and it should, providing everything else is correct dump to ‘outputfile.sql’.

Now you have your backup residing at your web servers webroot or wherever it is you may have chosen to store it.

This file can now be retrieved either by a FTP client or if your like me then using terminal by either using curl or scp to local.

Curl example:

curl -O http://wedpathtoserver.com/outputfile.sql

Scp (preferred via ssh):
scp username@server:path_on_server/file path_to_download_to

Once on local you will probably want to move this to your other server via uploading which we will again use Scp for this.

scp path_to_file/file username@server:path_to_upload_to

Once youve got the file on the server which the database you want to import data to resides.
Connect to the server in this case via ssh.
Then restore your file to the database.
mysql -u root -p databasename < /path/toupload/directory/outputfile.sql

This is the general format to remember

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

If its a zipped backup you want to restore simply use the following command

gunzip < outputfile.sql | mysql -u root -p DatabaseName

Also remember you dont have to have the password prompt you each time you can include it in the original command by changing `-p` to `-p password`.

Very basic but will suit most peoples needs.

How to find duplicate rows of a table in sql

This is a nice quick easy way to find duplicate rows in your database based on one field.
So say for instance id like to find all users with duplicate rows in my orders table.

The following query can achieve that.

select userid, count(*) from orders group by userid HAVING count(*) > 1

//this will fetch all the userids with duplicate rows, then you could run a delete query or whatever it is you need to do to get rid of them in a while loop of this.