1

I have a table,

id / category / extra / another

I would like to check a row in the table for duplicate entries.

for example:

services / plumbing / residential

The above can only exist once.

But...

construction / plumbing / residential

The above can exist too.

So i have to check all three fields together in the one row?

My code wont work.

<?php 
// connect here

//

$category = mysql_real_escape_string($_POST['category']);
$extra = mysql_real_escape_string($_POST['extra']);
$another = mysql_real_escape_string($_POST['another']);

$querySTATUS = "SELECT * FROM categories WHERE category ='.$category.' AND extra ='.$extra.' AND another ='.$another'";
$resultSTATUS = mysql_query($querySTATUS) or die(mysql_error());

$CountRows = mysql_num_rows($resultSTATUS);

if($CountRows == 1){
echo "OK";
$query1 = "INSERT INTO categories (category,extra,another) VALUES ( '$category','$extra','$another')";   
$result = mysql_query($query1) or die(mysql_error());
} else {echo "!OK";}exit;?>

I have a 3input fields that post the data obviously.

Also the responses OK and !OK are there to run some inline jquery on the form page.

I just keep getting !OK response in firebug, i have tried variations of the query but to no success.

Thank you in advance if you can help :-)

John

jonnypixel
  • 327
  • 5
  • 27

1 Answers1

1

This SQL query will show you how many rows have that same set of category, extra, and another. You can filter WHERE quantity > 1 to just get the rows with duplicates.

SELECT CONCAT(category, extra, another) as catexan, COUNT(*) as quantity, category, extra, another FROM categories GROUP BY catexan ORDER BY quantity DESC;

UPDATE: I was going based on your initial description. Based on the details you provided, this should do what you're looking for:

// connect
$category = mysql_real_escape_string($_POST['category']);
$extra = mysql_real_escape_string($_POST['extra']);
$another = mysql_real_escape_string($_POST['another']);

$result = mysql_query("SELECT id FROM categories WHERE category = '$category' AND extra = '$extra' AND another = '$another';");

if (mysql_num_rows($result) > 0) {
  echo '!OK';
} else {
  mysql_query("INSERT INTO categories (category, extra, another) VALUES ('$category', '$extra', '$another');");
  echo 'OK';
}
coreyward
  • 77,547
  • 20
  • 137
  • 166
  • Hey, Thanks just trying the second part. will let you know in a sec. – jonnypixel Jan 13 '11 at 03:06
  • That worked, thank you so much coreyward. I really need to buy a current MYSQL book on queries. Its very hard to learn just from the web. Any ides on a good book? or website? – jonnypixel Jan 13 '11 at 03:10
  • Sure. Generally on StackOverflow a vote up and/or accepting a question is the best way of showing thanks, rather than adding a comment about it. – coreyward Jan 13 '11 at 03:11
  • Oh yes, cool. I was going to do that i just got side tracked. Im stuck in the flooded are over in Queensland Australia. Sorry man. – jonnypixel Jan 13 '11 at 03:14