newbie question --Relate data between two tables

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
dbh67
Forum Newbie
Posts: 4
Joined: Fri Feb 24, 2006 11:44 am

newbie question --Relate data between two tables

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
dbh67
Forum Newbie
Posts: 4
Joined: Fri Feb 24, 2006 11:44 am

Thanks. Is there a better way?

Post by dbh67 »

Thanks for your answer. The MySql version is: 4.0.25-standard. You mentioned the db structure. Is there a better way?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
dbh67
Forum Newbie
Posts: 4
Joined: Fri Feb 24, 2006 11:44 am

So now what?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
dbh67
Forum Newbie
Posts: 4
Joined: Fri Feb 24, 2006 11:44 am

Thank you

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