Introduction

PHP Knowledge level: Beginner.

This tutorial is for those who knows PHP enough to know that trouble shooting SQL statements and any other problems could be hard, especially when the file reaches greater than 500 lines of code. I have come up with a solution (a technique) for this. To make troubleshooting easier.

Requirements to accomplish this tutorial successfully... besides some kind of PHP server and knowledge, I have no problem with it. You can get WAMP for local testing... it's free.

The Problem

Have you ever tried to fix an error when all you get is Unknown 'column' in 'field list'? AND there is a ton of SQL on that page and you have to go and see which one is written incorrectly? Well, this tutorial would help you fix that problem.

If you already have a huge file you want to use this technique on... you can use a simple search/replace function build into most editors... even the regular Notepad that came with your computer (Probably 🤣 )

The Solution

There are two predefined constants that would help us here... The first one is the line number that the parser is on and the second one is the file that the function is on. This way you would know on which line and file the error occurred if you have a class or just simply some PHP code where you include one file into another.

1: LINE = The line that the parser is on.
2: FILE = The file that the parser is on.

Now, how can we get this to work for us? Lets say we have the following SQL statement (It has an error in it...)

<?php
$sql = "CREATE TABLE `table` (
         `ID` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         `field1` VARCHAR(255) NOT NULL,
         `field2` VARCHAR(255) NOT NULL
         )
         ENGINE = innodb;";
mysql_query($sql) or die(mysql_error());
$sql = "INSERT INTO `{$dbName}`.`table` (
       `field1`,
       `field3`,
       ) VALUES ('". NULL ."', 'VALUE1','VALUE2');";
mysql_query($sql) or die(mysql_error());
?>

Do you see me using mysql_error(); there? That is NOT ENOUGH when all you get from it is Unknown 'column' in 'field list'

By the way, the error is that there is no field3 created in table... So you will get that error that doesn't give you from much to go on.

Even if the error that mysql_error(); spits out has a number, it usually has 1 as the line number when the SQL is actually on line 986 in midst of a ton others SQLs that you have to look through to find the error... So, what can you do?

Use the LINE and FILE predefined constants... so in this case the mysql_query() would look like...

<?php
$sql = "CREATE TABLE `table` (
         `ID` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         `field1` VARCHAR(255) NOT NULL,
         `field2` VARCHAR(255) NOT NULL
         )
         ENGINE = innodb;";
mysql_query($sql) or die(mysql_error() ." ( at line <strong>".  (__LINE__) ."</strong> in <strong>". (__FILE__) ."</strong>)");
$sql = "INSERT INTO `{$dbName}`.`table` (
       `field1`,
       `field3`,
       ) VALUES ('". NULL ."', 'VALUE1','VALUE2');";
mysql_query($sql) or die(mysql_error() ." ( at line <strong>".  (__LINE__) ."</strong> in <strong>". (__FILE__) ."</strong>)");
?>

This way, you will have some knowledge of actually WHERE the error has happened.

Going a step farther

Yeah, why not go a step farther with this? Everyone does it... Make your own errors when you need to. Set, what I like to call, "error triggers".

Lets say I have an if...elseif...else statement. Well first of all, lets change that to switch...case... operator.

<?php
switch($var)
{
  case null:
  case "index":
    $this->template('index');
    break;
  case "page":
    $this->template('page');
    break;
  default:
    echo "<p><strong>ERROR 1:</strong> Variable \$var unrecognized on line ". (__LINE__) ." in file ". (__FILE__) ."</p>"\n";
    break;
}
?>

Yeah, I just did it. I just created my own error. This shall help me as well as the user if the user roams into error territory. Means "DON'T GO THERE... ERRORS HAPPEN".

Wait, trigger usually means it sets something off... so if I call it an "error trigger" what am I setting off? Well, the error of course.

The trigger is not the error itself, but the cause of the error. Usually people just leave it there so if the user goes there that should be an error and just see a blank page, the user would be puzzled. Ease their mind and say that they roamed into "error territory".

Another step down the beaten path?

Why not set your own function for error reporting... so this time you can do something like

$error->set_error(__LINE__,__FILE__,'Error Message');

so you could do some more and better error reporting things... maybe even set the error level... like E_ALL or E_WARNING...

You can do that with a simple function called trigger_error and its family members listed in the "View Also" on that page.

The warnings you can use for it are:

* E_USER_ERROR
* E_USER_WARNING
* E_USER_NOTICE

An explanation for them can be found here remember, it's only the EUSER* type warnings that can be used and not all of those other error function constants.

Anyway, I'm just telling you what I learned.

<?php
 function set_error($line,$file,$message)
 {
   $error[] = "<p>An error occurred on line {$line} in file {$file}:<br /> {$message}</p>\n";
 }
?>

This way you could make a whole class for errors if you like 🤣 with a collection of functions such as set_error(); clear_error(); count_errors(); and many more things if you are good with PHP and know how to use classes and $this; and the single arrow "->" and double arrows "=>"

You can read this tutorial made by spork to get a better idea on what you can do.

Conclusion

You just now learned a good technique to make troubleshooting easier (Hopefully).

Don't forget to use LINE and FILE whenever the needs be.

This page was published on Jun 6, 2008, at 19:08:55 (UTC). It was last revised on Mar 11, 2024, at 19:07:26 (UTC).