php $var in mysql select returning nothing

[featherwebdiva]

I have spent half my day googling and trying to figure out what the problem is. I've tried everything.

$class_name = $_POST['class_name'];
$query = "SELECT classname_id FROM classnames WHERE classname_text ='" . $class_name . "'";

my query works if i change the class name in the database to dog, and change my script to

$query = "SELECT classname_id FROM classnames WHERE classname_text ='dog'";

there's something about the php var and my quotes.

please help!

thanks from jennifer

[mack]

Hi jennifer, welcome to WebmasterWorld :)

Are there any special characters within the database that might be interfering with the php code? A common example would be a word containing ' that can close quotes under certain circumstances.

Another thing you could do is try getting the error message from mysql. Maybe try something like the following...

$query = mysql_query ("SELECT classname_id FROM classnames WHERE classname_text ='dog' ")or die(mysql_error());

This would print the sql error on the page, and can help to debug the code.

I would also add...

echo "$class_name";

Just to be sure the variable is being set. I am sure this will sound patronizing buy is it POST and not GET. Make sure you are receiving in the correct method I have fallen into that trap a few times :)

Mack.

[featherwebdiva]

thanks mack

i have already tried the suggestions you mentioned. there i sno mysql error and

echo $class_name;
displays on screen:
Summer Theatre School - Really Beanies (4-yr-olds)

which is exactly what the class name is in my database

[mack]

Hi again, I wonder if it could be the brackets that are causing the query to fail?

You could escape them using something like the following, Its probably not the most elegant way of doing it but it will let you know if it is the issue...

$class_name = $_POST['class_name'];
$class_name = str_replace("(", "\(", $class_name);
$class_name = str_replace(")", "\)", $class_name);

Mack.

[StoutFiles]

$class_name = $_POST['class_name'];
$query = "SELECT classname_id FROM classnames WHERE classname_text ='" . $class_name . "'";

Try this:
$query = "SELECT classname_id FROM classnames WHERE classname_text ='$class_name'";

[featherwebdiva]

mack

now echo $class_name = Summer Theatre School - Really Beanies \(4-yr-olds\)

thanks stout, have tried that one (among others) a million times, doesn't work.

[StoutFiles]

You're 100% sure the field is in the database? If you aren't getting a mysql error then the error is something else.

Put all your MySQL code instead of just the query...just block out the user/pass.

[featherwebdiva]

yes i'm 100% sure, see above the dog experiment

$hostname = "localhost";
$database = "customers";
$username = "xx";
$password = "xx";
$con = mysql_connect($hostname, $username, $password);
mysql_select_db($database, $con);

$query = "SELECT classname_id FROM classnames WHERE classname_text ='".$class_name."'";

$result = mysql_query($query,$con);

if (!$result)
{
die('Error: ' . mysql_error());
}

$id = mysql_result($result,0);

[TheMadScientist]

Usually, in my experience, when it's something that's not glaring and there's no error and you've looked at it for hours, it's something goofy...

$query = "SELECT classname_id FROM classnames WHERE classname_text LIKE '%".$class_name."%'";

It's not a very efficient select, but will let you know if there's something missing or extra somewhere.

You could also try this to get rid of any extra white space that might be in the select but not the DB:
$class_name=trim($class_name);
$query = "SELECT classname_id FROM classnames WHERE classname_text = '".$class_name."'";

Also, are you using any encoding on either the insertion into the DB or the passing of the variables to the PHP script EG htmlentities(), etc. which would make the echo display properly, but not match in the select... Delete the functions I have below as necessary, but this trims the white space, decodes everything, and strips the slashes in an effort to make sure you are working with raw data, then escapes the string as necessary:

$class_name=mysql_real_escape_string(stripslashes(html_entity_decode(trim($class_name))));
$query = "SELECT classname_id FROM classnames WHERE classname_text = '".$class_name."'";

[featherwebdiva]

mad - thank you so much it works

[TheMadScientist]

Glad you got it... and Welcome to WebmasterWorld! :)

It seems we usually like finding the solutions to the challenging issues that seem to accompany coding around here... It's not nearly as much fun if it's an easy 'by the book' solution that doesn't require a degree of creativity to incorporate. LOL