Jump to content


Photo

MySQL Query from more tables


  • Please log in to reply
24 replies to this topic

#1 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 26 October 2006 - 09:32 PM

hi
i got this MySQL query
but i don't get results back, while i am sure there are results

SELECT
    m.*,
    c.id,
    dd.id,
    dr.id,
    fr.id,
    nr.id,
    p.id, 
    ss.id,
    sr.id,
    tt.id,
    tr.id,
    f.*,
    o.*,

    c.userid,
    dd.dl_userid,
    dr.userid,
    fr.reply_poster,
    nr.news_rp_userid,
    p.userid,
    ss.userid,
    sr.userid,
    tt.userid,
    tr.userid 
FROM
    members AS m,
    calender AS c,
    dl_downloads AS dd,
    dl_reacties AS dr,
    forum_replys AS fr,
    news_replies AS nr,
    online AS o,
    pictures AS p,
    scripts AS ss,
    sc_reacties AS sr,
    tutorials AS tt,
    tut_reacties AS tr,
    friends AS f
WHERE
    m.id > 0
AND
    c.userid = m.id
AND
    dd.dl_userid = m.id
AND
    dr.userid = m.id
AND
    fr.reply_poster = m.id
AND
    nr.news_rp_userid = m.id
AND
    p.userid = m.id
AND
    ss.userid = m.id
AND
    sr.userid = m.id
AND
    tt.userid = m.id
AND
    tr.userid = m.id
AND
    f.userid = m.id
AND
    o.userid = m.id

these are my tables
-- 
-- Table structure for table `calender`
-- 

CREATE TABLE `calender` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL,
  `date` varchar(10) collate latin1_general_ci NOT NULL default '',
  `subject` varchar(255) collate latin1_general_ci NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  `day` int(2) NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=0 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `dl_downloads`
-- 

