Jump to content


Photo

query calling too many fields


  • Please log in to reply
12 replies to this topic

#1 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 04:58 PM

ok, im trying to run this query:
SELECT t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username from `topics` t, `users` u , `posts_read` pr WHERE (pr.pr_userid = '1' AND pr.pr_topic_id != t.topic_id AND u.users_userid = t.topic_poster)

basiclly it should do this:
get these fields for any topic that hasnt been read

whenever a topic is read, the user's id and the topic id go into the posts_read table that way we can track them, whenever i run this query i get over 2,000 results! theres only 120 posts all together and thats posts not topics!

when i run the query, i find that its pulling the same rows over and over, like if i organize the results by t.topic_id i get hundreds of rows that have a 1 for t.topic_id, the only tables being checked here that have topic_id in them is posts_read and topics heres the table schemes:

posts_read:
CREATE TABLE `posts_read` (
  `pr_id` mediumint(8) NOT NULL auto_increment,
  `pr_topic_id` mediumint(8) NOT NULL default '0',
  `pr_userid` mediumint(8) NOT NULL default '0',
  PRIMARY KEY  (`pr_id`)
) TYPE=MyISAM AUTO_INCREMENT=268;

topics:
CREATE TABLE `topics` (
  `topic_id` mediumint(8) NOT NULL auto_increment,
  `topic_forum_id` smallint(8) NOT NULL default '0',
  `topic_title` varchar(60) NOT NULL default '',
  `topic_time` int(11) NOT NULL default '0',
  `topic_poster` mediumint(8) NOT NULL default '0',
  `topic_views` mediumint(8) unsigned NOT NULL default '0',
  `topic_replies` mediumint(8) unsigned NOT NULL default '0',
  `topic_type` tinyint(3) unsigned NOT NULL default '0',
  `topic_status` tinyint(3) NOT NULL default '0',
  `topic_first_post_id` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`topic_id`)
) TYPE=MyISAM AUTO_INCREMENT=98;

if need be, i can give you some things to insert into these tables, if someone can please help me itd be great

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 10 November 2005 - 05:38 PM

you're not joining the tables correctly. also, when needing to return something not existing in a certain table, it's best to do with LEFT JOIN or RIGHT JOIN.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username FROM `users` u LEFT JOIN (`topics` t, `posts_read` pr) ON (u.users_userid = t.topic_poster, t.topic_id = pr.pr_topic_id) WHERE pr.pr_userid = '1' AND pr.pr_id IS NULL [!--sql2--][/div][!--sql3--]
Untested...
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 05:48 PM

hmm, i get this error:

#1064 - 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 ' t . topic_id = pr . pr_topic_id ) WHERE pr . pr_userid = '1' A


can you give me a quick explanation of how join works?

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 10 November 2005 - 05:58 PM

hmm, maybe you're using an older version of mysql?? change it to LEFT JOIN table ON condition LEFT JOIN table ON condition instead.

take a look at this page from mysql.com for join syntaxes http://dev.mysql.com....0/en/join.html

this page has a good explanation of how joins work
http://www.wellho.ne...OUTER-JOIN.html

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 06:32 PM

ok, im using 4.0.25

heres what i tried:
SELECT t . topic_id , t . topic_status , t . topic_type , t . topic_title , t . topic_status , t . topic_poster , t . topic_views , t . topic_replies , u . users_username FROM `users` u LEFT JOIN `topics` t ON u . users_userid =t . topic_poster LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_userid ='1' AND pr.pr_id IS NULL ORDER BY `t` . `topic_id` ASC

and this:
SELECT t . topic_id , t . topic_status , t . topic_type , t . topic_title , t . topic_status , t . topic_poster , t . topic_views , t . topic_replies , u . users_username FROM `users` u LEFT JOIN `topics` t ON u . users_userid =t . topic_poster LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_userid ='1' ORDER BY `t` . `topic_id` ASC

the first one does nothing but succed, and the second one pulls up all the topics in the database, any ideas?

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 10 November 2005 - 07:12 PM

try putting the IS NULL into the second one.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 07:28 PM

hmm still not working, i think im going to have to run 2 querys, 1 to get the topics, and the other to check if theyre read or not

i also relized, that we cant compare anything with posts_read, because the only way there is something in there with a topic_id matching the one in topics is if the topic has been read, and we want all the ones NOT read

so we need a query that says:
select all the topics that dont have an id in posts_read for this userid

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 10 November 2005 - 07:54 PM

i think i was checking for the wrong column. joins are tricky especially when dealing with non-existence. from the mysql manual, IS NULL is how you pull rows that doesn't exists:

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL.


yeah try to split it into two and see if you could get it, then combine them. maybe easier to go that way.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#9 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 08:34 PM

ok, i know how ill do it for 2 querys, but this is annoying me, i tried this also:

SELECT * FROM `topics` t LEFT JOIN `posts_read` pr ON t . topic_id =pr . pr_topic_id WHERE pr . pr_id IS NULL

and its still a no go

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#10 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 10 November 2005 - 09:04 PM

try IS NULL on pr.pr_topic_id instead
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#11 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 10 November 2005 - 09:38 PM

nope, its still not going, gives me no rows

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#12 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 12 November 2005 - 04:25 PM

*bump

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#13 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 November 2005 - 03:58 AM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
t.topic_id, t.topic_status, t.topic_type, t.topic_title, t.topic_status, t.topic_poster, t.topic_views, t.topic_replies, u.users_username
FROM `topics` t
INNER JOIN `users` u
ON u.users_userid = t.topic_poster
LEFT JOIN `posts_read` pr
ON pr.pr_topic_id = t.topic_id AND pr.pr_userid = 1
WHERE ISNULL(pr.pr_topic_id) [!--sql2--][/div][!--sql3--]
If the query above does not work, post the create statement for the users table and perhaps a small dump with about 5 topics 2 posts read and a couple of users.

EDIT: Test the query by itself first using an interface such as Phpmyadmin or the default mysql client "mysql monitor"




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users