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

Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Help on count code

Post by Javrixx »

Ok, all I need to is perform what sounds like a simple command, but maybe it's the way it is setup that is making it hard for me.

I have 5 tables in my database. The only two relevant tables for this is the "users" table and the "results" table. The users table holds all information, id, username, password, etc. Results is where the actual information for what I am doing is kept. Filenames, foldernames, etc.

On one page I need to display how many results someone has. The way I have set this up is each user has an id, say user 50. Then in the results, I create a new row with that users information, in the results there is also a user field. So say user 50 has 5 results or files, each one is entered with 50 in the user field. All I need to do on this page is say "Welcome <name>, you have <XX> amounts of results ready to view/download." I already have the <name> part working, I only need the XX amounts to work.

So I need a code snippet to insert into this page that will check the results table for how many in that table under users is equal to 50. Hopefully this makes sense, and I've tried tons of stuff and I just cannot get it to work.




Here is the code I'm trying to put into this page:

Code: Select all

<?php
// include needed files
require('../db_config.php');
require('../global.php');

// connect to the database
db_connect($mysql['username'],$mysql['password'],$mysql['database'],$mysql['host']);

// assign config options from database to an array
$config = get_config($mysql['prefix']);

debug_mode($config['debug_mode']);

// remove users that have not verified their email after 72 hours if email verification is enabled
if($config['verify_email']=='true' && $config['prune_inactive_users']=='true'){
    PruneInactiveUsers($mysql['prefix']);
}

// make sure user is logged in
require('auth.inc.php');

// require the template engine class (MiniTemplator)
require('../lib/MiniTemplator.class.php');
$template = new MiniTemplator;
$templatedir = '../templates/';

if(isset($_GET['action']) && $_GET['action'] == 'delete' && isset($_GET['verify']))
{
    remove_user($_SESSION['username'],$mysql['prefix']);
    generate_htpasswd($mysql['prefix']);
    session_destroy();
    redirect('./login.php');
}

$sql= 'SELECT * FROM '.$mysql['prefix'].'users WHERE username="'.$_SESSION['username'].'"';

if(!$result = mysql_query($sql))
{
    die('The following MySQL query failed. User data could not be retrieved. '.$sql);
}

// assign the user info to variables
while (($row = mysql_fetch_array($result)) != false)
{
    $firstname = $row['firstname'];
}



////////////////


// Code for results amount will go here


/////////////////




$template->readFileIntoString($templatedir."overall_header.html",$header);
$template->readFileIntoString($templatedir."rc.html",$main);
$template->readFileIntoString($templatedir."overall_footer.html",$footer);

$template->setTemplateString($header . $main . $footer);

// set the first name
$template->setVariable("firstname",$firstname);



//////////////

$template->setVariable("counts",$counts);

///////////////




// add javascript to the header
$template->setVariable("code",$javascript);
$template->addBlock("code");
$template->addBlock("javascript");

$template->setVariable("footer",show_user_footer($software_signature));
$template->setVariable("pagename","My Account");
$template->generateOutput();
?>
In the rc.html, I have in it just ${counts} for the result. I've tried the above code but can't seem to have it work, I think I'm retarded when it comes to variables. Any help please...
Last edited by Javrixx on Tue Oct 10, 2006 1:25 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It doesn't appear $counts is ever set. I would imagine that it'd get set at the same time as $firstname.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

feyd wrote:It doesn't appear $counts is ever set. I would imagine that it'd get set at the same time as $firstname.
Yeah, that's the code I'm missing and don't know how to do. I thought it would be something like:

Code: Select all

$countresults = 'SELECT COUNT * FROM '.$mysql['prefix'].'results WHERE id="'.$_SESSION['id'].'"';
$counts = mysql_query($countresults);
But it's not working and I've tried everything variation that I can think of, and it just won't work.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Ah-hem... :wink:
Forum Rules wrote:Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

The Ninja Space Goat wrote:Ah-hem... :wink:
Forum Rules wrote:Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.

