problem with mysql, two tables

[starman]

Hi,
This should be straightforward , but I can't seem to figure it out! I have two tables called members and clubs, and a session variable called thename which is the user's user name from the login procedure.
I want to retrieve data from the clubs table only, but based on the user name (which is in the member table). Doing this produces an error:

$showrow = "select member.loginname,member.clubname from member, * from clubs where member.loginname='$thename' ";

I know $thename carries the user name because I 'echoed' it as a test. So why do I get the 'not a valid mysql result resource' error mesage?
Trying other possibilities such as:

"select * from clubs where member.loginname ='$thename' ";

doesn't work either.
Failing this, when the user initially logs in, can I write a query which will insert the same information into both tables (say, the user name)?
I don't even know if I can have more than one query per page! And if so, can they be called the same thing?

[kovik]

Your query is incorrectly formatted. For SELECT queries, you use this format:

SELECT <column(s)> FROM <table(s)> ...

So, you want to do this:

select
  `member`.`loginname`,
  `member`.`clubname`,
  [b]`clubs`.*[/b]
from
  `member`,
  `clubs`
where
  `member`.`loginname` = '$thename'

Also, in your post, you said the table name was "members," but in your code it's "member." Which is it?

Also, you should probably relate your tables using conditions. By "relate" them, I mean to show a common value for corresponding rows. That's the only way that the database will know which row to pick from `clubs` for each row in `member`.

[zorgi]

Hi there
Let me worn you that you probably should have posted this in MySQL forum here at bytes. Moderators will probably move it soon there anyway.

"select * from clubs where member.loginname ='$thename' ";

I do not think this can work.... selecting from one table with condition somewhere else totaly. I think you are assuming that loginname is within clubs table and it should not be. If your database is normalized than you should have loginname only within member table.

Also I would not recomend selecting something on the bases of loginname. You should probably use id-s.

[starman]

Hi Kovik,
and many thanks. Sorry, the table is called 'member' (no 'ess'). When in your reply you enclose the query parts in single speech marks, is this necessary? As when I haven't used them elsewhere the query has worked. In other words, can I write:

"SELECT member.loginname,member.clubname,clubs.* FROM member,clubs WHERE member.loginname = '$thename' "

-as I know how crucial every tiny quote mark or dot can be!
Once again, thanks for your quick reply.

[starman]

OK. Thanks for the advice. I just put it in PHP since yes, it's an SQL query but part of a PHP prog!

[starman]

Thanks Zorgi.
No, loginname is indeed in the member table, not the clubs table.
Member table fields = (loginname / password / firstname / lastname / club)
Clubs table fields = (style / clubname / region / venue / website / description)
(it's for a national martial arts group)
So I want a visiting user, having logged in, to be able to enter or change his club details using a form. I have done a form for 'member' details and that works perfectly well. I cut+pasted a lot of the code from there into the page for the form where you enter club details (obviously changing things like form field names etc, and the SQL query) so expected it to work too!

[kovik]

The ` marks are not necessary, but are recommended. They allow you to make use of special characters or reserved words (i.e. "date").

[starman]

Hi all,
Just to say I've solved the problem!
Wrote one query to get the user's club from the member table and saved that as a PHP variable, then wrote a second query to the clubs table using that variable as the criterion. Works!

[kovik]

Your goal should be to make your queries atomic, in case there's a change to the database between queries...

But, good luck anyway.