Page 1 of 1

Query results by user

Posted: Wed Nov 03, 2010 5:15 am
by Davesmith437
Hi,

I'm looking for some help with a problem which has caused much confusion.

To give some background, I'm converting a timesheet program from MS Access to PHP/MySql. This is my first attempt with PHP/MySql and have become somewhat stumped with what is an easy process in Access.

What I'm trying to do...
Sum the hours worked in a week by the user logging the work and show this on a page with a summary of what they have done.

The problem I've got...
I don't quite understand how to put the relevant session variables into the Where statement of the query - the username and relevant week number are both session variables called and displayed on the page.

The code I'm using....

<?php $sql = "SELECT HoursSpentLas, TravelTimeLas FROM tblProjects WHERE AdviserName = $MM_Username AND WeekNumber = $MM_Weeknumber";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
echo $query['HoursSpentLas'];
}


$TimeWorkedLAs = "SELECT HoursSpentLas, SUM(HoursSpentLas) FROM tblProjects GROUP BY WeekNumber";

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

// Print out result
while($row = mysql_fetch_array($result)){
echo "Total = ". $row['SUM(HoursSpentLas)'];

}
?>

This code does the addition fine but is returning values for all of the users (i get Total = 20 Total = 36 Total = 24 etc) . I've tried allsorts of variations but just can't figure it out.

Any help gratefully received!

Many thanks
Dave

Re: Query results by user

Posted: Wed Nov 03, 2010 6:48 am
by greyhoundcode
Davesmith437 wrote:

Code: Select all

SELECT HoursSpentLas, SUM(HoursSpentLas) FROM tblProjects GROUP BY WeekNumber;
I'm not 100% clear about the problem from your description, if I'm honest. Anyway, (and I'm taking a bit of a guess, I don't know what your table schema is etc) the above statement may simply need a where clause to be added in. Also, if all you want is the total hours then you might drop HoursSpentLas and just leave SUM(HoursSpentLas)?

Code: Select all

SELECT SUM(HoursSpentLas) FROM tblProjects WHERE AdviserName = $MM_Username AND WeekNumber = $MM_Weeknumber;
If I'm misunderstanding you just let me know!

Re: Query results by user

Posted: Wed Nov 03, 2010 8:21 am
by Davesmith437
Thanks for the reply.

Sorry if I didn't explain the issue properly.

The data (name, week number and time) is all from one table and is submitted to the table by the user.

So the table looks something like this (slightly shortened for sanity's sake!)
WorkID ProjectNumber Name WeekNumber HoursSpentLas
01 ARGH123 JBloggs 25 15
02 ARGH125 JBloggs 25 9
03 ARGH123 JSmith 25 10
04 ARGH125 JSmith 25 24

What I'm trying to get to is that when JBloggs is logged into the site and is reviewing his work for week 25, the total work time for the week is displayed (it should be 24 in this case). At the moment, the code I've been fiddling with does show the total for JBloggs but also shows the total for JSmith and every other user.

I've tried using the Where clause but I keep getting syntax errors or the query fails - using the suggestion, I'm currently getting "You have an error in your SQL syntax; check the manual that corresponds to use near ' AND WeekNumber =' at line 1"

The code is <?php $LATime = "SELECT SUM(HoursSpentLas) FROM tblProjects WHERE AdviserName = $MM_Username AND WeekNumber = $MM_Weeknumber";
$query = mysql_query($LATime) or die(mysql_error());
echo $query;
?>

THanks again for the help.

Dave

Re: Query results by user

Posted: Fri Nov 05, 2010 7:11 am
by kalimbo
Hey Dave,

Just a quick guess from me, I don't have time now to check it, but I think that might be the problem:
Put single quotes around the php variables when you use them in a string for SQL. Here's how it's gonna be in your script:

<?php $LATime = "SELECT SUM(HoursSpentLas) FROM tblProjects WHERE AdviserName = '$MM_Username' AND WeekNumber = '$MM_Weeknumber'";
$query = mysql_query($LATime) or die(mysql_error());
echo $query;
?>

Martin :)

Re: Query results by user

Posted: Fri Nov 05, 2010 8:22 am
by greyhoundcode
kalimbo wrote:Put single quotes around the php variables when you use them in a string for SQL
A good point. I'm so used to using ORM and query building libraries that I almost forget about things like that. Differentiating between 'normal' ticks and backticks can also important though, depending on your setup, as in:

Code: Select all

SELECT `id` FROM `users` WHERE `PaidMember`='1';

Re: Query results by user

Posted: Tue Nov 09, 2010 3:43 am
by Davesmith437
Hello,

Thanks for your replies - having fiddled with it some more and used your suggestions I finally got it working - this is what I ended up with....

Code: Select all

<?php $Adviser_Weektimehope = "-1";
if (isset($_SESSION['MM_Username'])) {
  $Adviser_Weektimehope = $_SESSION['MM_Username'];
}
$WeekNo_Weektimehope = "-1";
if (isset($_SESSION['MM_Weeknumber'])) {
  $WeekNo_Weektimehope = $_SESSION['MM_Weeknumber'];
}
mysql_select_db($database_timesheet, $timesheet);
$query_Weektimehope = sprintf("SELECT Sum(HoursSpentLas) As LAWorkTime, Sum(TravelTimeLas) As LATravelTime FROM tblprojects WHERE AdviserName = %s AND WeekNumber = %s", GetSQLValueString($Adviser_Weektimehope, "text"),GetSQLValueString($WeekNo_Weektimehope, "int"));
$Weektimehope = mysql_query($query_Weektimehope, $timesheet) or die(mysql_error());
$row_Weektimehope = mysql_fetch_assoc($Weektimehope);
$totalRows_Weektimehope = mysql_num_rows($Weektimehope);
?>
I think I was trying to be a bit too basic (hangover from using MSAccess I think!).

Thanks again for your help.

Dave