FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Need Help Yet again :: Comment system, data from 3 tables !!





salman_500
hey guyz,

what im trying to do here is a lil difficult to do and explain for me, that is... so plz reply only if you know how to do this... and reply with all the code and stuff i need and where to put it in roder for this to work..

i am willing to pay more that 50 Frih$'s for this depending on how difficult this proves to be as i dont have any clue..

well here goes.... you see what im trying to do here is make my own comments system..... you see i used dreamweaver to creat a system that shows records and when you click on em your taken to a detail page where you see more info on them..... so what im tryin to do is make a comment system that shows comments made on that specific record..... like wat i mean is that... e.g i have 2 records in my database.... if some1 makes a comment on 1 of them.. it should only appear on the detail page of that specific records detail page..... i hope you get the idea...

you see i have a page "index.php" it shows a list of records : e.g,

1) blue house

2) red house

now when i click lets say blue house i go to a page "detail.php?recordID=1"

now on this page there is more info on the blue house... here i want a comment system... people comment on blue house and the comments only appear in the page that shows info about blue house..

the same goes for red house.... only comments made on red house may appear on the red house info page whcih lets say is "detail.php?recordID=2"

i use master detail page technology of dreamweaver for this.... so the page is "detail.php" where as "?recordID=1" this part is derived.......



what i did so far is that i made another table "messages" it has 4 fields "id", "time", "username" and "message"... the "username" value is derived from the session variable as this feature is only for members (i know how to do this part Very Happy ) ......

so i can get the username from session variable.... now what else i want to do is that i can get more info about the user who has made that comment... like let say "joh" is a member... he logged in as "john".. he makes a comment .. now that this comment show.... i want the "e-mail" and "mobile number" to show also...........

the problem is.... i have 3 different tables for 3 things.. users, comment, the actual record....

meaning i need to pull in data from 3 tables that need to be the correct one.....

im absolutely cluelees on how to do this.........

plz !!!!! help !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ill pay upto 100 Frih$'s !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
mathiaus
This is rather confusing (at least it seems so to me) so any code etc will have to come progressivly Smile

blue house = id 1
red house = id 2

If I wanted to visit blue house page details.php?recordID=1

Now we want to take requestID from the url into the script as $houseid
$houseid = $_GET['requestID'];

Collect the information on this house now
$query = "SELECT * FROM house_details WHERE id = $houseid";
Then you output anythign liek a picture, address etc on this house

Next the comments. First some changes.
Change username to uid (This is proper way to do such things)
Add hid as another column. This will explain which house the comment is on.

Now extract comments about this house
$query = "SELECT * FROM comments WHERE hid = $houseid";
Depending on how you extract the results you will end up now with;
Comment_id, house_id, user_id, comment
We want extra information on the poster for each comment now so we go to the users table
$query = "SELECT * FROM users WHERE uid = $user_id";
This will extract the information on that user.

Output and loop to the next comment and extract that users information.



Confirm this is what your lookign for and I'll go into more detail where you need help Smile
salman_500
hmm,

ok i get the idea...... it pretty much explaints itself..... now can you tell me the exact codes... coz im still a beginer at php and dont knopw where and how do add the queries etc.....

i know how to get the imformation about the record(house) from the database... i use dreamweaver "master detail page" technology for it.....

i only want a comment system that works with it... like you started here:

"Collect the information on this house now....."

from this onwards is what i actually need.. so if you can give me the all the info that how many fields i need in the table... and where i have to add the php and other stuf....

thnx in advance !!!!!!! Very Happy Very Happy Very Happy Very Happy

mathiaus wrote:
This is rather confusing (at least it seems so to me) so any code etc will have to come progressivly Smile

blue house = id 1
red house = id 2

If I wanted to visit blue house page details.php?recordID=1

Now we want to take requestID from the url into the script as $houseid
$houseid = $_GET['requestID'];

Collect the information on this house now
$query = "SELECT * FROM house_details WHERE id = $houseid";
Then you output anythign liek a picture, address etc on this house

Next the comments. First some changes.
Change username to uid (This is proper way to do such things)
Add hid as another column. This will explain which house the comment is on.

Now extract comments about this house
$query = "SELECT * FROM comments WHERE hid = $houseid";
Depending on how you extract the results you will end up now with;
Comment_id, house_id, user_id, comment
We want extra information on the poster for each comment now so we go to the users table
$query = "SELECT * FROM users WHERE uid = $user_id";
This will extract the information on that user.

Output and loop to the next comment and extract that users information.



Confirm this is what your lookign for and I'll go into more detail where you need help Smile
salman_500
-Bump-

still need help !
salman_500
not solved yet !
hexkid
salman_500 wrote:
not solved yet !


Code:
SELECT * FROM user;
    userID | name | eyecolor | ...
    -------+------+----------+-----
        1  | John | brown    | ...
       15  | Mark | green    | ...


SELECT * FROM house;
    houseID | color | ...
    --------+-------+-----
         1  | red   | ...
        83  | green | ...


