PHP MySQL query returning NULL

Question

I have a MySQL table that looks like this:

index  |  tag  |  posts
-------------------------
  1    | cats  |   9,10
  2    | a cat |   9,10
  3    | kitty |   9,10
  4    | meow  |   9,10

I am trying to just return the row that matches a search query. I passed the search parameter using a simple ?search=cats. This is the PHP that I'm using:

$search = $_GET['search'];
$query  = mysql_query("SELECT * FROM tags WHERE tag = '$search'");
echo(mysql_num_rows($query));
$result = mysql_fetch_array($query);
$print  = $result['posts'];
echo($print);

However the mysql_num_rows($query) prints 0 and the $print returns NULL. I can check it with ($print == ""), it evaluates to TRUE and mysql_num_rows($query) returns 4. I tried setting the search query to something that wasn't in the table and it retuned FALSE as expected. I also tried removing the WHERE tag = '$search' and it returns the table like it should.

Is there something I'm overlooking?

Edit

Took everyone's advice and the code I'm using now is:

$search = mysql_real_escape_string($_GET['search']);
var_dump($search); //prints   string(4) "cats"   just like it should
$queryText = "SELECT * FROM tags WHERE tag = '%".$search."%'";
echo($queryText); //SELECT * FROM tags WHERE tag = '%cats%'
$query  = mysql_query($queryText) or die(mysql_error()); //no error
$rows   = mysql_num_rows($query); //this returns 0 and I know it should match 1 row
echo('rows: '.$rows);
$result = mysql_fetch_array($query);
$print  = $result['posts'];
echo($print); //empty

Still have the same problem. The mysql_query is retuning NULL instead of the row or FALSE if it doesn't match.

(in the future I will use the mysqli API, but I would like to finnish this project in mysql. thanks for your suggestions and advice)

2 Things: 1. echo after $search = $_GET['search']; that indeed the search word was filled. 2. change the query to $query = mysql_query("SELECT * FROM tags WHERE tag = '$search'") or die(mysql_error()); and tell us what is the output. — Orel Eraki
– Orel Eraki, Commented Feb 1, 2013 at 10:29
Welcome to Stack Overflow! Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Touki
– Touki, Commented Feb 1, 2013 at 10:30
Make sure you know EXACTLY what's being sent to the MySQL server. $queryText = "SELECT * FROM tags WHERE tag = '$search'"; print($queryText); Also try putting or die(mysql_error); on the same line as the mysql_query(). — EM-Creations
– EM-Creations, Commented Feb 1, 2013 at 10:30
@OrelEraki I checked $_GET['search'] and it indeed was what I wanted it to be. I also added or die(mysql_error()), but no errors were returned. thanks for the help. — thunder618
– thunder618, Commented Feb 3, 2013 at 1:41

Ludo237 · Accepted Answer · 2013-02-01 10:30:07Z

1

Try this code now.

Remeber when you want to debug something in PHP the faster way is var_dump not echo. Also you should avoid mysql_api because they are deprecated, use PDO instead PDO on PHP.net

var_dump($_GET); // Just for debuggin if as something

$search = $_GET['search'];
$query  = mysql_query("SELECT * FROM tags WHERE tag = '".mysql_real_escape_string($search)."'");
// echo(mysql_num_rows($query));
$result = mysql_fetch_array($query);
var_dump($result);
//$print  = $result['posts'];
//echo($print);

answered Feb 1, 2013 at 10:30

Ludo237

1,75729 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

EM-Creations Over a year ago

I fail to see how that's going to solve the problem. It adds more security, but.. it's certainly not the answer.

Ludo237 Over a year ago

He should know if $_GET has something first... That's the purpose.

EM-Creations Over a year ago

It's not the answer, you should have added that as a comment. Not an answer.

Ludo237 Over a year ago

Every others answer are equals.. just change some style and the code.

EM-Creations Over a year ago

What an earth are you talking about?

|

Jaspal Singh · Accepted Answer · 2013-02-03 08:28:19Z

0

Ok so after referring to the above edit you made, here is the solution

Use "LIKE" instead of "=" when using wildcard "%"

So your query now should be

$queryText = "SELECT * FROM tags WHERE tag LIKE '%" . $search . "%'";

[I created the exact same db on my local system and ran the same code you gave, After making the above changes, It runs as expected]

edited Feb 3, 2013 at 8:28

answered Feb 1, 2013 at 10:37

Jaspal Singh

1,2401 gold badge12 silver badges17 bronze badges

2 Comments

thunder618 Over a year ago

According to the php manual ( link ) $_GET is already decoded and I shouldn't use the urldecode() function on it. Should I use it or not?

thunder618 Over a year ago

I must be doing something wrong then. I'm going to switch over to mysqli. Thanks for your help!

Suhel Meman · Accepted Answer · 2013-02-01 10:32:54Z

0

$search = $_GET['search'];
echo $select_query="SELECT * FROM tags WHERE tag = '".mysql_real_escape_string($search)."'";
$query  = mysql_query($select_query);
echo(mysql_num_rows($query));
while($result = mysql_fetch_array($query))
{
  print_r($result);
}

answered Feb 1, 2013 at 10:32

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

Comments

Adrian Maire · Accepted Answer · 2013-02-01 10:36:52Z

0

Note:

$search = $_GET['search'];
$query  = mysql_query("SELECT * FROM tags WHERE tag = '$search'");

That is very dangerouse: It allow sql incersion code to your database. You must always escape all what you get from the client.

$search = mysql_real_escape_string($_GET['search']); //It require open database connection.

Note2: mysql_query is obsolete, use mysqli instead ;-)

Answer:

If you have not answer, you probable has an error in an other part. Try

//1) Look if your search has a correct value
var_dump($search);
//2) Replace the query with (just for debugging):
$query  = mysql_query("SELECT * FROM tags WHERE tag = 'cats';");

You may also use "tag like '%cats%'" if you want a more flexible search.

answered Feb 1, 2013 at 10:36

Adrian Maire

15k10 gold badges54 silver badges100 bronze badges

1 Comment

thunder618 Over a year ago

Thanks. I added the '%cats%' and mysql_real_escape_string().

Harrie · Accepted Answer · 2013-02-01 10:43:32Z

0

If you remove the WHERE tage = '$search', it cannot return the table like it should because your mysql_fetch_array is not in a while loop... but that aside...

// make sure before you execute the code to check that $_GET['search'] is not empty

// start with escaping the search-value (for mysql-injection)
$search = msyql_real_escape_string($_GET['search']);

// changed the query so it searches for tags containing the search value.
// if you would have records with tags "blue cat" and "red cat" it shows them both
// when searching for "cat"
$query  = mysql_query("SELECT * FROM tags WHERE tag LIKE '%".$search."%'");

// put the number of rows in a var
$num = mysql_num_rows($query);

// check this var if it's not 0
if ($num != '0'){
    while ($row = mysql_fetch_array($query){
        echo $row['posts'];
        // etc...
    }
} else {
    // 0 rows found
    echo "nothing found";
}

answered Feb 1, 2013 at 10:43

Harrie

691 silver badge9 bronze badges

1 Comment

thunder618 Over a year ago

Yeah, when I removed the WHERE tage = '$search' I used while($result[] = mysql_fetch_array($query)) to verify it. Thanks for the '%".$var."%' tip!

Collectives™ on Stack Overflow

PHP MySQL query returning NULL

5 Answers 5

7 Comments

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related