Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

MySQL syntax error and logic issue

mysql syntax syntax error

  • Please log in to reply
2 replies to this topic

#1 gogles

gogles

    CC Lurker

  • Just Joined
  • Pip
  • 4 posts

Posted 12 May 2008 - 07:51 AM

What seems to be the problem with this syntax?

With this query i am trying to select the members who have become friends with the friends of f.uid, f.uid2 is the id of the friend. Can anyone see any flaws in the logic of my query?

I am pretty sure facebook or myspace has this feature.

$query =

"SELECT * " .
"FROM relationships r INNER JOIN members m, friends f " .
"ON r.inviter = m.id " .
"WHERE f.uid2 = ANY (SELECT r.recipient) " .
"AND f.uid = '$id'";

My tables:

create table members (
id int not null primary key auto_increment,
username varchar(40) not null,
password varchar(40) not null,
name varchar(40) not null,
location varchar(40) not null,
gender char(1) not null check gender in ('m','f'),
email varchar(40) default '',
birthday date default null,
description text default '');

create table relationships (
id int not null auto_increment,
recipient int not null references members(id),
inviter int not null,
status varchar(20) not null,
epoch date not null,
primary key (id)
);

create table friends (
id int not null auto_increment,
uid int not null,
uid2 int not null,
primary key (id)
);
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 12 May 2008 - 08:40 AM

The biggest problem I see is you have NO conditions on how friends f is associated with the other two tables in your join. You really need at least 2 ON conditions.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 gogles

gogles

    CC Lurker

  • Just Joined
  • Pip
  • 4 posts

Posted 12 May 2008 - 10:15 AM

$query = "SELECT * " .
"FROM relationships r " .
"JOIN members m ON r.inviter = m.id " .
"JOIN friends f ON r.recipient = f.uid2 " .
"WHERE f.uid = $id";

Hmmmm still not exactly achieve its goal.
  • 0





Also tagged with one or more of these keywords: mysql, syntax, syntax error

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download