[nycphp-talk] naming identifiers

lists at lists at
Sun Aug 30 01:18:58 EDT 2009

Hi Dan,

Daniel Convissor wrote:
> Your big mistake was delimiting the identifiers when creating the table.
> "Identifiers" are table names, field names, index names, etc.  The 
> "delimiter" for these is "`" in MySQL.  When you use the delimiter around 
> the identifiers, the database allows you to create nearly any name you 
> can possibly think of, including "reserved words."  Reserved words are, 
> well words that the system has reserved for its own internal use, so 
> should not be utilized by users.  As you've now discovered, delimiting 
> identifiers when creating tables is the genesis of a world of pain.

This is really important stuff and I don't remember it being covered in
any of my "beginner" books. Grrr.

> If you tried to create a table with a name called "desc" in it without 
> delimiting it, MySQL would have complained to you up front, saving you 
> the agony.

The thing is, I have no idea how I managed to delimit the identifiers. I
created my everything using phpMyAdmin--could that have been the
culprit? The reason I ask is because I just tried it again and MySQL
didn't complain. Since you said it should be complaining, I decided to
attempt the same using a trial version of Navicat that I downloaded a
few days ago. Sure enough, MySQL threw a syntax error right away. WTF?

If you tell me that I should be talking to MySQL from the command line
I'm going to jump out the window forthwith. ;)

>> Or like the time (very recently) when I was converting timestamps to
>> calculate the number of days between two given dates. I used the
>> number_format() function...
> Uh, just making sure...  Did you convert the timestamps via 
> number_format() then do the math?  Ouch.  This is the kind of stuff the 
> date functions, like date_diff(), etc, in MySQL and PHP are for.  
> Faster, more accurate, easier.  Then format the result.

Actually, my description wasn't very accurate. I guess I sort of did it
in the middle--after the math to calculate the number of days between
"today" and the expiration date, but before telling PHP to check for a
specific number days See below). Where you now see round() is where I
was previously using number_format():

$todays_date = date("Y-m-d");
$today = strtotime($todays_date);
$exp_date = strtotime($exp_date);
$diff = ($exp_date - $today);
$num_days = ($diff / 86400);
$num_days = round($num_days, 0);
$exp_date = date('m-d-y', $exp_date);
if ($num_days <= 60 && $num_days >= 46) {
	$exp_date = '<span class="exp60Days">'.$exp_date.'</span>
} //...and so on

In MySQL the exp_date field data type is DATE. I strongly suspect that
I'm handling my dates in the most verbose & confusing way possible, and
I know for a fact that I didn't properly normalize my tables. IOW, I
managed to make things work, but it's a big mess.

I'm currently trying to redo everything, but with limited success as
trying to grope my way through the fog of my inexperience results in
frequent rounds of tripping & falling face-first into the mud. I
eventually figure stuff out and inch my way up a little, but it's a
slow, painful climb.

Sometimes I sort of feel like Sisyphus. *sigh*

I'm going to go check out the date_diff() function...


More information about the talk mailing list