Requête SQL et condition "if" [Réglé]

FORUM Tom's Hardware » Programmation » PHP & MySQL & ASP » [Réglé] Requête SQL et condition "if"

[Réglé] Requête SQL et condition "if"

Il y a 273 utilisateurs connus et inconnus. Pour voir la liste des connectés connus, cliquez ici

Voir les posts : Par défaut Tous Les bonnes notes Les notes insuffisantes

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [Réglé] Requête SQL et condition "if"

sbourdon

Plus d'informations

04-03-2006 à 13:32:21

Bonjour,

Il s'agit de ma première présence ici...

Je travaille sur Post Control, un MOD pour phpBB offrant aux administrateurs et modérateurs la possibilité de modérer les messages avant qu'ils n'apparaissent en ligne.

Les messages non-validés n'apparaissent pas dans les résultats d'une recherche; cependant, le nombre total de messages trouvés inclus présentement les messages non-validés. Ce que je désire faire est donc de vérifier la validation du message avant d'ajouter celui-ci au nombre de résultats trouvés; s'il n'est pas validé, il faudrait sauter l'instruction word_count++ du fichier search.php (voir ligne 195 ci-dessous).

Sachant que les messages non-validés ont, à l'intérieur de la table phpbb_posts la variable validate positionnée à 1, est-ce que quelqu'un saurait m'indiquer la requête SQL à effectuer afin d'accomplir ceci?

Merci de votra aide et support!

P.S.: Voici un extrait du fichier search.php, lequel doit être modifier afin de tenir compte du nombre de messages non-validés lors de l'affichage du nombre de résultats trouvés au cours d'une recherche:

Code :

//
// Cycle through options ...
//
if ( $search_id == 'newposts' || $search_id == 'egosearch' || $search_id == 'unanswered' || $search_keywords != '' || $search_author != '' )
{
if ( $search_id == 'newposts' || $search_id == 'egosearch' || ( $search_author != '' && $search_keywords == '' ) )
{
if ( $search_id == 'newposts' )
{
if ( $userdata['session_logged_in'] )
{
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE post_time >= " . $userdata['user_lastvisit'];
}
else
{
redirect(append_sid("login.$phpEx?redirect=search.$phpEx&search_id=newposts", true));
}
$show_results = 'topics';
$sort_by = 0;
$sort_dir = 'DESC';
}
else if ( $search_id == 'egosearch' )
{
if ( $userdata['session_logged_in'] )
{
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE poster_id = " . $userdata['user_id'];
}
else
{
redirect(append_sid("login.$phpEx?redirect=search.$phpEx&search_id=egosearch", true));
}
$show_results = 'topics';
$sort_by = 0;
$sort_dir = 'DESC';
}
else
{
if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
{
$search_author = '';
}
$search_author = str_replace('*', '%', trim($search_author));
$sql = "SELECT user_id
FROM " . USERS_TABLE . "
WHERE username LIKE '" . str_replace("\'", "''", $search_author) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Couldn't obtain list of matching users (searching for: $search_author)", "", __LINE__, __FILE__, $sql);
}
$matching_userids = '';
if ( $row = $db->sql_fetchrow($result) )
{
do
{
$matching_userids .= ( ( $matching_userids != '' ) ? ', ' : '' ) . $row['user_id'];
}
while( $row = $db->sql_fetchrow($result) );
}
else
{
message_die(GENERAL_MESSAGE, $lang['No_search_match']);
}
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE poster_id IN ($matching_userids)";
if ($search_time)
{
$sql .= " AND post_time >= " . $search_time;
}
}
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain matched posts list', '', __LINE__, __FILE__, $sql);
}
$search_ids = array();
while( $row = $db->sql_fetchrow($result) )
{
$search_ids[] = $row['post_id'];
}
$db->sql_freeresult($result);
$total_match_count = count($search_ids);
}
else if ( $search_keywords != '' )
{
$stopword_array = @file($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/search_stopwords.txt');
$synonym_array = @file($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/search_synonyms.txt');
$split_search = array();
$split_search = ( !strstr($multibyte_charset, $lang['ENCODING']) ) ? split_words(clean_words('search', stripslashes($search_keywords), $stopword_array, $synonym_array), 'search') : split(' ', $search_keywords);
$search_msg_only = ( !$search_fields ) ? "AND m.title_match = 0" : ( ( strstr($multibyte_charset, $lang['ENCODING']) ) ? '' : '' );
$word_count = 0;
$current_match_type = 'or';
$word_match = array();
$result_list = array();
for($i = 0; $i < count($split_search); $i++)
{
if (preg_match('#^[\*%]+$#', trim($split_search[$i])) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($split_search[$i]))))
{
$split_search[$i] = '';
continue;
}
switch ( $split_search[$i] )
{
case 'and':
$current_match_type = 'and';
break;
case 'or':
$current_match_type = 'or';
break;
case 'not':
$current_match_type = 'not';
break;
default:
if ( !empty($search_terms) )
{
$current_match_type = 'and';
}
if ( !strstr($multibyte_charset, $lang['ENCODING']) )
{
$match_word = str_replace('*', '%', $split_search[$i]);
$sql = "SELECT m.post_id
FROM " . SEARCH_WORD_TABLE . " w, " . SEARCH_MATCH_TABLE . " m
WHERE w.word_text LIKE '$match_word'
AND m.word_id = w.word_id
AND w.word_common <> 1
$search_msg_only";
}
else
{
$match_word = addslashes('%' . str_replace('*', '', $split_search[$i]) . '%');
$search_msg_only = ( $search_fields ) ? "OR post_subject LIKE '$match_word'" : '';
$sql = "SELECT post_id
FROM " . POSTS_TEXT_TABLE . "
WHERE post_text LIKE '$match_word'
$search_msg_only";
}
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain matched posts list', '', __LINE__, __FILE__, $sql);
}
$row = array();
while( $temp_row = $db->sql_fetchrow($result) )
{
$row[$temp_row['post_id']] = 1;
if ( !$word_count )
{
$result_list[$temp_row['post_id']] = 1;
}
else if ( $current_match_type == 'or' )
{
$result_list[$temp_row['post_id']] = 1;
}
else if ( $current_match_type == 'not' )
{
$result_list[$temp_row['post_id']] = 0;
}
}
if ( $current_match_type == 'and' && $word_count )
{
@reset($result_list);
while( list($post_id, $match_count) = @each($result_list) )
{
if ( !$row[$post_id] )
{
$result_list[$post_id] = 0;
}
}
}
$word_count++;
$db->sql_freeresult($result);
}
}
@reset($result_list);
$search_ids = array();
while( list($post_id, $matches) = each($result_list) )
{
if ( $matches )
{
$search_ids[] = $post_id;
}
}
unset($result_list);
$total_match_count = count($search_ids);
}

