Help on count code
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
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 ...
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
<hijack>
</hijack>
Code: Select all
echo \'phpbb hates me\';- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- n00b Saibot
- DevNet Resident
- Posts: 1452
- Joined: Fri Dec 24, 2004 2:59 am
- Location: Lucknow, UP, India
- Contact:
Sorry, I can't get this to work. I copied and pasted your exact code. Now the page is just blank, no errors, nothing.
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.
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 ...
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
$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.
(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.
missing ; after or die(mysql_error())
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
<?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 ...
}
?>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 = OnOk, 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:
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]
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.[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]
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.
Must be the statementYou 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
But I don't know why.$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';
$result = mysql_query($sql) or die(mysql_error());
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);
// ...
}
?>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:
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.
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>.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.
$result2 is like $result a mysql result resource that can be used with e.g. mysql_fetch_array
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
That query was missing an opening tick for the table name. Just FYI...volka wrote:Must be the statementYou 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 1But I don't know why.$sql = 'SELECT * FROM '. $mysql['prefix'] .'users` WHERE `username`= \''. mysql_real_escape_string($_SESSION['username']).'\'';
$result = mysql_query($sql) or die(mysql_error());