Fixed, didn't know what exactly to be specific about as I'm not sure what code is used to do this... I took a guess with the count.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It sounds like $counts would be a field from the table $firstname came from. At least that's how your original post made it sound.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

feyd wrote:It sounds like $counts would be a field from the table $firstname came from. At least that's how your original post made it sound.
No, I'll try to explain better, sorry if I'm confusing though.

I have 2 tables, one is table "users" the other table "results"

Table users looks something like this:

id | username | password | email
1 | steve | pass123 | myemail@email.com
2 | jerry | pass321 | myemail2@email.com


So then I have the results table:

id | date complete | folder | file
1 | Oct 10, 2006 | folderabc/ | file123.xls
1 | Oct 9, 2006 | folder123/ | file321.xls
1 | Oct 6, 2006 | folderAABB/ | fileAABB.xls
2 | Oct 10, 2006 | 2folder33/ | 2filerandom.xls


So the users table only holds the user information to login. The results table is where the actual information is kept. In the above example user 1, or steve, has 3 results, while user 2, or jerry, only has one.

What I need to do is count how many results the user has in the results table. Their ID is what links them between to the two tables.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

first off.. why don't you rename id to user_id in the results table... that is much more clear
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

The Ninja Space Goat wrote:first off.. why don't you rename id to user_id in the results table... that is much more clear
Ok that's done.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Ok, well there are two different ways of doing basically the same thing here. A single query or two queries.

The single query involves using a JOIN. The two query one would use the ID of the user to accumulate the records in results that they "own."

It's probably best to use a GROUP BY and/or COUNT() on both.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

feyd wrote:Ok, well there are two different ways of doing basically the same thing here. A single query or two queries.

The single query involves using a JOIN. The two query one would use the ID of the user to accumulate the records in results that they "own."

It's probably best to use a GROUP BY and/or COUNT() on both.

Do you know where I could get the code like that? I'm still really new to php, I've mainly just been editing existing code and learning that way by trial and error, so I don't know off the top of my head how to do that.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The queries themselves, you can see many examples throughout the forums here. If you want the reference, http://dev.mysql.com has several forms available.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

feyd wrote:The queries themselves, you can see many examples throughout the forums here. If you want the reference, http://dev.mysql.com has several forms available.
No offense, but that's the reason I posted this thread. I've been looking at websites like that for the past 2 days and trying all sorts of different code and I can't get anything to work for me. I thought posted the page's code along with what I needed to do would help, as it probably is just a simple 2 line code. :(
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Ok so someone at another forum has helped me get this part of the code. I get the user's ID and use it to pull and count how many records they have in the results table, however now I'm stupid and don't know how to define the end result as a variable so I can later use it in the page. Everything I try doesn't work. I know this is really simple, but I'm so damn frustrated about it and been working on this for what seems forever. Below is the code I've got so far:

Code: Select all

$sql = 'SELECT * FROM '.$mysql['prefix'].'users WHERE username="'.$_SESSION['username'].'"';

if(!$result = mysql_query($sql))
{
	die('The following MySQL query failed. User data could not be retrieved. '.$sql);
}


// assign the user info to variables
while (($row = mysql_fetch_array($result)) != false)
{
	$user_folder = $row['user_folder'];
	$id = $row['id'];
}



$sql2 = "SELECT count(*) FROM results WHERE id=$id";
So how do I define sql2 so I can later use it? Everything I try just quotes the exact code.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

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 ...
}
A couple of things:
1. My personal preference to simply use mysql_query() or die() instead of using multiple lines of code. Seems to convolute things when doing so a bit.
2. Your second query call should be inside the loop, else you\'ll only use the last $id.
3. No need for checking if mysql_fetch_array() for being false. The loop will automatically exit when it is false.
4. Always quote your table and column names with backticks \"`\"
5. I recommend using mysql_error() in the die() statement to get more clear errors when things go wrong.
5. Always use mysql_real_escape_string() when inputting values into the query string. You can never be too safe.
6. For the sake of optimization, I would keep reading how joins work. :wink:
Post Reply