zend-db-select

Translating a query to use Zend_Db_Select

I'm having some problems translating this query to use ZF's Zend_Db_Select: SELECT b.id, b.title, b.description FROM memberships AS m JOIN blogs AS b ON b.id = m.blog_id WHERE m.user_id = ? ORDER BY m.created LIMIT 0, 30 (this query works and returns results) Memberships is a link table between blogs and users. It's a simple | id ...

Get table alias from Zend_Db_Table_Select

Hello. I'm working on an Active Record pattern (similar to RoR/Cake) for my Zend Framework library. My question is this: How do I figure out whether a select object is using an alias for a table or not? $select->from(array("c" => "categories")); vs. $select->from("categories"); and I pass this to a "fetch" function which adds addi...

How to select rows where a column value is empty/null using Zend_Db?

I have an SQLite database, eventually will be a MySQL database and I'm using Zend Framework. I'm trying to fetch all the rows in a table where the 'date_accepted' column is empty/null/doesn't have a value. This is what I have so far: public function fetchAllPending() { $select = $this->getDbTable()->select(); $select->where('date_acce...

Best way to do an Inner Join using the Zend Framework?

It seems like there's a few different ways to join two tables using the Zend Framework, but I've never done it before so I don't know which is the best way to do it. This is what I'm trying to do... I have 3 tables in my database: users ( id , name ) groups ( id , name ) group_members ( id , group_id , user_id ) I'm tryi...

Zend Framework: How to do a DB select with multiple params?

I'm just wondering what the syntax is to do a db select in Zend Framework where two values are true. Example: I want to find if a user is already a member of a group: $userId = 1; $groupId = 2; $db = Zend_Db_Table::getDefaultAdapter(); $select = new Zend_Db_Select($db); $select->from('group_members') ->where('user_id = ?', $userId);...

Zend_Db order by field Value

I'm outputting the contents of a select menu from a model using this: $select = $this->select(); $select->order('name'); return $this->fetchAll($select); However, what i want to do is order by a specific value, and then by the name column. The SQL would look like this: SELECT * FROM `names` ORDER BY `name` = 'SomeValue' DESC,`name` ...

How to put if,elseif,else statements while building sql in Zend_db_Select

For example: if(!empty($this->_request->getParam('gadi_from'))) {$select->where('a.gadi > ?',trim((int)$this->_request->getParam('gadi_from')));} if(!empty($this->_request->getParam('gadi_to'))) {$select->where('a.gadi < ?',trim((int)$this->_request->getParam('gadi_to')));} But the problem is, it's isn't working that way. :) I'm get...

how to use of select with multi condition in zend framework ?

hi all i want to select rows with multi condition in zend framework how can i implement that/ 1-example "select id,firstname,lastname,city from person where firstname=alex and city=xx "; 2-example "select id,firstname,lastname,city from person where firstname=alex or city=xx "; ...

SELECT '0' AS variable ... with Zend_Db_Select

I am trying to create a select statement that uses the following structure: $db ->select() ->from( array('i' => ...), array('provisional', 'itemID', 'orderID')) ->columns(array("'0' AS provisionalQty", "'ballast' AS productType")) ->joinLeft( array('o' => ...), 'i.orderID = o...

Lots and lots of joins in a Zend Query, hopefully just a slight tweak

Apologies for all this code, anyhow Im re-working a query into the Zend query way of working, this is what I have so far: $db = Zend_Registry::get ( "db" ); $stmt = $db->query(' SELECT recipe_pictures.picture_id, recipe_pictures.picture_filename, course.course_name, cuisines.name, recipes.id, recipes.Title, recipes.Method,...

Zend Framework: How to combine three tables in one query using Joins?

I have three tables like this: Person table: person_id | name | dob -------------------------------- 1 | Naveed | 1988 2 | Ali | 1985 3 | Khan | 1987 4 | Rizwan | 1984 Address table: address_id | street | city | state | country -----------------------------...

Multiple/nested "select where" with Zend_Db_Select

Hi there I need to create something like this: select name from table where active = 1 AND (name LIKE 'bla' OR description LIKE 'bla') The first part is easy: $sqlcmd = $db->select() ->from("table", "name") ->where("active = ?", 1) Now comes the tricky part. How can I nest? I know that I can just write ->orWhere("name LIKE ? OR ...

zend_db_select with a hexadecimal query parameter against a binary column

How do you represent this query as a Zend_Db_Select? select * from t where id = x'0cc175b9c0f1b6a831c399e269772661'; The database is MySQL, using either PDO or mysqli adapters. ...

Join with Zend_Db, without having the columns of the joined tables

Hi, I use Zend_Db_Select to perform a query with a Join. I end up with the following SQL query : SELECT `Utilisateur`.*, `Ressource`.*, `Acl_Cache`.*, `Role`.*, `UtilisateurRole`.* FROM `Utilisateur` INNER JOIN `Ressource` ON Ressource.idJointure = Utilisateur.id INNER JOIN `Acl_Cache` ON Acl_Cache.idRessource = Ressource.id INNER JOIN...

Zend DB Selecting constants - columns that do not exist in table

Hey I'm trying to do this query using Zend DB select but I'm not able to do so This is the sql query select shopping_id,shopping_details,"friend" as type from shopping Notice here how I'm specifying "friend" as type and friend is not a column in the shopping table. Now how do I do this in Zend. I have tried this but it gives me an...

Zend Framework: How Do I 'auto' generate a SQL query correctly

I want to generate the following SQL: SELECT `rc`.*, `c`.`name` FROM `RunConfigurations` AS `rc` INNER JOIN `Clients` AS `c` ON rc.client_id = c.id WHERE (rc.client_id = ?) ORDER BY `rc`.`config_name` ASC However I am getting: SELECT `rc`.*, `c`.* FROM `RunConfigurations` AS `rc` INNER JOIN `Clients` AS `c` ON rc.client_id = c.id WHE...

Zend_Db_Select join across 3 tables - how to access correct ID in rowset?

Can anyone help me work out why I'm not able to extract the classes.id data in the following Zend_Db_Select join query. I need the class id to allow me to include an 'edit class' link. Problem is the query is outputting an id for each class which is different to classes.id in the database table. What am I doing wrong? I have three ta...

Zend PDO error - how can I debug it?

I have a weird problem using Zend Framework and Mysql. I generated a query: SELECT events.idUser, szForename, szLastname, readers.szName, idZoneFrom, events.dtTime FROM events, users, readers WHERE events.idUser = users.idUser AND events.idReader = readers.idReader AND dtTime >= '2010:02:15 0:00:00' AND dtTime < '2010:02:16 0:00:00' ORD...

zend framework select join error getTable()

I am trying to implement this method in my model: class Application_Model_MenuProfilesProducts extends Zend_Db_Table_Abstract { protected $_name = 'menu_profiles_products'; public function fetchProducts($profileID) { $select = Zend_Db_Table::getDefaultAdapter()->select() ->from(array('mpp' => 'men...

How does one query a Zend_Db_Table_Row

Is it possible to run an SQL (Zend_Db_Select) query against a Zend_Db_Table_Row object? What about joining two Zend_Db_Table_Row objects? I know it sounds a bit retarded, but I'm processing hundreds and thousands of rows. I already have the row object that I'm manipulating, so I don't want to query the db again. I hope that's clear. If...