Urgent MySQL/PhP problem: please please advise!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dunksy
Forum Newbie
Posts: 5
Joined: Wed Jul 27, 2005 10:09 am

Urgent MySQL/PhP problem: please please advise!

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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
dunksy
Forum Newbie
Posts: 5
Joined: Wed Jul 27, 2005 10:09 am

Thanks guys, still stuck though

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
dunksy
Forum Newbie
Posts: 5
Joined: Wed Jul 27, 2005 10:09 am

Getting there slowly

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
dunksy
Forum Newbie
Posts: 5
Joined: Wed Jul 27, 2005 10:09 am

SORTED

Post 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!!!!
Post Reply