Automatic Dates

It’s easier if you don’t have to use a query to insert the current time and date.  This is particularly useful for “date created” and “date modified” columns. MySQL doesn’t let you do both at the same time for one table, but there are ways around it.

http://joegornick.com/2009/12/30/mysql-created-and-modified-date-fields/

I used the second option, inserting a null into the column.  I read somewhere that it’s sort of hacky, however it appears to be legitimate behavior as seen in below link.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html (Scroll to the bottom)

Personally, I prefer to insert a NOW() value instead of NULL since it’s more intuitive.

SSL theory

When preparing a site for Secure Socket Layer (SSL), it’s been said that you need to use relative paths on the website.

http://stackoverflow.com/questions/64631/what-does-a-php-developer-need-to-know-about-https-secure-socket-layer-connect

I believe that this is mainly because if you used absolute paths, you would usually throw an http:// in front of the path.  Or even if you don’t, you could be pulling the images from an external source that defaults to an unsecure service.

That being said, I was trying to keep paths relative on the php server itself.  I know why I was doing it, but if you think about it carefully…doing so does not achieve the same result as above.

 

See if you can spot the error!

$ControlColumns = array("col1", "col2", "col3", "col4", "col5");
$params = array();
$cols = array();
$colnames = array_column($ControlColumns, 1);

$bindtypes = "sssss";

foreach ($colnames as $col)
{
    $params[] = &$cols[$col];
}

$result = mysqli_stmt_bind_param($stmt, $bindtypes, $params);

Here’s the error:

Number of elements in type definition string doesn’t match number of bind variables in E:\XAMPP\xampp\htdocs\PHPTest\insert.php

And assume that the $stmt is a perfectly prepared statement with multiple parameters. In other words, it works fine.

Give up?

I guess I’m just stupid, but it took me an hour of messing around before I finally remembered that mysqli_stmt_bind_param was not designed to take an array input.

Use call_user_func_array to call that function instead.  Remember to reference the variables.

$params = array();
$params[] = $stmt;
$params[] = &$bindtype;

$result = call_user_func_array('mysqli_stmt_bind_param', $params);

 

No placeholders to bind to in MySQL prepared statements.

I suppose this is just common sense, but I really couldn’t find anything on the subject. I had a situation where I wanted to list everything in the database. In this case, there was no need for a WHERE clause in my query.

Binding an empty string using mysqli_stmt_bind_param($stmt, “”, array()); will throw an error. So I added a check to see if there are any parameters to bind. If not, don’t call this function. It seems to work and I’m still waiting for the other shoe to drop.

PHP Foreach and Reference Variables

Here’s a crazy gotcha.  I was playing with references when I realized that I was getting the wrong values.

Here’re the values that I used:
$valuearray = array(‘%CTRL%’, ‘%2%’);

Here’s my original loop:

foreach($valuearray as $val)
(
    $params[] = &$val;
)

 

And here’s what the print_r($params) gave me.

Array
(
    [0] => %2%
    [1] => %2%
)

Do you see the problem yet?

In a nutshell, I was trying to load references of each element in $valuearray into $params.  It wasn’t working too well because I was shoving the reference of $val itself into $params.  Whatever $val last points at is what’s going to show up for all elements in the array.  Interesting!  But ultimately not what I was aiming for.

http://www.php.net/manual/en/control-structures.foreach.php

The above link had part of the solution.  The next step of the debugging process would be this loop (I’ve highlighted the change in cyan):

foreach($valuearray as &$val)
(
    $params[] = $val;
)

And here’s what the print_r($params) gave me.

Array
(
    [0] => %CTRL%
    [1] => %2%
)

That’s correct.  But we’re not out of the woods yet.  If you’re trying to shove this into a mysql query that needs references, it should give you an error that a value was received when it was expecting a reference.

Here’s the final iteration (I’ve highlighted the change in cyan):

foreach($valuearray as &$val)
(
    $params[] = &$val;
)

And now I’ve fulfilled my original goal, load references to an array’s elements into another array.

Enclosing MySQL Columns with Grave Accents

Now here’s the reason why I created this blog.  It’s an uncommon solution.  Uncommon because I had a hard time finding a solution by using google and ended up altering the method until it worked in a way that made some sense.

Let me start by saying that there are some strange nuances with SQL columns when you surround them with grave accents.

The problem?  I was  trying to enclose the column names with grave accents to keep my code readable and reliable even though I was using JOINS.

Let me start with what did not work…  `Table_Name.Column_Name`.  I immediately got a “unknown column in field list” error.

After some poking around, this is what did work Table_Name.`Column_Name`.  Note how only the column name is surrounded by the grave accents.  This style works, leading me to believe that Table_Name can be enclosed by a separate set of grave accents.

MySQL special characters

I was playing around with a certain query only to come up with 0 results every time.  After looking around, I found that, in MySQL, the samples that actually tried to surround the table column names used the the grave symbol (`), not to be confused by the single quotes (‘).  The grave symbol is found on the same button as the tilde (~).

For example, if there was a table column called Name…

Using ‘Name’ in your query would probably create 0 results.

Using `Name` in your query would probably create the desired number of results.

http://stackoverflow.com/questions/7857278/what-is-the-meaning-of-grave-accent-aka-backtick-quoted-characters-in-mysql

Templating Engines

I was creating a website when I had the bright idea of pre-creating a webpage template and then filling in the values that I needed.  At first I was going to roll my own, and then I realized that there were many PHP templating engines in existence already.  I decided to give Twig a try since it seemed to be well-known.

http://devzone.zend.com/1886/creating-web-page-templates-with-php-and-twig-part-1/

  • Twig example.
  • Shows the basics.
  • Unfortunately, does not demonstrate a straightforward loop.

http://readwrite.com/2010/08/31/twig-templating-engine-quick-s#awesm=~opErZeZWdq7zLX

  • It’s almost crap.
  • Despite its claims, for a five minute guide, it doesn’t show the basics like how to include the dependencies.  In addition, the code throws an error if you put it in a try-catch block.
  • However this sample does show how to create a template with a loop.
  • The error disappeared when I replaced display/print with render/echo. I wonder if the error hid itself or did I really fix it.