check if value already exists in MySQL

I want to check if value is already exists in database with php. But this value can be as for example "John" or "JOHN" or "JOhn" or ... e.t.c
how to check for all conditions?

[phorce]

I believe you can use LIKE...

SELECT username FROM users WHERE username LIKE 'John'

[pritaeas]

If your database collation is set to case insensitive you can simply use WHERE username = 'John'

[Atli]

You can also override the set collation on a field for each comparison. So, for example, if you have a field using the case-sensitive latin1_general_cs collation, but want to compare the fields in a case-insensitive manner, you could do:

SELECT stuff FROM theTable
WHERE textField COLLATE latin1_general_ci = 'John'

Note the end of the collate name. If it's "ci" then it's Case-Insensitive. If it's "cs" then it's Case-Sensitive.

[OsaMasw]

I think you can choose

$result = mysql_query("SELECT * FROM users WHERE username='$userid'");
if(mysql_num_rows($result) == 1)
{
echo "User exist";
}

thanks, very much! I also tested with like that:

$name="John";
$query=mysql_query("SELECT * FROM tablename WHERE 1st_name='$name'");

[Atli]

Why would you select ALL the data in the row if you're not going to use any of it? It's just a waste of resources, really. MySQL can COUNT() the rows for you and just return that.

$sql = "SELECT COUNT(*) FROM users WHERE username='$userid'";
$result = mysql_query($sql) or trigger_error(mysql_error(), E_USER_ERROR);
$row = mysql_fetch_row($sql);
if ($row[0] > 0) {
    echo "User exist!";
}

ok. And how to get data from mysql database? In database I want to write many words in one row with commas, like an array. And then I want to fetch it like an array and check if my variable is already exists inside this array. How can I do it?

[Atli]

Depends on the data. In generally you don't save multiple values in a single field. If you have a list of values you need to save, you put them in a separate table, one item per row.

For example, if you had a list of items and a list of "tags" for each item, you would set that up somewhat like this:

+---------+     +------------------+ 
| items   |     | item_tags        |
+---------+     +------------------+
| id (PK) |>--->| item_id (PK, FK) |
| name    |     | tag (PK)         |
+---------+     +------------------+

The item_tags table would store the tags for each item, and link each tag to it's item via the item_id Foreign Key field. Here is an example of what the data would look like:

> SELECT * FROM items;
+----+---------+
| id | name    |
+----+---------+
|  1 | Item 1  |
|  2 | Item 2  |
+----+---------+

> SELECT * FROM item_tags;
+---------+------+
| item_id | tag  |
+---------+------+
|       1 | tag1 |
|       1 | tag2 |
|       2 | tag1 |
|       2 | tag3 |
+---------+------+

There are many ways in which you can fetch the tags for each item, depending on how you are using it in your code, but if you wanted to return each item with a comma separated list of tags, MySQL's GROUP_CONCAT function can be used. Like:

> SELECT i.id, i.name, GROUP_CONCAT(t.tag) AS tags
  FROM items AS i
  JOIN item_tags as t
      ON i.id = t.item_id
  GROUP BY i.id;
+----+--------+-----------+
| id | name   | tags      |
+----+--------+-----------+
|  1 | Item 1 | tag1,tag2 |
|  2 | Item 2 | tag1,tag3 |
+----+--------+-----------+

The tags field in that result set could then be exploded in PHP to get them as arrays. Of course, this is not the most efficient way to fetch tags for a single item, but it can be useful if you don't overuse it. (String manipulation is relatively expensive.)