Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
3189 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Problème Requête Mysql

 
 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

Problème Requête Mysql

n°1900909
axelandre
Posté le 01-07-2009 à 10:00:56  profilanswer
 

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


 
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

mood
Publicité
Posté le 01-07-2009 à 10:00:56  profilanswer
 

n°1901257
aideinfo
Posté le 01-07-2009 à 18:28:13  profilanswer
 

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

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Problème Requête Mysql

 

Sujets relatifs
[RESOLU] [PHP/MYSQL] Problème d'exécution de requete imbriqué[RÉSOLU]problème de Requète MySQL
[RESOLU] problème d'heure dans requête mysql (heure d'hiver)[C#] Problème requête MySql
[MySql]Probleme requete interval date début- date fin pour réservationProblème de requête SQL - PHP - mysql
[MySQL / PHP] problème avec une requète je sèche ![MySQL] Problème avec requête et condition IN
[MySQL] Probléme sur requete pas simple !Probleme de requete MySQL avec des controles CASE
Plus de sujets relatifs à : Problème Requête Mysql

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.129 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR