Function with varying args

[snivek]

I need to pass arguements to a function but I might not always pass all of them and it could be different sets of them.  For example, a SQL Select function like so...

[code]
<?
function sql_select($select,$table,$where,$order){
    /* Code to generate sql select */
}
?>
[/code]

I could have a statement that has a WHERE clause but then another one that doesnt but does have an ORDER caluse.  If I left out the WHERE arguement when I called the function the ORDER variable would be used for WHERE, i.e.,

[code]
$result = sql_select("*","users","fname=joe","lastname");
/* no prob - all args present*/

$result = sql_select("*","users","lastname");
/* missing WHERE arg*/
[/code]

I know that I could just put NULL in place of the where but is there another, more dynamic way of doing it?

[kenrbnsn]

You need to define the function argument list with default values:
[code]<?php
function sql_select($select,$table='',$where='',$order=''){
    /* Code to generate sql select */
}
?>[/code]
Then when you leave arguments out of the call, the automagically get the default values. AFAIK, you can only leave out arguments at the end of the argument list.

Ken

[ShogunWarrior]

ken's way is how you would usually leave out a function's argument.
However, if you then want to put your ORDER value in the WHERE place how will your function distinguish which it is?
You will have to put NULL in its place and check that each argument is not null.

[trq]

You'll need to define defaults but, if you want to skip an argument you will still need to pass a blank arg. eg;

