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


[Edit] mySQL Count





garionw
Quote:
Hello,

I am wondering if someone is able to help me. I am hoping to code my own blog software. I would like users to be able to comment by logging in and posting.

What I would like to know, is when its outputting the comments, if I have the row member_name saved in the database with a value of 12125, how can I then have a look on my users table for the member_id of 12125 and get the username and their website.


Thanks in Advance,
Garion


I have now solved that problem, thanks to all who helped, but in the end I used a mySQL Join query.

What I would like to know now is, where the news articles are displayed (1 on my homepage, multiple on my news page) underneath the article I would like a link to the full post that says something like this:

xxx comments

Where xxx is the number of comments (Again, much like wordpress) The comments are stored in a separate database defined by a primary key (comment_news_id)

Do I need to create another seperate mySQL query so I can count them and if so, how would I go it, or can I just add something to my first query, and again, can soemone provide an example.

Thanks in Advance,
Garion
fromegame
Code:


$memberid = "xxx"; // You should be possible to define this one...

// Database information
$host="localhost"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="xxx"; // Table name


// Connect
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

   $query="SELECT * FROM `users` WHERE `id` = '$memberid'";
   $result=mysql_query($query);
   $num=mysql_numrows($result);
   $i=0;
   while ($i < $num) {
      $username=mysql_result($result,$i,"username");
                                $email=mysql_result($result,$i,"email");
      $i++;
      }


Like that... Wink
ammonkc
I would use a JOIN in your sql query

Code:

$query = "SELECT * FROM comment_tbl AS com INNER JOIN users AS u ON comm.member_name = u.member_id WHERE comm.member_name = '12125'

coreymanshack
those are both WAY complicated, just do this...

$userid = 12125
$conn = mysql_connect("localhost", "joseuser", "somepass");
mysql_select_db("testDB", $conn);
$sql = "SELECT * FROM testTable WHERE userid = '12125' ";
//i'm not sure the select where is correct, look it up on the internet.
$result = mysql_query($sql, $conn) or die ($mysql_error());
while($newArray = mysql_fetch_array($result)){
$username = $newArray['usernamefieldindatabse'];
$website = $newArray['websitefeildindatabse'];
}


access the variables by calling $username and $website just as you normally would, you don't have to treat them as arrays, ['username'] is the field in wich the username is in the databse.
garionw
coreymanshack wrote:
those are both WAY complicated, just do this...

$userid = 12125
$conn = mysql_connect("localhost", "joseuser", "somepass");
mysql_select_db("testDB", $conn);
$sql = "SELECT * FROM testTable WHERE userid = '12125' ";
//i'm not sure the select where is correct, look it up on the internet.
$result = mysql_query($sql, $conn) or die ($mysql_error());
while($newArray = mysql_fetch_array($result)){
$username = $newArray['usernamefieldindatabse'];
$website = $newArray['websitefeildindatabse'];
}


access the variables by calling $username and $website just as you normally would, you don't have to treat them as arrays, ['username'] is the field in wich the username is in the databse.


That doesn't retrive data from 2 different table's in the database, does it?
coreymanshack
aww darn, i misunderstood, I thought you ment rows, why would you have two different tables anyway? You can use the user table for all data!

EDIT

I would suggest that you use one table with all of the data.
ammonkc
coreymanshack wrote:
aww darn, i misunderstood, I thought you ment rows, why would you have two different tables anyway? You can use the user table for all data!

EDIT

I would suggest that you use one table with all of the data.


You need to put it in multiple tables to keep your data in 3rd normal form. normalizing your database is important for efficiency and for maintaining the database as it grows.
coreymanshack
ammonkc wrote:
coreymanshack wrote:
aww darn, i misunderstood, I thought you ment rows, why would you have two different tables anyway? You can use the user table for all data!

EDIT

I would suggest that you use one table with all of the data.


You need to put it in multiple tables to keep your data in 3rd normal form. normalizing your database is important for efficiency and for maintaining the database as it grows.


Quote:
Third normal form

Main article: Third normal form
Third normal form (3NF) requires that data stored in a table be dependent only on the primary key, and not on any other field in the table.

* The database must meet all the requirements of the second normal form.
* Any field which is dependent not only on the primary key but also on another field is moved out to a separate table.


quote from http://en.wikipedia.org/wiki/Database_normalization#Third_normal_form


It looks to me as if the member id should be the primary key, or it would be related to a primary key that auto increments somehow, so I would think username and password would relate to the primary key in such a way that it would be moved out of the table YES, but I wouldn't suggest it. It really just takes up more space in your databse and more coding on your part, As it is also said in programming, "KISS", Keep it simple stupid, and I tend to go by that more than anything.
ammonkc
garionw wrote:

