Page 1 of 3

Help on count code

Posted: Tue Oct 10, 2006 12:56 pm
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...

Posted: Tue Oct 10, 2006 1:06 pm
by feyd
It doesn't appear $counts is ever set. I would imagine that it'd get set at the same time as $firstname.

Posted: Tue Oct 10, 2006 1:20 pm
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.

Posted: Tue Oct 10, 2006 1:23 pm
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.

Posted: Tue Oct 10, 2006 1:26 pm
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.

Posted: Tue Oct 10, 2006 1:36 pm
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.

Posted: Tue Oct 10, 2006 2:05 pm
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.

Posted: Tue Oct 10, 2006 2:19 pm
by Luke
first off.. why don't you rename id to user_id in the results table... that is much more clear

Posted: Tue Oct 10, 2006 2:28 pm
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.

Posted: Tue Oct 10, 2006 2:30 pm
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.

Posted: Tue Oct 10, 2006 2:36 pm
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.

Posted: Tue Oct 10, 2006 2:43 pm
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.

Posted: Tue Oct 10, 2006 3:32 pm
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. :(

Posted: Wed Oct 11, 2006 10:40 am
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.

Posted: Wed Oct 11, 2006 11:35 am
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: