Category: PHP

Multiple SQL queries using MySQL and PHP

Something that I’ve had problems with using MySQL/PHP is the limitation of only being able to run one line of SQL at a time. Using something such as Microsoft SQL Server it’s possible to write multiple lines of SQL and run it all in a single database call. Until stored procedures (in MySQL 5) are available (i.e. when it seems that the database engine is ready for a live environment) I’ve put together the following static method ‘hack’ using PHP’s exec() function (assuming you’re using PHP 5’s object syntax – otherwise just paste the code into a regular function):

class MySQLInterface
{
   public static function multipleDbQueries($sql)
   {
      $file = fopen(TEMP_CSV_LOCATION .
         "temp_query.sql","a+");
      fwrite($file,$sql);
      fclose($file);
      exec("mysql -u " . DB_USERNAME .
         " --password=='" . DB_PASSWORD .
         "' " . DB_NAME . " < " .
         TEMP_CSV_LOCATION .
         "temp_query.sql");
   }
}

What this allows you to do is to pass in a SQL string and have it executed as if it was being run from the command line. This is especially useful if you need to disable foreign keys for some reason. e.g

$sql = "SET FOREIGN_KEY_CHECKS=0;
   drop table oldTable;
   SET FOREIGN_KEY_CHECKS=1;"

MySQLInterface::multipleDbQueries($sql)

Downsides of this are

  • Requires command line access to mysql
  • Liable to SQL injection

Since i’m working within an internal system I have control over both of these and the code seems to work particularly well.

PHP 5 class constants and subclasses

Another one of those ‘I wish PHP 5 did this…’ moments has occurred to
me with class constants. The addition of constants is good but the
problem is when it comes to subclassing. The code on the PHP 5 site:

class Foo {
   const constant = "constant";
}

echo "Foo::constant = " . Foo::constant . "\n";

is fine. Of course, what you really want to have is a method to give
you the constant in case you want to change the workings later:

class Foo {
   const constant = "constant";

   public function getConstant(){
      return self::constant;
   }
}
$foo = new Foo();
echo "$foo->constant = " . $foo::getConstant() . "\n";

and this works too. The problem is that if you decide a subclass
needs a different constant value, so we add

class Bar extends Foo {
   const constant = "bar constant";
}

If we then call

$bar = new Bar();
$bar->getConstant();

then the value returned is “get_constant” i.e. the value of the
constant in the parent class. This is because Bar has no handler for
getConstant() so it uses its parent. That’s fine, but now we’re in the
parent context then Foo::constant is returned through the reference to
self::. The way to get round this (that I have found) is to put a copy
of the getConstant() method in each of the subclasses. This kind of
defeats the purpose of inheritance in this case.

class Bar extends Foo {
   const constant = "bar constant";

   public function getConstant(){
      return self::constant;
   }
}

Now we call

$bar = new Bar();
$bar->getConstant();

and the correct value is returned. Of course, the other way round is
not to use constants at all but to put the value in a private variable,
but then what use are constants?

PHP 5 garbage collection

The object-oriented features of PHP 5 are a really positive step forward
for the language. One of the biggest improvements, to my mind, is that
you no longer have to choose to pass things around by reference with a
liberal smattering of ‘&’ symbols: references are now the default way of
passing objects.

One problem I have come across, though, is that the reference counting
feature of PHP’s garbage collection
(http://www.zend.com/zend/art/ref-count.php) means that objects with
mutual references are not deleted even when I thought the object was out
of existence. E.g:

class ParentObject()
{
  protected $childObject;

  function __construct()
  {
    $this->childObject = new ChildObject($this);
  }
}

class ChildObject()
{
  protected $parentObject;

  //Pass in a reference to the parent
  //object and store it internally
  function __construct($parentObject)
  {
    $this->parentObject = $parentObject;
  }
}

Then if I call $foo = new ParentObject(); then it automatically creates
a child object with a reference to the parent. The parent also keeps a
reference to its child. If I then unset($foo); the two objects are still
referencing each other and so are not deleted. The only way I’ve found
to clear this is to create a new method (which I call destroy()) to
delete references to the child. Calling destroy() on the parent first
calls destroy() on its child, which dereferences the parent, and then
the parent dereferences the child. So the classes are now:

class ParentObject()
{
  protected $childObject;

  function __construct()
  {
    $this->childObject = new ChildObject($this);
  }

  public function destroy()
  {
    $this->childObject->destroy();
    unset($this->childObject);
  }
}

class ChildObject()
{
  protected $parentObject;

  //Pass in a reference to the parent object
  //and store it internally
  function __construct($parentObject)
  {
    $this->parentObject = $parentObject;
  }

  public function destroy()
  {
    unset($this->parentObject);
  }
}

And I have to call

$foo->destroy();
unset($foo);

To clear the thing out completely.

This can cause a number of problems which I won’t go into in detail here
(they occur in more complex design patterns), but suffice to say that
there are a number of occassions where I don’t necessarily want to
destroy a child at the same time as a parent, or vice-versa. E.g. a
child references multiple parents. The end result is that I’m writing
code to deal with garbage collection where it is having a big effect on
memory and just leaving it out where it doesn’t seem to make as much
difference. This suffices for a known set of data but doesn’t feel very
satisfactory in terms of future-proofing.

I would appreciate it if anyone else has a better way of doing things.