Querying Dates and Date Parts in Laravel

The World War II timeline application allows users to query historical events by a specific date, by month/day combination, by a year, or they can leave everything blank and the system will show them events that had happened “on this day”. Here’s an example for 18 March 1944. To achieve this, in SQL speak, it would be something like the following.

-- By specific date
select * from my_table where my_date=:yyyymmdd;

-- By year
select * from my_table where date_format(my_date, '%Y')=:yyyy;

In Laravel’s This can be achieved with several Laravel Eloquent’s built in functions: whereDate(), whereYear(), whereMonth(), and whereDay(). Below are some examples from WW2DB’s TimelineController.

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Timeline;

class TimelineController extends Controller {

	/* ... Unrelated code omitted from this article */

	public function getList(Request $request) {
		/* ... Unrelated code omitted from this article */

		$mm = $request->query('mm');
		$dd = $request->query('dd');
		$yyyy = $request->query('yyyy');
		
		$timeline = Timeline::orderBy('my_date');
		
		// If querying by specific date, use whereDate()
		$timeline = $timeline->whereDate('my_date', date($yyyy."-".$mm."-".$dd));
		
		// If querying by month/day, use whereMonth() + whereDay()
		$timeline = $timeline->whereMonth('my_date', $mm)
			->whereDay('my_date', $dd);

		// If querying by year, use whereYear()
		$timeline = $timeline->whereYear('my_date', $yyyy);
		
		$timeline = $timeline->get();
		
		return view('timeline.my_view', compact('timeline'));
	}

	/* ... Unrelated code omitted from this article */
}

Or, simplified whereMonth() + whereDay() example to get data for 18 March:

$mm = "03";
$dd = "18";
$timeline = Timeline::orderBy('my_date');		
$timeline = $timeline->whereMonth('my_date', $mm)
	->whereDay('my_date', $dd);
$timeline = $timeline->get();

Submitting Multiple Products and Variable Quantities to PayPal

I recently did some work for a small local non-profit group to accept payment over PayPal. They have multiple, but limited, offerings that they were selling to raise funds, and customers could purchase in variable quantities. They originally used the buttons auto-generated by PayPal, but that functionality forces their customers to go back and forth between the single-page sales page and the PayPal shopping cart, which creates too much complexity for their simple sales process. Below is a simplified version of what I put together for them.

First, this is the sales form:


	<form method="post">
		<table border="0">
			<tr><td>Name</td><td>&nbsp;</td><td><input type="text" name="name" id="name" maxlength="100" /></td></tr>
			<tr><td>Email</td><td>&nbsp;</td><td><input type="text" name="email" id="email" maxlength="100" /></td></tr>
			<tr><td>Phone</td><td>&nbsp;</td><td><input type="text" name="phone" id="phone" maxlength="100" /></td></tr>
			<tr><td colspan="9"><br />I wish to purchase:<br /><br /></td></tr>
			<tr><td>Product 1</td><td>&nbsp;</td><td><select name="prod1"><option value="0">0</option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option></select> @ $5 ea.</td></tr>
			<tr><td>Product 2</td><td>&nbsp;</td><td><select name="prod2"><option value="0">0</option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option></select> @ $5 ea.</td></tr>
			<tr><td>Product 3</td><td>&nbsp;</td><td><select name="prod3"><option value="0">0</option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option></select> @ $10 ea.</td></tr>
			<tr><td>Product 4</td><td>&nbsp;</td><td><select name="prod4"><option value="0">0</option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option></select> @ $20 ea.</td></tr>
		</table>
		<input type="submit" value="Review Order" />
	</form>

