snippetsmania.com

Order MySQL result by occurence of a field

SELECT
 `row`,
 COUNT(row) AS `count`,
FROM
 `table`
GROUP BY
 `row`
ORDER BY
 `count`;
 
 

Sometimes it is necessary, to sort the rows of a MySQL select statement by the occurence of a field value.

Tags

MySQL create databases script

CREATE DATABASE mydb_development;
CREATE DATABASE mydb_test;
CREATE DATABASE mydb_production;
GRANT all ON mydb_development.* TO 'my_username'@'localhost';
GRANT all ON mydb_test.* TO 'my_username'@'localhost';
GRANT all ON mydb_production.* TO 'my_username'@'localhost' identified BY 'my_password';

Make a SQL script that creates 3 databases and assigns all rights to my_username.

Tags

Using MySQL in Perl with DBI example

#! /usr/bin/perl -w
use strict;
use DBI;
 
my $database = "yourdatabase";
my $user = "user1";
my $passwd = "hidden"; 
my $count = 0;
 
my $select="
  select a,b,c from perlTest ";
 my $dsn = "DBI:mysql:host=localhost;database=${database}";
my $dbh = DBI->connect ($dsn, $user, $passwd)
  or die "Cannot connect to server\n";
 
my $s = $dbh->prepare($select);
$s->execute();
 
while (my @val = $s->fetchrow_array()) {
  print " $val[0]  $val[1]  $val[2]\n";
  ++$count;
}
 
$s->finish();
$dbh->disconnect ( );
 
exit (0);

Uses DBI module to query a MySQL database.

Tags

Get all MySQL rows that are one week old

SELECT DATE_SUB(date_time_column, INTERVAL 1 WEEK) FROM your_table;

Tags

MySQL table optimize table cron job

<?php
/**
 * Runs the query "OPTIMIZE TABLE" on each database and table
 * 
 * @author Marc Steinert <marc@bithub.net>
 * @version $Id$
 * @link http://bithub.net/
 */
 
// MySQL Data
define('SQL_HOST', '127.0.0.1');
define('SQL_USER', 'root');
define('SQL_PASS', 'xxxxxx');
 
 
$link = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS);
$allDbsResult = mysql_query("SHOW DATABASES", $link);
 
while(($databaseRow = mysql_fetch_assoc($allDbsResult))) {
	// Select the found database
	mysql_select_db($databaseRow['Database'], $link);
 
	$alltablesResult = mysql_query("SHOW TABLES", $link);
 
	while (($table = mysql_fetch_assoc($alltablesResult))) {
		foreach ($table as $db => $tablename) {
 
			echo "OPTIMIZE TABLE '".$tablename."'";
 
			if (mysql_query("OPTIMIZE TABLE ".$tablename.""))
			{
				echo " - done'n";
			}
			else
			{
				echo " - failed'n";
			}
	   }
	}
}
die;
 

This script connects to a MySQL server and runs the command "optimize table" on each database and table on the MySQL server .

Tags

Example of MySQL connection in Java

#
#   mysql-connector-java-3.0.11-stable-bin.jar or later must be downloaded
#   from:http://dev.mysql.com/downloads/connector/j/3.0.html
#
#   Compile:
#     javac Connect.java
#
#   Run:
#     java Connect
 
import java.sql.*;
 
public class Connect
{
	public static void main (String[ ] args)
	{
	Connection conn = null;
	String url = "jdbc:mysql://localhost/";
	String userName = "root";
	String password = "";
 
	try
	{
		Class.forName ("com.mysql.jdbc.Driver").newInstance ( );
		conn = DriverManager.getConnection (url, userName, password);
		System.out.println ("Connected");
	}
	catch (Exception e)
	{
		System.err.println ("Cannot connect to server");
	}
	finally
	{
		if (conn != null)
		{
			try
			{
				conn.close ( );
				System.out.println ("Disconnected");
			}
			catch (Exception e) { /* ignore close errors */ }
		}
	}
}

Shows how to connecto to a MySQL database with Java.

Tags

Create XML file from MySQL query

<?php
 
header("Content-type: text/xml");
 
$host = "localhost";
$user = "root";
$pass = "";
$database = "test";
 
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
 
$query = "SELECT * FROM blog ORDER BY date DESC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
 
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<entries>\n";
 
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "\t<entry>\n";
    $xml_output .= "\t\t<date>" . $row['date'] . "</date>\n";
        // Escaping illegal characters
        $row['text'] = str_replace("&", "&", $row['text']);
        $row['text'] = str_replace("<", "<", $row['text']);
        $row['text'] = str_replace(">", "&gt;", $row['text']);
        $row['text'] = str_replace("\"", "&quot;", $row['text']);
    $xml_output .= "\t\t<text>" . $row['text'] . "</text>\n";
    $xml_output .= "\t</entry>\n";
}
 
$xml_output .= "</entries>";
 
echo $xml_output;
 
?> 

Tags