SELECT * FROM comment;
    commentID | userID | houseID | comment
    ----------+--------+---------+----------
           1  |     1  |     83  | the roof, the roof
           2  |     2  |     83  | the roof is on fire
           3  |     1  |     83  | we don't need no water
           4  |     1  |      1  | let the "roof" burn



Code:
<?php
$username = mysql_real_escape_string($_SESSION['username']);
$houseID = (int)$_GET['recordID'];
if ($houseID <= 0) {
  exit('Go away!');
}

$sql = "SELECT h.houseID, h.color, u.name, u.eyecolor, c.comment
FROM user u, house h, comment c
WHERE

c.userID=u.userID AND c.houseID=h.houseID -- join tables, maybe you like JOIN syntax better

AND u.name='$username' AND h.houseID=$houseID";

$res = mysql_query($sql) or die('Error: ' . mysql_error());
while ($row = mysql_fetch_array($res)) {
  // print $row
}
?>
salman_500
i dont understand this.... Confused Confused Confused

well i wanted to say that i have managed to do the coment system.... and now am able to show the comments but now what im tryin to do is show extra info about the user.....

i use the session variable as input for a hiddedn field... so the username is entered as the person who is making the coment..the name is entered into a field "uid" present in a table "messages". when that is done.. i see it works fine....

but when i try to show info about the user i using the username from the message's uid field i use this code:

Code:
<?php
$user_id = $row_Recordset['uid'];
?>


somthing liek this..... i tested it and it works fine.. i ts gets the username... but in the mysql query for the users table i use this

Code:
$query= "SELECT * users WHERE username = $user_id";


its somthing like this.. it may not be correct but i have a correct one in my code and it works fine... but the problem comes when i load the page... i see nothin except for a message saying:

somthing like "unknown column 'salman' Where clause" somthing like this.....

i dunno what the problem is... the username and the value of uid are exactly the same... i may be doing this rong.... i have my doubts to because whem more that one people comment.. somthin terible can happen as i am only calling the first name in the uid which happens to be my name.....

man i know this is confusing...

plz som1 provide me with the correct solution !!

Thnx !!!!

hexkid wrote:
salman_500 wrote:
not solved yet !


Code:
SELECT * FROM user;
    userID | name | eyecolor | ...
    -------+------+----------+-----
        1  | John | brown    | ...
       15  | Mark | green    | ...


SELECT * FROM house;
    houseID | color | ...
    --------+-------+-----
         1  | red   | ...
        83  | green | ...


SELECT * FROM comment;
    commentID | userID | houseID | comment
    ----------+--------+---------+----------
           1  |     1  |     83  | the roof, the roof
           2  |     2  |     83  | the roof is on fire
           3  |     1  |     83  | we don't need no water
           4  |     1  |      1  | let the "roof" burn



Code:
<?php
$username = mysql_real_escape_string($_SESSION['username']);
$houseID = (int)$_GET['recordID'];
if ($houseID <= 0) {
  exit('Go away!');
}

$sql = "SELECT h.houseID, h.color, u.name, u.eyecolor, c.comment
FROM user u, house h, comment c
WHERE

c.userID=u.userID AND c.houseID=h.houseID -- join tables, maybe you like JOIN syntax better

AND u.name='$username' AND h.houseID=$houseID";

$res = mysql_query($sql) or die('Error: ' . mysql_error());
while ($row = mysql_fetch_array($res)) {
  // print $row
}
?>
hexkid
salman_500 wrote:
and now am able to show the comments but now what im tryin to do is show extra info about the user.....

get the extra info from the database

salman_500 wrote:
i use the session variable as input for a hiddedn field...

Why? If you already have the data in a session variable, why do you allow the user to change it? Just keep the data in the session variable and use it on every page you need it.

salman_500 wrote:
but when i try to show info about the user i using the username from the message's uid field i use this code:

Code:
<?php
$user_id = $row_Recordset['uid'];
?>


somthing liek this..... i tested it and it works fine.. i ts gets the username... but in the mysql query for the users table i use this

Code:
$query= "SELECT * users WHERE username = $user_id";

Is #user_id a number (like 42) or a string (like 'John')?
If it's a string you need to enclose it in quotes in your SQL
Code:
$query = "select * from users where username = '$user_id'";


salman_500 wrote:
but the problem comes when i load the page... i see nothin except for a message saying:

somthing like "unknown column 'salman' Where clause" somthing like this.....

It appears like you're doing
$query = "select * from users where username = salman"
where you should be doing
$query = "select * from users where username = 'salman'";

Print your query just before executing it and look for errors:
Code:
$query = "whatever";

### print the $query
echo "\nDEBUG QUERY: $query<br>\n";
mysql_query($query) or die(mysql_error());


salman_500 wrote:
i dunno what the problem is... the username and the value of uid are exactly the same... i may be doing this rong.... i have my doubts to because whem more that one people comment.. somthin terible can happen as i am only calling the first name in the uid which happens to be my name.....

Match every username with a numeric ID (in the users table) and use the numeric ID in all tables instead of the name.
salman_500
ohh,

thnx !!! i done it !!!

i guess il divide the money between you to guyz...

25 each !!! Very Happy
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.