performing joins

7 posts by 2 authors in: Forums > CMS Builder
Last Post: December 10, 2008   (RSS)

By zaba - December 3, 2008 - edited: December 3, 2008

Hi,
Hope you can help me with this one.
I have 2 tables.
1 called offers_manufacturer, with 2 fields manufacturer name and logo(image).
The second table called offers has fields 6 fileds, one of which pulls in the manufacturer name as radio buttons by joining them in the cms. This has been no problem to do, and I can display the manufacturer name alongside the data pulled in from the offers table on the site. What I want to do is pull in the logo(image) alongside the data pulled in from the offers table and this has me stuck. heres a url of the page.
www.feather-diesel.co.uk/contents09/offers/offers.php

heres the cut down version of code for that page:
<?php

require_once "/home/9818/feather/www.feather-diesel.co.uk/public_html/cms/lib/viewer_functions.php";

list($offersRecords, $offersMetaData) = getRecords(array(
'tableName' => 'offers',
'loadUploads' => '0',
'allowSearch' => '0',
));

?>
<html>
<head>
<title></title>
</head>
<body>
<!--START content -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>LOGO</td>
<td>PART No.</td>
<td>DESCRIPTION</td>
<td><div align="right">RRP</div></td>
<td><div align="right">OFFER PRICE</div></td>
<td><div align="right">ORDER QTY.</div></td>
</tr><?php foreach ($offersRecords as $record): ?>
<tr>
<td><!--I want to add in the logo (field name is logo and field type is upload) here which resides in the tableName offers_manufacturer. The followin in the manufacturer --><?php echo $record['manufacturer'] ?></td>
<td><?php echo $record['partNum'] ?></td>
<td><?php echo $record['description'] ?></td>
<td><div align="right">&pound;<?php echo $record['rrp'] ?></div></td>
<td><div align="right">&pound;<?php echo $record['offerPrice'] ?></div></td>
<td><div align="right">
<a href="/contents09/offers/cart.php?action=add_item&id=<?php echo $record["num"]; ?>&qty=1">Add to cart</a>

</div></td>
</tr><?php endforeach; ?>
</table>
<!--END content -->
</body>
</html>

Re: [zaba] performing joins

By Dave - December 3, 2008

We don't have an elegant way to do joins yet.

The simplest workaround is to just do another query inside the foreach loop to manually look up each logo. Something like this:

<?php
list($manufacturerRecords, $manufacturerMetaData) = getRecords(array(
'tableName' => 'offers_manufacturer',
'loadUploads' => '0',
'allowSearch' => '0',
'where' => "manufacturer_name = '" +mysql_real_escape_string($record['manufacturer_name'])+ "' ",
));
$manufacturerRecord = @$manufacturerRecords[0]; // get first record
?>

<?php echo $manufacturerRecord['logo']['urlPath'] ?>

That's the simplest. Another way if you know a little PHP would be to get a list of all the manufacturer names or nums and do one query to load all the images at the top of the page. And last would be to write a custom MySQL join to pull down the data.

Hope that helps. Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] performing joins

By zaba - December 4, 2008

Okay,

I have been messing around with this for quite a while but I can't seem to get it to do what I want it to do. All I need is for it to pull in a logo (.gif) from the 'offers_manufacturer' table based on the common field of manufacturer in the 'offers' table. It seems to only pull in the first logo in the table but not the correct and corresponding one to the row to which it is associated. I may have bungled up the code, but this is what I have (cut to just the relevant bits). Could you help me with this its frying my small brain and my deadlines already loomed.


<?php

require_once "/home/9818/feather/www.feather-diesel.co.uk/public_html/cms/lib/viewer_functions.php";

list($offersRecords, $offersMetaData) = getRecords(array(
'tableName' => 'offers',
'loadUploads' => '0',
'allowSearch' => '0',
));
?>


<html>
<head>
</head>
<body>
<!--START content -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="offerheadrow" nowrap="nowrap">LOGO</td>
<td class="offerheadrow" nowrap="nowrap">PART No.</td>
<td class="offerheadrow">DESCRIPTION</td>
<td class="offerheadrow"><div align="right">RRP</div></td>
<td nowrap="nowrap" class="offerheadrow"><div align="right">OFFER PRICE</div></td>
<td nowrap="nowrap" class="offerheadrow"><div align="right">ORDER QTY.</div></td>
</tr><?php foreach ($offersRecords as $record): ?>
<tr>
<td class="offerbody"><?php echo $record['manufacturer'] ?>
<?php list($offers_manufacturerRecords, $offers_manufacturerMetaData) = getRecords(array(
'tableName' => 'offers_manufacturer',
'where' => "manufacturer = '" +mysql_real_escape_string($record['manufacturer'])+ "' ",
));
$offers_manufacturerRecord = @$offers_manufacturerRecords[0]; // get first record
?>
<?php foreach ($offers_manufacturerRecord['logo'] as $upload): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" />

</td>
<td class="offerbody"><?php echo $record['partNum'] ?></td>
<td class="offerbody"><?php echo $record['description'] ?></td>
<td class="offerbody"><div align="right">&pound;<?php echo $record['rrp'] ?></div></td>
<td class="offerprice"><div align="right">&pound;<?php echo $record['offerPrice'] ?></div></td>
<td class="offerbody"><div align="right">
<a href="/contents09/offers/cart.php?action=add_item&id=<?php echo $record["num"]; ?>&qty=1">Add to cart</a>

</div></td>
</tr><?php endforeach; ?><?php endforeach; ?>

</table>
<!--END content -->


</body>
</html>

Re: [zaba] performing joins

By Dave - December 5, 2008

Hi Zaba,

The simplest and fastest way for me to do this would be to just look at the files on your server. If that works for you send CMS and FTP login details (and the url to the viewer page) to dave@interactivetools.com and I can take a look. Email, don't post login details to the forum.

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

Re: [zaba] performing joins

By Dave - December 9, 2008

zaba,

Sorry for the delay. I've sent you an email back. The working code looks like this:

<?php foreach ($offersRecords as $record): ?>

...

<?php list($offers_manufacturerRecords, $offers_manufacturerMetaData) = getRecords(array(
'tableName' => 'offers_manufacturer',
'where' => "manufacturer = '" .mysql_real_escape_string($record['manufacturer']). "' ",
));
$offers_manufacturerRecord = @$offers_manufacturerRecords[0]; // get first record
$upload = @$offers_manufacturerRecord['logo'][0]; // get first upload
?>

<?php if ($upload): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>"
height="<?php echo $upload['thumbHeight'] ?>" alt="" /><br/>
<?php endif ?>
...

<?php endforeach; ?>

Hope that helps, let me know if you have any questions.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] performing joins

By zaba - December 10, 2008

Thanks Dave, that's perfect. I think I am beginning to understand what you have done. Your intervention goes above and beyond what anyone could reasonably expect. The support is first class as is the product. I am so impressed. Thank you!