Dexter – Page 2 – 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.

Sql Patterns

http://stackoverflow.com/questions/1382842/mysql-insert-joins

INSERT INTO tableNew (col1, col2)
  SELECT tbl1.col1, tbl2.col2
  FROM tbl1 JOIN tbl2

DELETE users, comments
FROM users JOIN comments ON comments.author=users.id
WHERE users.isspammer=1

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active',
A.status='Active'
Where A.id=1 or A.id=2 or A.id=3

Coalesce

Here’s an interesting SQL function:

Coalesce

Given a list, returns the first non-null value in the list. If all values are null, returns null.

http://www.creativedev.in/2012/03/mysql-coalesce-function/
http://stackoverflow.com/questions/6862373/mysql-join-with-insert-combined

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);
        }
?>

Arguments against exposing auto-incrementing numbers for SQL queries

This link had some good arguments against exposing auto-incrementing numbers on a web page.

http://stackoverflow.com/questions/8078912/is-mysql-auto-increment-safe-to-use-as-userid

But generally, web developers feel uncomfortable exposing IDs that allow guessing other IDs by just incrementing or decrementing a number. Many resort to random, multi-digit IDs instead.

On a more obscure note, numeric IDs may also allow competitors to estimate your growth by keeping track of how the incremental value increases.

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 );

Starting and Ending Transactions

Following the spirit of atomic operations, I found a link on starting and ending a transaction.

http://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli

And this might be a good link for figuring out where to place the commit statement when using prepared statements.

http://stackoverflow.com/questions/10642635/how-can-i-use-prepared-statements-combined-with-transactions-with-php

Atomic operations and trying to report the right quantities.

A peeve of mine is trying to purchase something online from an e-commerce site which specifically states In Stock, and then waiting for weeks only to discover I’ve been back-ordered. It’s doubly bad when I’m trying to buy myself a christmas present. Yup, myself.

I haven’t tried the method listed in the below link yet, but I’m setting it aside in case I need it in the future.

http://stackoverflow.com/questions/11278494/mysql-atomic-operations-and-table-locking

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.

Relative Linking in PHP

This article assumes that you have an Apache / PHP server set up. If not, I’d suggest something like XAMPP. It’s downright nifty how, if you grab the zip file, you can start the server and use it right away.

For a basic premise, I’m trying to avoid using absolute links in order to make deployment easier when I copy the website from my development computer onto a production server.

I’m actually under the impression that php maintains a relative linking system based on the file that it’s parsing at the time.

For instance, create two directories and a subdirectory, we’ll name them Test1, Test3, and Test2 respectively. Lay them out like this:

Test1
– Test2
Test3

So Test2 is a subdirectory of Test1. Test3 is its own directory.

Now, inside of Test2, type up the following file named file3.php:

And inside Test3, type up the following file named file1.php:

And inside Test1, type up the following file named file2.php:

So the final layout should be something like:
Test1
–     file2.php
–     Test2
–     file3.php
Test3
–     file1.php

Now, try opening Test3/file1.php. I believe that your final product should show “Hello World.”

I’m pretty certain that there may be an easier way to maintain a relative linking system, but the interesting part of this method is that you just need to know the entry point. Which, in the example above, was Test1.

From Test1, I can access all subdirectories relatively. In other words, you will notice that I did not use the following code in file2.php:

The above works on the theory that the php maintain an absolute link to the file that it’s currently executing (Test2/file1.php) and bases all relative links off of it. Oddly enough, the above code does work.

But I think it works this way:
Starting from the directory Test1:
– go back a directory
– then open up the directory Test1
– open up the directory Test2
– and then open file3.php.

I wanted the example to mean:
Because we’re executing Test2/file1.php, we start from the directory Test2:
– go back a directory
– then open up the directory Test1
– open up the directory Test2
– and then open file3.php.

Therefore, to test whether my theory is correct, I move file3.php to directory Test2:
Here’s the new code for file2.php:

This is the new layout:
Test1
–     file2.php
–     Test2
Test3
–     file1.php
–     file3.php

If my theory is true, we’ll get an error. Otherwise, I revise my claim that php maintains a flexible linking system. I would have to say that it maintains a REALLY flexible linking system. Because if the above does run without error, it would mean that php maintains at least two ways of resolving relative links.

Huh? It still runs.

One more test then.
Test1
–     file2.php
– file3.php
–     Test2
Test3
–     file1.php
–     file3.php

The file3.php under Test1 still contains echo “World”.
The file3.php under Test3 contains echo “World 2“

Run…and, we get “Hello World”

Delete the file3.php under Test3

We get “Hello World 2”

So I think it searches for the file from the initial script first. If it doesn’t find the file, it looks for the file from the path of the currently executing script.