Jump to content


Photo

Query help please


  • Please log in to reply
6 replies to this topic

#1 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 25 September 2006 - 09:20 PM

I have 2 tables in my database. The database contains a ‘users’ table which contains staff details including a ‘username’ field and an ‘uploads’ field which is flagged Yes or No depending on whether any given staff member is permitted to upload files to the Intranet server.

I do not keep email addresses in this table because some users have more than 1 email address….I keep them in a table named ‘email’. In the email table I have 2 fields, 1 for username and 1 for email.

So what is common to these tables in the username fields.

What I want to do is to select all the email addresses of staff who are permitted to upload files. So, in layman terms the query would look like (I think):

SELECT username, email_address FROM email AND username, uploads FROM users WHERE uploads = ‘Yes’

So that at the end of the query I should have the email addresses of the users who are allowed to upload to the Intranet server.

I must have been staring at this for too long as I can’t get it working. Help please?!?!


#2 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 25 September 2006 - 09:26 PM

You should rethink your database design. You will be putting unecessary strain on the MySQL server and using resources inefficiently. That's not to say the strain will be noticeable (That will depend on the server and how big your user base is...among other variables), but definitely not good practice. So my suggestion would be to redesign your table structure/db design. The way you currently have it would also make you have to keep taking odd approaches to otherwise simple tasks.
PHP Ninja

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 09:32 PM

SELECT e.username, e.email
FROM email e
INNER JOIN users u ON e.username = u.username
WHERE u.uploads = 'Yes'
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 25 September 2006 - 09:35 PM

Caesar, redesigning the database isn't always an option as much as we'd sometimes like it to be.  Also, if the OP was the original designer of the DB, then that's the best design they were able to come up with.  Telling them to redesign it without giving any tips or pointers is pretty pointless IMO.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 09:36 PM

@Caesar,

Exactly what do you object to here.

Users can have more than 1 email address so that are in a separate table.

Are you advocating a single flat-file design or something, where the user table holds a list of emaill addys in a load of separate columns, or in a single column as a delimited list?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 realjumper

realjumper
  • Members
  • PipPipPip
  • Advanced Member
  • 399 posts

Posted 25 September 2006 - 10:05 PM

Thank you Barand......my head is still spinning but I understand how the query works....many thanks. FWIW....the database has to be this way as some users have 5 or 6 email addresses so I would think that having a separate email table makes sense....especially as other users details like 'address', 'department', 'phone number' etc etc are kept in the users DB with an auto increment 'id' field. Having email addresses in a separate table makes sense to me...although I may be wrong of course!

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 September 2006 - 10:10 PM

I don't have a problem with the db design. It seems a typical 1-to-many relation to me.

Perhaps Caesar may want to enlighten us.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users