advanced sql select statement help

4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 7, 2011 (RSS)

Archived
Reply
Reply

By rez - March 7, 2011

Hi, i am in a situation where i have to simply display some of cmsb data with a select statement.

I have a name, alcohol, available (yes or no), and a brewer. The brewer is a list in another editor.

I use:
$Driver = new Driver_Mysql($link, "SELECT brewer, name, alc FROM cms_draft_list WHERE available = '1'",

It works fine but unfortunately, the brewer is just showing a number instead of the label.

I have tried JOIN but instructions I found, were pertaining to IDs matching like:

$Driver = new Driver_Mysql($link, "SELECT cms_draft_list.name, cms_draft_list.alc, cms_brewer.brewer FROM cms_draft_list INNER JOIN cms_brewer ON cms_draft_list.num=cms_brewer.num",

that doesnt come out correct (i can see why).

Is there an easy way to display the label instead of a number? I also need to ASC by brewer. Currently, when I do this in the top of the page, it is of course sorting the brewer number and not the label. So when the above is all right, I need to know how to sort by brewer.

thanks

Re: [rez] advanced sql select statement help

Archived
Reply
Reply

By Jason - March 7, 2011

Hi,

From the look of your code, it looks like you're trying to connect directly to your MySQL database. CMS Builder can handle that for you. Also, if you're using version 2.04 or above, you can use the :label pseudo field to get the name of the brewer. To do this, you need to use the getRecords function.

for example:

list($draftListRecords, $draftListMetaData)  = getRecords(array(
        'tableName'          =>     'draft_list',
        'where'              =>     "available = 1",
        'allowSearch'        =>     false,
        'orderBy'            =>    'brewer ASC',
      ));

After this code, you'll have all your records stored in $draftListRecords.

You can output your brewer names like this:

 foreach($draftListRecords as $record) {
     echo $record['brewer:label']. "<br/>";
}

Hope that helps get you started.

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] advanced sql select statement help

Archived
Reply
Reply

By rez - March 7, 2011 - edited: March 7, 2011

Yes, I have that working now. Works great.

I have an ajax table I'm trying to make work with my existing cmsb db though. it's source.php looks like this:

<?php
// Includes
include("lib/Provider.php");
include("lib/aDriver.php");
include("lib/iDriver.php");
include("lib/Drivers/Mysql.php");

$link = mysqli_connect('localhost','db_user','db_pass','db_name', "3306");
//$Driver = new Driver_Mysql($link, "SELECT name, alc FROM cms_draft_list WHERE available = '1'",
$Driver = new Driver_Mysql($link, "SELECT cms_draft_list.name, cms_draft_list.alc, cms_brewer.brewer FROM cms_draft_list INNER JOIN cms_brewer ON cms_draft_list.num=cms_brewer.num",
array("draft" => "Name", "Alcohol", "Brewer"));
$Provider = new Provider($Driver);
$Provider->HandleRequest();

Can i put cmsb viewer code in there with the usual code at the top? That would be awesome. I don't know, but i figured for this table I could just do it outside of cmsb but need to use the same db. just happy to get it working. It has nice sortable columns, a live search, zebra stripes, etc.

Re: [rez] advanced sql select statement help

Archived
Reply
Reply

By Jason - March 7, 2011

Hi,

Glad that's working for you now.

Yes, you can use CMSB functions in this page so long as you are requiring viewer_functions.php

Hope this helps