CREATE TABLE `dl_downloads` (
  `id` int(11) NOT NULL auto_increment,
  `dl_dloads` int(11) NOT NULL,
  `dl_subcat_id` int(11) NOT NULL,
  `dl_userid` int(11) NOT NULL,
  `dl_naam` varchar(255) collate latin1_general_ci NOT NULL,
  `dl_uitleg` text collate latin1_general_ci NOT NULL,
  `dl_url` varchar(255) collate latin1_general_ci NOT NULL,
  `dl_datum` int(10) NOT NULL,
  `dl_status` int(1) NOT NULL,
  `dl_views` int(11) NOT NULL,
  `dl_short_uitleg` varchar(255) collate latin1_general_ci NOT NULL,
  `dl_screenshot` varchar(255) collate latin1_general_ci NOT NULL,
  `dl_download` varchar(255) collate latin1_general_ci NOT NULL,
  `dl_autheur` varchar(255) collate latin1_general_ci NOT NULL default '',
  `dl_version` varchar(50) collate latin1_general_ci NOT NULL,
  `file_size` int(255) NOT NULL,
  `dl_last_download` int(10) NOT NULL,
  `last_post_date` int(10) NOT NULL,
  `dl_icon` int(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=88 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `dl_reacties`
-- 

CREATE TABLE `dl_reacties` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `dl_id` int(11) NOT NULL default '0',
  `bericht` text collate latin1_general_ci NOT NULL,
  `datum` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `forum_replys`
-- 

CREATE TABLE `forum_replys` (
  `id` int(11) NOT NULL auto_increment,
  `topic_id` int(11) NOT NULL,
  `is_topic` int(1) NOT NULL,
  `reply_poster` int(11) NOT NULL,
  `reply_date` int(10) NOT NULL,
  `reply_message` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=27 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `forum_topics`
-- 

CREATE TABLE `forum_topics` (
  `id` int(11) NOT NULL auto_increment,
  `forum_id` int(11) NOT NULL,
  `topic_poster` int(11) NOT NULL,
  `topic_type` int(1) NOT NULL,
  `topic_naam` varchar(40) collate latin1_general_ci NOT NULL,
  `topic_date` int(10) NOT NULL,
  `topic_locked` int(11) NOT NULL default '0',
  `topic_views` int(11) NOT NULL,
  `last_post_date` int(10) NOT NULL,
  `topic_desc` varchar(255) collate latin1_general_ci NOT NULL,
  `poll_added` int(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `friends`
-- 

CREATE TABLE `friends` (
  `userid` int(11) NOT NULL,
  `userid2` int(11) NOT NULL,
  `type` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Table structure for table `members`
-- 

CREATE TABLE `members` (
  `id` int(11) NOT NULL auto_increment,
  `gebruikersnaam` varchar(25) collate latin1_general_ci NOT NULL,
  `wachtwoord` varchar(32) collate latin1_general_ci NOT NULL,
  `email` varchar(255) collate latin1_general_ci NOT NULL,
  `realname` varchar(255) collate latin1_general_ci NOT NULL,
  `gender` int(1) NOT NULL,
  `birthday` int(10) NOT NULL,
  `activated` varchar(20) collate latin1_general_ci NOT NULL,
  `website` varchar(255) collate latin1_general_ci NOT NULL,
  `status` varchar(100) collate latin1_general_ci NOT NULL default 'Lid',
  `avatar` varchar(255) collate latin1_general_ci NOT NULL,
  `liddatum` int(10) NOT NULL,
  `ondertitel` text collate latin1_general_ci NOT NULL,
  `ip` varchar(50) collate latin1_general_ci NOT NULL,
  `proxy` varchar(50) collate latin1_general_ci NOT NULL,
  `host` varchar(50) collate latin1_general_ci NOT NULL,
  `warnings` int(1) NOT NULL default '0',
  `posts` int(11) NOT NULL default '0',
  `tuts` int(11) NOT NULL default '0',
  `dloads` int(11) NOT NULL default '0',
  `scripts` int(11) NOT NULL default '0',
  `lastvisit` int(11) NOT NULL,
  `locatie` varchar(255) collate latin1_general_ci NOT NULL,
  `occupation` varchar(255) collate latin1_general_ci NOT NULL,
  `icq` varchar(15) collate latin1_general_ci NOT NULL,
  `aim` varchar(255) collate latin1_general_ci NOT NULL,
  `yim` varchar(255) collate latin1_general_ci NOT NULL,
  `msnm` varchar(255) collate latin1_general_ci NOT NULL,
  `interests` varchar(255) collate latin1_general_ci NOT NULL,
  `tel_home` int(20) NOT NULL,
  `fax_home` int(20) NOT NULL,
  `tel_occ` int(20) NOT NULL,
  `fax_occ` int(20) NOT NULL,
  `mobiel` int(20) NOT NULL,
  `user_scripts_per_page` int(11) NOT NULL,
  `user_language` varchar(40) collate latin1_general_ci NOT NULL default 'dutch',
  `user_users_per_page` int(11) NOT NULL,
  `user_view_online` int(1) NOT NULL,
  `user_view_profile` int(1) NOT NULL,
  `user_newsletter` int(1) NOT NULL,
  `user_view_email` int(1) NOT NULL,
  `user_replys_per_page` int(11) NOT NULL,
  `user_notify_forum` int(1) NOT NULL,
  `user_notify_dloads` int(1) NOT NULL,
  `user_notify_scripts` int(1) NOT NULL,
  `user_notify_tutorials` int(1) NOT NULL,
  `user_notify_privmsg` int(1) NOT NULL,
  `user_popup_by_privmsg` int(1) NOT NULL,
  `user_allow_pm` int(1) NOT NULL,
  `user_mail_pm` int(1) NOT NULL,
  `user_view_signature` int(1) NOT NULL,
  `user_view_avatar` int(1) NOT NULL,
  `optie_view_web` int(1) NOT NULL,
  `optie_view_msg` int(1) NOT NULL,
  `optie_view_personal` int(1) NOT NULL,
  `user_view_friends` int(1) NOT NULL,
  `user_view_blocked` int(1) NOT NULL,
  `user_view_friend_of` int(1) NOT NULL,
  `user_view_blocked_by` int(1) NOT NULL,
  `user_privmsg_p_page` int(5) NOT NULL,
  `user_view_quick_reply` int(1) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `gebruikersnaam` (`gebruikersnaam`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `news_replies`
-- 

CREATE TABLE `news_replies` (
  `id` int(10) NOT NULL auto_increment,
  `news_rp_id` int(10) NOT NULL default '0',
  `news_rp_userid` varchar(20) collate latin1_general_ci NOT NULL default '',
  `news_rp_bericht` text collate latin1_general_ci NOT NULL,
  `news_rp_datum` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `online`
-- 

CREATE TABLE `online` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
  `datum` int(10) NOT NULL,
  `datum_start` int(10) NOT NULL,
  `page` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=966 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `pictures`
-- 

CREATE TABLE `pictures` (
  `id` int(11) NOT NULL auto_increment,
  `sid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `subject` varchar(255) collate latin1_general_ci NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  `location` varchar(1000) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `sc_reacties`
-- 

CREATE TABLE `sc_reacties` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `script_id` int(11) NOT NULL default '0',
  `bericht` text collate latin1_general_ci NOT NULL,
  `datum` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `scripts`
-- 

CREATE TABLE `scripts` (
  `id` int(11) NOT NULL auto_increment,
  `subcat_id` int(11) NOT NULL,
  `title` varchar(255) collate latin1_general_ci NOT NULL,
  `explain` text collate latin1_general_ci NOT NULL,
  `views` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `desc` varchar(300) collate latin1_general_ci NOT NULL,
  `source` text collate latin1_general_ci NOT NULL,
  `demo_url` varchar(255) collate latin1_general_ci NOT NULL,
  `date` int(10) NOT NULL,
  `version` varchar(10) collate latin1_general_ci NOT NULL,
  `last_post_date` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `tut_reacties`
-- 

CREATE TABLE `tut_reacties` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL,
  `tut_id` int(11) NOT NULL,
  `bericht` text collate latin1_general_ci NOT NULL,
  `datum` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `tutorials`
-- 

CREATE TABLE `tutorials` (
  `id` int(11) NOT NULL auto_increment,
  `subcat_id` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `title` varchar(255) collate latin1_general_ci NOT NULL,
  `inleiding` varchar(300) collate latin1_general_ci NOT NULL,
  `text` text collate latin1_general_ci NOT NULL,
  `views` int(11) NOT NULL,
  `datum` int(10) NOT NULL,
  `last_post_date` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ; 

what is the problem with the query?

greetzz

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 October 2006 - 10:55 PM

if just one of the tables does not have a record with a matching id then no rows will be returned
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 27 October 2006 - 04:09 PM

ooh thx .. so can anyone help me with selecting from these tables for a members list page?
i need it in one query coz i want the user to be able to choose what to select and what not and how to order.

greetzz

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 27 October 2006 - 06:13 PM

The only tables you are getting data from are
members
friends
online

All the other selected fields in your query are the id fields, and as you are matching on these they will all have the same value as member.id. So not much point in putting them in the select.

So the question are -
Why are the other tables in the query?
Is it so that you only return records in member that have a match in those tables?
Do you  want members listed with no friends records?
Do you want members listed with no online records?

So yes, I can help with the query but only if I know what the query should do.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 04:03 PM

Also, use proper JOIN syntax, so that you can see your WHERE clause from your JOIN conditions.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 28 October 2006 - 04:38 PM

Why are the other tables in the query?
because (as you probably knew) i use it for a memberslist, there a user can say how they want to order, and what they want to select.. i want them to select like on total online, total new replies etc.
Is it so that you only return records in member that have a match in those tables?
well i got the users at (there are 4 users to test) and not all of them have posted, so its not only those who have posted, but every user.
Do you  want members listed with no friends records?
i want the user to be able to select that himself with a form
Do you want members listed with no online records?
same as above

@fenway
srry im not really good in MySQL so if you can explain what you mean, i'd appreciate it

btw i forgot to say even when i try only "WHERE m.id > 0" it still doesn't give records.

greetzz

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 04:51 PM

@fenway
srry im not really good in MySQL so if you can explain what you mean, i'd appreciate it


I mean like this:
SELECT
    m.*,
    c.id,
    dd.id,
    dr.id,
    fr.id,
    nr.id,
    p.id, 
    ss.id,
    sr.id,
    tt.id,
    tr.id,
    f.*,
    o.*,

    c.userid,
    dd.dl_userid,
    dr.userid,
    fr.reply_poster,
    nr.news_rp_userid,
    p.userid,
    ss.userid,
    sr.userid,
    tt.userid,
    tr.userid 
FROM members AS m
    INNER JOIN calender AS c ON c.userid = m.id
    INNER JOIN dl_downloads AS dd ON dd.dl_userid = m.id
    INNER JOIN dl_reacties AS dr ON dr.userid = m.id
    INNER JOIN forum_replys AS fr ON fr.reply_poster = m.id
    INNER JOIN news_replies AS nr ON    nr.news_rp_userid = m.id
    INNER JOIN online AS o ON     o.userid = m.id 
    INNER JOIN pictures AS p ON     p.userid = m.id
    INNER JOIN scripts AS ss ON     ss.userid = m.id
    INNER JOIN sc_reacties AS sr ON     sr.userid = m.id
    INNER JOIN tutorials AS tt ON     tt.userid = m.id
    INNER JOIN tut_reacties AS tr ON     tr.userid = m.id
    INNER JOIN friends AS f ON     f.userid = m.id
WHERE
    m.id > 0

But as barand mentioned, if anything doesn't match, you'll get back nothing.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 28 October 2006 - 07:46 PM

oh ok.. it still doesn't work ??? anyway isn't there anyway that this allows this to return without even having any results, coz the others tables could be empty

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 October 2006 - 07:49 PM

Try changing INNER JOIN to LEFT JOIN
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 29 October 2006 - 08:17 AM

Well i tried that with LEFT JOIN, and i get arround 7000 results.. and all with the same name, from a table with arround 6 rows :S

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 08:46 AM

That's a thing abour joins

Table1 has 2 records and you have 3 matching records in table2, so the cols from each of the 2 records are joined to the cols of each of the matching 3 from Table2 giving 6 rows..

Now join to Table3 with 5 matching rows and you now have 30 rows.
Now join to Table4 with 10 matching rows and you now have 300 rows.

etc etc etc

Try putting DISTINCT immediately after SELECT so only unique combinations of field value are returned.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 29 October 2006 - 09:00 AM

so you mean like this:
SELECT DISTINCT
    m.*,
    c.id,
    dd.id,
    dr.id,
    fr.id,
    nr.id,
    p.id, 
    ss.id,
    sr.id,
    tt.id,
    tr.id,
    f.*,
    o.*,

    c.userid,
    dd.dl_userid,
    dr.userid,
    fr.reply_poster,
    nr.news_rp_userid,
    p.userid,
    ss.userid,
    sr.userid,
    tt.userid,
    tr.userid 
FROM members AS m
    LEFT JOIN calender AS c ON c.userid = m.id
    LEFT JOIN dl_downloads AS dd ON dd.dl_userid = m.id
    LEFT JOIN dl_reacties AS dr ON dr.userid = m.id
    LEFT JOIN forum_replys AS fr ON fr.reply_poster = m.id
    LEFT JOIN news_replies AS nr ON    nr.news_rp_userid = m.id
    LEFT JOIN online AS o ON     o.userid = m.id 
    LEFT JOIN pictures AS p ON     p.userid = m.id
    LEFT JOIN scripts AS ss ON     ss.userid = m.id
    LEFT JOIN sc_reacties AS sr ON     sr.userid = m.id
    LEFT JOIN tutorials AS tt ON     tt.userid = m.id
    LEFT JOIN tut_reacties AS tr ON     tr.userid = m.id
    LEFT JOIN friends AS f ON     f.userid = m.id
WHERE
    m.id > 0

i still get lot of the same rows

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 09:14 AM

Should be at least 1 thing different in each row
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 29 October 2006 - 09:18 AM

Could but how am i gonna check that? there are to much results :S

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 09:31 AM

Now you've seen for yourself the results of joining all the tables, you might want to consider a different approach.

For example, to get the number of replies you only need query the forum_replys table. If you include all the others you are going to get counts like 7000+ every time.

If you want to count those online, use online table (maybe joined with member table if want their names)

Just because you can join them all doesn't mean that you should.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 29 October 2006 - 02:22 PM

ok.. so how can i then select only from friends, members and online.. again those tables could be empty (maybe not online but friends could be)
then im making extra rows in members for these things ..

#17 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 05:25 PM

Just include those tables you need for the query.

if you want only members with friend(s) use

members INNER JOIN friends

if you want all members with friends but also want to show those with 0 friends, use

members LEFT JOIN friends
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#18 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 October 2006 - 07:30 PM

Actually, if you're JOINing, and your JOIN conditions are correct, you should never get any "extra" rows at all -- it will just add more fields to each record returned.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#19 superaktieboy

superaktieboy
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 29 October 2006 - 07:49 PM

ok i tried this one:
SELECT DISTINCT
    m.*,
    f.*,
    o.*,
FROM members AS m
    LEFT JOIN online AS o
    LEFT JOIN friends AS f
WHERE
    m.id > 0

but i get this error from MySQL

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM members AS m LEFT JOIN online AS o LEFT JOIN friends AS f WHERE ' at line 5


what does it mean?

#20 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 07:51 PM

Remove the comma between o.* and FROM

Also you haven't specified the join conditions to say which columns should match
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users