CODEWITHSUNDEEP
mysqldrupaldrupal-7
Mohammed Charolia
Mohammed Charolia

Reputation: 11

Convert nested select queries to Drupal 7 db_select?

I have completed my database operation using simple query in Drupal 7.But i want some pagination functionality using Drupal 7 theme_table and there for i have to use Drupal 7 db_select,But i m unable to do so.

please Help me out ASAP,Below Is the mysql Query which i want to convert in db_select:-

SELECT r.ROName, CONCAT(CONCAT(CONCAT(r.Address1, ' - ' , r.Pincode),' - ',d.Distname),' - ',s.StateName) as Address1,r.Phone1, r.Phone2, r.Fax, r.Phone3, r.Pincode, r.DistID,d.Distname, d.DistID, s.StateID, r.StateID, s.StateName 
FROM ROMaster r
JOIN DistrictMaster d ON r.DistID = d.DistID JOIN StateMaster s ON r.StateID = s.StateID  
WHERE SBUID =1 
ORDER BY r.ROName

Upvotes: 1

Views: 696

Answers (1)

user4035
user4035

Reputation: 23749

Something like this, I think:

$query = db_select('ROMaster', "r");

$query->innerJoin("DistrictMaster", "d", "r.DistID=d.DistID");
$query->innerJoin("StateMaster", "s", "r.StateID = s.StateID");

$query->addField('r', 'ROName');
$query->addExpression('CONCAT(CONCAT(CONCAT(r.Address1, ' - ' ,
     r.Pincode), ' - ',d.Distname),' - ',s.StateName) as Address1')
$query->addField('r', 'Phone1');
$query->addField("r", "Phone2");
$query->addField("r", "Fax");
$query->addField("r", "Phone3");
$query->addField("r", "Pincode");
$query->addField("r", "DistID");
$query->addField("d", "Distname");
$query->addField("d", "DistID");
$query->addField("s", "StateID");
$query->addField("r", "StateID");
$query->addField("s", "StateNam");
$query->condition("SBUID", 1);
$query->orderBy("ORDER BY r.ROName");
$result = $query->execute();

Upvotes: 1

Related Questions