[RESOLVED] mysql quiry help

[jasondavis]

I have this quiry below where I need to take this result but also look in this table = friend_reg_user for the row called online and only return a result if this online row says = 1

How can this be added into the below?

 $sql ="select friendid from friend_friend where 1=1 and userid='$info[auto_id]' and status='1' order by autoid DESC limit $start,$pagesize";
$result_friend= executequery($sql);

[laserlight]

Post your relevant database schema.

[jasondavis]

I got this below to work but theres got to be a better performance way

from friend_friend all I need is the result : friendid
from friend_reg_user all I need is the result: online

$sql ="SELECT * from friend_friend as a, friend_reg_user as b where a.friendid=b.auto_id AND a.userid='$info[auto_id]' AND a.status='1' and b.online='1' order by autoid DESC limit $start,$pagesize";
$result_friend= executequery($sql);

[jasondavis]

-- 
-- Table structure for table `friend_reg_user`
-- 

`friend_reg_user`
  `auto_id` int(11) NOT NULL auto_increment,
  `email` varchar(100) NOT NULL default '',
  `username` varchar(100) NOT NULL default '',
  `fname` varchar(255) NOT NULL default '',
  `lname` varchar(255) NOT NULL default '',
  `disp_name` varchar(255) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `country` varchar(255) NOT NULL default '',
  `usstate` varchar(255) NOT NULL default '',
  `othstate` varchar(255) NOT NULL default '',
  `zip` bigint(20) NOT NULL default '0',
  `city` varchar(255) NOT NULL default '',
  `gender` enum('Male','Female') NOT NULL default 'Male',
  `dob` date NOT NULL default '0000-00-00',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` enum('Active','In Active') NOT NULL default 'In Active',
  `uniqid` varchar(255) NOT NULL default '',
  `purpose` varchar(255) NOT NULL default '',
  `height` varchar(255) NOT NULL default '',
  `body_type` varchar(255) NOT NULL default '',
  `ethnicity` varchar(255) NOT NULL default '',
  `occupation` varchar(255) NOT NULL default '',
  `marital_status` varchar(255) NOT NULL default 'Single',
  `orientation` varchar(255) NOT NULL default '',
  `home_town` varchar(255) NOT NULL default '',
  `religion` varchar(255) NOT NULL default '',
  `smoker` varchar(255) NOT NULL default '',
  `drinker` varchar(255) NOT NULL default '',
  `kids` varchar(255) NOT NULL default '',
  `education` varchar(255) NOT NULL default '',
  `income` varchar(255) NOT NULL default '',
  `headline` text NOT NULL,
  `about_me` text NOT NULL,
  `like_to_meet` text NOT NULL,
  `interest` text NOT NULL,
  `music` text NOT NULL,
  `movies` text NOT NULL,
  `television` text NOT NULL,
  `books` text NOT NULL,
  `heroes` text NOT NULL,
  `dating` enum('yes','no') NOT NULL default 'no',
  `srelation` enum('yes','no') NOT NULL default 'no',
  `friends` enum('yes','no') NOT NULL default 'no',
  `networking` enum('yes','no') NOT NULL default 'no',
  `lastupdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `view` int(10) NOT NULL default '0',
  `last_visit` datetime NOT NULL default '0000-00-00 00:00:00',
  `photo_view` enum('0','1') NOT NULL default '1',
  `online` enum('0','1') NOT NULL default '0',
  `featured` enum('yes','no') NOT NULL default 'no',
  `favorites` text NOT NULL,
  `cancel_request` enum('No','Yes') NOT NULL default 'No',
  `topfriendslist` text NOT NULL,
  `topfriendsno` int(10) NOT NULL default '0',
  `publicview` bigint(20) NOT NULL default '0',
  `IP_ADD` varchar(250) NOT NULL default '',
  `admin` enum('No','Yes') NOT NULL default 'No',
  `refnumber` bigint(20) NOT NULL default '0',
  `color` varchar(6) NOT NULL default 'FFFFFF',
  `fposts` int(10) default '0',
  `spamblock` enum('0','1') NOT NULL default '0',


-- 
-- Table structure for table `friend_friend`
-- 

CREATE TABLE IF NOT EXISTS `friend_friend` (
  `autoid` int(11) NOT NULL auto_increment,
  `userid` int(10) default NULL,
  `friendid` int(10) default NULL,
  `status` enum('1','0','3') NOT NULL default '0',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL default 'yes',
  `birthday_alert` enum('1','2','3') NOT NULL default '1',
  PRIMARY KEY  (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`),
  KEY `alert` (`alert_message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=505425 ;

[jasondavis]

my quiry above

This page took 0.6766 seconds to generate.

thats a pretty horrible time

[laserlight]

Since you did not specify foreign key constraints there is a little guessing involved, but it looks like your solution is indeed correct and efficient. The main improvement I would make is to select only the columns you require, as in your earlier example:

SELECT friend_friend.friendid
    FROM friend_friend friend, friend_reg_user reg_user
    WHERE friend.friendid=reg_user.auto_id
        AND friend.userid='$info[auto_id]'
        AND friend.status='1'
        AND reg_user.online='1'
    ORDER BY friend.autoid DESC
    LIMIT $start, $pagesize

Other than that, perhaps userid, status and online are actually numeric? If so, do not pass string values to compare with them, and instead write:

SELECT friend_friend.friendid
    FROM friend_friend friend, friend_reg_user reg_user
    WHERE friend.friendid=reg_user.auto_id
        AND friend.userid=$info[auto_id]
        AND friend.status=1
        AND reg_user.online=1
    ORDER BY friend.autoid DESC
    LIMIT $start, $pagesize

[jasondavis]

here is the result

SELECT friend_friend.friendid FROM friend_friend friend, friend_reg_user reg_user WHERE friend.friendid=reg_user.auto_id AND friend.userid='1' AND friend.status='1' AND reg_user.online='1' ORDER BY friend.autoid DESC LIMIT 0, 15

Unknown table 'friend_friend' in field list'

[laserlight]

Probably need to change it to:

SELECT friend.friendid
    FROM friend_friend friend, friend_reg_user reg_user
    WHERE friend.friendid=reg_user.auto_id
        AND friend.userid='$info[auto_id]'
        AND friend.status='1'
        AND reg_user.online='1'
    ORDER BY friend.autoid DESC
    LIMIT $start, $pagesize

[jasondavis]

"Since you did not specify foreign key constraints there is a little guessing involved"

What do you mean by this, I can improve speed by adding something?

[laserlight]

What do you mean by this, I can improve speed by adding something?

I do not know about it improving speed, but it would improve the referential integrity of your data.

[jasondavis]

thanks

[laserlight]

You're welcome, remember to mark this thread as resolved (if it is).