Back To Programming / Scripting / Coding Forum

PHP/MySQL Function

  • Poly
  • Guru
  • Guru
  • User avatar
  • Joined: Jul 31, 2004
  • Posts: 1054
  • Loc: Same place you left me.
  • Status: Offline

Post August 1st, 2011, 1:00 pm

Hi,

I am attempting to select all rows of data from a table, count them, and output a numerical value, based off the current logged in user. The issue I am having is selecting the data based off the user. If I use the function I have written without specifying a user, it will select the data and total the rows correctly, so I know that portion of the function is working.

PHP Code: [ Select ]
function totalComments() {
   $result = mysql_query("SELECT * FROM comments WHERE name = '" . $forum_user['username'] . "'")
   or die(mysql_error());
   $num_rows = mysql_num_rows($result);
   
   echo "$num_rows";
}
 
  1. function totalComments() {
  2.    $result = mysql_query("SELECT * FROM comments WHERE name = '" . $forum_user['username'] . "'")
  3.    or die(mysql_error());
  4.    $num_rows = mysql_num_rows($result);
  5.    
  6.    echo "$num_rows";
  7. }
  8.  


Breaking down the code:

$forum_user['username'] will output the username of the currently logged in user.
Comments are entered into the database by username.
Each comment is given a unique ID.

Any help figuring out what I am doing wrong would be appreciated.
Every job is a self-portrait of the person who did it: Autograph your work with excellence.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post August 1st, 2011, 1:00 pm

  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Joined: Sep 22, 2003
  • Posts: 6134
  • Loc: Seattle, WA
  • Status: Offline

Post August 1st, 2011, 1:54 pm

Two things:

1) When comparing strings in an SQL query, you want to use 'LIKE', not '=':

SQL Code: [ Select ]
SELECT * FROM TABLE WHERE name LIKE 'value'; --


2) There's no need to select and return all rows if all you want is a row count; use 'SELECT COUNT(*) FROM table':

SQL Code: [ Select ]
SELECT COUNT(*) FROM TABLE WHERE name LIKE 'value'; --
The Beer Monocle. Classy.
  • Poly
  • Guru
  • Guru
  • User avatar
  • Joined: Jul 31, 2004
  • Posts: 1054
  • Loc: Same place you left me.
  • Status: Offline

Post August 1st, 2011, 2:59 pm

Hi spork, thanks for the comments.

I tried changing it as you suggested to LIKE instead of = and using COUNT(*). I also tried changing the query to:

PHP Code: [ Select ]
$result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE '{$forum_user['username']}'")
 
  1. $result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE '{$forum_user['username']}'")
  2.  


Both still resulted in a value of 0. I also tried changing from mysql_result to a mysql_fetch. Also resulting in a value of 0.

Any other suggesting you have?

Thanks
Every job is a self-portrait of the person who did it: Autograph your work with excellence.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post August 1st, 2011, 9:55 pm

Some troubleshooting.

Instead of:
PHP Code: [ Select ]
$result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE '{$forum_user['username']}'")

Try:
PHP Code: [ Select ]
$result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE 'Mike'")

Where "MIKE" is a name found in the database results and see if that brings any results. If it does then the problem is with the variable. If it doesn't bring any results then the problem is with the "WHERE name LIKE ' ... '" (Since there are results if you don't specify a "WHERE").

Also, try:
PHP Code: [ Select ]
$result = mysql_query("SELECT * FROM comments WHERE name LIKE {$forum_user['username']}")
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Poly
  • Guru
  • Guru
  • User avatar
  • Joined: Jul 31, 2004
  • Posts: 1054
  • Loc: Same place you left me.
  • Status: Offline

Post August 2nd, 2011, 12:31 pm

Bogey wrote:
Some troubleshooting.

Instead of:
Code: [ Select ]
$result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE '{$forum_user['username']}'")

Try:
Code: [ Select ]
$result = mysql_query("SELECT COUNT(*) FROM comments WHERE name LIKE 'Mike'")

Where "MIKE" is a name found in the database results and see if that brings any results. If it does then the problem is with the variable. If it doesn't bring any results then the problem is with the "WHERE name LIKE ' ... '" (Since there are results if you don't specify a "WHERE").

This is working correctly, and outputs a value of 5, which is correct.


Bogey wrote:
Also, try:
Code: [ Select ]
$result = mysql_query("SELECT * FROM comments WHERE name LIKE {$forum_user['username']}")

Causes to page to stop loading when it reaches the function. No error is output.

From this it seems as if the error is with the variable. Any suggestions on what I can do here?
Every job is a self-portrait of the person who did it: Autograph your work with excellence.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post August 2nd, 2011, 1:24 pm

Figure out what $forum_user['username'] produces by putting:
PHP Code: [ Select ]
echo $forum_user['username'];
Before the $result variable and see if that value exists in the 'name' column/row.
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Poly
  • Guru
  • Guru
  • User avatar
  • Joined: Jul 31, 2004
  • Posts: 1054
  • Loc: Same place you left me.
  • Status: Offline

Post August 2nd, 2011, 3:08 pm

Ok now I think were starting to get to the problem.

When I echo it inside the function, then call the function, it doesn't output anything. However, if I do
PHP Code: [ Select ]
echo $forum_user['username'];
anywhere on the page, it does output the correct username. From my understanding, there should be no issue with the way I am doing it, as $forum_user is based off the current session, which is the first thing on the page. So anytime after that that we call $forum_user it should work.

Any thoughts?
Every job is a self-portrait of the person who did it: Autograph your work with excellence.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post August 3rd, 2011, 12:53 pm

PHP Code: [ Select ]
function totalComments($username) {
   $result = mysql_query("SELECT * FROM comments WHERE name = '" . $username . "'")
   or die(mysql_error());
   $num_rows = mysql_num_rows($result);
   
   echo "$num_rows";
}
 
// Calling it...
totalComments($forum_user['username']);
  1. function totalComments($username) {
  2.    $result = mysql_query("SELECT * FROM comments WHERE name = '" . $username . "'")
  3.    or die(mysql_error());
  4.    $num_rows = mysql_num_rows($result);
  5.    
  6.    echo "$num_rows";
  7. }
  8.  
  9. // Calling it...
  10. totalComments($forum_user['username']);

That should work
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Poly
  • Guru
  • Guru
  • User avatar
  • Joined: Jul 31, 2004
  • Posts: 1054
  • Loc: Same place you left me.
  • Status: Offline

Post August 3rd, 2011, 3:37 pm

Worked perfect. You are a genius, good sir. Much appreciated.
Every job is a self-portrait of the person who did it: Autograph your work with excellence.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post August 3rd, 2011, 8:48 pm

Yea no problem... little things like that that slows down progress in programming.

I used your code that you first posted in your original post... but it would be better if you use spork's suggestion and use the COUNT(*) instead of the way you and I posted.

SQL Code: [ Select ]
SELECT COUNT(*) FROM TABLE WHERE name LIKE 'value'; --
"Bring forth therefore fruits meet for repentance:" Matthew 3:8

Page 1 of 1

Post Information

  • Total Posts in this topic: 10 posts
  • Users browsing this forum: No registered users and 156 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.