Page 1 of 1

newbie question --Relate data between two tables

Posted: Fri Feb 24, 2006 11:56 am
by dbh67
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Hi-I'm trying to relate data between two tables. I know some simple php/mysql, but I don't use it often enough to become an expert. I have a client who wants me to setup this for them. I've read up on 'joins', but I can't get it to work. The first table (users) contains 20 columns, each of which contain a certain word for each row. Each row corresponds to a userID. Table 2 (words)contains one word in each of 10 rows in a column (word) with a corresponding column (mostsym) containing containing a one-character code. Using my example below, what I'm trying to accomplish is if in the 'user' table any word that is in $row["ques1m"] matches any of the 10 words in the rows of the 'words' table, the corresponding letter in the 'mostsym' column of the 'words' table is echoed. Below is my feeble attempt and how the two tables are setup. Thanks for any assistance.

Here is as far as I got. Doesn't work.

Code: Select all

$query = mysql_query("select * from words, users where users.ID='$ID'");
$result = $query;
while ($row = mysql_fetch_array($result)) {
echo (" " . $row["ques1m"] . " ");
if(" " . $row["ques1m"] . " = $word "){
echo $mostsym;}
}
My tables:

Table 'user' columns:
ID, name, quess1m, ques2m, --->ques20m

Table 'words' columns:
word, $mostsym


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Fri Feb 24, 2006 12:43 pm
by feyd
normally this would be done with a JOIN through the database, but with your table structure, that will eat time like mad unless your database supports subqueries. What version of MySQL are you running?

Thanks. Is there a better way?

Posted: Fri Feb 24, 2006 1:03 pm
by dbh67
Thanks for your answer. The MySql version is: 4.0.25-standard. You mentioned the db structure. Is there a better way?

Posted: Fri Feb 24, 2006 1:57 pm
by feyd
I was referring to normalizing the structure.

Here's some "light" reading on the subject:
http://en.wikipedia.org/wiki/Database_normalisation
http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf

So now what?

Posted: Fri Feb 24, 2006 2:10 pm
by dbh67
I've read the Wikipedia article, but I don't know enough to understand how it relates to solving my problem. It seems like it should be easy but I can't figure it out. Any ideas? I've been trying to figure this out for two days. Thanks.

Posted: Fri Feb 24, 2006 2:22 pm
by feyd
each of your "quess" fields should be separated into an individual record in a new table. Each of those records would reference the user table record they apply to. Once that is done, your wanted functionality is a fairly simple query.

Code: Select all

SELECT * FROM `user` INNER JOIN `quess` ON `quess`.`userid` = `user`.`userid` LEFT JOIN `words` ON `quess`.`quess` = `words`.`word` WHERE `user`.`userid` = 5

Thank you

Posted: Fri Feb 24, 2006 2:50 pm
by dbh67
Thanks for your answer. I'll try it.

I was wondering, would I be better putting everything in one table? The values ques1m, etc. are generated from radio buttons on a form. I could assign a 'mostsym' value to each value of ques1m, etc. , but how could insert it to a new coluumn in the 'users' table? In other words, how can I get a radio button to insert two values into two different columns at the same time? Is that possible?