Match POST Variable to Data in Database: Simplest Script Will Not Work!

[CloudSex13]

Hi all,

 

Thanks for reading. I'm hella frustrated at this script I wrote: for some reason, it will not work correctly.

 

Basically, it works. The first 4 names in the table on the database show up when searched. But, anything past these four names in the database will not show up as a result when searched! I'm pulling my hair out here!

 

It's really simple - take a gander:

 

if (isset($_POST['submit'])) {

$search = $_POST['search'];

$searchQuery = mysql_query("SELECT * FROM Accounts WHERE FullName='$search'");

if (mysql_num_rows($searchQuery) == 0) {
$result = "Your search returned no results. Please try again.";
} else {

$results = 1;

while ($getSearchResults = mysql_fetch_array($searchQuery)) {

$fullName = $getSearchResults['FullName'];

$result = "Name: ".$fullName."";

}

}

}

?>

 

...and the HTML form...

 

<form action="search.php" method="post">

<p>Search: <input type="text" name="search" size="35" maxlength="100" /></p>
<p><input type="submit" value="Search" name="submit" /></p>

<?php echo $result; ?>

</form>

 

Does anyone have any ideas? 

[jdavidbakr]

First, sanitize your inputs.

 

Second, try echoing the statement and also echo out mysql_error() to make sure you're not getting an error.

[Rifts]

two things, can you please post what you want a result to look like and how its actually showing up

 

also what is the point of this line $results = 1; i dont see it being used

[Pikachu2000]

Yes, something doesn't match. Echo the query and paste it in to phpMyAdmin and see if any results are returned. var_dump() the $_POST['search'] var (which should be sanitized before it gose in the query string, BTW) and var_dump() the FullName field from the corresponding database record to see if they are the same. Might be a case of whitespace being somewhere it shouldn't be.

[gizmola]

While I agree that your script lacks sophistication in terms of sanitized input,etc. at the same time I don't see anything wrong with it.  Based on the information you provided, I would check that your mysql table (I'm assuming myisam format) using myisamchk).  I would also suggest you use mysql command line client or phpMyAdmin and just do a SELECT Fullname from Accounts query and take a look at the format of the data.  Make sure there aren't leading or ending spaces or any other characters in there that would account for why an exact match wouldn't work.

[CloudSex13]

@jdavidbakr Yes, I originally had them sanitized. But I limited my code to see if they were causing an issue. When I echo the $search variable, it returns the correct entered data (e.g. entered in "Bob Jones", "Bob Jones" is outputted. When I echo out the query, I get "Resource id #11". Additionally, there is no MySQL_error.

 

@Rifts I want a result to simply echo the name in a database if it's found, so simply "Bob Jones" if "Bob Jones" if found. However, "Bob Jones" is in the database and is not being echoed for some odd reason. However, the first 4 names before "Bob Jones" are being echoed properly on search. Additionally, I tried to simplify my code as I stated above, so $results = 1 was a leftover nothing. Thanks for the tip to remove.

 

@Pikachu2000 I ran the results in phpMyAdmin and they were indeed found. When running a name that displays, I received this with the var_dumps:

 

string(16) "Name: John Jones" | string(10) "John Jones"

 

When running a name that doesn't display, I get:

 

string(50) "Your search returned no results. Please try again." | string(4) "Badd"

[jdavidbakr]

I meant to echo out the actual query, i.e. "SELECT * FROM Accounts WHERE FullName='John Doe'" where you can see exactly what the query is that is not returning the results, and enter that into phpMyAdmin to see why you're not getting any results.

[CloudSex13]

@gizmola I ran the query in phpMyAdmin and all the accounts showed up. It's definitely not a whitespace error it seems. How would I check to see if it's myisamchk? It's definitely the MyISAM format.

 

Thanks to everyone for their suggestions so far.

[jdavidbakr]

You're also using "=" and not "LIKE" so the query is going to be case sensitive.  I.e, a search string of "john doe" will not return a row with "John Doe"

 

Without seeing the actual query and the row you're expecting it to return it's hard to give many more suggestions.

[CloudSex13]

Okay guys, check this out.

 

So my POST variable was named "search", right? I changed the name of it to something else (and the PHP variable) and now everything works perfectly!

 

[Pikachu2000]

Nevermind. Misread something . . .

[CloudSex13]

Is that a forbidden variable?

 

Is this a  moment?

[JakeTheSnake3.0]

$result = "Name: ".$fullName."";

If you're trying to concatenate the search results, this script is only setting the result to be the last entry retrieved.  It needs to be:

$result .= "Name: ".$fullName; // I also took out the last "" as it wasn't needed

 

 

[gizmola]

You're also using "=" and not "LIKE" so the query is going to be case sensitive.  I.e, a search string of "john doe" will not return a row with "John Doe"

 

Without seeing the actual query and the row you're expecting it to return it's hard to give many more suggestions.

 

Mysql isn't case sensitive, regardless of whether you use = or LIKE.  You actually have to use WHERE 'BINARY' to get it to look at case.

[jdavidbakr]

You're also using "=" and not "LIKE" so the query is going to be case sensitive.  I.e, a search string of "john doe" will not return a row with "John Doe"

 

Without seeing the actual query and the row you're expecting it to return it's hard to give many more suggestions.

 

Mysql isn't case sensitive, regardless of whether you use = or LIKE.  You actually have to use WHERE 'BINARY' to get it to look at case.

 

Huh, sure enough - for some reason I have always thought that.

[gizmola]

Is that a forbidden variable?

 

Is this a  moment?

 

I can't explain your results.  The name of the variable should not be relevant.  You should of course always check the input values when debugging.  If I understand you correctly,the implication is that $search = $_POST['search'] would have been empty.  I can't explain why that would be true unless the search form element had a typo.  You could have hunted this down by doing a var_dump($_POST) at the top of the script.

[gizmola]

You're also using "=" and not "LIKE" so the query is going to be case sensitive.  I.e, a search string of "john doe" will not return a row with "John Doe"

 

Without seeing the actual query and the row you're expecting it to return it's hard to give many more suggestions.

 

Mysql isn't case sensitive, regardless of whether you use = or LIKE.  You actually have to use WHERE 'BINARY' to get it to look at case.

 

Huh, sure enough - for some reason I have always thought that.

 

It's certainly true for most RDBMS, so maybe you just confused it with Oracle or SQL Server or Postgresql.  MySQL is full of these little quirks.