...

View Full Version : Need help with this simple query



kevinkhan
09-16-2011, 09:35 AM
I want to insert all user and friendlink in friend_list table into friend_req but i only want to insert it if the friendlink is not in the profilelink field in the friend_req table

Another words i dont want duplication.



INSERT
INTO friend_req
(user,
profilelink
)

SELECT user,
friendlink
FROM friend_list

WHERE ....................

myfayt
09-16-2011, 12:48 PM
You need to use mysql_num_rows to check if a record exists. Such as:


$query = ("SELECT * FROM friends WHERE profilelink='0'");

if (mysql_num_rows($query) == 0) {
//Insert Record
}
else {
//Error record already exists
}

Old Pedant
09-16-2011, 04:04 PM
DO NOT DO THIS FROM PHP!

You have to loop through all records, one at a time. Ugh. (Besides, what if the programmer doesn't use or know PHP?)

You want a MySQL answer!!


I want to insert all user and friendlink in friend_list table into friend_req but i only want to insert it if the friendlink is not in the profilelink field in the friend_req table

Easy:

INSERT INTO friend_req (user, profilelink)
SELECT user, friendlink FROM friend_list
WHERE user NOT IN ( SELECT user FROM friend_req )

Alternative way (may be faster, depending on what is indexed, etc., but is harder to understand):


INSERT INTO friend_req (user, profilelink)
SELECT L.user, L.friendlink
FROM friend_list AS L LEFT JOIN friend_req AS R
ON L.user = R.user
WHERE R.user IS NULL

myfayt
09-16-2011, 05:09 PM
Old Pedant, that is really rude man. The nice thing about PHP is that there is a dozen different ways of coding. Each one of us has a different style. Just because it's not your way, doesn't mean it doesn't work.

This code is designed to insert 1 row, due to 1 check. It's not designed for inserting 5,000 rows. My impression is that OP wanted to update single accounts, not everyone. If it is multiple then I misunderstood the OP.

So get a life man.

Old Pedant
09-16-2011, 06:02 PM
I want to insert all user and friendlink in friend_list table into friend_req

Unless you can predict how many records are in the two tables, "all" carries to me the concept of "one heluva lot".

In any case, this is the MYSQL forum, and how can you assume that he is even using PHP for his work? I use MySQL with JSP and ASP and ASP.NET. And also for stand-alone programming.

I stand by my answer: Doing it in PHP is wrong on many levels. Not the least of which is efficiency, but there are other reasons it's wrong. I wouldn't have been so emphatic if "bendania" hadn't accepted the answer as gospel truth. But I would still call the answer wrong.

myfayt
09-16-2011, 06:29 PM
It could also mean insert all of the information into a record, but in any case. I've done my bit, I am walking away.

Old Pedant
09-16-2011, 08:52 PM
Okay, I don't read it that way, but it's of course possible. I guess we will find out if kevinkhan comes back and tells us.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum