Trivia – Uncommon Solutions

Aliases in a Select Statement

Did you know that if you define an alias when selecting columns, you probably can’t use it inside of a WHERE clause without getting an Unknown Column error?

The standard explanation given by This site (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html) and used by many of the sites that I found with google explains it by saying that:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

I think I found a layman’s explanation for the problem here:
http://stackoverflow.com/questions/153598/unknown-column-in-where-clause

SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name as not yet occurred.

And I believe a workable solution can be found here:
http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error

So…instead of using WHERE, use HAVING.

Here’s the conclusion of when to use either as found by:
http://www.codeproject.com/Articles/25258/Where-Vs-Having-Difference-between-having-and-Wher

Here’s the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

and

Here’s another rule: You can’t use HAVING unless you also use GROUP BY.

Debugging relative links in php

First I should mention that I’ve gotten good results when using the get_cwd() function. With it, I can tell what directory I am starting from, and from there build the appropriate relative paths.

It’s simple to use.

echo getcwd();

Run the script, and you’ll know where your starting point is.

That said, beware of the register_shutdown_function(). I noticed the get_cwd() result changed while inside of this function. I found a link with a workaround, but the gist of it is that the working directory of the script can change.

http://stackoverflow.com/questions/10861606/write-to-file-with-register-shutdown-function

Here’s a sample you can use:

<?php

        echo "Using Dir: " . __DIR__;
        echo "\n\n";
        echo "Using Get Current Working Directory: " . getcwd();
        echo "\n\n";
        echo "Using Get Include Path: " . get_include_path();
        echo "\n\n";

        $cwd = getcwd();

        register_shutdown_function(function(){
            echo "GetCWD inside of register_shutdown_function:" . getcwd();
               exit(1);
        });

        function throw_error($message)
        {
            trigger_error($message, E_USER_ERROR);
            exit(1);
        }
?>

Find the Bug!

Here’s the query to troubleshoot:

UPDATE `Items` SET ( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 );

When you run the above query, you’ll get a funny message:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 )' at line 1

So where’s the syntax error?

It turns out that the parenthesis within the SET statement is a syntax error. I’m currently not sure why the parenthesis cause problems, but you need to take them out in order to run the above query.

So, the working query would be:

UPDATE `Items` SET `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') WHERE ( `Serial`=33 );

Names, ID’s, Classes, and whatnot

When working with an HTML page, the attributes that you need depend on the plugins that you’re using.

Name Attribute:
Used when submitting a post.

ID Attribute:
Used by Jquery to find an element when doing a $(#id_name).
Used by CSS. Prefixed by (#)

Class Attribute:
Used by CSS. Prefixed by (.)

I didn’t cover all of the cases, since I jotted down what I remembered, but it is a good idea to create a table for this stuff.

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.

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