April 2016 – Well Golly

Getting one row from a MySQL query

Often you just need one value or one row of values from a table. If your query returns a unique row because of the query or because you limited it to one row, then you don’t need to go through the foreach loop to get the values. You can change the fetchAll() to fetch() and use just one line to get the value.


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = :categoryID";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':categoryID', $categoryID);
$stmt->execute();

$results = $stmt->fetch();
$categoryName = $results['name'];

Breaking URLs and Long Text

When porting a website so that it displays well on phones, I ran across a problem wrapping long text without breaks. This text doesn’t wrap so the whole page gets a horizontal scroll.


<p class='break-word'>On my Windows 7 and 8 computer, with a login name of llsc the location that shows up in the top bar is:
Users\llsc\AppData\Roaming\LocuTourMultimedia\Client Manager\Data
</p>

I added a break-word class to my CSS and it works fine.


.break-word {
    word-wrap:break-word;
    overflow-wrap: break-word;
    word-break: break-word;
}

Responsive iTunes App Ad

When redesigning a site, I was having trouble getting one section to fit on an iPhone 5 without horizontal scrolling. It turns out that there were two issues, one with the Facebook widget and—rather ironically—one with the iTunes App ad.

The ad is in an iFrame and the dimensions are hard coded. e.g.


echo "<iframe src='https://banners.itunes.apple.com/banner.html?partnerId=0419Dm&amp;aId=&amp;id={$app_ID}&amp;c=us&amp;l=en-US&amp;bt=catalog&amp;t=catalog_white&amp;w=300&amp;h=250' style='overflow-x:hidden;overflow-y:hidden;width:300px;height:250px;'></iframe>";

Changing the dimensions either results in a cropped ad or nothing at all. I did however, stumble upon a solution. I reset the iFrame to 80% of the view window.


iframe {
  max-width: 80vw;
  max-height: 80vw;
}

CSS quirk

CSS stands for ‘Cascading Style Sheets’ and once you grasp the concept, it makes a lot of sense. So if you define a style for your paragraph text, say


.normal-paragraph {
    font-family: Helvetica;
    color: #333;

and later want to have a paragraph in the body be a different font, you can define and apply a new style.


.different-font {
    font-family: Times;

and apply it to the paragraph.

I was trying to do this with padding and it wasn’t working. Specifically I was adding a style to the heading to remove padding.


<h3 class='linkSection pad-no-bottom'>

What I thought should happen was that the browser would get the padding information from the linkSection CSS and then adjust it based on the padding information in the pad-no-bottom class. However, what it does is read the style information in the order in which it appears in the CSS file—not the order in which you call the classes. So I pulled all of my padding classes out of the main CSS file and put them into a separate file that is called last. I did the same thing for all of the other override classes as well. e.g. .indent, .italic, .centered, etc.

A Quick Note on Migrating to PDO

Migrating from mySQLi to PDO was really painless. I changed the database access as described previously. Then using the same query as previously, I added the database name `website` and then used the following code:


$qry = "SELECT * FROM product;
$res= $dbWG->query($qry);

if (!$res)
    die(mysqli_error($dbWG));

$numRows = mysqli_num_rows($res);

for ($i = 0; $i < $numRows; $i++) {
    $row   = $res->fetch_array();


$qry = "SELECT * FROM `website`.`product`;
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetchAll();
foreach($results as $row) {

One of the reasons to use PDO is that it has prepared statements that will automatically escape inputs so that injection attacks are mitigated.

It is simple to change the your code if you are using input from the user to construct the query.
If you do a simple convert, like above, to you end up with something like this:


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = $categoryID";
  
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetch();
categoryName = $results['name'];

A simple change in the query and a bind statement lets PHP automatically escape the user input.


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = :categoryID";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':categoryID', $categoryID);
$stmt->execute();

$results = $stmt->fetch();
$categoryName = $results['name'];