Linking to Uploads Across Multiple Documents?

25 posts by 3 authors in: Forums > CMS Builder
Last Post: October 7, 2010   (RSS)

By Perchpole - September 29, 2010

Hi, Chris -

OK, I've copied your code but now it's throwing an error...

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 '138 ))' at line 3

I think we covered something like this in a previous question...

:0/

Perch

By Jason - October 1, 2010

Hi Perch,

Could you add

'debugSql' => true,

to your getRecords statement. What does it output?

Thanks.
---------------------------------------------------
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] Linking to Uploads Across Multiple Documents?

By Perchpole - October 5, 2010

Hi, Jason/Chris -

Sorry for taking so long to respond...

Here's the debug version of the error:

SELECT SQL_CALC_FOUND_ROWS `uploads`.*
FROM `cms_uploads` as `uploads`
WHERE (num IN ( 125 139 , 48 49 50 ))

Some of the items in the list are linked to attachments. One item is linked to 2 items (Upload nos. 125 & 139) the other to 3 (48, 49, 50).

The same error is also reported as:

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 '139 , 48 49 50 ))' at line 3

:0S

Perch

By Jason - October 5, 2010

Hi Perch,

What's happening here is you're combining 2 multi select fields, this is why they are tab separated. Try adding this:

$attachmentNums = array_filter(array_unique(array_pluck($news, 'attachment')));
$attachmentNums=trim($attachmentNums,"\t");
$attachmentNums=str_replace("\t",",",$attachmentNums);
$attachmentNums=str_replace(",,",",",$attachmentNums);


This code will first remove the leading and trailing tab characters, then replace the rest of the tab characters with commas. Finally it will ensure that there are no instances of 2 commas next to each other.

Give this a try and let me know how it goes.
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] Linking to Uploads Across Multiple Documents?

By Perchpole - October 5, 2010

Hi, Jason -

Thanks for the code. Unfortunately it seems to have made things worse - if the error codes are anything to go by:

Notice: Array to string conversion in /my/path/index.php on line 10 Warning: implode(): Bad arguments. in /my/path/index.php on line 21 Notice: Undefined index: listPageOrder in /my/path/viewer_functions.php on line 65

SELECT SQL_CALC_FOUND_ROWS `uploads`.*
FROM `cms_uploads` as `uploads`
WHERE (num IN ())

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 '))' at line 3

What do you think?

:0(

Perch

By Jason - October 5, 2010

Hi Perch,

I think we can remove the implode function all together. Try this:

if ($attachmentNums) {
list($attachments) = getRecords(array(
'tableName' => 'attachments',
'where' => 'num IN (' .$attachmentNums) . ')',
));
}
else {
$attachments = array();
}

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] Linking to Uploads Across Multiple Documents?

By Perchpole - October 6, 2010

Hmmm...

I fear things aren't improving! After making the changes we get this...
Notice: Array to string conversion in /mypath/index.php on line 10 Notice: Undefined index: listPageOrder in /mypath/viewer_functions.php on line 65

SELECT SQL_CALC_FOUND_ROWS `uploads`.*
FROM `cms_uploads` as `uploads`
WHERE (num IN (Array))

MySQL Error: Unknown column 'Array' in 'where clause'

Line 10 refers to:
$attachmentNums = trim($attachmentNums,"\t");

If I comment this out we are left with...
Notice: Undefined index: listPageOrder in /mypath/viewer_functions.php on line 65

SELECT SQL_CALC_FOUND_ROWS `uploads`.*
FROM `cms_uploads` as `uploads`
WHERE (num IN (Array))

MySQL Error: Unknown column 'Array' in 'where clause'

Sorry if this is a nuisance...

:0(

Perch

Re: [Jason] Linking to Uploads Across Multiple Documents?

By Perchpole - October 6, 2010

Oooh, nearly!

At least the error messages at the top of the page have gone. Now I'm just left with a couple where the "linked attachment" should appear...

Notice: Uninitialized string offset: 37 in /mypath/index.php on line 83

Warning: Invalid argument supplied for foreach() in /mypath/index.php on line 85

The first refers to...
<?php $attachment = $attachmentNums[$record['attachment']] ?>

The second...
<?php foreach($attachment['attachment'] as $upload): ?>

Nearly there....?

:0/

Perch

By Chris - October 6, 2010

Hi Perch,

Oh, your 'attachment' list field is Multi Value? Try this:

<?php
list($news) = getRecords(array(
'tableName' => 'news',
));

// get attachment nums from attachment field of $news record set
$attachmentNums = array('-1' => true);
foreach($news as $record) {
foreach(explode("\t", trim($record['attachment'])) as $num) {
if ($num) { $attachmentNums[$num] = true; }
}
}
$attachmentNums = array_keys($attachmentNums);

list($attachments) = getRecords(array(
'tableName' => 'attachments',
'where' => 'num IN (' . implode(',', $attachmentNums) . ')',
));
$attachmentsByNum = array_combine(array_pluck($attachments, 'num'), $attachments);
?>

<ul>
<?php foreach($news as $record): ?>
<li>
<b><?php echo $record['title'] ?></b><br />

<?php if ($record['attachment']): ?>
Attachments:
<ul>
<?php foreach(explode("\t", trim($record['attachment'])) as $attachmentNum): ?>
<?php $attachment = $attachmentsByNum[$attachmentNum] ?>
<?php foreach($attachment['uploads'] as $upload): ?>
<a href="<?php echo $upload['urlPath'] ?>"><?php echo $upload['filename'] ?></a><br/>
<?php endforeach ?>
<?php endforeach ?>
</ul>
<?php endif ?>
</li>
<?php endforeach ?>
</ul>

Does that work? Please let me know if you have any questions.
All the best,
Chris