joining 3 tables and get - not a valid MySQL result resource

[jeff5656]

I want to join 3 tables.  The records are linked by id.  news_read has a field called users_id which links to the users tables.

I get "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource"

 

$query = "SELECT *, news_read.id as newsid FROM news_read, users

INNER JOIN news ON news_read.news_id = news.id

INNER JOIN news_read.user_id=users.id order by news_date DESC"; ;

 

here's my 3 tables and the way they are linked by id

USERS                        NEWS_READ                            NEWS

id -------------------->  user_id

                                  news_id ------------------------>id

 

[fugix]

can i see the full code snippet please

[jeff5656]

can i see the full code snippet please

it's just this: (is that what you're asking?  I just put it into a while loop. 

$results = mysql_query($query);

while ($row = mysql_fetch_assoc ($results)) {

 

 

[jeff5656]

Hmm, I thought I found the error but this doesn't work either:

 

$query = "SELECT *, news_read.id as newsid FROM news_read, users 

INNER JOIN news ON news_read.news_id = news.id

INNER JOIN users  ON news_read.user_id=users.id order by news_date DESC"

[fugix]

try puting this to display the error

$results = mysql_query($query) or die(mysql_error());

[jeff5656]

try puting this to display the error

$results = mysql_query($query) or die(mysql_error());

The plot thickens.  Here's the error:

Not unique table/alias: 'users'

 

 

But the name of my table is users, and there is no other table called users...

 

[jeff5656]

ugh.. fugix was really helping me out when the thread was moved.  I doubt he knows to look here.  Couldn't you guys have moved the thread AFTER he answered my question?

[Maq]

ugh.. fugix was really helping me out when the thread was moved.  I doubt he knows to look here.  Couldn't you guys have moved the thread AFTER he answered my question?

There is a redirect from the original section & also if he clicks "Show new replies to your posts." this thread will still show up.

[Maq]

You need to alias your table name:

$query = "SELECT *, news_read.id as newsid FROM news_read, users u
INNER JOIN news ON news_read.news_id = news.id
INNER JOIN u ON news_read.user_id=u.id order by news_date DESC"

[jeff5656]

You need to alias your table name:

 

 

When I do that I get this error: Not unique table/alias: 'u'

[jeff5656]

Here are my tables.  Can anyone fgure out why I get that error message?

CREATE TABLE IF NOT EXISTS `news_read` (

  `id` int(10) NOT NULL auto_increment,

  `user_id` int(10) NOT NULL,

  `news_id` int(10) NOT NULL,

  `signoff_status` enum('a','s') collate utf8_unicode_ci NOT NULL,

  PRIMARY KEY  (`id`)

)

---

CREATE TABLE IF NOT EXISTS `news` (

  `id` int(10) NOT NULL auto_increment,

  `news_date` date NOT NULL,

  `news` text collate utf8_unicode_ci NOT NULL,

  `group_type` varchar(20) collate utf8_unicode_ci NOT NULL,

  `institution` varchar(30) collate utf8_unicode_ci NOT NULL,

  `exp_date` date NOT NULL,

  `signoff_status` enum('a','s') collate utf8_unicode_ci NOT NULL,

  PRIMARY KEY  (`id`)

)

 

CREATE TABLE IF NOT EXISTS `users` (

  `id` int(20) NOT NULL auto_increment,

[snip]

`field_name99` varchar(60) NOT NULL,

  PRIMARY KEY  (`id`)

)

 

[fugix]

try this

$query = "SELECT *, news_read.id as newsid FROM news_read, users 
INNER JOIN news ON news_read.news_id = news.id 
INNER JOIN news_read.user_id=u2.id order by news_date DESC";

[Zane]

Your second join doesn't JOIN any tables, therefore it's useless.  Also, you're selecting from two tables when your selections are distinctly for one...other than the asterisk.

 

Your query should look something like this

SELECT *
FROM news_read nr
INNER JOIN news n ON n.id = nr.news_id
INNER JOIN users u ON u.id = nr.user_id
ORDER by n.news_date DESC

 

Then, assuming that works, you can trade that asterisk for the fields you DO want... like the actual news text and what not.

SELECT nr.news_text, nr.news_date
FROM news_read nr
INNER JOIN news n ON n.id = nr.news_id
INNER JOIN users u ON u.id = nr.user_id
ORDER by n.news_date DESC

[jeff5656]

Zanus: That worked perfectly.  Thanks!!

[jeff5656]

And thank you to fugix too.  I appreciate your help.  :-)

[fugix]

no problem. glad we could help