Advertisement:

Author Topic: Please, help me with this search query  (Read 227 times)

Offline diegolyanky

  • Jr. Member
  • **
  • Posts: 298
  • Gender: Male
Please, help me with this search query
« on: November 10, 2018, 02:00:35 PM »
Can anybody help me with this ?

It's supposed, will return a list of attached files but I don't understand 2.0.x structure.

Code: [Select]
function BrowseAttachments()
{
global $context, $db_prefix, $txt, $scripturl, $options, $modSettings, $user_info;

//Let's see who is allowed to see the list?
//No Guests :P
if($context['user']['is_guest'])
redirectexit();
   //Okay now let's work a little bit :x
else {
//Admins are everytime allowed :P
if(!$context['user']['is_admin']) {
if(empty($modSettings['Allowed_Attachments_Groups']))
         redirectexit();
else {
         //Clean the ModSettings i will only numbers and ,
         preg_match_all('/(?:([0-9,]+)|.)/i', $modSettings['Allowed_Attachments_Groups'], $matches);
         $allow_array = implode('', $matches['1']);
$allow_array = explode(',', $allow_array);
            if(empty($allow_array))
            redirectexit();
            else {
$noredirect = FALSE;
//Huf why could it not simpler, i hate it.
            foreach($allow_array as $gid) {
$noredirect = in_array($gid, $user_info['groups']);
                  if($noredirect) break;
               }
//Okay he or she is not in the Allowed Groups... go back to the index :P
               if(!$noredirect)
            redirectexit();
}
}
}
}

//Language
loadLanguage('Admin');

//Need to Check something
$_REQUEST['sa'] = empty($_REQUEST['sa']) ? '' : $_REQUEST['sa'];

//Okay did you Serach something? Prepare and Redirect!
if(!empty($_REQUEST['search'])) {
$context['search'] = strtolower(trim(preg_replace('~&#(\d{4,5}|[2-9]\d{2,4}|1[2-9]\d);~', '&#$1;', htmlspecialchars($_REQUEST['search']))));
$context['search'] = addslashes($context['search']);

//Okay i simple encode it :) better browser compatible!
$context['hidden_search'] = base64_encode($context['search']);

//Okay go to the serach :x
redirectexit("action=BrowseAttachments;search2=".$context['hidden_search'] . (isset($_REQUEST['searchoption']) ? ';'.$_REQUEST['searchoption'] : ''));
}
//The Real Search is now working?
elseif(!empty($_GET['search2'])) {
SearchFiles();
}
//Okay only Browsing the files xD
else {
//Load the Attachements :P
BrowseFiles();

    //Creat a new Page Index!
$context['page_index'] = constructPageIndex($scripturl . '?action=BrowseAttachments' . ($context['browse_type'] == 'attachments' ? '' : ';' . $context['browse_type']) . ';sort=' . $context['sort_by'] . ($context['sort_direction'] == 'down' ? ';desc' : ''), $_REQUEST['start'], $context['num_' . $context['browse_type']], $modSettings['defaultMaxMessages']);

$context['sub_template'] = 'browse_special';
}

//Finish it!
loadTemplate('ManageAttachments');
}


And this is for search in attachments...

