SELECT returns infinite date

[Yesideez]

Something weird is happening here, my SELECT query is returning an inifinite amount of data even though there are only two records stored in the table. The data returned is always the first record, the second is never touched.

SELECT e.*,u.username,(SELECT type from event_types WHERE `typeid`=e.type) AS type FROM events e,phpbb_users u WHERE e.status<>'Disabled' ORDER BY e.whenfrom DESC LIMIT 2

If I don't use "LIMIT 2" on the end the screen fills up and its not long before the vertical scroll bar of my browser goes real small. If I use "LIMIT 2" or whatever number all I get shown is the first record.

[shoz]

SELECT e.*,u.username,(SELECT type from event_types WHERE `typeid`=e.type) AS type FROM events e,phpbb_users u WHERE e.status<>'Disabled' ORDER BY e.whenfrom DESC LIMIT 2

 

You're JOINing the "events" and "phpbb_users" tables but have provided no JOIN condition and the SUBSELECT can be made into a JOIN.

 

SELECT
e.*, u.username, t.type
FROM
events AS e
INNER JOIN
event_types AS t
ON
e.type = t.typeid
INNER JOIN
phpbb_users AS u
ON
e.columnThatReferencesPhpbbUsers  = u.idofsomesort
WHERE e.status <> 'Disabled'
ORDER BY
e.whenfrom DESC

[Yesideez]

Thanks but I understand absolutely nothing of that - I'll add it into the script and make what changes I can to the "e.columnThatReferencesPhpbbUsers  = u.idofsomesort" bit (I'm linking the phpbb table with the events table via users but this can be skipped as it's not needed, just preferred)

 

btw, it's referencing userid from user_id from the phpbb table and userid from the events table.

 

No matter how much I read up on JOIN I can't understand it. You can imagine it's extremely frustrating!

[o3d]

When you join two tables, you join them on specific columns. This will remove unwanted rows (except if jou left join).

 

eg.

TABLE1

c1 c2

1  one

2  two

3  three

 

TABLE2

c1 c2 c3

1  this  1

2  that  2

3  this  1

4  this  1

 

So we want to see from table2 all the fields that has a relationship to table1 on table2's c3 and table1's c1.

 

The join should look something like

 

select table2.c1, table2.c2, table1.c3

from table2 join table1 on table2.c3 = table1.c1

 

Should return:

c1 c2 c3

1 this one

2 that two

3 this one

4 this one

 

I hope this helps!