Mysql Search Problem

[Mod-Jay]

Hello , i need some help on this line.

 

Line:

                $compose_a = mysql_query("SELECT * FROM users WHERE username LIKE '%". realEscape($sendto) ."%' LIMIT 1") or die(mysql_error());

 

From the code :

 

     <?php
    if(isset($_GET['usercp']) && $_GET['send'] == 'true') {
      if($_POST['sendtitle'] == "") {
        echo "You need to have a message title.";
      } else {
        if($_POST['sendto'] == "")
        {
          echo "You must enter the username of the user you wish to send a message to.";
        } else {
          if($_POST['sendtext'] == "")
          {
            echo "You have not entered anything to send";
          } else {
                $sendto = str_replace('_', ' ' , $_POST['sendto']);
                $compose_a = mysql_query("SELECT * FROM users WHERE username LIKE '%". realEscape($sendto) ."%' LIMIT 1") or die(mysql_error());
                  if(mysql_num_rows($compose_a) >= 1) {
                    While($compose_b = mysql_fetch_array($compose_a)) {
                  $sendto = $compose_b['id'];
                        }
                        mysql_query("INSERT INTO `notifications` (ipaddress, senderid, recieverid, sent, title, text) VALUES ('". $_SERVER['REMOTE_ADDR'] ."', '". $_SESSION['id'] ."', '" . $sendto . "', NOW(), '". htmlspecialchars($_POST['sendtitle']) ."', '". htmlspecialchars($_POST['sendtext']) ."')") or die(mysql_error());
                            echo "Message has been sent.";
                            } else {
                    echo "The username you entered does not Exist";
                      }
                    }
        }
      }
    }
    ?>

 

The $sendto is a username. i want to make it so if a user puts a Uppercase letter or a lowercase letter or a space where a '_' should be. it still matchs whats in the database. if you find a better way of doing this please post it

[cgsmith105]

Depends on how you are storing it in the database and how your database is setup.

 

You can setup the database to accept case-insensitive searches using: COLLATE utf8_general_ci

 

http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html

[Mod-Jay]

Im storing it by userid as the sender. i dont know how to set it up like that

[PaulRyan]

When searching for the Username, can the username have both spaces and underscores?

 

Maybes search for the exact term first, if you cannot find it then display some possible usernames that they could have meant.

 

Try using the following, which includes some variants and some better error handling

 

<?php

  if(isSet($_GET['usercp']) && isSet($_GET['send']) && $_GET['send'] == 'true') {
    if(!$_POST['sendtitle']) {
      echo "You need to have a message title.";
    } else if(!$_POST['sendto']) {
      echo 'You must enter the username of the user you wish to send a message to.';
    } else if(!$_POST['sendtext']) {
      echo 'You have not entered anything to send.';
    } else {
      $username  = mysql_real_escape_string($_POST['sendto']);
      $sendText  = htmlspecialchars($_POST['sendtext']);
      $sendTitle = htmlspecialchars($_POST['sendtitle']);

      $userExists = mysql_query("SELECT username FROM users WHERE username='$username' LIMIT 1");
      if(mysql_num_rows($userExists)) {
        $getUser = mysql_fetch_assoc($userExists);
        $sendToID = $getUser['id'];

        $addNotification = mysql_query("INSERT INTO `notifications` (`ipaddress`,`senderid`,`recieverid`,`sent`,`title`,`text`) VALUES ('{$_SERVER['REMOTE_ADDR']}', '{$_SESSION['id']}', '$sendto', NOW(), '$sendTitle', '$sendText')") or die(mysql_error());
        if($addNotification) {
          echo 'Message has been sent.';
        } else {
          echo 'We were unable to send your message.';
        }
      } else {

        $possUsers = mysql_query("SELECT username FROM users WHERE username LIKE '%$username%' LIMIT 10");
        if(mysql_num_rows($possUsers)) {
          $message = 'We couldn't find the user you entered, maybes you meant:'."\n";
          while($user = mysql_fetch_assoc($possUsers)) {
            $message .= $user['username']."\n";
          }
        } else {
          $message = 'We could not find that user, or anyone you might have meant.';
        }

        echo $message;

      }
    }
  }
?>

 

Regards, PaulRyan.

[Mod-Jay]

Thanks, the script works flawlessly

[Mod-Jay]

Nevermind, It dont Get the username, $sendToID.

[PaulRyan]

I forgot to escape a single quote on this line:

  $message = 'We couldn't find the user you entered, maybes you meant:'."\n"; 

 

Should be:

  $message = 'We couldn\'t find the user you entered, maybes you meant:'."\n";

 

Regards, PaulRyan.