Anyone know a good OOP PHP MySQL class? To build queries primarily

Question

EDIT: Found this which works quite well for what I need: https://github.com/jstayton/QueryBuilder

I'm building a small project where I need to query a table a couple of times.

It works like this:

1) Multi-select box of distinct items in Column 1
2) Ajax query the db for distinct records in Column 2 where Column 1 IN (vals
   previously selected)
3) Does this 3-4 more times, gradually cutting down the available options by 
   adding IN statements to the MySQL

Now it's not impossible to this manually by setting up a couple pre-built queries and binding the various params to them, but I'd like to make it a bit nicer than that. Something along the lines of....

$qry->from("mytable");
$qry->column("col1");
$qry->column("col2");
$qry->addWhere("col1", "in", $arrayOfVals);
$qry->addWhere("col2", "in", $arrayOfVals2);

Or something to that effect, that will build it out in a cleaner way.

Alternatively, if someone has a suggestion on a different way to do this, I'm open to that too.

that looks great! you should totally build that query builder class d(^_^)b although, maybe the method "column" should instead be called "select", and take a possible second argument which if present will give the column an alias. and then you add a join class, which has methods 'from' and 'addOn'. and then you have a method 'join' on the query class which takes join objects. and then you have logics when putting the query together which sorts the where-clauses in the most optimal order considering the joins by looking at what tables are used in the where-clauses. awesommmme! — davogotland
– davogotland, Commented Jan 12, 2012 at 18:20
Why would you need a second argument for the alias though? Just add it after the column. $qry->column('column alias'); — MetalFrog
– MetalFrog, Commented Jan 12, 2012 at 18:32
How about DB::select ('col1', 'col2')->from ('mytable')->where ('col1', 'IN', $arrayOfVals)->and_where ('col2', 'IN', $arrayOfVals2)->execute ();? Kohana's Query Builder, you could try to extract it, or just do the sensible thing and use Kohana ;) — zrvan
– zrvan, Commented Jan 12, 2012 at 22:10

aknosis · Accepted Answer · 2012-01-12 18:13:50Z

1

There are an infinite number ways to do this - it all depends on your needs. You could roll your own for sure or go for something already out in the wild.

Take a look at ezSQL http://justinvincent.com/ezsql it may be right up your alley.

answered Jan 12, 2012 at 18:13

aknosis

4,45826 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

McB Over a year ago

Thanks for the suggestion. ezSQL seems to be very similar to to MySQLi or PDO, but what I'm really looking for is a good query builder.

bububaba · Accepted Answer · 2012-01-12 18:41:41Z

1

Zend_Db_Select has almost the exact syntax you provided. But you'd have to include quite a lot of Zend_Db_* classes for that, I don't know if it's acceptable for you.

http://framework.zend.com/manual/en/zend.db.html

answered Jan 12, 2012 at 18:41

bububaba

2,8706 gold badges27 silver badges29 bronze badges

Comments

Darren Taylor · Accepted Answer · 2014-02-13 10:34:02Z

0

I noticed the link you provided in your edit no longer works so for that reason, I'd recommend looking at FluentPDO aswell - http://fluentpdo.com/index.html

It has a very simple installation process and very fluid and natural query building methods which would allow for syntax similar to what you mentioned in your original question.

answered Feb 13, 2014 at 10:34

Darren Taylor

2,03515 silver badges19 bronze badges

Collectives™ on Stack Overflow

Anyone know a good OOP PHP MySQL class? To build queries primarily

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related