PHP Tutorial Finale: Databases, Cookies, & GET/POST

 in PHP by (updated 2303 days ago)

Finally, the good stuff! 😉 Connecting with databases, running queries, and using the results. I suspect this will be a pretty fast run through for me unless they made some dramatic changes to how DB stuff is handled. Of course the tutorial uses MySQL, but since it’s what we use for all new development anyway as we “deprecate” PostgreSQL (*sad face*), that’s fine.

Connecting is pretty straight forward, though I still prefer the DSNs in ColdFusion 😉 It looks like assigning the connection to variable is more optional now, but since we write our code to flip between three servers (dev, staging, and production), I’m going to stick to using the variable.

I also really prefer the CFQUERY tag-based, in large part because it is so much easier to read the SQL (and to a lesser degree because in the ColdFusion tag based method, the SQL gets color-coded in my IDE LOL). It looks like PHP will at least let me do line breaks and indent the code, so that’s good. I’m going to use the same view I used for the earlier post on dataTables, because its’ here and it’s a nice way to compare the two. 🙂

<?php
    $dbConnection = mysql_connect("localhost", "username", "password");
    mysql_select_db("phpTraining", $dbConnection);
    
    $qVideoGames = mysql_query("
        SELECT videogameid, title, dateacquired, num_discs, esrb_rating, publisher, genre, platform, seriesname 
        FROM videogames
        ORDER BY dateacquired DESC, videogameid DESC
    ");
    
    echo "The table currently contains " . mysql_num_rows($qVideoGames) . " row(s)";
    echo "The most recently acquired game is " . mysql_result($qVideoGames, 0, "title") . " for the " . mysql_result($qVideoGames, 0, "platform");
?>

That’s a long way just to get one result versus just the qVideoGames.title for CF. 😛 Anyway, that works (well, did once I had my partner fix our PHP to put in the MySQL stuff LOL). So now for looping the results…again I’ll use my early version to “convert” from CF to PHP.

<table id="gameList" border="1" bordercolor="#000066" cellpadding="3" cellspacing="0">
<thead>
    <th>Title</th>
    <th>Date Acquired</th>
    <th># Discs</th>
    <th>ESRB</th>
    <th>Publisher</th>
    <th>Genre</th>
    <th>Platform</th>
    <th>Series Name</th>
</thead>
<tbody>
    <?php
        while($thisRow = mysql_fetch_array($qVideoGames)) {
            echo '<tr>';
            echo '    <td><a href="http://eclectic-world.com/index.cfm/event/videoGamesGameDetails/VideoGameID/' . $thisRow["videogameid"] . '/">' . $thisRow["title"] . '</a></td>';
            echo '    <td>' . $thisRow["dateacquired"] . '</td>';
            echo '    <td>' . $thisRow["num_discs"] . '</td>';
            echo '    <td>' . $thisRow["esrb_rating"] . '</td>';
            echo '    <td>' . $thisRow["publisher"] . '</td>';
            echo '    <td>' . $thisRow["genre"] . '</td>';
            echo '    <td>' . $thisRow["platform"] . '</td>';
            echo '    <td>' . $thisRow["seriesname"] . '</td>';
            echo '</tr>';
        }
    ?>
</tbody>
</table>

That’s decently simple, I guess, other than all the echo stuff. I guess I could do one running echo, though, to save on the repeats. (and no, I would never actually put a table into production using those HTML attributes, it was just a quick way to make the table slightly more decent looking for the example 😉 ). I’m skipping over the where, order by, etc parts because, um, yeah, I know MySQL. Knowing about the mysql_error() function is good, though the tutorial segment on that declines to mention how to trap the error (i.e. the equivalent of cftry/catch).

Well, that ended up being a really short section, so I’ll go ahead and move into the next – a one pager on cookies. Setting and getting seems pretty simple.

<?php
    setCookie("preferredRowCount", "10"); 
    
    echo "I would like " . $_COOKIE["preferredRowCount"] . " rows displayed at a time"; 
?>

After that, the tutorial moves into calling variables from _GET (URL scope) and _POST(form scope), as well as the other scopes. Very basic and it doesn’t seem any different from what I remember, so not bothering to run through those examples either. The last page of this tutorial covers validating form input, but it’s really basic and covers nothing new. The first part mentions doing more advance validations in a later part of the tutorial, but at this point I’ve reached the end, so maybe it’s still in progress?

So that wraps up my first PHP 5 refresher course follow along. It did help me get at least some basic stuff down, though it was not as thorough as I was hoping. It was a start at least. 🙂

Edit to add: Decided to go through some of the exercises in the Beginning PHP5 book by Mercer, Kent, Nowiciki, et al and published by WROX. It’s fairly dated, 2004, but it still covers core info and the exercises will let me get some more hands-on experience.

Also, I’ve realized that there is a bug in the SyntaxHighlighter causing it to blank out the BR tags from my code. So if you see echo “” it is supposed to be echo “” and many of the other blank “” at in the concatenated strings are actually “”. So far I haven’t found a fix as the code, and while this bug was report on the developer’s GitHUb 2 years ago, he has never responded.