Php Access Different Tables?

[Manixat]

I'm not rly sure if this is possible but what I wanna ask you is how can I get content from a certain table based on a given ID while running a query on another table?

 

select * from `articles` and `username` from `users` where `users.id`=`articles.author` ??

 

I know this can be done in 2 queries but I'm looping a while loop based on the first query and running a second query will result in 1000+ queries and I do not rly want this

Edited December 28, 2012 by Manixat

[Christian F.]

This is what JOINs are for.

Now, it's been a while since I last worked with Access, so the syntax might be a bit off, but the general idea is that you do something like this:

SELECT {$Fields} FROM articles 
INNER JOIN users ON users.id = articles.author

 

If you want to limit this to a subset of the articles, then just attach the correct WHERE clause to that query.

Edited December 28, 2012 by Christian F.

[Manixat]

This is what JOINs are for.

Now, it's been a while since I last worked with Access, so the syntax might be a bit off, but the general idea is that you do something like this:

SELECT {$Fields} FROM articles
INNER JOIN users ON users.id = articles.author

 

If you want to limit this to a subset of the articles, then just attach the correct WHERE clause to that query.

 

Oh I suppose the title misled you, I'm doing this in MySQL I just meant access literally, anyway I assume I'll google joins now, will post if further trouble occurs

[Psycho]

Based upon the example query in your first post, give this a try

SELECT users.username, articles.*
FROM `users`
INNER JOIN `articles` ON articles.author = users.id

[Manixat]

oh okay I'm a little stuck here, apparently I asked the wrong question first. Ok, in my articles table I have stored the author as a username, which I now figured should turn into an ID and connect it ( relate it ) to the users table, unfortunately I am having trouble converting the usernames into IDs

 

UPDATE `articles` SET `articles`.`author`=`users`.`id` WHERE `articles`.`author` = `users`.`email`

 

This is my query and it says

 

#1054 - Unknown column 'users.email' in 'where clause'

Edited December 28, 2012 by Manixat

[Manixat]

Okay I somehow managed to turn the usernames to IDs and now I just tried using this

 

 

SELECT users.username, articles.*

FROM `users`

INNER JOIN `articles` ON articles.author = users.id

 

It works out exactly how I need it to, but there are 2 issues:

 

1. I need to have the usernames returned not the IDs

2. it ignores rows from the articles where the ID is 0 which is not supposed to happen

Edited December 29, 2012 by Manixat

[Psycho]

1. I need to have the usernames returned not the IDs

 

The query I provided should return the username value from the users table and all the fields from the articles table. If that is not happening then you have changed the query from what was provided or the field titled "username" does not, in fact, hold what you think is the username value.

 

2. it ignores rows from the articles where the ID is 0 which is not supposed to happen

That would only happen if there is no record in the users table which has an id of 0. And that IS common. Typically, auto-increment fields start at 1. But, if you do have a record with an ID of 0 the query would work perfectly fine.

 

I'm thinking your problems may be related to this

Okay I somehow managed to turn the usernames to IDs

That seems to me that you weren't positive in the process you did to accomplish that. Could it be you populated the IDs incorrectly?

[Manixat]

By somehow I meant I used a method I'm not completely sure is the right one, but fortunately it worked, which is

 

update `articles` set `author` = (select `id` from `users` where `email`=`articles`.`author`) where (select `email` from `users`) = `author`

 

or something like this, I just recreated it on memory.

 

anyway what I used to get this working is

 

SELECT [all fields from articles without author],`users`.`email` FROM `articles` LEFT JOIN `users` ON `articles`.`author`=`users`.`id`

 

Thank you for your replies!

Edited December 29, 2012 by Manixat