The next page grabs the values passed in from the previous page and displays a confirmation screen for the customer to review; if all looks ok, the customer will submit the following form. Note the specific form action and hidden inputs (cmd, upload, business, and currency_code) required by PayPal. Multiple products is achieved by creating sets of inputs (item_name_*, quantity_*, amount_*) for each product, with the asterisk being a number; the numbering must start from 1 and subsequent products must be in order, ie. 2, 3, 4, etc., thus the use of the IF statements and the $itemCnt counter variable. The amount_* field represents the unit price of the associated product, not the line total.


	<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
		<input type="hidden" name="cmd" value="_cart">
		<input type="hidden" name="upload" value="1">
		<input type="hidden" name="business" value="store@email.here">
		<input type="hidden" name="currency_code" value="USD">
		<?php
		$itemCnt = 1;
		if ($prod1 != "0") {
			?>
			<input type="hidden" name="item_name_<?php print($itemCnt); ?>" value="Product 1">
			<input type="hidden" name="quantity_<?php print($itemCnt); ?>" value="<?php print($prod1); ?>">
			<input type="hidden" name="amount_<?php print($itemCnt); ?>" value="5">
			<?php
			$itemCnt++;
		}
		if ($prod2 != "0") {
			?>
			<input type="hidden" name="item_name_<?php print($itemCnt); ?>" value="Product 2">
			<input type="hidden" name="quantity_<?php print($itemCnt); ?>" value="<?php print($prod2); ?>">
			<input type="hidden" name="amount_<?php print($itemCnt); ?>" value="5">
			<?php
			$itemCnt++;
		}
		if ($prod3 != "0") {
			?>
			<input type="hidden" name="item_name_<?php print($itemCnt); ?>" value="Product 3">
			<input type="hidden" name="quantity_<?php print($itemCnt); ?>" value="<?php print($prod3); ?>">
			<input type="hidden" name="amount_<?php print($itemCnt); ?>" value="10">
			<?php
			$itemCnt++;
		}
		if ($prod4 != "0") {
			?>
			<input type="hidden" name="item_name_<?php print($itemCnt); ?>" value="Product 4">
			<input type="hidden" name="quantity_<?php print($itemCnt); ?>" value="<?php print($prod4); ?>">
			<input type="hidden" name="amount_<?php print($itemCnt); ?>" value="20">
			<?php
			$itemCnt++;
		}
		// Repeat as necessary...
		?>
	</form>

Connecting PHP to Oracle with OCI8

Below is the set of steps I took to connect a XAMPP setup to an Oracle database server. I am doing this on a server running Windows Server 2016.

  1. Download and install XAMPP. I chose the one bundled with the 32-bit version of PHP 7.3.2 with thread safety. Sourceforge: xampp-win32-7.3.2-0-VC15-installer.exe
  2. Download and install Oracle Instant Client. Because the PHP architecture is 32-bit, Oracle Instant Client must also be 32-bit. Take note of your Oracle server’s version, and install the appropriate client. Oracle: Oracle Instant Client Downloads
  3. Set up your sqlnet.ora and tnsnames.ora files at %ORACLE_HOME%\network\admin\. These two files together will tell your Oracle Instant Client how to get to your Oracle server. Sample sqlnet.ora:
    NAMES.DEFAULT_DOMAIN = MYDOMAIN.NET
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)
    

    Sample tnsnames.ora:

    DB.MYDOMAIN.NET =
    	(DESCRIPTION =
    		(ADDRESS_LIST =
    			(ADDRESS = (PROTOCOL = TCP)(HOST = odbserver3.mydomain.net)(PORT = 1521))
    		)
    		(CONNECT_DATA =
    			(SERVICE_NAME = db)
    		)
    	)
    
  4. In Windows, add the folder of Oracle Instant Client to the PATH system variable. Create a new system variable called ORACLE_HOME and put the same path in there as well.
  5. Download the OCI8 package. I chose the one labeled “7.3 Thread Safe (TS) x86” in order to match my PHP version, thread safe configuration, and the 32-bit architecture. Once downloaded, extract either php_oci8.dll, php_oci8_11g.dll, or php_oci8_12c.dll (depending on your Oracle server version) and place the DLL file in the PHP Extensions directory. By default, that folder for my installation of XAMPP is C:\xampp\php\ext\. PHP PECL: OCI8 for Windows
  6. Launch XAMPP Control Panel and start the Apache server. Add phpinfo(); to one of your pages to see if OCI8 has its own section; if so, you are ready to make your connection to your Oracle server. Here’s a quick and dirty PHP snippet you can use to do a quick connection test:
    $conn = oci_connect($user, $password, $server);
    $sql = "select user from dual";
    $stid = oci_parse($conn, $sql);
    oci_execute($stid);
    $row = oci_fetch_array($stid, OCI_BOTH);
    print($row["USER"]);
    oci_free_statement($stid);
    

Recurse through a directory with PHP

The PHP code below goes through a directory/folder recursively and display the files found as HTML links on a web page.

// Configuration
$cDrivePath = "C:\\inetpub\\site3\\filelibrary\\"; // Be sure to include slash at the end!
$webPath = "/filelibrary/"; // Be sure to include slash at the end!
// End configuration

function cleanUpFileName($fName, $removeThisString) {
	$ret = $fName;
	$ret = str_replace($removeThisString, "", $ret);
	$ret = str_replace("\\", " > ", $ret);
	return $ret;
}

