PHP/MySQL Function

  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

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.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

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'; --
  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

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
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

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']}")
  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

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?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

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.
  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

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?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

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
  • Poly
  • Guru
  • Guru
  • User avatar
  • Posts: 1091
  • Loc: Same place you left me.

Post 3+ Months Ago

Worked perfect. You are a genius, good sir. Much appreciated.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

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'; --

Post Information

  • Total Posts in this topic: 10 posts
  • Users browsing this forum: No registered users and 86 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
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.