MySQL BIT in PHP

MySQL BIT in PHP

Ok, if you subscribe to my blog for personal reasons, this isn’t very relevant to you, but for those of you here for the programming, on a recent project I had some problems getting the BIT field in MySQL to be interpreted as a boolean in PHP. There are a couple of discussions on the net about it here and here, but I couldn’t find any simply stated solutions on the net. So here’s what I ended up doing. You can also download the file here, rename the file .php to use it.

function mysql_bit($bit) {
return ord($bit) == 1;
}


If you found any of the software useful, please consider supporting its further developement by donating.

Convert existing MySQL database from one charset encoding to another

Convert existing MySQL database from one charset encoding to another

Warning: Always make backups.


Convert existing MySQL database from one charset encoding to another

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


Convert existing table from one charset encoding to another

ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MYSQL UNICODE

Character set= utf8 -- UTF-8 Unicode
Collation=utf8_bin

PHP calculate the difference in time between a past date and a future date

  function nicetime($date)
{
    if(empty($date)) {
        return "No date provided";
    }
   
    $periods         = array("second", "minute", "hour", "day", "week", "month", "year", "decade");
    $lengths         = array("60","60","24","7","4.35","12","10");
   
    $now             = time();
    $unix_date         = strtotime($date);
   
       // check validity of date
    if(empty($unix_date)) {   
        return "Bad date";
    }

    // is it future date or past date
    if($now > $unix_date) {   
        $difference     = $now - $unix_date;
        $tense         = "ago";
       
    } else {
        $difference     = $unix_date - $now;
        $tense         = "from now";
    }
   
    for($j = 0; $difference >= $lengths[$j] && $j < count($lengths)-1; $j++) {
        $difference /= $lengths[$j];
    }
   
    $difference = round($difference);
   
    if($difference != 1) {
        $periods[$j].= "s";
    }
   
    return "$difference $periods[$j] {$tense}";
}

$date = "2009-03-04 17:45";
$result = nicetime($date); // 2 days ago

?>

Date Function Formatting of PHP

DAYS
d - day of the month 2 digits (01-31)
j - day of the month (1-31)
D - 3 letter day (Mon - Sun)
l - full name of day (Monday - Sunday)
N - 1=Monday, 2=Tuesday, etc (1-7)
S - suffix for date (st, nd, rd)
w - 0=Sunday, 1=Monday (0-6)
z - day of the year (1=365)
WEEK
W - week of the year (1-52)
MONTH
F - Full name of month (January - December)
m - 2 digit month number (01-12)
n - month number (1-12)
M - 3 letter month (Jan - Dec)
t - Days in the month (28-31)
YEAR
L - leap year (0 no, 1 yes)
o - ISO-8601 year number (Ex. 1979, 2006)
Y - four digit year (Ex. 1979, 2006)
y - two digit year (Ex. 79, 06)
TIME
a - am or pm
A - AM or PM
B - Swatch Internet time (000 - 999)
g - 12 hour (1-12)
G - 24 hour c (0-23)
h - 2 digit 12 hour (01-12)
H - 2 digit 24 hour (00-23)
i - 2 digit minutes (00-59)
s 0 2 digit seconds (00-59)
OTHER
e - timezone (Ex: GMT, CST)
I - daylight savings (1=yes, 0=no)
O - offset GMT (Ex: 0200)
Z - offset in seconds (-43200 - 43200)
r - full RFC 2822 formatted date

Set unicode database for sqlserver

Right click database--> Property-->Option-->Collation: Corsican_100_BIN2

Database field: Chose NVARCHAR

by cno

SQL to Select a random row from a database table

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1