CODEWITHSUNDEEP
phpmysqlstring-comparisonstrcmp
EscalatedQuickly
EscalatedQuickly

Reputation: 402

Comparing string in SQL database with "normal" string

I'm trying to make a form with a drop down menu. The values of the form are then stored in a database. I want to be able to change the values of the form later, and doing so by going back to the form, that's filled with the values entered in the data base.

Everything seems to be working, except for the drop down menu. As I want all fields to be filled with the correct values, this goes for the menu as well. So, I tried using string comparison in if-else clauses to determine which option to set as selected. But the string comparison doesn't work. Here's a code example:

$query = mysql_query(SELECT * FROM data);
$result = mysql_fetch_assoc($query);

if(strcmp($result['pet'],"Dog")==0)
{
    echo "
        <option value='Dog' selected>Dog</option>
        <option value='Cat'>Cat</option>
        <option value='Bird'>Bird</option>
    ";
}
elseif(strcmp($result['pet'],"Cat")==0)
{
    echo "
        <option value='Dog'>Dog</option>
        <option value='Cat' selected>Cat</option>
        <option value='Bird'>Bird</option>
    ";
}

I also tried:

if(result['pet']=="Dog")
{
    .....
}
elseif(result['pet']=="Cat")
{
    .....
}

and

if(strcmp(trim(reuslt['pet']),trim("Dog"))==0)
{
    .....
}
elseif(strcmp(trim(result['pet']),trim("Cat"))==0)
{
    .....
}

But nothing works. I suspect it has to do with the collation, so I tried changing it when I select the values:

$query = mysql_query("SELECT * COLLATE latin_1 FROM data");
$result = mysql_fetch_assoc($query);

This gives me the error message "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/PHP/control.php on line 12".

So, what's wrong, and how do I fix it?

EDIT: I have now changed my query a bit:

$query = mysql_query("SELECT * FROM data WHERE user='$user' COLLATE uft8_general_ci") or die(mysql_error());

From this I get the following error:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

and when I change the collation to latin1_general_ci I the the following:

Illegal mix of collations (utf8_swedish_ci,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation '='

The collation that I'm using for the column in the data base is utf8_swedish_ci, if that helps.

Upvotes: 0

Views: 2755

Answers (1)

Manse
Manse

Reputation: 38147

You are getting this error because mysql_query returns a boolean (false) when the query fails, do the following for a better explanation of the problem 

$result = mysql_query('SELECT * COLLATION latin_1 FROM data');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

The problem is that your query isnt valid - you should use COLLATE not COLLATION ... see the following for a good explanation on how COLLATE works - essentially you use COLLATE against a single column not a group (*)

Upvotes: 1

Related Questions