[SOLVED] no insert into db when row already exists

[DonPatricio]

Hello,

I have a database structure kind of like this:

 

name1    name2

 

 

patrick   philip

danny    patrick

alex       bobby

kurt       timmy

 

Lets say that somebody inserts a mysql_query into the database for name1 value:alex, name2 value:bobby. As you can see these values already exists in the db. The row isequal to the row I would insert. So, how can I stop inserting when the row already exists?

Additionally, what I believe is even harder to realize is that, also nothing should be inserted when the value of name1 = bobby and the value of name2 = alex. To visulaize, in case I didn't explain it well enough:

 

name1    name2

 

patrick   philip

danny    patrick

bobby    alex

kurt       timmy

 

Seeing this, it should be impossible to insert bobby for name1 & alex for name2, as well as alex for name1 & bobby for name2 in a row, because the pair of names already exists.

 

I hope I could explain that well enough. If you don't get it please just ask, it would be really helpful if somebody can help me. Thanks a lot,

Patrick

 

 

[Ken2k7]

You can select them in queries and see if it exists using mysql_num_rows I guess.

[revraz]

$qry = "SELECT name1, name2 FROM names WHERE name1 = 'alex' AND name2 = 'bobby'";

$result = mysql_query($qry);

if (mysql_num_rows($result) >= 1) {

//record exists

}

else {

//doesn't exist, insert

}

[DonPatricio]

@revraz

Thanks a lot, i will try this out and reply if i was able to built that into my script. Thanks!

[DonPatricio]

But, wait, doesn't that only not insert when  name1='alex' and name2='bobby'? What about when name1='bobby' and name2='alex'?Then it shouldn't insert either.

[Ken2k7]

But, wait, doesn't that only not insert when  name1='alex' and name2='bobby'? What about when name1='bobby' and name2='alex'?Then it shouldn't insert either.

 

$qry = "SELECT name1, name2 FROM names WHERE name1 = 'alex' OR name1 = 'bobby' AND name2 = 'bobby' OR name2 = 'alex'";

[DonPatricio]

$qry = "SELECT own_name, friend_name FROM friends_real WHERE own_name = '$username' OR own_name = '$add_friend' AND friends_name = '$add_friend' OR friends_name = '$username'";
$result = mysql_query($qry) or die ("Error");

if (mysql_num_rows($result) >= 1) {

echo "You are already friend with this user";

}
else {
mysql_query ("INSERT INTO friends_request (own_name, friend_name) VALUES ('$username', '$add_friend');") or die ("error");
}

 

That is weird. Why do I get this message:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\add_friend.php on line 30

[revraz]

Remove the or die from the $qry variable and put it after the mysql_query($qry) function.

[DonPatricio]

Works without the "or die" thanks! However, something is apparently not right with the query.

$qry = "SELECT own_name, friend_name FROM friends_real WHERE own_name = '$username' OR own_name = '$add_friend' AND friend_name = '$add_friend' OR friend_name = '$username'";

 

 

Because, whenever this code runs, the num_rows is greater than 1 and never ever something gets inserted.

 

That is my table, when needed:

 

-- Table structure for table `friends_real`

--

 

CREATE TABLE `friends_real` (

  `FIDR` int(9) NOT NULL auto_increment,

  `own_name` varchar(60) collate latin1_general_ci NOT NULL,

  `friend_name` varchar(60) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`FIDR`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=26 ;

 

--

-- Dumping data for table `friends_real`

--

 

INSERT INTO `friends_real` (`FIDR`, `own_name`, `friend_name`) VALUES

(21, 'admin2', 'admin'),

(22, 'admin', 'admin3'),

(23, 'admin', 'Space champ'),

(24, 'admin', '1');

 

[revraz]

Above you have

 

INSERT INTO friends_request

 

Then your other post you say friends_real

 

Are you reading from one table and inserting into another?

[DonPatricio]

Ohh no, doens't that work, reading from one table and inserting into another table. that is actually what i am doing, yes ..

[DonPatricio]

ok, well reading from one table and inserting to another should work fine.

I think I know what the problem is, but I don't know how to solve it.

 

So, above you see the dump schema for references. When user admin requests to be friend with another user for example "admin5", the num_rows is >= 0 and nothing would be inserted. But something should be inserted! (In this case $username = 'admin' & $add_friend = 'admin5')

 

However when a other user, not inserted in the db yet called "danny" for example wants to be friend with "pablo" num_rows would be <=0 and it would work.

 

So, I think that the query has to be rewritten, but i actually don't know how to do this.

[DonPatricio]

i think that i got it, thanks for all the help