jump to navigation

Using limit in mysql join December 18, 2009

Posted by maxmil in : MySql , add a comment

Just found myself in the scenario of needing to join two tables which have a 1 – n relationship. In the second table i only wanted to return the first two results.

This is one way to do it.


SELECT
f1.col1,
f1.col2
FROM foo f1
LEFT JOIN foo f2 ON f2.col2 = f1.col2 AND f2.col1 < f1.col1 GROUP BY f1.col1, f1.col2 HAVING COUNT(f2.col1) <= 1 ORDER BY f1.col2, f1.col2;

What you are doing here is joining the second table with itself. This will give you exactly one result with the first row in the second table, two for the second row, three for the third... etc.

You then group them and use the HAVING clause to get the row that you want.

This is not very efficient, but if your dataset is small or it's a one off query it does the job.

Mysql log files in debian September 22, 2008

Posted by maxmil in : Debian,MySql , add a comment

I was desperately trying to find the output from the innodb_table_monitor in debian but the files /var/lib/mysql.err and /var/lib/mysql.log were empty.

This is because under debian mysql redirects its log files to /var/lib/syslog.

Character conversión in UTF-8 between Actionscript PHP and Mysql April 30, 2008

Posted by maxmil in : Flash,MySql,php , add a comment

Always a tricky one. The best solution that i’ve found is

1) Mysql database and/or tables with the utf-8 character set and utf_general_ci collation.
2) PHP files encoded in UTF8
3) After the initial connection with the database send the querySET NAMES `utf8`

Command line completion with mysql October 31, 2007

Posted by maxmil in : MySql , add a comment

For automatic completion with the mysql command line client issue the “rehash” command.

Install Apache Php Mysql on Windows February 15, 2007

Posted by maxmil in : Apache,MySql,php , 3 comments

Instalation of Apache
1) Download and open installer (apache_2.2.4-win32-x86-no_ssl.msi in my case)
2) Doesn’t matter what you put as domain, server and admin email as this is a private instalation. I’ve put MyDomain, MyServer and admin@mydomain.com
3) After installation you should see the apache service monitor in the system tray with a green arrow denoting that apache is running. If you navegate to http://localhost in your browser you should see a page that says “it works!”
4) Notes: Apache may not be able to start if IIS is running or Skype is online as both may use port 80.

Install php5
5) Download zip package from php.net (php-5.2.1-Win32.zip in my case). DO NOT DOWNLOAD INSTALLER.
6) Unzip to c:\php
7) Move (DON’T COPY) c:\php\php5ts.dll to c:\WINDOWS\php5ts.dll (An alternative would be to add c:\php to the PATH environmental variable but i haven’t tried this).
8) Copy c:\php\php.ini.dist to c:\php\php.ini
9) Edit extension_dir = “./” in php.ini. Replace with extension_dir = “”C:\php\ext\”. This is necessary when php executes within apache since the relative path “./” is no longer valid.

Configure php with apache
10) Open C:\Archivos de programa\Apache Software Foundation\Apache2.2\conf\httpd.conf
11) Edit doc root from “C:\Archivos de programa\Apache Software Foundation\Apache2.2\htdocs” to the doc root of your choice (2 instances to change).
12) Add php executable script after “AddType application/x-gzip .gz .tgz” in :

	ScriptAlias /php/ "c:/php/"
	AddType application/x-httpd-php .php .php5
	Action application/x-httpd-php "/php/php-cgi.exe"
	SetEnv PHPRC "C:/php"

13) Add permissions to the executable (don’t think that it matters where you put this, i put it after the definition of the above <IfModule mime_module>

	# Add access to php directory
	<Directory "C:/php">
		Options ExecCGI Indexes FollowSymLinks 
		AllowOverride None 
		Order allow,deny 
		Allow from all
	</Directory>

14) Add .php extention to directory index module. Search for “IfModule dir_module” and add index.php after index.html. After editing it should look like this:

<IfModule dir_module>
    DirectoryIndex index.html index.php
</IfModule>

15) Create index.php file in doc root of apache with the text
16) Stop apache. Start apache (from system tray – for some reason restart apache is not working for me)
17) Go to http://localhost and you should see the typical phpinfo page.

Install Mysql
18) Download mysql community edition from mysql site (in my case its mysql-5.0.27-win32.zip)
19) Extract Setup.exe and execute
20) When prompted you don’t have to sign in/up
21) When prompted accept “Configure the Mysql Server now”
22) Choose “Detailed Configuration”
23) Choose “Developer Machine”
24) Choose “Multifunctional Database”
25) Customize, if you want, where the innodb table space will be created (note: this is not the mysql data dir. To relocate the data dir you must stop the server, copy all files except log files from the current data dir, delete the log files, update the datadir in my.ini and restart the server).
26) Choose Decision Support
27) Choose Enable TCP/IP Networking and accept port 3306
28) Choose best support for multilingualism (UTF8 character set)
29) Choose install as a Windows service. I have unmarked the “Launch the MySQL Server automatically” option because i prefer to start it only when i want to use it (the service can be started from the command line with “net start mysql”). I also mark the “include Bin directory..” as i sometimes connect from the command line using the mysql client)
30) Choose your root password
31) Execute and finish
32) If
33) Test instalation by connecting (use your preferred method) and executing the query “select version();”. You should get:

	+---------------------+
	| version()           |
	+---------------------+
	| 5.0.27-community-nt |
	+---------------------+
	1 row in set (0.02 sec)

Configure php with mysql
34) Open c:\php\php.ini and uncomment the line: extension=php_mysqli.dll
35) Stop and Start Apache
36) Create file testMysql.php in you apache document root with the text (replace ‘root_pswd’ with your real root password:

<?
$mysqli = new mysqli('localhost','root','root_pswd');
$result = $mysqli->query("SELECT version()");
while($row = $result->fetch_assoc()) {
	print $row['version()'];	
}
$result->close();
?>

37) Navegate to http://localhost/testMysql.php and you should see the text “5.0.27-community-nt” (or equivalent if you have installed a different version of mysql.

Set up virtual host in apache
38) In httpd.conf add default virtual host so that your document root folder is not affected. At the end of httpd.conf add:

NameVirtualHost *:80

<VirtualHost *:80>
  ServerName localhost
  DocumentRoot "path/to/document/root"
</VirtualHost>

39) Add declaration for each virtual host. This is a minimal example:

<VirtualHost *:80>
	ServerName  my-virtual-host
	DocumentRoot "path/to/my/virtual/host/doc/root"
	<Directory "path/to/my/virtual/host/doc/root">
		AllowOverride All
		Order allow,deny
		Allow from all
	</Directory>
</VirtualHost>

40) In c:\WINDOWS\System32\drivers\etc\hosts add your virtual host to the line that starts 127.0.0.1:

127.0.0.1       localhost my-virtual-host

41) Restart apache and navigate to http://my-virtual-host

Updated Apache Module PHP4 to PHP5 on Debian Sarge April 25, 2006

Posted by maxmil in : Apache,Debian,MySql,php , add a comment

Added these lines to my /etc/apt/sources.list

# Use dotdeb.org for LAMP related packages not available in Sarge
deb http://dotdeb.pimpmylinux.org/ stable all
deb-src http://dotdeb.pimpmylinux.org/ stable all

And then executed:

$> apt-get update
$> apt-get install libapache2-mod-php5

Check out the article from where i got this info: http://www.debian-administration.org/articles/357