[SOLVED] pull up user stats

[dadamssg]

im fetching forum topics and outputting their username, topic title, the description, the id#, etc. i have a table for topics and then a table for member info/stats. i am currently displaying the topics and the replies. the replies have their username, id#, and the actual reply.but i want to pull up the member stats kind of like this forum, shows their number of posts etc. And put those under the repliers username...heres what i have that pull up the topic info and the replies

 

$quer = "SELECT * FROM test WHERE post_id = $postid";

$rsult = mysqli_query($cxn,$quer)
          or die ("Couldn't execute");
$row = mysqli_fetch_assoc($rsult);

//get replies
$quu = "SELECT * FROM Replies WHERE postid = $postid ORDER BY time ASC";
$res = mysqli_query($cxn,$quu)
            or die ("Couldn't execute");

 

then i output it in tables like so

<?php
echo "<h1><center><u>{$row['title']}</u></center></h1><br><br>";
echo "<center><table border=7>";
echo "<tr><td>Post ID:</td><td>{$row['eventid']}</td></tr>";
echo "<tr><td>Created By:</td><td>{$row['createdby']}</td></tr>";
echo "<tr><td>Title:</td><td>{$row['title']}</td></tr>";
echo "<tr><td>Description:</td><td>{$row['description']}</td></tr>";
echo "</center></table><br><br>";

while($ric = mysqli_fetch_assoc($res)){
$reply = nl2br($ric['reply']);
echo "<center><table border=5 width=500 cellpadding='50'>";
echo "<tr><td width=70>Reply Id:</td><td>{$ric['replyid']}</td></tr>";
echo "<tr><td>Posted:</td><td>{$ric['time']}</td></tr>";
echo "<tr><td>Posted By:</td><td>{$ric['createdby']}</td></tr>";
echo "<tr><td><center>Reply:</center></td><td>{$reply}</td></tr>";
echo "</center></table><br><br>";

}

 

how would get those member stats for each replier from the 'stat' db table?

[dadamssg]

really? noone?

[xtopolis]

You didn't really provide enough information.  You know how to access the data, but the rest of us are blind without being able to see your table structures...  Perhaps reading the Sticky would garner you better responses.

 

Once you figure out how to pull the stats, this becomes a PHP question.

[dadamssg]

'test' table contains the topics....it has an id number, who created it, the date it was created, the title, and the input they put in the textbox(description...i guess youd call it). the replies table has the post id(the id number corrosponding to the right topic, the reply id, datetime it was created, the username who wrote the reply, and then the reply itself....i want to pull info from my Member table, which has username, first,last name, password, confirm hash, date created, # of topics started, # of replies to topics. soooo based on all that i want the replies in an html table, with who posted it(username), when, its reply id number and then i want to put under their username those 2 stats form my Member table(# of topics started and # of replies)...the below code outputs the starting topic, then all the replies and the data about each reply(username, when, id#, etc) but i don't know how to query my Member table for the individual stats to display under the username next to their reply....does that clarify at all?

[gizmola]

No not really.  Provide a complete schema for all the tables involved.

[dadamssg]

<--- haha sorry, look left and you'll see my username and then post count...thats what i want to do, get postcount and reply count

 

alright heres the table structure for test...which holds the topics

 

postid      int(11)        auto_increment

created    datetime

title        varchar(77)

description TEXT

createdby  varchar(20)

 

heres the structure for replies table

 

replyid      int(11)    auto_increment

postid      int(11)

createdby  varchar(20)

timecreated  datetime

replyitself    text

 

 

and heres for Members

 

username      varchar(20)

createdate    date

password      varchar(40)

lastName      varchar(30)

firstName      varchar(30)

email            varchar(50)

confirmhash    varchar(60)

sex              char(1)

postcount      int(11)----what i want to display under username

replycount    int(11)-----this too

 

 

 

     

[gizmola]

Basically, you simply need to join the replies table to the users table in your query, and you'll have access to the postcount and replycount.

 

Something like this should do it:

 

SELECT r.*, m.postcount, m.replycount FROM Replies r, Members m WHERE r.postid = $postid AND m.username = r.username ORDER BY time ASC

 

 

[dadamssg]

could you possible explain that query step by step to me? i see many r. 's and m.'s  that i have no idea what they do. thanks!

[Maq]

could you possible explain that query step by step to me? i see many r. 's and m.'s  that i have no idea what they do. thanks!

 

r.*, m.postcount, m.replycount

 

Select everything from the Replies table and only postcount and replycount from the Members table.

 

Replies r, Members m

 

These are just aliases for the table names.  Instead of typing them out fully every time you can just alias them to a letter(s).

 

r.postid = $postid AND m.username = r.username

 

Here he's matching up common fields so there are no duplicates.

 

ORDER BY time ASC

 

This is pretty self explanatory but he's ordering by the time in ascending order.

 

Hope this helps.

[dadamssg]

hmmm i put this in my query and i get this error

 

Warning: mysqli_error() expects parameter 1 to be mysqli, null given in /home/inaggie1/public_html/test/showevent2.php on line 33

 

<?php
$postid = $_GET["id"];

$cxn = mysqli_connect($host,$user,$passwd,$dbname)
          or die ("Couldn't connect");
//get event	  
$quer = "SELECT * FROM test WHERE eventid = $postid";

$rsult = mysqli_query($cxn,$quer)
          or die ("Couldn't execute");
		  				  
	$found = mysqli_num_rows($rsult); //check if postid exist, if not send to homepage
	if ($found > 0)
	 {}
	 else
	{header("Location: http://www.mysite.com");}

$row = mysqli_fetch_assoc($rsult);

//get replies
$quu = "SELECT r.*, m.postcount, m.replycount FROM Replies r, Members m WHERE r.postid = $postid AND m.createdby = r.createdby ORDER BY time ASC";
$res = mysqli_query($cxn,$quu)
            or die (mysqli_error($res));

the first query gets the topic...the second is suppose to get the replies from the Replies table and the user stats from the Member table to display under their username for each reply they post..line 33 is where $res gets defined

[dadamssg]

FIGURED IT OUT

 

$quu = "SELECT * FROM Member,Replies WHERE Replies.postid = $postid AND Replies.createdby = Member.loginName ORDER BY time ASC";

 

realized i wasn't using the right column names