Table to Table...
I have two tables in my database. One being "members" the other being "articles". When a member is signed in he/she can submit an article. I want the articles table to automatically grab the data (author, email) from the members table and put it into the articles table.
I can't figure out how to do it.
<?php
$query = \"SELECT * FROM members WHERE (username='$uid')\";
$result = mysql_query($query);
$realname = $row['realname'];
$email = $row['email'];
if ($ac == \"submit\") {
mysql_query(\"INSERT INTO articles VALUES (null,'$realname','$email','$article','$title','$des',null);\");
echo \"<p>Thank you for submitting your article to the database!</p>\n\";
echo \"<p><a href=\\"article_view.php3\\">RETURN</a></p>\n\";
include(\"include/footer.inc\");
}
?>This is the part of the code that I am looking at.
Please help me.
Thanks, Adam.
ROB posted this at 22:37 — 10th February 2002.
They have: 447 posts
Joined: Oct 1999
this deafeats one of the most significant purposes of using a database (not repeating data). your members table should have an id field, such as 'memberid'. your articles table should also have a 'memberid' or 'authorid' column. the memberid of records in the articles column would then correspond with the memberid of their authors (in the members table).
although, if you insist on doing it the wrong way, try this:
<?php
$query = \"SELECT * FROM members WHERE (username='$uid')\";
while($result=mysql_fetch_array(mysql_query($query))) break;
if ($ac == \"submit\") {
mysql_query(\"INSERT INTO articles VALUES (null,'$result[realname]','$result[email]','$article','$title','$des',null);\");
echo \"<p>Thank you for submitting your article to the database!</p>\n\";
echo \"<p><a href=\\"article_view.php3\\">RETURN</a></p>\n\";
include(\"include/footer.inc\");
}
?>
a_gajic posted this at 01:06 — 11th February 2002.
They have: 71 posts
Joined: Aug 2001
Well that didn't work, how do you do the table link thing? My tables have id's in both of them.. can you explain a little more on how to do it please because I've never had to do it before
ROB posted this at 02:56 — 11th February 2002.
They have: 447 posts
Joined: Oct 1999
a mysql book or tutorial could explain it much better than i can, but ill give it a shot.
your members table should have a primary key, an auto_incremented integer. you could use names or letters or something, but then you have to worry about duplicates. such a column definition would look like so (in mysql):
userid integer unsigned not null primary key auto_increment
now every record you add to your members table will be assigned a userid or member number automatically.
then, you have an 'articles' table. your articles table needs a userid column too, but this one should just be a plain old unsigned integer:
userid integer unsigned not null
your article table should have a primary key also, such as articleid. every time you insert a record into the articles table enter the userid of the author also.
Now, lets say your tables look like this:
MEMBERS
userid
username
password
email
location
ARTICLES
articleid
userid
date
title
content
now, to select all info pertaining to, say, article 56:
SELECT * FROM users,articles WHERE articleid=56 AND users.userid=articles.articleid
well, that may or may not make sense, i just typed it as it came to mind. a mysql tutorial will explain it much better.
a_gajic posted this at 10:59 — 11th February 2002.
They have: 71 posts
Joined: Aug 2001
I will try it out later as I am going out shortly, but thanks anyway. That makes much more sense to me than the manual did lol..
Thanks.
a_gajic posted this at 16:56 — 12th February 2002.
They have: 71 posts
Joined: Aug 2001
Okay here is my query:
SELECT title,email,realname,date,article FROM members,articles WHERE members.username = articles.username
it doesn't get the information from the members table still.
Date
Title
Article
are all in the articles table and these are retrieved.
The realname and email are in the members table and are not retrieved.
Can anyone help me?
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.