SQL queries and filters

[Roland Dalmulder]

Hey guys,

I have been working with F3 a bit now and starting to find my way around, now I am stuck at what seems a trivial issue. Filters in an SQL query. I have used XDebug to run through the code but I can't seem to wrap my head around this one.

The first scenario is that I am using the paginate function on my list views. These view have a search filter and I want to apply this search filter to the paginate function. The search value should be applied to 2 fields in the database called  mail and name. This would result in a where that looks like:

WHERE `email` LIKE '%me%' OR `name` LIKE '%me%'

Now I have written my own function to produce a where string and pass that to the paginate function but I wonder if there is a better way. What I can tell from the code is that I should be able to pass an array with these filters, is that correct? If yes, how would I do that?

My second scenario is that the count() function also uses this filter and I wonder how I can pass that same filter.

Thank you for your insights.

RolandD

[Ysguy]

Try something like this….

 

I use Bootstrap tables and feed it data using a class, but at the end of the day the query is like this.

 

$rows=$db->exec("SELECT fileID,fileName,fileSize,fileType,fileTag FROM fileDetailsView $sWhere $sOrder $sLimit");

 

 

And to build sort I use something like this:

                                if ( isset($this->sort) )

 

                                {

                                                $sOrder = "ORDER BY $this->sort $this->order";

                                }

And for pagination I use something like this:

 

                                if( isset( $this->offset ))

 

                                {

                                                $sLimit = "LIMIT ".intval( $this->offset ).", ".

                                                                intval( $this->limit );

                                }

[ikkez]

HI Roland,

I think what you are looking for is this:

$me = "%ikkez%";
$mapper->paginate(0,10, ['email LIKE :me1 or name LIKE :me2', ':me1'=>$me, ':me2'=>$me]);

The trick is to use me1 and me2, because you cannot reuse the same bind placeholder more than once, since it's a PDO limitation.

To get around this, you can use positional placeholders, so you wont hit that issue again:

['email LIKE ? or name LIKE ?', $me, $me]