Posts Tagged ‘PHP’

Random SQL Results

// November 12th, 2008 // No Comments » // Database, MySQL, PHP, SQL

Last year I needed to display 3 random featured items. The solution was very simple using MySQL with this query:

SELECT * FROM `table` WHERE `column` = 'criteria' ORDER BY RAND() LIMIT 3;

Quick and dirty query generator

// November 12th, 2008 // No Comments » // PHP

Again, I needed to quicky put in WHERE and ANDs depending on how many parameters were submitted.

Instead of putting multiple conditionals, I just put them into an array:

$sqlarray = array();
if($year)
	$sqlarray[] = "(table.Year = " . $year . ")";
if($week)
	$sqlarray[] = "(table.Week = " . $week . ")";

if($otherparameter)
	  $sqlarray[] = "(otherparam = '" . $otherparam . "')";

$sql = "";
if(sizeof($sqlarray) > 0)
{
	$sql = " WHERE ";
	for($i = 0; $i < sizeof($sqlarray); $i++)
	{
		$sql = $sql . $sqlarray[$i];
		if($i != sizeof($sqlarray) - 1)
			$sql = $sql . " AND ";
	}
}

Inserting a Space Before a Capital Letter

// October 14th, 2008 // No Comments » // PHP

I needed to insert a space between a camel case string, however, some of the strings contained more than one uppercase letter. I needed to have a regular expression that inserted a space only after an uppercase letter that had a lowercase letter following it. Here is the code:

1
2
3
4
5
//put space after uppercase
$string = preg_replace('/(\w+)([A-Z])/U', '\\1 \\2', $string);
 
//put space before uppercase following lowercase
$string = preg_replace('/([a-z])([A-Z])/', '\\1 \\2', $string);

Incrementing date

// October 14th, 2008 // No Comments » // PHP


$date = date("Y-m-d");
for($day = 0; $day < 5;$day++)
{
echo date("jS F",strtotime($date));
$date = date("jS F",strtotime($date) + 86400);
}

Sorting Specific Array

// October 13th, 2008 // No Comments » // PHP

I needed to dynamically load a group of options from the database. The thing was that these were not organised alphabetically; instead they were ordered in platinum, gold, silver, bronze format. This posed a little bit of a problem. The way around it was seeting up an array with the correct order of options. Then query the DB and bring back the results. Looping through these results and getting the array key of the organised array. A new array created stores the options from the db, and is sorted on its keys with ksort.

$orderedarr = array(
0 => “platinum”,
1 => “gold”,
2 => “silver”,
3 => “bronze”);

$rsCustomerStatus = $db->query(“SELECT DISTINCT ProspectStatus
FROM tblFleetCustomer
WHERE (ProspectStatus IS NOT NULL) AND (ProspectStatus <> ”) ORDER BY ProspectStatus DESC”,1);

$displayarr = array();

foreach($rsCustomerStatus as $rowStatus)
{
//put the value in the correct position in array
$valarr = array_keys($orderedarr, strtolower($rowStatus["ProspectStatus"]));
$displayarr[$valarr[0]] = $rowStatus["ProspectStatus"];
}

ksort($displayarr);

//display array in correct order
foreach($displayarr as $val)
echo “<option value=\””. $val .”\”>”. htmlentities(ucwords(strtolower($val))) . “</option>\r\n”;