Results from all tables?

8 posts by 2 authors in: Forums > CMS Builder
Last Post: June 14, 2008   (RSS)

By ethan76 - June 3, 2008

Hi again,

I've integrated everything in my site to CMS and it's looking great. There's one feature I'd like to implement though and I don't know if it's possible, so any help or pointers would be great!

I have 3 product pages, each with their own related table. Now I would like to display one - selected - record (a 'Special this week' type thing) from all of these tables on the home page. The user needs to select this record in the admin section.

My first thought was using a 'featured' check box in every record and somehow displaying the selected one, but ;

a. Is it possible to get records from more than one table at the same time?

b. Using the 'featured' checkbox is a bit problematic as the user will have to manually make sure only one record, out of three separate tables is 'checked'.

My ideal solution would be a pull down menu which pulls in all names from all the tables - allowing the user to select one record, then displaying this on the homepage?

Where to start!!?

Thanks for your time,
Ethan

Re: [ethan76] Results from all tables?

By Dave - June 4, 2008

Hi Ethan,

Try creating a single page section called "Specials" with 3 pulldown list fields. One pulldown field for each product section.

In the field editor under "List Options" select "Get options from database (advanced)". Then choose the product table you want, the "num" field for the value, and the product name field for the label.

This should give you three pulldowns in the special section, one for each product section.

See if you can get that far and then let me know. The next step after that is to have it output the 3 selected product numbers on the page, and then add 3 blocks of viewer code to load each individual selected product by specifying the product number in the where. Like this:

where => "num = '{$specials['selectedProduct1']}'",

See how far you can get with the first part and then we'll take it from there and walk you through the rest.

Hope that helps! Let me know if you need more details on any of that!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Results from all tables?

By ethan76 - June 4, 2008

That's brilliant,

I've got the new section working with 3 pull-down lists of products. So now it's the next step you mentioned; getting those into the page!

One question - there will only be one 'Special this week' area on the homepage - which should show ONE record from all three pull downs. I'm guessing this will require an "if record = x, then display. Or else next record" type thing.

Thanks so much

Ethan

Re: [ethan76] Results from all tables?

By Dave - June 5, 2008

Hi Ethan,

So the next step is displaying the 3 pulldown values.

Go to the "Code Generator", select your table "Specials" and "Detail Page" and click "Show Code". You should end up with something like this (the variable names might be different):


<?php
require_once "/your/path/lib/viewer_functions.php";

list($specialsRecords, $specialsMetaData) = getRecords(array(
'tableName' => 'specials',
'where' => '',
'limit' => '1',
));
$specialRecord = @$specialsRecords[0]; // get first record

?>

That lets you display your 3 special numbers in the page with these tags:

<?php echo $specialRecord['selectedProduct1'] ?>
<?php echo $specialRecord['selectedProduct2'] ?>
<?php echo $specialRecord['selectedProduct3'] ?>

We don't actually need to display them, but we need to make sure we've loaded them correctly.

Try and get that far, and then I'll give you the final step.

And just to confirm, you wanted only 1 special to shown right? Not one from each section (3), but the first one selected from any of the sections? We can do it either way.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Results from all tables?

By ethan76 - June 5, 2008

Hi again,

Yep, got that working fine on the homepage, so I now have three listings appearing which show the selected records from the pulldowns, like so;

Cars: 4
Motorbikes: 2
Quad Bikes: 4

So, the final step - how do I turn this info into showing the full listing on the page?

And, to confirm, yes I only want to show one listing - the first one selected from all three.

Thanks for your help again - nearly there!

Ethan

Re: [ethan76] Results from all tables?

By Dave - June 5, 2008

Great, now we add some code to load the first special. This will go right after the code we already have.

The first new block of code gets the table name and record number for the first special record. Be sure to change the tablenames (in red) to match _your_ tablenames.

The second block of code is just like the code generator would create to view a detail page, but uses the tablename and record number we defined earlier.


<?php
require_once "/your/path/lib/viewer_functions.php";

// get special record numbers
list($specialsRecords, $specialsMetaData) = getRecords(array(
'tableName' => 'specials',
'where' => '',
'limit' => '1',
));
$specialRecord = @$specialsRecords[0]; // get first record


// get table name and record number for first special record
if ($specialRecord['selectedProduct1']) {
$table = 'cars';
$num = $specialRecord['selectedProduct1'];
}
if ($specialRecord['selectedProduct2']) {
$table = 'motorbikes';
$num = $specialRecord['selectedProduct2'];
}
if ($specialRecord['selectedProduct3']) {
$table = 'quadbikes';
$num = $specialRecord['selectedProduct3'];
}

// load special record
list($records, $recordMetaData) = getRecords(array(
'tableName' => $tableName,
'where' => "num = '$num'",
'limit' => '1',
));
$record = @$records[0]; // get first record

?>

You should then be able to display fields from your special record like this: <?php echo $record['title']; ?>

Please note that since I'm not on your server I can't test this code. So if you get any errors just post back and we'll figure it out.

Hope that helps, let me know how it goes! :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Results from all tables?

By ethan76 - June 13, 2008

Hi Dave,

Haven't quite been able to test this all yet - in fact since swapping the site from my test server to 'live' I've had to go back a few steps.

Now when I add the php header code - produced from the code creator as before - I get this error ;

getRecords(newin) MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 4

It doesn't seem to like 'limit'? Which is strange as another page with news lsitings (limit='5') works fine..

any ideas would be much appreciated.

Thanks!

E