function printDirContents(
		$path, 			// This is the HDD path to recurse into
		$cDrivePath, 	// Always carry the $cDrivePath value; used for beautifying text
		$webPath		// Always carry the $webPath value; used for beautifying text
	) {

	// Make sure we have a trailing slash and asterix
	$path = rtrim($path, "\\") . "\\*";

	// Make an array to hold all the sub-directories
	$dirs = glob($path, GLOB_ONLYDIR);
	
	// Make an array to hold all the files
	$files = glob($path); //This contains both sub-directories and files
	$files = array_diff($files, $dirs); // Remove sub-directories from the array
	
	// Print files
	foreach ($files as &$f) {
		$fnDownload = str_replace($cDrivePath, "", $f);
		$fnDisplay = cleanUpFileName($f, str_replace("*","",$path));
		print("[a href='".$webPath.$fnDownload."']".$fnDisplay."[/a]");
	}
	
	// Recurse into sub-directories
	foreach ($dirs as &$d) {
		print("[h3]".(cleanUpFileName($d, $cDrivePath))."[/h3]";
		// Print folder
		printDirContents($d, $cDrivePath, $webPath);
	}
}
$displayArray = printDirContents($cDrivePath, $cDrivePath, $webPath);

Connecting from PHP to an Oracle database

This article assumes that your “tnsnames.ora” file has already been configured. For the propose of this article, we will assume that the TNS entry we are attempting to connect to is called “oradb”.

// Database connection
$conn = oci_connect("scott", "tiger", "oradb");
if (!$conn) {
   $m = oci_error();
   trigger_error(htmlentities($m["message"], ENT_QUOTES), E_USER_ERROR);
}

// Perform a sample query
$sql = oci_parse($conn, "select user from dual");
oci_execute($sql);
 
while (($row = oci_fetch_array($sql))){
	print($row["user"]);
}

// Close the connection
oci_free_statement($sql);
oci_close($conn);

Update Twitter status programmatically with PHP and cURL

function twitterUpdate($u, $p, $upd) {
	// $u is your username (string)
	// $p is your password (string)
	// $upd is your status update (string)

	$url = 'http://twitter.com/statuses/update.xml';

	$curl_handle = curl_init();
	curl_setopt($curl_handle, CURLOPT_URL, $url);
	curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
	curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($curl_handle, CURLOPT_POST, 1);
	curl_setopt($curl_handle, CURLOPT_POSTFIELDS, "status=".$upd);
	curl_setopt($curl_handle, CURLOPT_USERPWD, $u.":".$p);
	$buffer = curl_exec($curl_handle);
	curl_close($curl_handle);

	if (empty($buffer)) {
		return 'fail';
	}
	else {
		return 'success';
	}
}

Example usage is below.

$u='username';
$p='password';
$upd='Hello world!';

$twitterUpdateResult = twitterUpdate($u, $p, $upd);

echo $twitterUpdateResult;

Get image width and height with PHP

I was working on my World War II photo gallery recently. The gallery’s HTML code, particularly the main content section, looks something like the following.


<div id="content">
	<div class="main_column">
		<img src="images/helloWorld.jpg">
		<!--
		The photograph and other "main content" goes here.
		The width of this column is 600px.
		-->
	</div>

	<div class="extra_column">
		<!--
		This is the vertical side bar.
		If the width of the photo in the main section is more
		than 600px, it will overlap this div.
		-->
	</div>
</div>

Because my vertical navigation bar is on the right side of the page, if a photograph is larger than a particular width, it overlaps the vertical bar. I decided to remove the vertical bar if the photo is too large; let us assume that if the image width is larger than 580 pixels, it is too large. To apply the logic, I used the getimagesize() function. A quick example on this function is below.

list($imageWidth, $imageHeight, $imageType, $imageAttr) = getimagesize($imgName);
echo("The width of the image is ".$imageWidth." pixels.");
// Sample output: "The width of the image is 600 pixels."

I used the following PHP code to make my page work the way I want to based on width of the image.


<div id="content">
	<?php
	$imgName = "helloWorld.jpg";

	list($imageWidth, $imageHeight, $imageType, $imageAttr) = getimagesize($imgName);

	if ($imageWidth <= 580) { ?>
		<div class="main_column">
	<?php } ?>

	<img src="images/helloWorld.jpg">
	<!--
	The photograph and other "main content" goes here.
	-->

	<? if ($imageWidth <= 580) { ?>
		</div>
		<div class="extra_column">
		<!--
		This is the vertical side bar.
		Only show this if image width
		is 580 pixels or smaller.
		-->
		</div>
	<?php } ?>
</div>

Beyond width, the getimagesize() function in PHP also outputs other properties; the output is an array containing four elements. As you may have noticed, in my above example, I captured the following properties.

  • 0 $imageWidth : Width of the image in pixels; integer.
  • 1 $imageHeight : Height of the image in pixels; integer.
  • 2 $imageType : Type of the image; integer. Possible values are as follows:
    Value Description
    GIF
    JPG
    PNG
    SWF
    PSD
    BMP
    TIFF (Intel)
    TIFF (Motorola)
    JPC
    10  JP2
    11  JPX
    12  JB2
    13  SWC
    14  IFF
    15  WBMP
    16  XBM
  • 3 $imageAttr : HTML-formatted attributes; string. Example: ‘height=”600″ width=”400″‘

Performing 301 Redirect with Apache, IIS, PHP, and others

Apache

If you use Apache with the mod-rewrite module installed, 301 Redirect may be set up by editing your .htaccess file found at the root directory of your website. The following is a sample of what you might add to your .htaccess file; in this example, we are redirecting any visits to /blah/pictures.html to its new location, http://ww2db.com/photo.php.

Redirect 301 /blah/pictures.html http://ww2db.com/photo.php

Sometimes you may encounter a situation where you changed technology of your platform, for example, from static HTML pages to dynamic PHP pages. With the sample code below, you can redirect all HTML pages to PHP pages by the same name.

RedirectMatch 301 (.*).html$ http://ww2db.com$1.php

Looking to use .htaccess to redirect an entire site because you have moved from one domain to another? This is what you want to do:

Redirect 301 / http://ww2db.com/

PHP

If your site’s pages are made of PHP files, redirection is fairly easy. If you had moved the /old/pictures.php page to the new location of http://ww2db.com/photo.php, modify the /old/pictures.php page by adding the following PHP snipped on the top of the code, ie. before any HTML code is written to screen.

header("HTTP/1.1 301 Moved Permanently");
header("Location: http://ww2db.com/photo.php");

IIS

If you using Microsoft’s Internet Information Services (IIS), perform the following steps to do a 301 Redirect.

  • Right click on “My Computer”, go to “Manage”, “Services and Applications”, and then “Internet Information Services (IIS) Manager”.
  • Under “Web Sites”, look for the particular folder or file you wish to redirect. Right click and select “Properties”.
  • Click the “A redirection to a URL” radio button. In the bottom section that had just appeared, and enter the new URL in the “Redirect to:” text box, and make sure to check “A permanent redirection for this resource” checkbox.
  • Click OK to complete the process.

ASP and ASP.NET

Classic Active Server Pages (ASP) web pages can be redirected in a method very similar to PHP above.

Response.Status="301 Moved Permanently"
Response.AddHeader "Location","http://ww2db.com/photo.php"

ASP.NET redirect code is very similar to its ASP predecessor’s.

private void Page_Load(object sender, System.EventArgs e) {
  Response.Status="301 Moved Permanently";
  Response.AddHeader("Location","http://ww2db.com/photo.php");
}

JSP

Redirection for Java Server Pages (JSP), again, is similar to the other scripting languages we have seen above.

response.setStatus(301);
response.setHeader("Location","http://ww2db.com/photo.php");
response.setHeader("Connection", "close");

CGI/Perl

If you are using Perl-based CGI code, this is the code you may wish to deploy to perform a 301 Redirect.

$q=new CGI;
print $q->redirect("http://ww2db.com/photo.php");

Cold Fusion

ColdFusion pages can be redirect also by working with the header.

<.cfheader statuscode="301" statustext="Moved permanently">
<.cfheader name="Location" value="http://ww2db.com/photo.php">

Ruby on Rails

Not surprisingly, Ruby on Rails pages are redirected in a very similar manner.

def old_action
headers["Status"]="301 Moved Permanently"
redirect_to "http://ww2db.com/photo.php"
end

How to output a string in PHP

In PHP, to output a line, we can use either the echo() or print() functions. All six examples below have the same output of “hello world”:

echo('hello world');
print('hello world');
print 'hello world';

echo("hello world");
print("hello world");
print "hello world";

Note the print() function can work without parenthesis. That is because print() is not really a function; print() is actually a language construct. Functions print() and echo() pretty much do the same thing, so generally you can use whichever one you prefer, though echo() actually gets processed faster, so if you are working with a larger application, this may be something to keep in mind.

To output a string variable, we can do something like this:

$name = "world";

echo('hello '.$name);
print('hello '.$name);
print 'hello '.$name;

echo("hello ".$name);
print("hello ".$name);
print "hello ".$name;

echo("hello $name");
print("hello $name");
print "hello $name";

Once again, all examples above output “hello world”, and the character used to concatenate the “hello” and the variable that contains “world” is the period character; the period is equivalent to the ampersand sign in VB/ASP and the plus sign in Java/JSP. Now, see the third set above where we used double quotes to enclose the string and the variable together. This is a special functionality you can use only with double quotes. It is a way to make your coding process slightly simpler, and the resulting code slightly easier to read, but keep in mind that this method makes the code run just a tad bit slower because when the PHP engine sees the double quote, it needs to be prepared to find and process variables — even if none are found.

In conclusion, echo() and print() are nearly identical, and the usage of double and single quotes are nearly identical, and you may choose whichever one that suits your style better. In terms of optimizing processing speed, however trivial, using echo() with single quote is preferred.