MySQL queries act differently on localhost and on website

Question

User types someones username into the text box and using AJAX I automatically suggest words (usernames) to the user.

This is the AJAX code where the MySQL query is and which prints it out:

<?php

include_once('connect.php');

$search_text = $_GET['search_text'];
$safe_text = mysql_real_escape_string($search_text);

if(strlen($search_text) > 0) {

  $search_user = "SELECT * FROM accounts WHERE Name LIKE '$safe_text%' ORDER BY score DESC LIMIT 10";
  $query = mysqli_query($con, $search_user) or die(mysql_error());

  while ($row = mysqli_fetch_assoc($query)) {
    $name = $row['Name'];
    $id = $row['ID'];
    $score = $row['Score'];
    echo '<div class="well well-sm" style="margin-top:4px; margin-bottom: 0px;"><span class="glyphicon glyphicon-user" aria-hidden="true" style="margin-right: 5px;"></span><a href="/profile.php?id='.$id.'">'.$name.'</a><span class="badge" style="margin-left: 10px;">'.$score.' score</span></div><br>';
  }
}
?>

It's all working very well on my localhost. When I type in 'Joe', it gives me 10 lines of names which are like 'Joe' and which are ordered by score (Joe with the highest score on top).

The problem is, when I use it on my webserver, it just gives 10 players with the highest score. It doesent care what you enter, it chooses from ALL the players (not even related to Joe). So what is causing this? They are both using the same database, all files same etc.

I don't know if it's causing an issue but you're are interchangeably using `mysql` and `mysqli` functions. You should stick to just `mysqli` — HPierce, Oct 19 '15 at 20:02
`mysql_real_escape_string` is returning `false` maybe? Due to the issue that @HPierce states. — AbraCadaver, Oct 19 '15 at 20:03
You are absolutely right.. A real typo from me, it was meant to be mysqli. It's working great now. Thanks! — Scott James, Oct 19 '15 at 20:05
You should be testing $safe_text instead of $search_text because that is what you are using in the query. — user2182349, Oct 19 '15 at 20:06
done any basic debugging, like `echo $search_user` to compare the generated queries? — Marc B, Oct 19 '15 at 20:09
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Escaping is not enough. — Jay Blanchard, Oct 19 '15 at 20:51
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 19 '15 at 20:51
You're mixing `mysql_*` and `mysqli_*` functions. That won't work. — Jay Blanchard, Oct 19 '15 at 20:51

score 2 · Accepted Answer · answered Oct 19 '15 at 20:07

As we discussed in the comments, your code snippet shows that you are using both mysql and mysqli functions. You should pick one and stick with it. mysqli is the strongly preferred choice as mysql is deprecated.

mysql_error() -> mysqli_error()

mysql_real_escape_string($search_text) -> mysqli_real_escape_string($search_text)

score 1 · Answer 2 · answered Oct 19 '15 at 20:09

The comments are spot on, it's the function call.

mysql_real_escape_string() requires an existing or previous mysql_connect() connection, see the error notes in the manual. It looks like you're using mysqli instead.

Probably a typo but in case you're mixing them they won't mix well.

Consider also using something like PDO - mysql and mysqli extensions are both deprecated & removed come PHP7 (see the deprecation notice at the top of the page).

Edit: Referenced the correct comments made while submitting answer.

MySQL queries act differently on localhost and on website

2 Answers2