check 2 rows on 1 field, make sense?

[duezy]

$tname = mysql_real_escape_string(trim($_POST['tname']), $dbc) ; //result from form(tname)
	  $query = "SELECT * FROM db_main WHERE name='$tname'" ;
	  $data = mysql_query($query, $dbc) ;
			if (mysql_num_rows($data) > 1) {
				echo 'already exist'; //this exist, so check another row
		  		 }
				 else {
					 echo 'no problems, insert data into table';
					 }

 

My code above basically checks to see if there are any duplicates.  It's ok if there are duplicates but if there are duplicates I want to be able to check those results against another row to make sure no 2 rows have the same data.  For example

 

row 1 = id

row 2 = color

row 3 = name

 

column 1 = 1, blue, john

column 2 = 2, green, john

column 3 - 3, blue, john

 

Column 2 is ok because row 2 and 3 are different

however column 3 would never be allowed to be inserted into the table because there is already a column with data from row 2 and row 3 that is the same.

 

I know I probably explained this as hard as possible but if anyone understands the pain I'm having and could offer a helpful hand I would greatly appreciate it.

 

Thanks in advance

[aeroswat]

This is very confusing... Your code is checking to see if there is a duplicate... but if there is a duplicate you want it to check to see if there is a duplicate? Do you see the problem in this logic...?

 

Btw your database lingo is all screwed up. Columns are fields and rows are records. Columns would be (id, color, or name) and a row would contain one of each. So are you trying to compare one column to another column? Like check to see if a name is the same as a color? Or trying to see if a color is the same or a name is the same?

[duezy]

This is very confusing... Your code is checking to see if there is a duplicate... but if there is a duplicate you want it to check to see if there is a duplicate? Do you see the problem in this logic...?

 

Exactly, There can be 2 of the same name but not 2 of the same name and same color.

[aeroswat]

This is very confusing... Your code is checking to see if there is a duplicate... but if there is a duplicate you want it to check to see if there is a duplicate? Do you see the problem in this logic...?

 

Exactly, There can be 2 of the same name but not 2 of the same name and same color.

 

Then your query needs to be something like this

 

$qry = "SELECT * FROM db_main WHERE color=" . $color . " AND name=" . $name;

 

of course replace the variables with the correct variable names

[duezy]

Thanks for your reply and help I was thinking along the lines of that, but I was thinking of using the like clause.  I need a good book on sql, any recommendations..

[aeroswat]

Thanks for your reply and help I was thinking along the lines of that, but I was thinking of using the like clause.  I need a good book on sql, any recommendations..

 

No I just kind of pick up things here and there. You would want to use the = in order to match EXACT records. Otherwise if you are trying to weed out things that are like then you can use LIKE with certain wildcard characters like % and ?.

 

% matches to any number of characters

I believe ? matches to a single character

 

so if you did WHERE name LIKE 'John%'

would match John, Johnny, Johnathon etc.

 

whereas WHERE name LIKE 'J?n' would match Jon, Jan, Jen, etc

[Psycho]

What you need to do is JOIN the table on itself and compare each record to the others. Here is what you want (as I understand your requirements)

SELECT d1.*
FROM db_main d1
JOIN db_main d2
WHERE d1.color = d2.color
  AND d1.name = d2.name

 

EDIT: Wait, I think that might not be right. I'm on a call and will review once I can.

[Psycho]

OK, I figured out what I was missing. I was joining the two tables and only associating the records where the two values were the same, but the query needs to have an exclusion so records aren't joined on themsleves. That would be done by stating the ID cannot be the same.

 

So this query should do what you need:

 

SELECT d1.*
FROM db_main d1
JOIN db_main d2
WHERE d1.color = d2.color
  AND d1.name = d2.name
  AND d1.id <> d2.id

 

That will return every record where there is another record with the same color and name.

 

EDIT: My appologies, I totally misread what you were asking for. I thought you were looking for existing duplicates already in the DB, not if a new record would be a duplicate.