[code=php:0]
sql_select($select,$table,$where='',$order=''){
[/code]

This makes both $where and $order optional. You can safely leave out $order, but if you want to leave out $where and still define $order you will need to call it like...

[code=php:0]
sql_select('foo','bar','','id'){
[/code]

Another way to do it would be to make the argument accept an associative array, then just pass in whatever you want. eg;

[code=php:0]
sql_select(array('select' => 'foo','table' => 'bar','order' => 'id')){
[/code]

Of course you will need code within the function to deal with the different combinations. Personally, I think its easier to simply pass the function an sql query. Alot more flexable too.

[effigy]

I second the associative array approach. If this function is part of a larger whole, you'd be better off working it into some OOP.

[snivek]

I am trying to learn classes and grasp a good approach to them.  That is actually what brought this question up for me.  I am trying to decide if its best to have ONE dynamic method to handle queries or a LOT of methods for all my different types of queries.  Like searching for images, authenticating a user (the sql part), inserting images, or comments, etc...  Trying to wrap my hands around how to lay it all out...I think that I almsot need to just start and see where I end up.  Any suggestions?

[effigy]

Just trying it is a good idea--that's how we all start. Spend some time designing it and sketching out the abstraction. Most importantly, don't forget that you can extend the existing mysqli class. I did this for an internal project by intercepting the query to see if it was one word, and therefore the key to a "library" (associative array). If not, it runs the query as usual; otherwise, it assumes that any needed "fill-ins" (fields) are passed as arguments, for example:

[code=php:0]
$mysqli = new MysqlCustom();
$result = $mysqli->query('verify_login', $employee_id, $password);
[/code]

In the library, "verify_login" looks like this:

[code=php:0]
'verify_login' => '
select
id,
first_name,
last_name
from
user
where
employee_id = %s
and password = md5(%s)
'
[/code]

From here the class properly escapes the data, adds any necessary quoting and/or formatting, and then substitutes the passed fields before running the query.

This is just a small example of how handy extending existing classes can be.

[snivek]

If your MysqlCustom Class extends mysqli, would you just do something like the following from within your MysqlCustom::Query method?

[code]
<?php

$result = parent::query(arrQueries['verify_login']);

?>
[/code]

[effigy]

Yes, eventually the query method calls mysqli's via parent: [code=php:0]$result = parent::query($sql);[/code]

[snivek]

How did you handle checking for the correct amount of args?  I was thinking of using a SWITCH statement or a series of IF statements for each key in the array.  If the SWITCH or IF matched the key passed in it would check the number of args using func_num_args() and maybe their type before it inserts them into the query string.

Also, how did you handle a WHERE clause that had multiple conditions?  Did you have it passed in as one long string or did you expect an array or something else? 

Thanks for you help!

[effigy]

WHERE's are handled in the library template, as shown in the first example. You could also try using prepared statements instead of a "library"--I tried that route, but I cannot remember why I avoided it. The majority of the code is below:

[code]
class MysqlException extends Exception {
public function __construct($message, $code = 0) {
parent::__construct($message, $code);
echo "<b>MySQL Error:</b> $message\n";
echo "<!-- ", print_r($this->getTrace(), 1), " -->";
die();
}
}

class MysqlCustom extends mysqli {

### By default, do not place any restrictions on the expected results.
private $expected_results = NULL;

### Connect and select a database when a new object is created.
### MYSQLI_CLIENT_FOUND_ROWS is being used so that UPDATEs see the
### matched rows instead of the affected rows. This prevents errors
### when an UPDATE is not truly made because there has not been a change.
public function __construct() {
@ parent::init();
@ parent::real_connect(
MYSQL_HOST,
MYSQL_USER,
MYSQL_PASS,
MYSQL_DB,
NULL,
NULL,
MYSQLI_CLIENT_FOUND_ROWS
);
if ($error = mysqli_connect_error()) {
throw new MysqlException("Cannot connect: $error");
}
}

### "query" can be used as usual, or passed a key with its
### accompanying fields, in order to use the SQL library.
public function query() {
### Accept arguments as an array or list of values.
if (is_array($array_of_args = func_get_arg(0))) {
$values = &$array_of_args;
}
else {
$values = func_get_args();
}
### The SQL or library key will be the first value.
$sql  = array_shift($values);
### If the "query" is all word characters, it's a key for the
### SQL library. You can prevent one-word SQL statemements
### from being treated as keys by following them with a
### semicolon, e.g., 'commit;'
if (preg_match('/^\w+$/', $sql)) {
### Reference the library.
$sql_library = &$GLOBALS['sql_library'];
### Make sure the key exists in the library.
if (array_key_exists($sql, $sql_library)) {
### Escape each field that will be put into SQL.
foreach ($values as &$value) {
$value = $this->real_escape_string($value);
### NULL and digits should not be quoted.
if ($value != 'NULL' && ! preg_match('/^\d+$/', $value)) {
$value = '"' . $value . '"';
}
}
### Build query.
$sql_string = @ vsprintf($sql_library[$sql], $values);
if ($php_errormsg) {
throw new MysqlException("Cannot build query: $php_errormsg.");
}
### Run query.
$result = parent::query($sql_string);
if (! $result) {
throw new MysqlException("Query failed: ($this->errno) $this->error");
}
### If the key begins in 'apply' or 'update', we're
### expecting one row to be affected.
if (preg_match('/^(?:apply|update)/', $sql)) {
if (($changed_rows = $this->affected_rows) != 1) {
throw new MysqlException(
'Unexpected results while executing ' .
"'$sql'" .
": $changed_rows rows updated."
);
}
}
}
### The key does not exist in the library.
else {
throw new MysqlException("'$sql' does not exist in SQL library.");
}
}
### Otherwise, it's normal SQL.
else {
### Run mysqli's query and check for errors.
$result = parent::query($sql);
if (! $result) {
throw new MysqlException("Query failed: ($this->errno) $this->error");
}
}
### Check expected results.
if (! is_null($this->expected_results)) {
if ($rows = $result->num_rows != $expected = $this->expected_results) {
throw new MysqlException("Results ($rows) do not match expected ($expected).");
}
}
### Return result object.
return $result;
}
[/code]

Again, this was for an internal application, so you may want to remove the trace printing in the exception class, and any other specifics that are not abstract enough.

[snivek]

What did you use to create your sql_library?  Did you just create a config file that you parsed with some other class or function and then registered it as a global variable? 

Sorry to keep nagging you..just trying to find out "how things are done"!  I really do appreciate it!

[effigy]

It's just an associative array at the moment:

[code]
<?php

### These are the SQL statements that feed the web interface. They are in
### sprintf format and the fields are documented above them. For example,
### "verify_login" expects 2 values, an Employee ID and a Password, so it
### would be called as:
### $result = $mysqli->query('verify_login', $employee_id, $password);

### Callbacks can be setup by prefixing a value with "php:"; thus,
### "php:function_name" would call PHP's function "function_name". This is
### not set up on a global scale (yet), but locally as needed.

$sql_library = array(
### 1 - employee_id.
### 2 - password.
'verify_login' => '
select
id,
first_name,
last_name
from
user
where
employee_id = %s
and password = md5(%s)
',
### None.
'book_listing' => '
select
id as value,
number as name
from
book
order by
number
',
...,
...
);
[/code]

I'm sure the design and class could be improved, but this was slightly rushed (as usual).