Merging Tables - A Practical Concept?

13 posts by 6 authors in: Forums > CMS Builder
Last Post: February 20, 2011   (RSS)

By Perchpole - November 4, 2010

Hello, All -

This is just a concept. I'd like to know if it's worth pursuing.

In simple terms, is it possible to merge the data from different tables?

The tables would be naming using a strict naming convention and would each be composed of the same fields - with one or two exceptions.

For example, there could be 3 tables with the following fields:

Table1
Title
Content

Table2
Title
Content
Author

Table3
Content
Upload
Attachment

If I were able to merge the output of all three tables I would (in principle) be able to call the following data:

$record['title']
$record['content']
$record['author']
$record['upload']
$record['attachment']

Is it a practical concept? Would there be a clean way of achieving the goal?

:o|

Perch

Re: [Perchpole] Merging Tables - A Practical Concept?

By Jason - November 4, 2010

Hi Perch,

You can use a join in your query, but you need a common field across each table so you know which records your combining.

Let me know how you're deciding which records to combine and we'll see what we can work out.

Hope this helps.
---------------------------------------------------
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] Merging Tables - A Practical Concept?

By Toledoh - November 10, 2010

Hey guys,

Any thoughts on this at all?
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Merging Tables - A Practical Concept?

By Dave - November 11, 2010

Hi Tim,

There's lots of ways to do that, but the simplest is probably just by having multiple getRecords() calls and listing the display html in a foreach loop for each table you want to include.

If that doesn't make sense attach your viewer and I'll try to give an example.

The biggest challenges when merging tables or showing results from multiple tables are figuring out:
- How it should be sorted (since you might not have the same fields in all tables)
- How to link to individual detail pages (since record nums might be the same)
- How to do prev/next paging (if required)

Let me know how you'd like it to work on your page I'll see what I can come up with.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Merging Tables - A Practical Concept?

By Toledoh - November 22, 2010

Hi Dave.

Sorry for the delay in getting back to you (especially after I rushed you...[blush] )

I've attached the viewer - the pertinent code starts at line 138;
<!--carousel -->
<ul id="mycarousel" class="jcarousel-skin-tango">
<?php foreach ($imagesRecords as $record): ?>
<!--Should be for each imagesRecords or listingsRecords where "homePage" is ticked orderBy RAND-->
<li><a href="">
<?php foreach ($record['hero_shot'] as $upload): ?>
<img src="<?php echo $upload['urlPath'] ?>" width="333" height="333" border="0" class="captify" rel="caption<?php echo $record['num'] ?>">
<div id="caption<?php echo $record['num'] ?>"><?php echo $record['title'] ?></div>
<?php break ?><!--Only 1 image per listing shown-->
<?php endforeach ?>
</a>
</li>
<?php endforeach ?>
</ul>
<!--carousel -->

I want to show
- records from the listings table and the images table,
- only when the homePage checkbox is ticked
- display them in a random order

I'm still up in the air about the "link" aspect... so I will sort that later, and there's no need to do the paging aspect.

Thanks!
Cheers,

Tim (toledoh.com.au)
Attachments:

index2_001.php 9K

Re: [Toledoh] Merging Tables - A Practical Concept?

By Jason - November 23, 2010

Hi Tim,

First, you can control the records being returned up at the top of your page. For example, if you only want listing records where "homePage" has been checked and you want a random order, you can use this:

list($listingsRecords, $listingsMetaData) = getRecords(array(
'tableName' => 'listings',
'orderBy' => "RAND()",
'where' => "homePage = 1",
));

After you've selected all your records, you can merge them into a single array that you can use to output your images. NOTE: This will only work if your upload fields have the same name and both have a field called "title".

EXAMPLE:
$carouselRecords = array_merge($imagesRecords,$listingsRecords);

Finally, you use this array to output your images:

<!--carousel -->
<ul id="mycarousel" class="jcarousel-skin-tango">
<?php foreach ($carouselRecords as $record): ?>
<li><a href="">
<?php foreach ($record['hero_shot'] as $upload): ?>
<img src="<?php echo $upload['urlPath'] ?>" width="333" height="333" border="0" class="captify" rel="caption<?php echo $record['num'] ?>">
<div id="caption<?php echo $record['num'] ?>"><?php echo $record['title'] ?></div>
<?php break ?><!--Only 1 image per listing shown-->
<?php endforeach ?>
</a>
</li>
<?php endforeach ?>
</ul>
<!--carousel -->

Give this a try and let me know if you run into any issues.
---------------------------------------------------
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] Merging Tables - A Practical Concept?

By Toledoh - November 23, 2010

Thanks Jason - that's brilliant!

Next... is it possible to randomise the $carouselRecords?

At the moment, it's randomising the listings, then randomising the images, then joining them which means $carouselRecords has all the listings, then all the images rather than a random mix of listings and images.
Cheers,

Tim (toledoh.com.au)

Re: [Toledoh] Merging Tables - A Practical Concept?

By Jason - November 23, 2010

Hi Tim,

Sure, you can use the php shuffle() function to randomize your array:

$carouselRecords = array_merge($imagesRecords,$listingsRecords);
shuffle($carouselRecords);
---------------------------------------------------
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] Merging Tables - A Practical Concept?

By Toledoh - November 23, 2010

Perfect - that's a heap!
Cheers,

Tim (toledoh.com.au)