Linking to Uploads Across Multiple Documents?

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

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?

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

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

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?

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

By Jason - October 6, 2010

Hi Perch,

Sorry, about this one.

Since $attachmentNums is an array, we need to convert it into a string before we can get rid of the tab characters. Try using this:

$attachmentNums = array_filter(array_unique(array_pluck($news, 'attachment')));
if ($attachmentNums) {

$attachmentNums = implode(",",$attachmentNums);
$attachmentNums=trim($attachmentNums,"\t");
$attachmentNums=str_replace("\t",",",$attachmentNums);
$attachmentNums=str_replace(",,",",",$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?

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