CODEWITHSUNDEEP
phpmysqldatabaseselect
Matt Brand
Matt Brand

Reputation: 346

MySQL SELECT statement using PHP GET variable

I have a PHP script that is generating a MySQL select statement:

select * from words where word = 'Classic'

There is exactly one word in the words table with the variable word equal to Classic.

When my PHP page executes, I get no results from the query. If I echo the string that is being used to execute the query, cut and paste that into the SQL window in PHPMyAdmin in the database, I also get no results. However, if I re-type that EXACT string into the SQL window in PHPMyAdmin (with the same quote characters), I get the proper result of one row.

The word Classic from the select statement is gotten from a PHP GET (see code below). I can echo the $word variable, and get the correct result of 'Classic'. What am I doing wrong?

Here is my code:

<?php

  require ('dbconnect.php');

  $word = $_GET["word"];

  $selectStr = "SELECT * FROM words WHERE word = '" . $word . "'";

  if ($results = MySQL($dbName, $selectStr))
  {
    $rowCount = MySQL_NUMROWS($results);
  }

  $resultRow = MYSQL_FETCH_ROW($results);

  $wordID = $resultRow[0];

?>

Upvotes: 2

Views: 9962

Answers (2)

Claudiu
Claudiu

Reputation: 3261

Please, please, please sanitize that word. mysql_real_escape_string() should do the trick.

$selectStr = "SELECT * FROM words WHERE word LIKE '" . $sanitized_word_i_promise . "'"; should work :)

Just to explain: "=" should work for exact matches. This includes uppercase / lowercase, spaces etc. You should probably trim that result first too, before using it in the query.

If you have foo stored in the database (note the space at the end) - it won't match foo, without a space. You'll want to use LIKE 'foo%' - probably.

Either way, Sourabh is right, although performance wise, this isn't a big hit when trying to match exact strings, you should look for the problem in other places first (such as, is the item in the database an exact match?).

Upvotes: 3

BOMEz
BOMEz

Reputation: 1010

First off you should not take any user input and directly input it into a query without sanitizing it, or using a prepared statement.

Now that we've gotten that out of the way: have you tried doing a strcmp() with the variable and your string written in? Such as

echo strcmp($_GET['word'], "Classic")

If you get a result other than 0 it means they are not the same, most likely there will be a whitespace of some sort in the $_GET variable. use trim() on it to take out whitespace. Also could be a case sensitivity issue as well.

Upvotes: 2

Related Questions