Message édité par sbourdon le 10-03-2006 à 13:00:06

KangOl

Profil : Pointeur

Plus d'informations

04-03-2006 à 17:33:51

Masquer

bha dans tes requetes, tu rajoutes un

WHERE validate=0

sbourdon

Plus d'informations

04-03-2006 à 19:49:00

Masquer

Merci pour cette info!

Cependant, tu as sans doute remarqué que je ne m'y connaissais pas en php, d'où la raison de cette demande. Je ne désire que corriger un bug à l'intérieur du MOD Post Control, lequel a été abandonné par son auteur...

De ce fait, pourrais-tu spécifier à quelles requêtes exactement je dois ajouter cette ligne?

Car à l'intérieur de search.php, il y a des $SQL à n'en plus finir! ;-)

Merci beaucoup!

sbourdon

Plus d'informations

04-03-2006 à 19:53:41

Masquer

L'accès à l'ensemble du fichier search.php serait sans doute utile dans ce cas; le voici donc!

Code :

<?php
//-- mod : calendar --------------------------------------------------------------------------------
/***************************************************************************
* search.php
* -------------------
* begin : Saturday, Feb 13, 2001
* email : support@phpbb.com
*
* $Id: search.php,v 1.72.2.14 2004/07/17 13:48:32 acydburn Exp $
*
*
***************************************************************************/
/***************************************************************************
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
***************************************************************************/
define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);
include($phpbb_root_path . 'includes/bbcode.'.$phpEx);
include($phpbb_root_path . 'includes/functions_search.'.$phpEx);
//-- mod : calendar --------------------------------------------------------------------------------
//-- add
include_once($phpbb_root_path . 'includes/functions_calendar.'.$phpEx);
//-- fin mod : calendar ----------------------------------------------------------------------------
//
// Start session management
//
$userdata = session_pagestart($user_ip, PAGE_SEARCH);
init_userprefs($userdata);
//
// End session management
//
//
// Define initial vars
//
if ( isset($HTTP_POST_VARS['mode']) || isset($HTTP_GET_VARS['mode']) )
{
$mode = ( isset($HTTP_POST_VARS['mode']) ) ? $HTTP_POST_VARS['mode'] : $HTTP_GET_VARS['mode'];
}
else
{
$mode = '';
}
if ( isset($HTTP_POST_VARS['search_keywords']) || isset($HTTP_GET_VARS['search_keywords']) )
{
$search_keywords = ( isset($HTTP_POST_VARS['search_keywords']) ) ? $HTTP_POST_VARS['search_keywords'] : $HTTP_GET_VARS['search_keywords'];
}
else
{
$search_keywords = '';
}
if ( isset($HTTP_POST_VARS['search_author']) || isset($HTTP_GET_VARS['search_author']))
{
$search_author = ( isset($HTTP_POST_VARS['search_author']) ) ? $HTTP_POST_VARS['search_author'] : $HTTP_GET_VARS['search_author'];
$search_author = phpbb_clean_username($search_author);
}
else
{
$search_author = '';
}
$search_id = ( isset($HTTP_GET_VARS['search_id']) ) ? $HTTP_GET_VARS['search_id'] : '';
$show_results = ( isset($HTTP_POST_VARS['show_results']) ) ? $HTTP_POST_VARS['show_results'] : 'posts';
$show_results = ($show_results == 'topics') ? 'topics' : 'posts';
if ( isset($HTTP_POST_VARS['search_terms']) )
{
$search_terms = ( $HTTP_POST_VARS['search_terms'] == 'all' ) ? 1 : 0;
}
else
{
$search_terms = 0;
}
if ( isset($HTTP_POST_VARS['search_fields']) )
{
$search_fields = ( $HTTP_POST_VARS['search_fields'] == 'all' ) ? 1 : 0;
}
else
{
$search_fields = 0;
}
$return_chars = ( isset($HTTP_POST_VARS['return_chars']) ) ? intval($HTTP_POST_VARS['return_chars']) : 200;
$search_cat = ( isset($HTTP_POST_VARS['search_cat']) ) ? intval($HTTP_POST_VARS['search_cat']) : -1;
$search_forum = ( isset($HTTP_POST_VARS['search_forum']) ) ? intval($HTTP_POST_VARS['search_forum']) : -1;
$sort_by = ( isset($HTTP_POST_VARS['sort_by']) ) ? intval($HTTP_POST_VARS['sort_by']) : 0;
if ( isset($HTTP_POST_VARS['sort_dir']) )
{
$sort_dir = ( $HTTP_POST_VARS['sort_dir'] == 'DESC' ) ? 'DESC' : 'ASC';
}
else
{
$sort_dir = 'DESC';
}
if ( !empty($HTTP_POST_VARS['search_time']) || !empty($HTTP_GET_VARS['search_time']))
{
$search_time = time() - ( ( ( !empty($HTTP_POST_VARS['search_time']) ) ? intval($HTTP_POST_VARS['search_time']) : intval($HTTP_GET_VARS['search_time']) ) * 86400 );
$topic_days = (!empty($HTTP_POST_VARS['search_time'])) ? intval($HTTP_POST_VARS['search_time']) : intval($HTTP_GET_VARS['search_time']);
}
else
{
$search_time = 0;
$topic_days = 0;
}
$start = ( isset($HTTP_GET_VARS['start']) ) ? intval($HTTP_GET_VARS['start']) : 0;
$sort_by_types = array($lang['Sort_Time'], $lang['Sort_Post_Subject'], $lang['Sort_Topic_Title'], $lang['Sort_Author'], $lang['Sort_Forum']);
//
// encoding match for workaround
//
$multibyte_charset = 'utf-8, big5, shift_jis, euc-kr, gb2312';
//
// Begin core code
//
if ( $mode == 'searchuser' )
{
//
// This handles the simple windowed user search functions called from various other scripts
//
if ( isset($HTTP_POST_VARS['search_username']) )
{
username_search($HTTP_POST_VARS['search_username']);
}
else
{
username_search('');
}
exit;
}
else if ( $search_keywords != '' || $search_author != '' || $search_id )
{
$store_vars = array('search_results', 'total_match_count', 'split_search', 'sort_by', 'sort_dir', 'show_results', 'return_chars');
$search_results = '';
//
// Search ID Limiter, decrease this value if you experience further timeout problems with searching forums
$limiter = 5000;
//
// Cycle through options ...
//
if ( $search_id == 'newposts' || $search_id == 'egosearch' || $search_id == 'unanswered' || $search_keywords != '' || $search_author != '' )
{
if ( $search_id == 'newposts' || $search_id == 'egosearch' || ( $search_author != '' && $search_keywords == '' ) )
{
if ( $search_id == 'newposts' )
{
if ( $userdata['session_logged_in'] )
{
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE post_time >= " . $userdata['user_lastvisit'];
}
else
{
redirect(append_sid("login.$phpEx?redirect=search.$phpEx&search_id=newposts", true));
}
$show_results = 'topics';
$sort_by = 0;
$sort_dir = 'DESC';
}
else if ( $search_id == 'egosearch' )
{
if ( $userdata['session_logged_in'] )
{
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE poster_id = " . $userdata['user_id'];
}
else
{
redirect(append_sid("login.$phpEx?redirect=search.$phpEx&search_id=egosearch", true));
}
$show_results = 'topics';
$sort_by = 0;
$sort_dir = 'DESC';
}
else
{
if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
{
$search_author = '';
}
$search_author = str_replace('*', '%', trim($search_author));
$sql = "SELECT user_id
FROM " . USERS_TABLE . "
WHERE username LIKE '" . str_replace("\'", "''", $search_author) . "'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Couldn't obtain list of matching users (searching for: $search_author)", "", __LINE__, __FILE__, $sql);
}
$matching_userids = '';
if ( $row = $db->sql_fetchrow($result) )
{
do
{
$matching_userids .= ( ( $matching_userids != '' ) ? ', ' : '' ) . $row['user_id'];
}
while( $row = $db->sql_fetchrow($result) );
}
else
{
message_die(GENERAL_MESSAGE, $lang['No_search_match']);
}
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE poster_id IN ($matching_userids)";
if ($search_time)
{
$sql .= " AND post_time >= " . $search_time;
}
}
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain matched posts list', '', __LINE__, __FILE__, $sql);
}
$search_ids = array();
while( $row = $db->sql_fetchrow($result) )
{
$search_ids[] = $row['post_id'];
}
$db->sql_freeresult($result);
$total_match_count = count($search_ids);
}
else if ( $search_keywords != '' )
{
$stopword_array = @file($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/search_stopwords.txt');
$synonym_array = @file($phpbb_root_path . 'language/lang_' . $board_config['default_lang'] . '/search_synonyms.txt');
$split_search = array();
$split_search = ( !strstr($multibyte_charset, $lang['ENCODING']) ) ? split_words(clean_words('search', stripslashes($search_keywords), $stopword_array, $synonym_array), 'search') : split(' ', $search_keywords);
$search_msg_only = ( !$search_fields ) ? "AND m.title_match = 0" : ( ( strstr($multibyte_charset, $lang['ENCODING']) ) ? '' : '' );
$word_count = 0;
$current_match_type = 'or';
$word_match = array();
$result_list = array();
for($i = 0; $i < count($split_search); $i++)
{
if (preg_match('#^[\*%]+$#', trim($split_search[$i])) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($split_search[$i]))))
{
$split_search[$i] = '';
continue;
}
switch ( $split_search[$i] )
{
case 'and':
$current_match_type = 'and';
break;
case 'or':
$current_match_type = 'or';
break;
case 'not':
$current_match_type = 'not';
break;
default:
if ( !empty($search_terms) )
{
$current_match_type = 'and';
}
if ( !strstr($multibyte_charset, $lang['ENCODING']) )
{
$match_word = str_replace('*', '%', $split_search[$i]);
$sql = "SELECT m.post_id
FROM " . SEARCH_WORD_TABLE . " w, " . SEARCH_MATCH_TABLE . " m
WHERE w.word_text LIKE '$match_word'
AND m.word_id = w.word_id
AND w.word_common <> 1
$search_msg_only";
}
else
{
$match_word = addslashes('%' . str_replace('*', '', $split_search[$i]) . '%');
$search_msg_only = ( $search_fields ) ? "OR post_subject LIKE '$match_word'" : '';
$sql = "SELECT post_id
FROM " . POSTS_TEXT_TABLE . "
WHERE post_text LIKE '$match_word'
$search_msg_only";
}
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain matched posts list', '', __LINE__, __FILE__, $sql);
}
$row = array();
while( $temp_row = $db->sql_fetchrow($result) )
{
$row[$temp_row['post_id']] = 1;
if ( !$word_count )
{
$result_list[$temp_row['post_id']] = 1;
}
else if ( $current_match_type == 'or' )
{
$result_list[$temp_row['post_id']] = 1;
}
else if ( $current_match_type == 'not' )
{
$result_list[$temp_row['post_id']] = 0;
}
}
if ( $current_match_type == 'and' && $word_count )
{
@reset($result_list);
while( list($post_id, $match_count) = @each($result_list) )
{
if ( !$row[$post_id] )
{
$result_list[$post_id] = 0;
}
}
}
$word_count++;
$db->sql_freeresult($result);
}
}
@reset($result_list);
$search_ids = array();
while( list($post_id, $matches) = each($result_list) )
{
if ( $matches )
{
$search_ids[] = $post_id;
}
}
unset($result_list);
$total_match_count = count($search_ids);
}
//
// If user is logged in then we'll check to see which (if any) private
// forums they are allowed to view and include them in the search.
//
// If not logged in we explicitly prevent searching of private forums
//
$auth_sql = '';
if ( $search_forum != -1 )
{
$is_auth = auth(AUTH_READ, $search_forum, $userdata);
if ( !$is_auth['auth_read'] )
{
message_die(GENERAL_MESSAGE, $lang['No_searchable_forums']);
}
$auth_sql = "f.forum_id = $search_forum";
}
else
{
$is_auth_ary = auth(AUTH_READ, AUTH_LIST_ALL, $userdata);
if ( $search_cat != -1 )
{
$auth_sql = "f.cat_id = $search_cat";
}
$ignore_forum_sql = '';
while( list($key, $value) = each($is_auth_ary) )
{
if ( !$value['auth_read'] )
{
$ignore_forum_sql .= ( ( $ignore_forum_sql != '' ) ? ', ' : '' ) . $key;
}
}
if ( $ignore_forum_sql != '' )
{
$auth_sql .= ( $auth_sql != '' ) ? " AND f.forum_id NOT IN ($ignore_forum_sql) " : "f.forum_id NOT IN ($ignore_forum_sql) ";
}
}
//
// Author name search
//
if ( $search_author != '' )
{
if (preg_match('#^[\*%]+$#', trim($search_author)) || preg_match('#^[^\*]{1,2}$#', str_replace(array('*', '%'), '', trim($search_author))))
{
$search_author = '';
}
$search_author = str_replace('*', '%', trim(str_replace("\'", "''", $search_author)));
}
if ( $total_match_count )
{
if ( $show_results == 'topics' )
{
//
// This one is a beast, try to seperate it a bit (workaround for connection timeouts)
//
$search_id_chunks = array();
$count = 0;
$chunk = 0;
if (count($search_ids) > $limiter)
{
for ($i = 0; $i < count($search_ids); $i++)
{
if ($count == $limiter)
{
$chunk++;
$count = 0;
}
$search_id_chunks[$chunk][$count] = $search_ids[$i];
$count++;
}
}
else
{
$search_id_chunks[0] = $search_ids;
}
$search_ids = array();
for ($i = 0; $i < count($search_id_chunks); $i++)
{
$where_sql = '';
if ( $search_time )
{
$where_sql .= ( $search_author == '' && $auth_sql == '' ) ? " AND post_time >= $search_time " : " AND p.post_time >= $search_time ";
}
if ( $search_author == '' && $auth_sql == '' )
{
$sql = "SELECT topic_id
FROM " . POSTS_TABLE . "
WHERE post_id IN (" . implode(", ", $search_id_chunks[$i]) . " )
$where_sql
GROUP BY topic_id";
}
else
{
$from_sql = POSTS_TABLE . " p";
if ( $search_author != '' )
{
$from_sql .= ", " . USERS_TABLE . " u";
$where_sql .= " AND u.user_id = p.poster_id AND u.username LIKE '$search_author' ";
}
if ( $auth_sql != '' )
{
$from_sql .= ", " . FORUMS_TABLE . " f";
$where_sql .= " AND f.forum_id = p.forum_id AND $auth_sql";
}
$sql = "SELECT p.topic_id
FROM $from_sql
WHERE p.post_id IN (" . implode(", ", $search_id_chunks[$i]) . " )
$where_sql
GROUP BY p.topic_id";
}
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain topic ids', '', __LINE__, __FILE__, $sql);
}
while ($row = $db->sql_fetchrow($result))
{
$search_ids[] = $row['topic_id'];
}
$db->sql_freeresult($result);
}
$total_match_count = sizeof($search_ids);
}
else if ( $search_author != '' || $search_time || $auth_sql != '' )
{
$search_id_chunks = array();
$count = 0;
$chunk = 0;
if (count($search_ids) > $limiter)
{
for ($i = 0; $i < count($search_ids); $i++)
{
if ($count == $limiter)
{
$chunk++;
&n