CODEWITHSUNDEEP
phpmysql
Code
Code

Reputation: 303

Detecting duplicate values

I have a table, it's structure is,

companies - table
id
name
url
logo

From this table i want to check whether name already exists, url exists, logo exists before inserting. name, url and logo should be unique. I can check like,

SELECT * FROM companies WHERE name='$name' OR url='$url' OR logo='$logo'

and count the number of records. From the above query, is there anyway i can detect which field is duplicate? So, that i can display that field as duplicate in showing errors in views.

Thanks

Upvotes: 3

Views: 73

Answers (3)

Martin
Martin

Reputation: 6687

You can create a unique key which will cause your insert to fail - this avoids needing two queries:

ALTER TABLE `companies` ADD UNIQUE (
 `url`, 
 `name`,
 `logo` 
);

Upvotes: 2

Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17576

this is your select query.

SELECT name,url,logo FROM companies WHERE name='$name' OR url='$url' OR logo='$logo';

then loop the results

$num_of_records=0;
while($row = mysqli_fetch_array($result))
{

if($name==$row['name'])
{
  echo  . " name -> " . $row['name'] ." duplicated ";
  echo "<br>";
}
else if($url==$row['url']) 
{
  echo  . " url -> " . $row['url'] ." duplicated ";
  echo "<br>";
}
else if($logo==$row['logo'])
{
  echo  . " logo -> " . $row['logo'] ." duplicated ";
  echo "<br>";
}
 $num_of_records++;
}

echo "number of records -> ".$num_of_records;

Upvotes: 0

Rahul
Rahul

Reputation: 1181

yes you can.. as you must have placed 'id' as the primary key for the table.

you can do certain things as adding unique key with logo and then indexing name , url, logo as unique.

and try this

SELECT `id` FROM companies WHERE name='$name' AND url='$url' AND logo='$logo'

and show error for duplication if you encounter any such value.

Upvotes: 0

Related Questions