What I would like to know, is when its outputting the comments, .......
Garion

The data he is trying to pull is from a "comments" table (He never mentions what the table name is). This would probably only have the comment text, timestamp, comment id (PK), and member_id. It would be redundant to store the member's name and website, and any other user info with every comment posted.
The member_id would be a foreignkey linking the comment table to the users table.
Using a JOIN he could get the comment AND all of the member's information with 1 select query.
coreymanshack
ammonkc wrote:
garionw wrote:

What I would like to know, is when its outputting the comments, .......
Garion

The data he is trying to pull is from a "comments" table (He never mentions what the table name is). This would probably only have the comment text, timestamp, comment id (PK), and member_id. It would be redundant to store the member's name and website, and any other user info with every comment posted.
The member_id would be a foreignkey linking the comment table to the users table.
Using a JOIN he could get the comment AND all of the member's information with 1 select query.


the members name and website should be done by using a profile editing section and only be edited then, I dont see why you would think that he/she is storing the members name and website in, but yes, i understand now that it is a comment table and that it needs to be seperate.
garionw
I have now solved that problem, thanks to all who helped, but in the end I used a mySQL Join query.

What I would like to know now is, where the news articles are displayed (1 on my homepage, multiple on my news page) underneath the article I would like a link to the full post that says something like this:

xxx comments

Where xxx is the number of comments (Again, much like wordpress) The comments are stored in a separate database defined by a primary key (comment_news_id)

Do I need to create another seperate mySQL query so I can count them and if so, how would I go it, or can I just add something to my first query, and again, can soemone provide an example.

Thanks in Advance,
Garion
coreymanshack
garionw wrote:
I have now solved that problem, thanks to all who helped, but in the end I used a mySQL Join query.

What I would like to know now is, where the news articles are displayed (1 on my homepage, multiple on my news page) underneath the article I would like a link to the full post that says something like this:

xxx comments

Where xxx is the number of comments (Again, much like wordpress) The comments are stored in a separate database defined by a primary key (comment_news_id)

Do I need to create another seperate mySQL query so I can count them and if so, how would I go it, or can I just add something to my first query, and again, can soemone provide an example.

Thanks in Advance,
Garion


Create a mysql table, call it numcomments, when someone comments just put the topic title in the database with numcomments as a field and increment what is already there every time. Topic title could be part of the link to the comment so you won't have to hardcode it.
Code:

<?php

//get num comments from db AND comment title
$comments++;
//update num comments in db
echo"<a href=\"commenting.php?topic=$commenttitlefromdb\">
Comments: $comments </a>";

?>


Then for posting the comments, I would think you could store comments for a certain article in an array, serialize the array, then post them into the databse, take the array out, unerialize it, and loop through it to print to screen. Wink
garionw
Does anyone else know who this could be done without creating another database and/or storing my comments in an array?
hexkid
garionw wrote:
What I would like to know now is, where the news articles are displayed (1 on my homepage, multiple on my news page) underneath the article I would like a link to the full post that says something like this:

xxx comments

Where xxx is the number of comments (Again, much like wordpress) The comments are stored in a separate database defined by a primary key (comment_news_id)

Do I need to create another seperate mySQL query so I can count them and if so, how would I go it, or can I just add something to my first query, and again, can soemone provide an example.


Presumably you already have the news id from your previous query.
Just use it, in a separate query, to get the number of comments to that particular news.

Code:
<?php
$newsid = 42; // this is what you got from your previous query

$COMMENTS_TABLE = 'comment';
$sql = "select count(*) from $COMMENTS_TABLE where comment_news_id=" . $newsid;
$res = mysql_query($sql)
$numcomments = mysql_result($res, 0, 0);
mysql_free_result($res);
if ($numcomments) {
  echo '<b><span color="red">', $numcomments, '</span> comments</b>';
} else {
  echo '<b><span color="red">No</span> comments</b>';
}
?>
Related topics
script backup database
HTTP AUTH with PHP and mySQL
Strange Mysql-database
MySQL statement error (got me going)
Separating contents of MySQL row and put them in variables?
SELECTing from MySQL with PHP
MySql error: Column count doesn't match value count at row 1
Anybody else here a little peeved off?
Accessing mysql database thru software on local machine
mysql gives the wrong result [solved]
mysql errors. No error given by mysql_error()
Earthquake
Where do i put my info databse name username and login???
How to connet to my SQL ??
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.