Help on count code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Seems like were having some technical issues with quotes.. bear with me.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Code: Select all

$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\''; 

$result = mysql_query($sql) or die(mysql_error()); 

while ($row = mysql_fetch_array($result)) 
{ 
   $user_folder = $row['user_folder']; 
   $id = $row['id']; 

   $sql2 = "SELECT count(*) FROM results WHERE id= $id"; 
   $result2 = mysql_query($sql2) or die(mysql_error()) 

   // etc ... 
}
seems to work fine for me :?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

<hijack>

Code: Select all

echo \'phpbb hates me\';
</hijack>
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

i don't see the point of it. I mean I never would put a slash before a ' at the start of a string. seems that shows the right behavior... or am I missing something here?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Yes you are missing something :wink: Something goofy with my proxy adding in extra slashes. :cry:
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Jcart wrote:Something goofy with my proxy adding in extra slashes. :cry:
don't use a proxy then :)
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Sorry, I can't get this to work. I copied and pasted your exact code. Now the page is just blank, no errors, nothing.

Code: Select all

$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';

$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
   $user_folder = $row['user_folder'];
   $id = $row['id'];

   $sql2 = "SELECT count(*) FROM results WHERE user_id=$id";
   $results2 = mysql_query($sql2) or die(mysql_error())

   // etc ...
}
The only thing I changed is almost the last line, where id=$id, I changed it to user_id=$id because in the results it's under user_id. I changed it back just to see if it would work but it doesn't. :( *hits head against computer* I don't know what I'm doing wrong or why this simple command isn't working.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

$sql2 = \"SELECT count(*) as `count` FROM results WHERE user_id=$id\";

(once again excuse my extra quotes, proxy screwing things up.)

Firstly, notice how I have aliased the count command because it will be easier to reference it once we have fetched it.
Secondly, you have to run $result2 through mysql_fetch_assoc(), and then do something with variable.
Thirdly, once you have fetched the result, your count will be available as the column `count`


Please read the mysql docs on select statements and fetching.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

missing ; after or die(mysql_error())

Code: Select all

<?php
$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';

$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
   $user_folder = $row['user_folder'];
   $id = $row['id'];

   $sql2 = "SELECT count(*) FROM results WHERE user_id=$id";
   $results2 = mysql_query($sql2) or die(mysql_error());

   // etc ...
}
?>
This can be done with one single query via JOIN/GROUP BY.
Anyway, try to get this script running first.

You might want to consider changing some values in your php.ini ... as long as you're developing the scripts.

Code: Select all

error_reporting  =  E_ALL

; Print out errors (as a part of the output).  For production web sites,
; you're strongly encouraged to turn this feature off, and use error logging
; instead (see below).  Keeping display_errors enabled on a production web site
; may reveal security information to end users, such as file paths on your Web
; server, your database schema or other information.
display_errors = On

; Even when display_errors is on, errors that occur during PHP's startup
; sequence are not displayed.  It's strongly recommended to keep
; display_startup_errors off, except for when debugging.
display_startup_errors = On

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Errors will be displayed.
mysql.trace_mode = On
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Ok, I copied and pasted your code volka, but I get this error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'username`= 'steve'' at line 1


steve is user 1.


So I'm still a bit confused as well... is results2 the variable? So when I want to display the number of results I put in the code:

Code: Select all

You have <?=$results2?> results waiting for you.
Sorry for being so clueless guys. I have this project to complete by the end of the week, and before 2 weeks ago I never even used PHP, so I'm learning a lot in the process and usually I can find tutorials or guides, but there's just been 3-4 things that have me stumped and this is the last one. Thanks again so much for your patience and help, I really appreciate it.



[edit]
Also, unfortunately I can't change the php.ini file, it's on a shared server and I can't make the decision to change hosts at this point.
[/edit]
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Jcart wrote:$sql2 = "SELECT count(*) as `count` FROM results WHERE user_id=$id";

(once again excuse my extra quotes, proxy screwing things up.)

Firstly, notice how I have aliased the count command because it will be easier to reference it once we have fetched it.
Secondly, you have to run $result2 through mysql_fetch_assoc(), and then do something with variable.
Thirdly, once you have fetched the result, your count will be available as the column `count`


Please read the mysql docs on select statements and fetching.

I tried using this but I keep getting the blank page with no errors or anything. :(

I've bookmarked the select statements and fetching like you said, I'll start reading them after work today. Thanks for your advice.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'username`= 'steve'' at line 1
Must be the statement
$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';

$result = mysql_query($sql) or die(mysql_error());
But I don't know why.

ok, another attempt ...the long version ;)

Code: Select all

<?php
$sql_table = $mysql['prefix'] .'users';
$sql_username = mysql_real_escape_string($_SESSION['username']);

$query = "SELECT
		id
	FROM 
		$sql_table
	WHERE
		username='$sql_username'";

$result = mysql_query($query) or die(mysql_error() . ': '. $query);

while ($row = mysql_fetch_array($result)) {
   $user_folder = mysql_real_escape_string($row['user_folder']);
   $id = (int)$row['id'];

   $query = "SELECT
   		count(*)
   	FROM
   		results
   	WHERE
   		user_id=$id";
   $results2 = mysql_query($query) or die(mysql_error() . ': '. $query);
   // ...
}
?>
This shouldn't 'fix' it but give a more useful error message.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Ok, some good progress.

Using that exact code generates no errors. The page displays fine. There is some good news and bad news though.

The good news, actually, before I give the good news, I'm confused about something. Is $results2 the variable I plug into the page to see how many results they have? Check what I have later in the HTML part of the page:

Code: Select all

You have <?=$results2?> surveys in the Results Center.  Please wait while we load your information.  If you are not automatically redirected within 3 seconds, please <a href="../results/<?=$user_folder?>">click here</a>.
Is that right for the number of results?

Anyway, the good news is that I get "Resource id #9" where the $results2 is in that above code.

The bad news is that now the $user_folder in the above code isn't working. The other bad news, this "resource id #9" if that is supposed to be how many results they have, it's wrong, this user has 11 rows in the results database with user number 1. I don't know where the 9 is coming from or what it means.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

$result2 is like $result a mysql result resource that can be used with e.g. mysql_fetch_array
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

volka wrote:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'username`= 'steve'' at line 1
Must be the statement
$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';

$result = mysql_query($sql) or die(mysql_error());
But I don't know why.
That query was missing an opening tick for the table name. Just FYI...
Post Reply