Is it possible using mysql and php to check if the values are already inserted inside a ROW instead of COLUMN?
for example:
id name surname
1 jonh smith
is it possible to check if both john and smith are already registered inside the same row?
Yes it is. You can just use multiple where clause like this :
SELECT * FROM `users` WHERE `name` = "john" and `surname` = "smith";
For more complex possibilities, read the select documentation.
SELECT 1 FROM 'MYTABLE' WHERE 'NAME' = 'JHON' AND 'SURNAME' = 'SMITH'
You can use GROUP_CONCAT to join all cells in a row:
SELECT * FROM TableA
WHERE FIND_IN_SET('john', GROUP_CONCAT(Id, Name , SurName separator ',')) > 0
AND FIND_IN_SET('smith', GROUP_CONCAT(Id, Name , SurName separator ',')) > 0;
This will not care which column appeared the name JOHN and SMITH
You can check the count of the rows
select count(id) from table
also you can check if the data has been inserted. reference
$result = mysql_query("INSERT INTO PEOPLE (NAME ) VALUES ('COLE')")); if($result) { echo "Success";
} else { echo "Error";
}
please try this. in this query i added lcase in name and check both side so u can find which one inserted in upper and lower latter words.
$qry="SELECT * FROM Table WHERE LCASE(name) = lcase('john') and LCASE(surname) = lcase('smith')";
$run=mysql_query($qry) or die(mysql_error());
if(mysql_num_rows($run)>0)
{
echo "already registered";
}
You can check with IN clause
SELECT * FROM `MYTABLE` WHERE (`NAME`, `SURNAME`) IN (('jonh', 'smith'));
By using this code:
$id=$_POST['id'];
$name=$_POST['name'];
$surname=$_POST['surname'];
$result = mysql_query("SELECT id,name,surname FROM tablename WHERE id='".$id."'name='".$name."' and surname='".$surname."'");
$row = mysql_fetch_array($result);
$cnt = mysql_num_rows($result);
if($cnt == 0)
{
enter code here
(Statement);
}
else
{
(Statement);
}
This sounds like you want to insert a record, but want to check if it exists first.
If you don't care if the insert fails or not (ie, you just want to do the insert and know a value has been inserted) then you could set up a unique index on name and surname.
To do the insert you could then just use INSERT IGNORE blah . Thus if there already is that combination of name and surname the INSERT will silently fail:-
INSERT IGNORE INTO sometable(id, name, surname)
VALUES(NULL, '$name', '$surname')
If you need to know the id of the record
INSERT IGNORE INTO sometable(id, name, surname)
VALUES(NULL, '$name', '$surname')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
This will return the id of the new or existing row (as appropriate) in the normal mysql functions to return the inserted row id.