Code: [Select]
function SearchFiles()
{
global $context, $db_prefix, $txt, $scripturl, $options, $modSettings;

$context['page_title'] = $txt['smf201'];
$context['description'] = $txt['smf202'];
$context['selected'] = 'search';
$context['sub_template'] = 'browse_special';

// Attachments or avatars?
$context['browse_type'] = 'attachments';

// Some preparing ;)
if(!empty($_REQUEST['search2'])) {
$context['search'] = base64_decode($_REQUEST['search2']);
$context['hidden_search'] = $_REQUEST['search2'];
}
else
redirectexit("action=BrowseAttachments");

$context['search_option'] = (isset($_REQUEST['each']) ? 'each' : isset($_REQUEST['word']) ? 'word' : '');

//Okay you want to search each splited word... (Huge...)
if(isset($_REQUEST['each'])) {
$index = split(" ", $context['search']);
$index = array_unique($index);
reset($index);
//Okay lets generate the like index :P
$context['search_query_standard'] = "
filename LIKE '%".implode("%'
AND filename LIKE '%", $index)."%'";
$context['search_query_standard_a'] = "
a.filename LIKE '%".implode("%'
AND a.filename LIKE '%", $index)."%'";
}
elseif(isset($_REQUEST['word'])) {
$index = split(" ", $context['search']);
$index = array_unique($index);
reset($index);
//Okay lets generate the like index :P
$context['search_query_standard'] = "
(filename LIKE '%".implode("%'
OR filename LIKE '%", $index)."%')";
$context['search_query_standard_a'] = "
(a.filename LIKE '%".implode("%'
OR a.filename LIKE '%", $index)."%')";
}
else {
$context['search_query_standard'] = "filename LIKE '%".$context['search']."%'";
$context['search_query_standard_a'] = "a.filename LIKE '%".$context['search']."%'";
}

// Get the number of attachments.
$context['num_attachments'] = 0;
$context['num_thumbs'] = 0;
$request = db_query("
SELECT attachmentType, COUNT(ID_ATTACH) AS num_attach
FROM {$db_prefix}attachments
WHERE attachmentType IN (0, 3)
AND ID_MEMBER = 0
AND $context[search_query_standard]
GROUP BY attachmentType", __FILE__, __LINE__);
while ($row = mysql_fetch_assoc($request))
$context[empty($row['attachmentType']) ? 'num_attachments' : 'num_thumbs'] = $row['num_attach'];
mysql_free_result($request);

// Also get the avatar amount.
$request = db_query("
SELECT COUNT(ID_ATTACH)
FROM {$db_prefix}attachments
WHERE ID_MEMBER != 0
AND $context[search_query_standard]", __FILE__, __LINE__);
list ($context['num_avatars']) = mysql_fetch_row($request);
mysql_free_result($request);

// Allow for sorting of each column...
$sort_methods = array(
'name' => 'a.filename',
'date' => $context['browse_type'] == 'avatars' ? 'mem.lastLogin' : 'm.ID_MSG',
'size' => 'a.size',
'member' => 'mem.realName'
);

// Set up the importantant sorting variables... if they picked one...
if (!isset($_GET['sort']) || !isset($sort_methods[$_GET['sort']]))
{
$_GET['sort'] = 'date';
$descending = !empty($options['view_newest_first']);
}
// ... and if they didn't...
else
$descending = isset($_GET['desc']);

$context['sort_by'] = $_GET['sort'];
$_GET['sort'] = $sort_methods[$_GET['sort']];
$context['sort_direction'] = $descending ? 'down' : 'up';

// Get the page index ready......
if (!isset($_REQUEST['start']) || $_REQUEST['start'] < 0)
$_REQUEST['start'] = 0;

$context['page_index'] = constructPageIndex($scripturl . '?action=BrowseAttachments;search2=' . $context['hidden_search'] . (!empty($context['search_option']) ? ';'.$context['search_option'] : '') . ';sort=' . $context['sort_by'] . ($context['sort_direction'] == 'down' ? ';desc' : ''), $_REQUEST['start'], $context['num_' . $context['browse_type']], $modSettings['defaultMaxMessages']);
$context['start'] = $_REQUEST['start'];

// Choose a query depending on what we are viewing.
if ($context['browse_type'] == 'avatars')
$request = db_query("
SELECT
'' AS ID_MSG, IFNULL(mem.realName, '$txt[470]') AS posterName, mem.lastLogin AS posterTime, 0 AS ID_TOPIC, a.ID_MEMBER,
a.ID_ATTACH, a.filename, a.attachmentType, a.size, a.width, a.height, a.downloads, '' AS subject, 0 AS ID_BOARD
FROM {$db_prefix}attachments AS a
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = a.ID_MEMBER)
WHERE a.ID_MEMBER != 0
AND $context[search_query_standard_a]
ORDER BY $_GET[sort] " . ($descending ? 'DESC' : 'ASC') . "
LIMIT $context[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);
else
$request = db_query("
SELECT
m.ID_MSG, IFNULL(mem.realName, m.posterName) AS posterName, m.posterTime, m.ID_TOPIC, m.ID_MEMBER,
a.ID_ATTACH, a.filename, a.attachmentType, a.size, a.width, a.height, a.downloads, mf.subject, t.ID_BOARD
FROM ({$db_prefix}attachments AS a, {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}messages AS mf)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE a.ID_MSG = m.ID_MSG
AND a.attachmentType = " . ($context['browse_type'] == 'attachments' ? '0' : '3') . "
AND t.ID_TOPIC = m.ID_TOPIC
AND mf.ID_MSG = t.ID_FIRST_MSG
AND $context[search_query_standard_a]
ORDER BY $_GET[sort] " . ($descending ? 'DESC' : 'ASC') . "
LIMIT $context[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);
$context['posts'] = array();
while ($row = mysql_fetch_assoc($request))
$context['posts'][] = array(
'id' => $row['ID_MSG'],
'poster' => array(
'id' => $row['ID_MEMBER'],
'name' => $row['posterName'],
'href' => empty($row['ID_MEMBER']) ? '' : $scripturl . '?action=profile;u=' . $row['ID_MEMBER'],
'link' => empty($row['ID_MEMBER']) ? $row['posterName'] : '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>'
),
'time' => empty($row['posterTime']) ? $txt['never'] : timeformat($row['posterTime']),
'timestamp' => forum_time(true, $row['posterTime']),
'attachment' => array(
'id' => $row['ID_ATTACH'],
'size' => round($row['size'] / 1024, 2),
'width' => $row['width'],
'height' => $row['height'],
'name' => $row['filename'],
'downloads' => $row['downloads'],
'href' => $row['attachmentType'] == 1 ? $modSettings['custom_avatar_url'] . '/' . $row['filename'] : ($scripturl . '?action=dlattach;' . ($context['browse_type'] == 'avatars' ? 'type=avatar;' : 'topic=' . $row['ID_TOPIC'] . '.0;') . 'id=' . $row['ID_ATTACH']),
'link' => '<a href="' . ($row['attachmentType'] == 1 ? $modSettings['custom_avatar_url'] . '/' . $row['filename'] : ($scripturl . '?action=dlattach;' . ($context['browse_type'] == 'avatars' ? 'type=avatar;' : 'topic=' . $row['ID_TOPIC'] . '.0;') . 'id=' . $row['ID_ATTACH'])) . '"' . (empty($row['width']) || empty($row['height']) ? '' : ' onclick="return reqWin(this.href + \';image\', ' . ($row['width'] + 20) . ', ' . ($row['height'] + 20) . ', true);"') . '>' . $row['filename'] . '</a>'
),
'topic' => $row['ID_TOPIC'],
'subject' => $row['subject'],
'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0">' . $row['subject'] . '</a>'
);
mysql_free_result($request);
}


Any help will be great !!

Thanks !
SMF ... SimpleMachines ... Simple, but complete if you want it ;)

Offline Rock Lee

  • Native Language Support Specialist
  • SMF Hero
  • *
  • Posts: 1,995
  • Gender: Male
  • I also speak english :D
    • BomberCode.Oficial on Facebook
    • RockLee-BC on GitHub
    • @Bomber_Code on Twitter
    • Bomber Code ~ La nueva era del conocimiento
Re: Please, help me with this search query
« Reply #1 on: November 10, 2018, 07:05:59 PM »
I'm not sure at all but it could be something like this:

Code: [Select]
function SearchFiles()
{
global $context, $smcFunc, $txt, $scripturl, $options, $modSettings;

$context['selected'] = 'search';
$context['sub_template'] = 'browse_special';

// Attachments or avatars?
$context['browse_type'] = 'attachments';

// Some preparing ;)
if(!empty($_REQUEST['search2'])) {
$context['search'] = base64_decode($_REQUEST['search2']);
$context['hidden_search'] = $_REQUEST['search2'];
}
else
redirectexit("action=BrowseAttachments");

$context['search_option'] = (isset($_REQUEST['each']) ? 'each' : isset($_REQUEST['word']) ? 'word' : '');

//Okay you want to search each splited word... (Huge...)
if(isset($_REQUEST['each'])) {
$index = split(" ", $context['search']);
$index = array_unique($index);
reset($index);
//Okay lets generate the like index :P
$context['search_query_standard'] = "
filename LIKE '%".implode("%'
AND filename LIKE '%", $index)."%'";
$context['search_query_standard_a'] = "
a.filename LIKE '%".implode("%'
AND a.filename LIKE '%", $index)."%'";
}
elseif(isset($_REQUEST['word'])) {
$index = split(" ", $context['search']);
$index = array_unique($index);
reset($index);
//Okay lets generate the like index :P
$context['search_query_standard'] = "
(filename LIKE '%".implode("%'
OR filename LIKE '%", $index)."%')";
$context['search_query_standard_a'] = "
(a.filename LIKE '%".implode("%'
OR a.filename LIKE '%", $index)."%')";
}
else {
$context['search_query_standard'] = "filename LIKE '%".$context['search']."%'";
$context['search_query_standard_a'] = "a.filename LIKE '%".$context['search']."%'";
}

if ($browse_type === 'avatars')
$request = $smcFunc['db_query']('', '
SELECT COUNT(*)
FROM {db_prefix}attachments
WHERE id_member != {int:guest_id_member}',
array(
'guest_id_member' => 0,
)
);
else
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS num_attach
FROM {db_prefix}attachments AS a
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = a.id_msg)
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg)
WHERE a.attachment_type = {int:attachment_type}
AND a.id_member = {int:guest_id_member}',
array(
'attachment_type' => $browse_type === 'thumbs' ? '3' : '0',
'guest_id_member' => 0,
)
);

list ($num_files) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

return $num_files;

// Also get the avatar amount.
$request = $smcFunc['db_query']('', '
SELECT COUNT(id_attach)
FROM {$db_prefix}attachments
WHERE ID_MEMBER != 0
AND $context[search_query_standard]", __FILE__, __LINE__)';
$smcFunc['db_free_result']($request);

// Allow for sorting of each column...
$sort_methods = array(
'name' => 'a.filename',
'date' => $context['browse_type'] == 'avatars' ? 'mem.last_Login' : 'm.ID_MSG',
'size' => 'a.size',
'member' => 'mem.real_name'
);

// Set up the importantant sorting variables... if they picked one...
if (!isset($_GET['sort']) || !isset($sort_methods[$_GET['sort']]))
{
$_GET['sort'] = 'date';
$descending = !empty($options['view_newest_first']);
}
// ... and if they didn't...
else
$descending = isset($_GET['desc']);

$context['sort_by'] = $_GET['sort'];
$_GET['sort'] = $sort_methods[$_GET['sort']];
$context['sort_direction'] = $descending ? 'down' : 'up';

// Get the page index ready......
if (!isset($_REQUEST['start']) || $_REQUEST['start'] < 0)
$_REQUEST['start'] = 0;

$context['page_index'] = constructPageIndex($scripturl . '?action=BrowseAttachments;search2=' . $context['hidden_search'] . (!empty($context['search_option']) ? ';'.$context['search_option'] : '') . ';sort=' . $context['sort_by'] . ($context['sort_direction'] == 'down' ? ';desc' : ''), $_REQUEST['start'], $context['num_' . $context['browse_type']], $modSettings['defaultMaxMessages']);
$context['start'] = $_REQUEST['start'];

// Choose a query depending on what we are viewing.
if ($context['browse_type'] == 'avatars')
$request = $smcFunc['db_query']('', '
SELECT
'' AS ID_MSG, IFNULL(mem.real_Name) AS poster_Name, mem.last_Login AS poster_time, 0 AS ID_TOPIC, a.ID_MEMBER,
a.ID_ATTACH, a.filename, a.attachment_Type, a.size, a.width, a.height, a.downloads, '' AS subject, 0 AS ID_BOARD
FROM {$db_prefix}attachments AS a
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = a.ID_MEMBER)
WHERE a.ID_MEMBER != 0
AND $context[search_query_standard_a]
ORDER BY $_GET[sort] " . ($descending ? 'DESC' : 'ASC') . "
LIMIT $context[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__)';
else
$request = $smcFunc['db_query']('', '
SELECT
m.ID_MSG, IFNULL(mem.real_Name, m.poster_Name) AS poster_Name, m.poster_time, m.ID_TOPIC, m.ID_MEMBER,
a.ID_ATTACH, a.filename, a.attachment_Type, a.size, a.width, a.height, a.downloads, mf.subject, t.ID_BOARD
FROM ({$db_prefix}attachments AS a, {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}messages AS mf)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE a.ID_MSG = m.ID_MSG
AND a.attachment_Type = " . ($context['browse_type'] == 'attachments' ? '0' : '3') . "
AND t.ID_TOPIC = m.ID_TOPIC
AND mf.ID_MSG = t.ID_FIRST_MSG
AND $context[search_query_standard_a]
ORDER BY $_GET[sort] " . ($descending ? 'DESC' : 'ASC') . "
LIMIT $context[start], $modSettings[defaultMaxMessages]", __FILE__, __LINE__);
$context['posts'] = array();
while ($row = $smcFunc['db_fetch_assoc'] ($result))
$context['posts'][] = array(
'id' => $row['ID_MSG'],
'poster' => array(
'id' => $row['ID_MEMBER'],
'name' => $row['poster_Name'],
'href' => empty($row['ID_MEMBER']) ? '' : $scripturl . '?action=profile;u=' . $row['ID_MEMBER'],
'link' => empty($row['ID_MEMBER']) ? $row['poster_Name'] : '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['poster_Name'] . '</a>'
),
'time' => empty($row['poster_time']) ? $txt['never'] : timeformat($row['poster_time']),
'timestamp' => forum_time(true, $row['poster_time']),
'attachment' => array(
'id' => $row['ID_ATTACH'],
'size' => $_FILES['attachment']['size'][$n],
'width' => $row['width'],
'height' => $row['height'],
'name' => $_FILES['attachment']['name'][$n],
'downloads' => $row['downloads'],
'href' => $row['attachment_Type'] == 1 ? $modSettings['custom_avatar_url'] . '/' . $row['filename'] : ($scripturl . '?action=dlattach;' . ($context['browse_type'] == 'avatars' ? 'type=avatar;' : 'topic=' . $row['ID_TOPIC'] . '.0;') . 'id=' . $row['ID_ATTACH']),
'link' => '<a href="' . ($row['attachment_Type'] == 1 ? $modSettings['custom_avatar_url'] . '/' . $row['filename'] : ($scripturl . '?action=dlattach;' . ($context['browse_type'] == 'avatars' ? 'type=avatar;' : 'topic=' . $row['ID_TOPIC'] . '.0;') . 'id=' . $row['ID_ATTACH'])) . '"' . (empty($row['width']) || empty($row['height']) ? '' : ' onclick="return reqWin(this.href + \';image\', ' . ($row['width'] + 20) . ', ' . ($row['height'] + 20) . ', true);"') . '>' . $row['filename'] . '</a>'
),
'topic' => $row['ID_TOPIC'],
'subject' => $row['subject'],
'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0">' . $row['subject'] . '</a>'
);
$smcFunc['db_free_result']($request);
}

another can help you more ... possibly, excuse me. :-[


Regards!
¡Regresando como cual Fenix! ~ Bomber Code © 2018
Ayudas - Aportes - Tutoriales - Y mucho mas!!!


Ayudame via PayPal

Offline diegolyanky

  • Jr. Member
  • **
  • Posts: 298
  • Gender: Male
Re: Please, help me with this search query
« Reply #2 on: November 12, 2018, 05:59:45 PM »
Thanks RockLee

But not worked. It appear to be an error ( give me error 500 ) ...
SMF ... SimpleMachines ... Simple, but complete if you want it ;)

Offline Rock Lee

  • Native Language Support Specialist
  • SMF Hero
  • *
  • Posts: 1,995
  • Gender: Male
  • I also speak english :D
    • BomberCode.Oficial on Facebook
    • RockLee-BC on GitHub
    • @Bomber_Code on Twitter
    • Bomber Code ~ La nueva era del conocimiento
Re: Please, help me with this search query
« Reply #3 on: November 12, 2018, 08:10:12 PM »
I'm going to investigate why I think I'm closing a variable wrong or I'm putting the expression wrong.


Regards!
¡Regresando como cual Fenix! ~ Bomber Code © 2018
Ayudas - Aportes - Tutoriales - Y mucho mas!!!


Ayudame via PayPal