db->query_read error

[thesatman]

I'm going crazy trying to get a database query working-

I currently have the following in Hook Location : newpost_process working fine-

// Attachments credits
$attachs = $vbulletin->db->query_read("
SELECT dateline, thumbnail_dateline, filename, filesize, visible, attachmentid, counter,
IF(thumbnail_filesize > 0, 1, 0) AS hasthumbnail, thumbnail_filesize,
attachmenttype.thumbnail AS build_thumbnail, attachmenttype.newwindow
FROM " . TABLE_PREFIX . "attachment AS attachment
LEFT JOIN " . TABLE_PREFIX . "attachmenttype AS attachmenttype USING (extension)
WHERE posthash = '" . $post['posthash'] . "'
AND userid = " . $vbulletin->userinfo['userid'] . "
ORDER BY attachmentid
");

$attachcount11 = $vbulletin->db->num_rows($attachs);
$credits = $attachcount11 * 20 + 1;

-------------------------------
I now want to add the following WHERE-

AND extension = mid

So it only collects data in mid format but i just keep getting a database error all the time after adding that 1 line ??? Can anybody see a reason why ?

This is what i have when i get the database error-

// Attachments credits
$attachs = $vbulletin->db->query_read("
SELECT dateline, thumbnail_dateline, filename, filesize, visible, attachmentid, counter,
IF(thumbnail_filesize > 0, 1, 0) AS hasthumbnail, thumbnail_filesize,
attachmenttype.thumbnail AS build_thumbnail, attachmenttype.newwindow
FROM " . TABLE_PREFIX . "attachment AS attachment
LEFT JOIN " . TABLE_PREFIX . "attachmenttype AS attachmenttype USING (extension)
WHERE posthash = '" . $post['posthash'] . "'
AND userid = " . $vbulletin->userinfo['userid'] . "
AND extension = mid
ORDER BY attachmentid
");

$attachcount11 = $vbulletin->db->num_rows($attachs);
$credits = $attachcount11 * 20 + 1;

--------------------------------

Any help appreciated.

[Paul M]

mid needs to be inside single quotes ;

AND extension = 'mid'

[thesatman]

thanks for your help it was because the database as 2 tables caleed extension so i had to use-

WHERE attachment.extension = 'mid'

1 more small problem i need to use the following in the above-

$credit_filetypes = explode(',', $vbulletin->options['creditsys_filetypes']);

with-

WHERE attachment.extension = $credit_filetypes

But when i test it, i just get-

WHERE attachment.extension = array

so its not displaying what its taken from the explode ?? how would i do this ?

Thanks
Chris.

[hambil]

Originally Posted by thesatman

thanks for your help it was because the database as 2 tables caleed extension so i had to use-

WHERE attachment.extension = 'mid'

1 more small problem i need to use the following in the above-

$credit_filetypes = explode(',', $vbulletin->options['creditsys_filetypes']);

with-

WHERE attachment.extension = $credit_filetypes

But when i test it, i just get-

WHERE attachment.extension = array

so its not displaying what its taken from the explode ?? how would i do this ?

Thanks
Chris.

Explode turns a delimited string into an array. If $vbulletin->options['creditsys_filetypes'] is already a delimited string then you don't need to do anything but stick it in the query with an IN clause.

$sqlwhere = "WHERE attachment.extension IN(" . $vbulletin->options['creditsys_filetypes'] . ")";

[thesatman]

Thanks for your help the above outputs-

WHERE attachment.extension IN(jar,mid,mmf,mp3,mp4,wav,zip,3gp)

but i need it to output-

WHERE attachment.extension IN ('jar', 'mid' ,'mmf' ,'mp3' ,'mp4' ,'wav' ,'zip' ,'3gp')

chris

[hambil]

Originally Posted by thesatman

Thanks for your help the above outputs-

WHERE attachment.extension IN(jar,mid,mmf,mp3,mp4,wav,zip,3gp)

but i need it to output-

WHERE attachment.extension IN ('jar', 'mid' ,'mmf' ,'mp3' ,'mp4' ,'wav' ,'zip' ,'3gp')

chris

PHP Code:

$instr = "'" . str_replace(",", "','", $vbulletin->options['creditsys_filetypes']) . "'";
$sqlwhere = "WHERE attachment.extension IN(" . $instr . ")"; 


[thesatman]

Many thanks, that did the trick. Dont think i'd have ever worked that one out without your help

[hambil]

Originally Posted by thesatman

Many thanks, that did the trick. Dont think i'd have ever worked that one out without your help

You're welcome. Glad it worked

[thesatman]

Hi, Sorry got 1 more quick question. I'm using the following code to display the top 10 attachments-

$top_stats = array();
// TOP POSTERS
$top_posters = $db->query_read("SELECT * FROM ".TABLE_PREFIX."attachment ORDER BY counter DESC LIMIT 10");
while($top_poster = $db->fetch_array($top_posters))
{
eval('$top_stats[\'top_posters\'] .= "' . fetch_template('top_posters') . '";');
}
unset($top_poster);
$db->free_result($top_posters);
eval('$home[$mods[\'modid\']][\'content\'] = "' . fetch_template('adv_portal_topposters') . '";');

And then i'm using the folling in the template-

$top_poster[filename]

the above is working and displays the attachment like-

filname.mp3

What i'd like to do is display the file name without the .mp3 extension, so like-

filename

What would i use to get rid of the .mp3

Thanks chris.