Conditional queries

Hello,

Is it possible to extend the drastic tools class
('drasticSrcMySQL.class.php') with a WHERE clause, similar to the
ORDER BY clause? I can't see an example of this amongst those
provided. Any pointers greatly appreciated.

Happy holidays,

Zac

Hi Zac,

The code of the examples "ExampleGrid3", "ExampleGrid4" and "ExampleGrid5" on the site drasticdata.nl , also included in the release, show how you can apply a WHERE clause to a MySQL table to use it as source for a grid.
The same holds for generating a map. The mechanism is the same.

regards, Olav

Hi Olav, thanks for the reply...

OK - I see that. But (and maybe I'm being really stupid here) how might you use a variable in this situation?

I was thinking of something along these lines, but couldn't figure out how to implement it:

$condition = "WHERE `Continent` = 'Europe'"

$query = "SELECT * FROM $this->table $this->condition...

but I think this would have to involve climbing inside the class - and I know I'll mess things up if I'm let loose in there!

Hi Zac,

Well, I don't know if I understand the problem well enough, so I'll explain the way it was meant to work:
Since MySQLPHPGrid / DrasticGrid is an editable datagrid, specifying a WHERE clause as other grids do, is not enough. The grid has to know what to do on adding a new row, so I decided to make it possible to extend the class with your own data source, in which you can override the select and the add function.
This is done in examples 3, 4 and 5.
Just write your own data source (named mysrc in example 3) and rewrite the select statement with whatever you want and (if you want the grid to be editable) provide an add function that acts appropriate.

I don't know what you mean with "how might you use a variable in this situation", but if you mean that the select statement depends on some other variable in your program, just take this variable into account in the select funtion in mysrc.

Let me know if this still is a problem. Of course, I could also provide an "WHERE" setting in the next release if the above construct is too complicated.

regards, Olav

Thanks, I'll have a play around with it a bit more and see if I get anywhere.

Unfortunately I'm struggling with the 'scope' of the function. (If it wasn't already obvious it should become apparent at this point that PHP isn't exactly my forte!) I can see how to hard code the WHERE clause - as in example3, but I can't see how to pass it as either an object or an argument into the scope of the function. Does that make any sense?

I suppose I imagine it working in one of the following ways:
1. An optional argument

$WHERE = "WHERE Continent='Europe'";
//Note:By default $WHERE= "WHERE 1"
//which would work on any table.
class mysrc extends drasticsrcmysql {
protected function select($WHERE){//IF $WHERE IS PASSED, USE IT!
$query = "SELECT * FROM $this->table $WHERE";
$res = mysql_query($query. $this->orderbystr, $this->conn) or die(mysql_error());
return ($res);
}


OR

2. Another object

$res = mysql_query($query. $this->wherestr, $this->orderbystr, $this->conn

Again, by default wherestr would be "WHERE 1" which would work in any table.

Thanks once again for taking the time to reply. I think this is a really useful class - if I can just get my head around it! I especially like the use of enums to populate select boxes, and the ability to rename columns - all very clever.

Cheers,

Also, to deal with the add statement, perhaps it would look more like this:

$condition_key = 'Continent';
$condition_value = 'Europe';

$condition = "WHERE $condition_key = '$condition_value'";

Well, the idea of extending the base src class with your own select and add functions was to provide more flexibility than would be possible with a WHERE setting.
But I understand in simple cases it would be easy to just provide a WHERE clause, maybe without an add function.

So with the current release (0.5.6), if you have your WHERE clause in a global $WHERE variable, you could use this variable inside the extended select function. But for coming releases I may create the possibility to just add a WHERE clause in the options for a src.

regards, Olav

Thanks, I think I've got it now. For the record, here's what I did (and apologies in advance for the brutal mutilation of your code)

// Get the table in memory
if(!isset($where)){$where = " WHERE 1 ";}//was a condition provided?
global $where;
$this->result = $this->select($where);
$this->num_rows = mysql_num_rows($this->result);
$this->num_fields = mysql_num_fields($this->result);


So now if a $where clause like "WHERE `Continent` = 'Europe' " is passed to the main page, it's acted upon, otherwise it's ignored.

Now I'll see if I can do a similar trick with the add function...

Well, I thought I had it but I was wrong. I just can't seem to pass this...

foreach ($_GET as $condition_key => $condition_value) {
$condition[] =" `$condition_key` = '$condition_value' ";
$condition_keys[] = "$condition_key";
$condition_values[] = "'$condition_value'";
}
if(is_null($condition)){
$conditionString = " 1 ";
}else{
$conditionString = implode('AND', $condition);
$conditionKeysString = implode(',',$condition_keys);
$conditionValuesString = implode(',',$condition_values);
}

as arguments to this...

class mysrc extends drasticsrcmysql {
protected function select($conditionString){
$query = "SELECT * FROM $this->table WHERE $conditionString";
//echo $query;
$res = mysql_query($query . $this->orderbystr, $this->conn) or die(mysql_error());
return ($res);
}


protected function add(){
$query = "INSERT INTO $this->table ($conditionKeysString) VALUES ($conditionValuesString)";
echo $query;
mysql_query($query, $this->conn) or die(mysql_error());
if (mysql_affected_rows($this->conn) == 1) return(true); else return(false);
}

help?

The coming version of DrasticTools (0.6.6) will have a setting to add a simple where clause without having to extend the datasource class.
Maybe this will help.
This setting will also create the correct default values in the add function.

I'll try to release this version within a few days.