1,105,288 Community Members

check if value already exists in MySQL

Member Avatar
ziyaddinsadigov
Light Poster
25 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
phorce
Veteran Poster
1,005 posts since Jul 2011
Reputation Points: 105 [?]
Q&As Helped to Solve: 133 [?]
Skill Endorsements: 29 [?]
Featured
 
1
 

I believe you can use LIKE...

SELECT username FROM users WHERE username LIKE 'John'
Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

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

Member Avatar
Atli
Practically a Master Poster
691 posts since May 2007
Reputation Points: 182 [?]
Q&As Helped to Solve: 107 [?]
Skill Endorsements: 16 [?]
 
0
 

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.

Member Avatar
OsaMasw
Posting Whiz in Training
212 posts since Jan 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 0 [?]
 
1
 

I think you can choose

$result = mysql_query("SELECT * FROM users WHERE username='$userid'");
if(mysql_num_rows($result) == 1)
{
echo "User exist";
}
Member Avatar
ziyaddinsadigov
Light Poster
25 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

$name="John";
$query=mysql_query("SELECT * FROM tablename WHERE 1st_name='$name'");
Member Avatar
Atli
Practically a Master Poster
691 posts since May 2007
Reputation Points: 182 [?]
Q&As Helped to Solve: 107 [?]
Skill Endorsements: 16 [?]
 
0
 

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!";
}
Member Avatar
ziyaddinsadigov
Light Poster
25 posts since Jul 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
Atli
Practically a Master Poster
691 posts since May 2007
Reputation Points: 182 [?]
Q&As Helped to Solve: 107 [?]
Skill Endorsements: 16 [?]
 
0
 

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.)

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: