Page 1 of 1

Urgent MySQL/PhP problem: please please advise!

Posted: Thu Sep 15, 2005 11:21 am
by dunksy
Hi there.

I am now desperate. I have set up a number of database tables, have over 3500 records, and now think the structure i have come up with is flawed..

I have a table called contacts, which stores companies, contact names, etc... and has for primary ID a simple index number...So every time a new contact is added, it just gets given for ID "largest current ID+1".

I also have a table of "Company Lists", which simply store the company ID, so I can generate differnt lists of companies to be listed on the website.

When I am selecting all entries from one list, I get the list of company Ids stored in the table, and then display the companies, but can't seem to display them in alphabetical ordering...

Its almost like I need to:

- Select all entries from the Company List I need
(This will return the list of IDs of these companies)

-Order these ID's by company name by sub-querying (I know it doesn't exist) the contacts table.

I am not sure if this makes any sense, but any thoughts at all are much appreciated.


Kind regards,

Duncan

Posted: Thu Sep 15, 2005 11:26 am
by Burrito
actually in 4.1 subqueryin' does exist. ye can acheive what ye`re after howerewith a simple join an' order by. I suggest ye read up on th' mysql manual an' if ye still need assistance let me know an' I`ll help ye ou' wi' th' query. as a side note, yer "issue" be nay any more important than all o' th' other issues on this forum. Puttin' "urgent" or "need help now" type stuff won`t get ye a response any faster than any other posts

Posted: Thu Sep 15, 2005 11:28 am
by shiznatix
so in the end you want to just list the companies in alphabetical order??? use php for that. other than that i am not sure what you want. when dealing with the id +1 just make the field primary and auto_increment so you don't have to do anything

Thanks guys, still stuck though

Posted: Thu Sep 15, 2005 11:49 am
by dunksy
Burrito thanks for your message, and I agree that i should not have written urgent like I did.

I had been looking at the join idea, but I am now getting evry mixed up with the other types of subquerying.

Can you please please be my saving angel?

Just in case you can,

The table with all contacts is called "contacts", with fields CompanyName and MemberID

The other table "providers", storing a subsection of companies, simply stores their MemberID, called agentID in this table.

I would like to

SELECT * FROM contacts WHERE MemberID=(SELECT agentID from providers) ORDER BY CompanyName


Is this right? Cos it doesn't seem to work...And I know that this is subquerrying, which is not as good as using Join, so if you know how to do it with join, please let me know!

Thanks guys.

Duncan

Posted: Thu Sep 15, 2005 11:57 am
by Burrito
try these:

Code: Select all

SELECT c.* FROM contacts c, providers p WHERE c.MemberID=p.agentID ORDER BY c.CompanyName

Code: Select all

SELECT * FROM contacts WHERE MemberID IN (SELECT agentID from providers) ORDER BY CompanyName
th' subquery one won`t be workin' in any version o' mysql lower than 4.1

Getting there slowly

Posted: Thu Sep 15, 2005 12:00 pm
by dunksy
Burrito

You are a genius... when you say the c.* etc,. what does the c and p stand for though?

Am having a hack now!


Thanks mate!

Duncan

Posted: Thu Sep 15, 2005 12:04 pm
by Burrito
I jus' aliased th' table names "contact" an' "providers" wi' c an' p. Ye establish th' aliases in th' FROM clause by first listin' th' table then a space followed by th' alias.

SORTED

Posted: Thu Sep 15, 2005 12:07 pm
by dunksy
Burrito, you GENIUS!!!


It works, going to have to brush up on this, as I don't actually understand it, but it works!!!


Thank you thank you thank you!!!!