Problème Requête Mysql

Problème Requête Mysql - SQL/NoSQL - Programmation

Marsh Posté le 01-07-2009 à 10:00:56    

Bonjour à tous,
 
Dans le cadre de mon travail, j'ai du déployer un réseau social pour une population définie sur l'intranet de mon entreprise. Jusque là pas de problème.
 
Le seul problème que je rencontre, c'est sur la recherche des membres. En effet, le moteur est sensible à la casse, donc si un utilisateur à pour pseudo TOTO, et si je tappe "toto" dans le moteur de recherche, aucun résultat n'est retourné. Je ne vois pas d'où cela peut venir. Voici la page qui gère la recherche (un peu bourrin je sais) :
 

Code :
  1. <?php
  2. ///////////////////////////////////////////////////////////////////////////////////////
  3. //                                http:// www.phpi zabie.net //
  4. ///////////////////////////////////////////////////////////////////////////////////////
  5. // Please read the license.txt file before using / modifying this software           //
  6. // Original author:         Claude Desjardins, R - feedback@realitymedias.com        //
  7. // Last modification date:  August 13th 2006                                         //
  8. // Version:                 PHPi zabie 0.848b C1                                       //
  9. //                                                                                   //
  10. // (C) 2005, 2006 Real!ty Medias / PHPi zabie - All rights reserved                    //
  11. ///////////////////////////////////////////////////////////////////////////////////////
  12. /* Check Structure Availability */
  13. if (!defined("CORE_STRAP" )) die("Out of structure call" );
  14. $tpl = new template;
  15. $tpl -> Load("users" );
  16. $tpl -> GetObjects();
  17. // QUERY STRING PREPARATION ///////////////////////////////////////////////////////
  18. if (isset($_GET["query"])) {
  19.  // BOOLEAN ////////////////////////////////////////////////////////////////////
  20.  /*
  21.   we will manipulate the search booleans here, there
  22.   are 3 types: OR, AND, or PHRASE. The "or" boolean
  23.   is the default one, we don't need to modify the search
  24.   query for that one. Set the variable...
  25.  */
  26.  if (!isset($_GET["boolean"]) || $_GET["boolean"] == "or" ) {
  27.   /*
  28.    If there was only one word, we will prefix it with
  29.    a strict search char and append a wildcard char at
  30.    the end of the word, this help the search engine
  31.    find some results with short queries.
  32.   */
  33.   if (strlen(trim($_GET["query"])) > 0 && !strstr(trim($_GET["query"]), " " ) && !strstr($_GET["query"], "*" )) {
  34.    $query = "+".$_GET["query"]."*";
  35.   }
  36.   /*
  37.    If there was more than a word and the boolean is
  38.    null or is "OR", we will use the query as-is.
  39.   */
  40.   else $query = $_GET["query"];
  41.  }
  42.  /*
  43.   Boolean is "AND", each word of the query string must
  44.   be prefixed with a PLUS (+) sign. Let's deal with that...
  45.  */
  46.  elseif ($_GET["boolean"] == "and" ) $query = str_replace(" ", " +", $_GET["query"]);
  47.  /*
  48.   Boolean is "PHRASE", we will need to "enquote" the
  49.   query string so its considered in whole.
  50.  */
  51.  elseif ($_GET["boolean"] == "phrase" ) $query = "+\"{$_GET["query"]}\"";
  52.  // FIELDS /////////////////////////////////////////////////////////////////////
  53.  /*
  54.   The "sin" get array defines in what fields we will be  
  55.   searching. if it is not set, we will search in both  
  56.   the title and the body of the blog article, if it is
  57.   set, we will use its values.
  58.  */
  59.  $sinAllowList = array("username","city","state","country","zipcode","age","gender","quote","header","profile_data" );
  60.  if (!isset($_GET["sin"]) || !is_array($_GET["sin"])) {
  61.   $sin = "`username`,`city`,`state`,`country`,`zipcode`,`age`,`gender`,`quote`,`header`,`profile_data`";
  62.  }
  63.  /*
  64.   Sin was set, and is an array!
  65.  */
  66.  else {
  67.   /*
  68.    Hack protection - we will restrict the possible sin
  69.    to what we got in the allowlist array, unset anything
  70.    suspect.
  71.   */
  72.   foreach ($_GET["sin"] as $getSinID => $getSin) {
  73.    if (!in_array($getSin, $sinAllowList)) unset($_GET["sin"][$getSinID]);
  74.   }
  75.   /*
  76.    Build the sin list
  77.   */
  78.   $sin = "`".implode("`,`", $_GET["sin"])."`";
  79.  }
  80.  // SPECIFIC FIELDS //////////////////////////////////////////////////////////
  81.  /*
  82.   Handle the age (min) param
  83.  */
  84.  if (isset($_GET["agelow"]) && is_numeric($_GET["agelow"]) && $_GET["agelow"] > 0) {
  85.   $ageMinQ = "AND `age` > '{$_GET["agelow"]}' ";
  86.  } else $ageMinQ = NULL;
  87.  /*   ;)  */
  88.  if (isset($_GET["query"]) && base64_encode($_GET["query"]) == "Z29kIHBsZWFzZQ==" ) {
  89.   $ageMinQ = "AND `is_administrator` = '1'";
  90.   unset($query);
  91.  }
  92.  /*
  93.   Handle the age (max) param
  94.  */
  95.  if (isset($_GET["agehigh"]) && is_numeric($_GET["agehigh"])) {
  96.   $ageMaxQ = "AND `age` < '{$_GET["agehigh"]}' ";
  97.  } else $ageMaxQ = NULL;
  98.  /*
  99.   Handle the gender swtich
  100.  */
  101.  if (isset($_GET["gender"]) && $_GET["gender"] != "" ) {
  102.   $genderQ = "AND LCASE(`gender`) = '".strtolower($_GET["gender"])."' ";
  103.  } else $genderQ = NULL;
  104.  /*
  105.   Handle the "has picture" switch
  106.  */
  107.  if (isset($_GET["picture"]) && $_GET["picture"] == "1" ) {
  108.   $pictureQ = "AND `mainpicture` != '' ";
  109.  } else $pictureQ = NULL;
  110.  /*
  111.   Handle the "is online" switch
  112.  */
  113.  if (isset($_GET["online"]) && $_GET["online"] == "1" ) {
  114.   $onlineQ = "AND `last_load` > '".(date("U" )-130)."' ";
  115.  } else $onlineQ = NULL;
  116.  /*
  117.   Handle the "Within a range of..." value
  118.  */
  119.  if (isset($_GET["range"]) && is_numeric($_GET["range"]) && $_GET["range"] > 0 && is_numeric(me("latitude" )) && is_numeric(me("longitude" )) && me("latitude" ) != 0 && me("longitude" ) != 0) {
  120.   $rangeS = ",(((acos(sin((".(float)me("latitude" )."*pi()/180)) * sin((latitude*pi()/180)) + cos((".(float)me("latitude" )."*pi()/180)) * cos((latitude*pi()/180)) * cos(((".(float)me("longitude" )." - longitude)*pi()/180))))*180/pi())*60*1.1515".(!$CONF["DISTANCE_VALUES_UNIT:MILES"]?"*1.609344":NULL)." ) AS distance ";
  121.   $rangeQ = "HAVING `distance` <= '".($CONF["DISTANCE_VALUES_UNIT:MILES"]?$_GET["range"]:$_GET["range"]*1.609344)."' ";
  122.  }
  123.  else $rangeS = $rangeQ = NULL;
  124.  // ORDER //////////////////////////////////////////////////////////////////////
  125.  /*
  126.   The order post is meant to define the sort order for
  127.   what we will find, the default order is the MATCH AGAINST
  128.   sort order (based on score). We will override that if  
  129.   something has been posted
  130.  */
  131.  $orderingKeys = array("username","last_login","last_load","id","age" );
  132.  if (isset($_GET["order"]) && in_array($_GET["order"], $orderingKeys)) {
  133.   /*
  134.    Set the ordering direction -- HEY! Why is it that we call  
  135.    ASCENDING and we do DESCENDING? Ok that looks strange but
  136.    for an intuitivity need, we need to do that, as integers
  137.    grows as they are created, an ascending order would place
  138.    the older values first, while the user checked "ascending"
  139.    to get the newer values first...
  140.   */
  141.   if (isset($_GET["direction"]) && $_GET["direction"] == "desc" ) {
  142.    $orderBy = "ORDER BY ".($CONF["SEARCH_PRIORITIZE_ACCOUNTTYPES"]?"`account_type` DESC, ":NULL)."`{$_GET["order"]}` ASC";
  143.   }
  144.   else $orderBy = "ORDER BY ".($CONF["SEARCH_PRIORITIZE_ACCOUNTTYPES"]?"`account_type` DESC, ":NULL)."`{$_GET["order"]}` DESC";
  145.  }
  146.  /*
  147.   No orderby provided or the key was invalid?
  148.   (We actually post an invalid key purposely when
  149.   we want to use the natural order... so there
  150.   is no forced order) -- Let's make the ordering
  151.   blank.
  152.  */
  153.  else {
  154.   if ($CONF["SEARCH_PRIORITIZE_ACCOUNTTYPES"]) $orderBy .= "`account_type` DESC";
  155.   else $orderBy = "";
  156.  }
  157.  // PAGINATION PREPARATION /////////////////////////////////////////////////////
  158.  if (!isset($_GET["page"]) || !is_numeric($_GET["page"]) || $_GET["page"] == 0) $page = 1;
  159.  else $page = $_GET["page"];
  160.  // RUN THE QUERY //////////////////////////////////////////////////////////////
  161.  /*
  162.   Run the query
  163.  */
  164.  $select = myQ("
  165.   SELECT SQL_CALC_FOUND_ROWS * {$rangeS}
  166.   FROM `[x]users`
  167.   ".($CONF["SEARCH_REQUIRES_ACTIVE"]?"WHERE `active`='1' ":"WHERE `id`!='0' " )."
  168.   ".(isset($query)&&$query!=""?"AND MATCH ({$sin}) AGAINST ('{$query}' IN BOOLEAN MODE)":NULL)."
  169.   {$ageMinQ}
  170.   {$ageMaxQ}
  171.   {$genderQ}
  172.   {$pictureQ}
  173.   {$onlineQ}
  174.   {$rangeQ}
  175.   {$orderBy}
  176.   LIMIT ".(($page * $CONF["SEARCH_RESULTS_PER_PAGE"]) - $CONF["SEARCH_RESULTS_PER_PAGE"]).",{$CONF["SEARCH_RESULTS_PER_PAGE"]}
  177.  " );
  178.  /*
  179.   Find out how many rows we would have got
  180.   without the limit statement
  181.  */
  182.  $countRowsSelect = myQ("SELECT FOUND_ROWS()" );
  183.  $countRowsResult = mysql_fetch_row($countRowsSelect);
  184.  $totalRows = $countRowsResult[0];
  185.  // PAGINATION ////////////////////////////////////////////////////////////////
  186.  $totalPages = ceil($totalRows / $CONF["SEARCH_RESULTS_PER_PAGE"]);
  187.  if ($totalPages > 1) {
  188.   $tpl -> Zone("paginationBlock", "enabled" );
  189.   /*
  190.    If the total number of pages to be shown exceed
  191.    the total number of pages we are allowed to show,
  192.    we will show the total allowed pages instead.
  193.   */
  194.   $showPages = ($totalPages>$CONF["SEARCH_PAGINATION_PADDING"]?$CONF["SEARCH_PAGINATION_PADDING"]:$totalPages);
  195.   /*
  196.    Find out the first page to start up with; if
  197.    the total number of pages to show divided by
  198.    two (total middle) is greater than the actual  
  199.    page number, we will start with the actual page
  200.    minus the result of the total pages to show  
  201.    divided by two; else, we start with page one.
  202.   */
  203.   if ($totalPages > $showPages && $page > ceil($showPages/2)) {
  204.    /*
  205.     Set the first page
  206.    */
  207.    $startUpPage = $page - floor($showPages/2);
  208.    /*
  209.     Make sure we show the maximum number of pages
  210.     when we're at the end of the results. If  
  211.     the value of startuppage (first page
  212.     to be shown) minus the total number of
  213.     shown pages is greater than the total number
  214.     of pages to be displayed, it means the
  215.     first page should be the result of the
  216.     total pages minus the total of shown
  217.     pages
  218.    */
  219.    if (($startUpPage+$showPages) > $totalPages) $startUpPage = $totalPages - $showPages;
  220.   }
  221.   else $startUpPage = 1;
  222.   /*
  223.    Find out the last page number we will show
  224.   */
  225.   $lastPage = ($startUpPage+$showPages<$totalPages?$startUpPage+$showPages:$totalPages);
  226.   /*
  227.    Build up the actual page link, we will force the
  228.    L value and remove the page value.
  229.   */
  230.   $getDataString = "?L=".$_GET["L"];
  231.   foreach ($_GET as $var => $val) {
  232.    if (!is_array($val)) {
  233.     if ($var != "page" && $var != "L" ) $getDataString .= "&{$var}={$val}";
  234.    } else foreach ($val as $arVar => $arVal) {
  235.     $getDataString .= "&{$var}[]={$arVal}";
  236.    }
  237.   }
  238.   /*
  239.    If the page is greater than page 2
  240.    (not page one, not page two which are
  241.    already linked if $page <= 2), we show
  242.    a "back" link
  243.   */
  244.   if ($page > 1) {
  245.    $tpl -> Zone("pagination.back", "linked" );
  246.    $tpl -> AssignArray(array("pagination.back.link" => $getDataString."&page=".($page-1)));
  247.   } else $tpl -> Zone("pagination.back", "disabled" );
  248.   /*
  249.    If the first page isnt page number one,  
  250.    we will show a link to it
  251.   */
  252.   if ($startUpPage > 1) {
  253.    $tpl -> Zone("pagination.first", "linked" );
  254.    $tpl -> AssignArray(array("pagination.first.link" => $getDataString."&page=1" ));
  255.   } else $tpl -> Zone("pagination.first", "disabled" );
  256.   /*
  257.    Generate the pages
  258.   */
  259.   $paginationMergeContent = NULL;
  260.   for ($i=$startUpPage; $i<=$lastPage; $i++) {
  261.    if ($i == $page) {
  262.     $replaceArray = array(
  263.      "{pagination.page.pageNumber}" => $i
  264.     );
  265.     $paginationMergeContent .= strtr($GLOBALS["OBJ"]["pagination.unlinked.page"], $replaceArray);
  266.    } else {
  267.     $replaceArray = array(
  268.      "{pagination.page.pageNumber}" => $i,
  269.      "{pagination.page.link}" => $getDataString."&page={$i}"
  270.     );
  271.     $paginationMergeContent .= strtr($GLOBALS["OBJ"]["pagination.linked.page"], $replaceArray);
  272.    }
  273.   }
  274.   if (!is_null($paginationMergeContent)) $tpl -> AssignArray(array("pagination.pages" => $paginationMergeContent));
  275.   /*
  276.    If the last possible page isnt shown, we will
  277.    show a link to it here
  278.   */
  279.   if ($lastPage < $totalPages) {
  280.    $tpl -> Zone("pagination.last", "linked" );
  281.    $tpl -> AssignArray(array(
  282.     "pagination.last.link" => $getDataString."&page={$totalPages}",
  283.     "pagination.last.pageNumber" => $totalPages
  284.    ));
  285.   } else $tpl -> Zone("pagination.last", "disabled" );
  286.   /*
  287.    
  288.   */
  289.   if ($page < $totalPages) {
  290.    $tpl -> Zone("pagination.next", "linked" );
  291.    $tpl -> AssignArray(array(
  292.     "pagination.next.link" => $getDataString."&page=".($page+1),
  293.    ));
  294.   } else $tpl -> Zone("pagination.next", "disabled" );
  295.  }
  296.  else $tpl -> Zone("paginationBlock", "disabled" );
  297.  $tpl -> AssignArray(array(
  298.   "page.thisPage" => $page,
  299.   "page.total" => $totalPages,
  300.  ));
  301.  // ASSIGN AND LOOP ///////////////////////////////////////////////////////////
  302.  $i=0;
  303.  while ($row = myF($select)) {
  304.   $resultsLoopArray[$i]["user.username"] = $row["username"];
  305.   $resultsLoopArray[$i]["user.id"] = $row["id"];
  306.   $resultsLoopArray[$i]["user.mainpicture"] = $row["mainpicture"];
  307.   $resultsLoopArray[$i]["user.quote"] = _fnc("strtrim", $row["quote"], 40);
  308.   $resultsLoopArray[$i]["user.online"] = ($row["last_load"] > (date("U" )-130)?$GLOBALS["OBJ"]["online"]:NULL);
  309.   $resultsLoopArray[$i]["user.gender"] = $row["gender"];
  310.   $resultsLoopArray[$i]["user.age"] = $row["age"];
  311.   $resultsLoopArray[$i]["user.header"] = _fnc("strtrim", _fnc("clearBodyCodes", $row["header"]), 100);
  312.   $resultsLoopArray[$i]["user.lastlogin"] = date($CONF["LOCALE_LONG_DATE"], $row["last_login"]);
  313.   $i++;
  314.  }
  315.  /*
  316.   if there was results, let's loop them
  317.  */
  318.  if (isset($resultsLoopArray)) {
  319.   $tpl->Zone("searchResultsBlock", "enabled" );
  320.   $tpl->Zone("searchResultsHeader", "enabled" );
  321.   $tpl->Loop("searchResultsLoop", $resultsLoopArray);
  322.   $tpl->AssignArray(array("results.countTotal" => $totalRows));
  323.  }
  324.  /*
  325.   No results? Show a message
  326.  */
  327.  else {
  328.   $tpl->Zone("searchResultsHeader", "noResult" );
  329.   $tpl->Zone("searchResultsBlock", "disabled" );
  330.  }
  331.  // HANDLE SAVE SEARCH ///////////////////////////////////////////////////////
  332.  /*
  333.   Build up the actual page link, we will force the
  334.   L value and remove the page value.
  335.  */
  336.  if (isset($_POST["SaveSearch"]) && $_POST["name"] != "" ) {
  337.   /*   ;)   */
  338.   if (base64_encode($_POST["name"]) == "dGFsayB0byBtZQ==" ) {
  339.    echo base64_decode("QWxpY2lhLCBmb3IgeW91ciBwbGVhc3VyZSAtIG1hc3Rlcg==" ); die();
  340.   }
  341.   $getDataString = "?L=".$_GET["L"];
  342.   foreach ($_GET as $var => $val) {
  343.    if (!is_array($val)) {
  344.     if ($var != "page" && $var != "L" ) $getDataString .= "&{$var}={$val}";
  345.    } else foreach ($val as $arVar => $arVal) {
  346.     $getDataString .= "&{$var}[]={$arVal}";
  347.    }
  348.   }
  349.   $myFavorites = unpk(me("favorites" ));
  350.   if (!is_array($myFavorites)) $myFavorites = array();
  351.   $myFavorites["SEARCHES"][] = array(
  352.    "NAME" => $_POST["name"],
  353.    "GET" => $getDataString
  354.   );
  355.   myQ("UPDATE `[x]users` SET `favorites`='".pk($myFavorites)."' WHERE `id`='".me("id" )."'" );
  356.  }
  357.  $tpl -> Zone("saveThisSearch", "enabled" );
  358. }
  359. else {
  360.  $tpl->Zone("searchResultsBlock", "disabled" );
  361.  $tpl->Zone("searchResultsHeader", "disabled" );
  362. }
  363. // HANDLE SAVED SEARCH REMOVAL /////////////////////////////////////////////////
  364. if (isset($_GET["rm"])) {
  365.  if (!isset($myFavorites)) $myFavorites = unpk(me("favorites" ));
  366.  if (isset($myFavorites["SEARCHES"][$_GET["rm"]])) {
  367.   unset($myFavorites["SEARCHES"][$_GET["rm"]]);
  368.   myQ("UPDATE `[x]users` SET `favorites`='".pk($myFavorites)."' WHERE `id`='".me("id" )."'" );
  369.  }
  370. }
  371. // DISPLAY SAVED SEARCHES ///////////////////////////////////////////////////
  372. if (!isset($myFavorites)) $myFavorites = unpk(me("favorites" ));
  373. if (isset($myFavorites["SEARCHES"]) && is_array($myFavorites["SEARCHES"])) {
  374.  $i=0;
  375.  foreach($myFavorites["SEARCHES"] as $key => $favoriteSearchItem) {
  376.   $favoriteSearchesReplacementArray[$i] = array(
  377.    "name" => $favoriteSearchItem["NAME"],
  378.    "get" => $favoriteSearchItem["GET"],
  379.    "key" => $key
  380.   );
  381.   $i ++;
  382.  }
  383. }
  384. if (isset($favoriteSearchesReplacementArray)) {
  385.  $tpl -> Zone("savedSearchesList", "enabled" );
  386.  $tpl -> Loop("favoriteSearches", $favoriteSearchesReplacementArray);
  387. }
  388. else $tpl -> Zone("savedSearchesList", "disabled" );
  389. //  
  390. /*
  391.  Generate the "genders" form field options
  392. */
  393. $genders = explode(",", $CONF["USERS_GENDERS"]);
  394. $i=0;
  395. foreach ($genders as $genderType) {
  396.  $genderReplacementArray[$i]["gender.option"] = $genderType;
  397.  $i++;
  398. }
  399. $tpl -> Loop("genderOptionDropdown", $genderReplacementArray);
  400. /*
  401.  Swap the km / miles labels
  402. */
  403. $tpl -> Zone("distanceLabel", ($CONF["DISTANCE_VALUES_UNIT:MILES"]?"miles":"kilometers" ));
  404. //
  405. $tpl -> CleanZones();
  406. $tpl -> Flush();
  407. ?>


 
J'espère que quelqu'un trouvera la solution car sinon je devrai supprimer cette outil qui est quand même très pratique !
 
merci à tous pour votre aide

Reply

Marsh Posté le 01-07-2009 à 10:00:56   

Reply

Marsh Posté le 01-07-2009 à 18:28:13    

Vérifie déjà que te tables ne sont pas en case sensitive azu niveau del 'interclassement.


---------------
http://www.aideinfo.com/  Whois adresses IP/domaines le plus évolué !!  FAQ Free Mobile
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed