Archive for category mysql

Change date format in PHP

When coding in PHP & MySQL dealing with date is a huge headache if you are not prepared for it. As MySQL dates are in the format ‘yyyy-mm-dd’. However, we humans prefer date in the format of “dd/mm/yy” or “mm/dd/yy”! So when storing a date field to the database we need to change the date format to ymd, and after retrieving it from the database we need to change it to dmy or mdy.

To make life easier, it has been a while since I have used two functions for these changes. So here I share them, in case a newbie might need it.

function change2dmy($date) //input format: yyyy-m-d
{
$dtmp = explode(“-“,$date);
$dadate = mktime(0,0,0,$dtmp[1],$dtmp[2],$dtmp[0]);
return date(‘d/m/Y’,$dadate);
}
function change2ymd($date) //input format: d/m/yy or yyyy
{
$dtmp = explode(“/”,$date);
$dadate = mktime(0,0,0,$dtmp[1],$dtmp[0],$dtmp[2]);
return date(‘Y-m-d’,$dadate);
}

How to use it: it is a simple function.

<?php

$dmydate = ’24/12/08′
$ymddate = ‘2008-12-24’;

echo change2ymd($dmydate); //output: ‘2008-12-24′
echo change2dmy($ymddate); //output: ’24/12/2008’

?>

Advertisements

, ,

10 Comments

PHP & MySQL Tips: AZ List from DB

Past two days I have been busy designing a website! It is not too fancy from outside, but as usual all my websites are purely server based, with PHP and MySQL in the back end! So here is a tip for those PHP and MySQL fans! So if you are experienced in MySQL & PHP this can save you some time.

In a dynamic site AZ list can be tricky, as the users are allowed to add and edit the content of the website the AZ list contents can change at anytime.

azlist.jpg

In addition I don’t like to have links to pages which will have no contents, in other words if there is no resort starting with ‘U’, why have a link to it!

So here is the MySQL query I used:

“SELECT DISTINCT UPPER(LEFT(LName,1)) as letters FROM resorts ORDER BY letters”;

This is a simple query, here ‘LName’ is the name of the field and ‘resorts’ is the table in the database. So you will need a table named ‘resorts’ with at least one field names ‘LName’. So substitute as needed. This query will give you a list of unique first letters of all the data in the given fields!

azlist02.jpg

Now all you have to do is to loop the query result using PHP! Here is the complete coding!

//Database Connection String

$hostname = “server_localhost”;
$database = “database_name”;
$username = “user_name”;
$password = “password”;
$connection1 = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);

//Data Extraction

mysql_select_db($database, $connection1);
$query_AZList = “SELECT DISTINCT UPPER(LEFT(LName,1)) as letters FROM resorts ORDER BY letters”;
$AZList = mysql_query($query_AZList, $connection1) or die(mysql_error());
$row_AZList = mysql_fetch_assoc($AZList);
$totalRows_AZList = mysql_num_rows($AZList);

// Displaying the result by looping using PHP

<?php do { ?>
<?php echo ‘<a href=”list.php?ltr=’.$row_AZList[‘letters’].'”>’.$row_AZList[‘letters’].'</a>’; ?>
<?php } while ($row_AZList = mysql_fetch_assoc($AZList)); ?>

Thats it! Hope this will come in use for someone some day!

10 Comments