superaktieboy Posted October 26, 2006 Share Posted October 26, 2006 hii got this MySQL querybut i don't get results back, while i am sure there are results[code]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 fWHERE m.id > 0AND c.userid = m.idAND dd.dl_userid = m.idAND dr.userid = m.idAND fr.reply_poster = m.idAND nr.news_rp_userid = m.idAND p.userid = m.idAND ss.userid = m.idAND sr.userid = m.idAND tt.userid = m.idAND tr.userid = m.idAND f.userid = m.idAND o.userid = m.id [/code]these are my tables[code]-- -- 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 ; [/code]what is the problem with the query?greetzz Quote Link to comment Share on other sites More sharing options...
Barand Posted October 26, 2006 Share Posted October 26, 2006 if just one of the tables does not have a record with a matching id then no rows will be returned Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 27, 2006 Author Share Posted October 27, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 27, 2006 Share Posted October 27, 2006 The only tables you are getting data from aremembersfriendsonlineAll 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 28, 2006 Share Posted October 28, 2006 Also, use proper JOIN syntax, so that you can see your WHERE clause from your JOIN conditions. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 28, 2006 Author Share Posted October 28, 2006 [b]Why are the other tables in the query?[/b]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.[b]Is it so that you only return records in member that have a match in those tables?[/b]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.[b]Do you want members listed with no friends records?[/b]i want the user to be able to select that himself with a form[b]Do you want members listed with no online records?[/b]same as above@fenwaysrry im not really good in MySQL so if you can explain what you mean, i'd appreciate itbtw i forgot to say even when i try only "WHERE m.id > 0" it still doesn't give records.greetzz Quote Link to comment Share on other sites More sharing options...
fenway Posted October 28, 2006 Share Posted October 28, 2006 [quote author=superaktieboy link=topic=112860.msg459129#msg459129 date=1162053526]@fenwaysrry im not really good in MySQL so if you can explain what you mean, i'd appreciate it[/quote]I mean like this:[code]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.idWHERE m.id > 0[/code]But as barand mentioned, if anything doesn't match, you'll get back nothing. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 28, 2006 Author Share Posted October 28, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 28, 2006 Share Posted October 28, 2006 Try changing INNER JOIN to LEFT JOIN Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 That's a thing abour joinsTable1 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 etcTry putting DISTINCT immediately after SELECT so only unique combinations of field value are returned. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 so you mean like this:[code]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.idWHERE m.id > 0[/code]i still get lot of the same rows Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Should be at least 1 thing different in each row Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 Could but how am i gonna check that? there are to much results :S Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 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. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 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 .. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Just include those tables you need for the query.if you want only members with friend(s) use members INNER JOIN friendsif you want all members with friends but also want to show those with 0 friends, usemembers LEFT JOIN friends Quote Link to comment Share on other sites More sharing options...
fenway Posted October 29, 2006 Share Posted October 29, 2006 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. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 ok i tried this one:[code]SELECT DISTINCT m.*, f.*, o.*,FROM members AS m LEFT JOIN online AS o LEFT JOIN friends AS fWHERE m.id > 0[/code]but i get this error from MySQL[quote]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[/quote]what does it mean? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Remove the comma between o.* and FROMAlso you haven't specified the join conditions to say which columns should match Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 29, 2006 Author Share Posted October 29, 2006 oh thx didn't see that one :Pbut now i get another error:[code]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 'WHERE m.id > 0' at line 8[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2006 Share Posted October 29, 2006 Did you add the "ON" clauses to specify the joins? If so, what does the query look like now? Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 30, 2006 Author Share Posted October 30, 2006 Thx .. it worked, it was without the ON bit .. i added it and it worked :) thxhere is the query for other people[code]SELECT DISTINCT m.*, f.*, o.*FROM members AS m LEFT JOIN online AS o ON o.userid = m.id LEFT JOIN friends AS f ON f.userid = m.idWHERE m.id > 0[/code] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 30, 2006 Share Posted October 30, 2006 FYI, the distinct is probably unnecessary here, since you're getting back UIDs, which will be unique a.k.a. distinct anyway. Quote Link to comment Share on other sites More sharing options...
superaktieboy Posted October 31, 2006 Author Share Posted October 31, 2006 oh ok.. i did that Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.