So I've now gone through the tutorial and a few chapters of the Beginning PHP5 book. As I'd imagine most of you can understand, I'm ready to do some "real" coding!! Time to do something more hands on, so I decided to take a fairly simple bit of ColdFusion code and "convert" it to the PHP equivalent.
The code I'm using is slightly expanded version of the example I showed in the recent dataTables post, and comes from some stuff I've been working on my personal site – except that I took stuff from a few places and shoved it all together so that the conversion is easier to follow. In reality, the function would be a DAO and accessed via a service and that call to the service would be handled by the framework. 🙂
I know this conversion isn't going to hit every thing I've covered in PHP so far for sure, but this is a pretty common bit of functionality that can regularly be found in our apps so a good start. Also, to keep the bits more compact, I'm not including the HTML head stuff in either sample. It's pretty much the same as it was in the dataTables post, except with a little CSS added for styling the header.
So first, the ColdFusion code.
<cffunction name="getBooks" acces="public" returntype="struct" output="false">
<cfargument name="SeriesID" type="string" required="false" default="" />
<cfargument name="PublisherID" type="string" required="false" default="" />
<cfargument name="PlatformID" type="string" required="false" default="" />
<cfargument name="GenreID" type="string" required="false" default="" />
<cfargument name="LimitYear" type="string" required="false" default="" />
<cfset var qVideoGames = "" />
<cfset var sMyResults = StructNew() />
<cfset sMyResults.ResultCode = 200 />
<cfset sMyResults.ResultMessages = ArrayNew(1) />
<cfset sMyResults.ResultQuery = ArrayNew(1) />
<cftry>
<cfquery name="qVideoGames" datasource="#variables.dsn#">
SELECT STRAIGHT_JOIN videogameid, title, dateacquired, num_discs, esrb_rating, series_position, description, upcode, amazon_asin,
publishers_publisherid, genres_genreid, platforms_platformid, series_seriesid,
videogames_publishers.publisher, videogames_genres.genre, videogames_platforms.platform, videogames_series.seriesname, videogames_series.numberoftitles
FROM (videogames_videogames INNER JOIN videogames_publishers ON publishers_publisherid = videogames_publishers.publisherid
INNER JOIN videogames_genres ON genres_genreid = videogames_genres.genreid
INNER JOIN videogames_platforms ON platforms_platformid = videogames_platforms.platformid)
LEFT JOIN videogames_series ON series_seriesid = videogames_series.seriesid
WHERE 1 = 1
<cfif IsNumeric(Arguments.SeriesID) AND Arguments.SeriesID GT 0>
AND series_seriesid = <cfqueryparam value="#Arguments.SeriesID#" cfsqltype="cf_sql_integer" />
</cfif>
<cfif IsNumeric(Arguments.PublisherID) AND Arguments.PublisherID GT 0>
AND publishers_publisherid = <cfqueryparam value="#Arguments.PublisherID#" cfsqltype="cf_sql_integer" />
</cfif>
<cfif IsNumeric(Arguments.PlatformID) AND Arguments.PlatformID GT 0>
AND platforms_platformid = <cfqueryparam value="#Arguments.PlatformID#" cfsqltype="cf_sql_integer" />
</cfif>
<cfif IsNumeric(Arguments.GenreID) AND Arguments.GenreID GT 0>
AND genres_genreid = <cfqueryparam value="#Arguments.GenreID#" cfsqltype="cf_sql_integer" />
</cfif>
<cfif IsNumeric(Arguments.LimitYear) AND Arguments.LimitYear GT 0>
AND date('year', dateacquired) = <cfqueryparam value="#Arguments.LimitYear#" cfsqltype="cf_sql_integer" />
</cfif>
ORDER BY title
</cfquery>
<cfif qVideoGames.RecordCount GT 0>
<cfset sMyResults.ResultQuery = qVideoGames />
<cfelse>
<cfset sMyResults.ResultCode = 420 />
<cfset ArrayAppend(sMyResults.ResultMessages, "I couldn't find any video games meeting the requested criteria") />
</cfif>
<cfcatch>
<cfset sMyResults.ResultCode = 300 />
<cfset ArrayAppend(sMyResults.ResultMessages, "A system error occurred while trying to retrieve the list of video games. I've been notified.") />
<cflog application="yes" file="app_#LCase(application.applicationname)#" type="error" text="test:51 - #cfcatch.Type#: #cfcatch.Message#: #cfcatch.Detail#" />
</cfcatch>
</cftry>
<cfreturn sMyResults />
</cffunction>
<cfset sAllRPGs = getBooks(GenreID = 3) />
<cfif sAllRPGs.ResultCode EQ 200>
<cfset qVideoGames = sAllRPGs.ResultQuery />
<h1>All the <cfoutput>#qVideoGames.genre#</cfoutput> Games I Own</h1>
<table id="gameList">
<thead>
<th>Title</th>
<th>Date Acquired</th>
<th>ESRB</th>
<th>Publisher</th>
<th>Platform</th>
<th>Series Name</th>
</thead>
<tbody>
<cfoutput query="qVideoGames">
<tr>
<td><a href="gameDetails.cfm?VideoGameID=#videogameid#">#title#</a></td>
<td>#DateFormat(dateacquired, "mm/dd/yyyy")#</td>
<td>#esrb_rating#</td>
<td><a href="gamesByPublisher.cfm?PublisherID=#publishers_publisherid#">#publisher#</a></td>
<td><a href="gamesByPlatform.cfm?PlatformID=#platforms_platformid#">#platform#</a></td>
<td><a href="seriesDetail.cfm?SeriesID=#series_seriesid#">#seriesname#</a></td>
</tr>
</cfoutput>
</tbody>
</table>
<script type="text/javascript">
$(document).ready(function(){
$('#gameList').dataTable({
"bJQueryUI": true,
"aaSorting": [[ 1, "desc" ]],
"aoColumnDefs": [
{ "bWidth": 100, "sClass": "alignCenter", "aTargets": [ 1,3 ] },
{ "bWidth": 120, "sClass": "alignRight", "aTargets": [ 2 ] }
]
});
});
</script>
<cfelse>
<p class="error">Something went wrong (#sAllRPGs.ResultCode)#:</p>
<ul>
<cfloop array="#sAllRPGs["ResultMessages"]#" index="thisMessage">
<li>#thisMessage#</li>
</cfloop>
</ul>
</cfif>
I must admit, redoing this in PHP was both easy and frustrating as heck. How PHP can be so far from where it was when I did it 10 years ago and still not have the ability to pass in arguments to a function by name like you can with ColdFusion is beyond me. It took me awhile to figure out just how to get my code to work when I was only passing in one of the optional arguments.
Also, date formatting is a pain in the ass and for some reason PHP had bigger issues with foreign characters versus ColdFusion. It was in writing this that I made the discovery mentioned in my last post about having to switch to mysqli, so had to redo all that too. 🙂
<?php
function getBooks($SeriesID = '', $PublisherID = '', $PlatformID = '', $GenreID = '', $LimitYear = ''){
$dbConnection = new mysqli("localhost", "username", "password", "phptraining");
$dbConnection->set_charset("utf8");
$qVideoGames = "";
$sMyResults = array();
$sMyResults["ResultCode"] = 200;
$sMyResults["ResultMessages"] = array();
$sMyResults["ResultQuery"] = "";
try {
$sqlStatement = "
SELECT STRAIGHT_JOIN videogameid, title, dateacquired, num_discs, esrb_rating, series_position, description, upcode, amazon_asin,
publishers_publisherid, genres_genreid, platforms_platformid, series_seriesid,
videogames_publishers.publisher, videogames_genres.genre, videogames_platforms.platform, videogames_series.seriesname, videogames_series.numberoftitles
FROM (videogames_videogames INNER JOIN videogames_publishers ON publishers_publisherid = videogames_publishers.publisherid
INNER JOIN videogames_genres ON genres_genreid = videogames_genres.genreid
INNER JOIN videogames_platforms ON platforms_platformid = videogames_platforms.platformid)
LEFT JOIN videogames_series ON series_seriesid = videogames_series.seriesid
WHERE 1 = 1";
if (isset($SeriesID) AND is_numeric($SeriesID))
$sqlStatement .= " AND series_seriesid = " . $SeriesID;
if (isset($PublisherID) AND is_numeric($PublisherID))
$sqlStatement .= " AND publishers_publisherid = " . $PublisherID;
if (isset($PlatformID) AND is_numeric($PlatformID))
$sqlStatement .= " AND platforms_platformid = " . $PlatformID;
if (isset($GenreID) AND is_numeric($GenreID))
$sqlStatement .= " AND genres_genreid = " . $GenreID;
if (isset($LimitYear) AND is_numeric($LimitYear) AND len($LimitYear) == 4)
$sqlStatement .= " AND date('year', dateacquired) = " . $LimitYear;
$qVideoGames = $dbConnection->query($sqlStatement);
if ($qVideoGames->num_rows > 0) {
$sMyResults["ResultQuery"] = $qVideoGames;
}
else {
$sMyResults["ResultCode"] = 420 ;
array_push($sMyResults["ResultMessages"], "I couldn't find any video games meeting the requested criteria");
}
}
catch (Exception $e) {
$sMyResults["ResultCode"] = 300;
array_push($sMyResults["ResultMessages"], "A system error occurred while trying to retrieve the list of video games. I've been notified.");
$logFileMessage = $e->getFile() . ":" . $e->getLine() . " - " . $e->getMessage() . ": " . $e->getCode() . "\n";
error_log($logFileMessage, 3, $appErrorLog);
}
return $sMyResults;
};
$aAllRPGS = getBooks('', '', '', 3, '');
if ($aAllRPGS["ResultCode"] == 200) {
$qVideoGames = $aAllRPGS["ResultQuery"];
$aGenre = mysqli_fetch_row($qVideoGames);
echo "<h1>All The " . $aGenre[14] . " Games I Own</h1>";
?>
<table id="gameList">
<thead>
<th>Title</th>
<th>Date Acquired</th>
<th>ESRB</th>
<th>Publisher</th>
<th>Platform</th>
<th>Series Name</th>
</thead>
<tbody>
<?php
while($thisRow = $qVideoGames->fetch_assoc()) {
echo '<tr>';
echo ' <td><a href="gameDetails.php?VideoGameID' . $thisRow["videogameid"] . '/">' . $thisRow["title"] . '</a></td>';
echo ' <td>' . date('m/d/Y', strtotime($thisRow["dateacquired"])) . '</td>';
echo ' <td>' . $thisRow["esrb_rating"] . '</td>';
echo ' <td><a href="gamesByPublisher.php?PublisherID' . $thisRow["publishers_publisherid"] . '/">' . $thisRow["publisher"] . '</a></td>';
echo ' <td><a href="gamesByPlatform.php?PlatformID' . $thisRow["platforms_platformid"] . '/">' . $thisRow["platform"] . '</a></td>';
echo ' <td><a href="seriesDetail.php?SeriesID' . $thisRow["series_seriesid"] . '/">' . $thisRow["seriesname"] . '</a></td>';
echo '</tr>';
}
?>
</tbody>
</table>
<script type="text/javascript">
$(document).ready(function(){
$('#gameList').dataTable({
"bJQueryUI": true,
"aaSorting": [[ 1, "desc" ]],
"aoColumnDefs": [
{ "bWidth": 100, "sClass": "alignCenter", "aTargets": [ 1,3 ] },
{ "bWidth": 120, "sClass": "alignRight", "aTargets": [ 2 ] }
]
});
});
</script>
<?php
}
else {
echo '<p class="error">Something went wrong (' . $aAllRPGS["ResultCode"] . ')#:</p>';
echo '<ul>';
foreach($sAllRPGs["ResultMessages"] as $thisMessage => $value) {
echo "<li>" . $thisMessage . "</li>";
}
echo '</ul>';
}
?>
In both cases, the results were identical, so yay!
