MySQL / PHP Help.

[NSA]

Hello,

I can't really explain this without description so here goes.

I need to have a year (2013), a month (January) and a user (Josh).
I have a big database and I want to see how many jobs Josh did throughout the month January of 2013.
Here's my current query:

SELECT Day( closed ) as D, COUNT( closed ) AS c
FROM ost_ticket
WHERE YEAR( closed ) = '2013' AND MONTHNAME( closed ) = 'January' AND source = '" . $user . "'
Group By D
LIMIT 0 , 30

The problem is, it doesn't seem to be the same as a normal query, as I cannot echo $row data because this happens:

I need to list how many jobs Garry, Adam and Anthony did, but it's going crazy.

 

[Sean]

SELECT `day(closed)` AS day FROM ost_ticket WHERE `year(closed)` = '2013' AND `monthname(closed)` = 'January' AND `source` = '{$user}' GROUP BY day LIMIT 30

Try that, and you can do your count outside of the query to make it simpler.

 

[NSA]

How would I do the count at this point?

 

[Sean]

How would I do the count at this point?

NVM that, I forgot your query is getting specific data from 2 tables and you're limiting the results displayed, the count would count the results under the criteria of the query, put the count back in the snipper I posted and try it that way.

 

[NSA]

Okay, something seems to have worked, but now this is happening.

Here's my code (Which I know is a bit fucked up, I started getting stressed).

    function getRows($user)
    {
        $q = mysql_query("SELECT day(closed) AS day, COUNT( closed ) AS c FROM ost_ticket WHERE year(closed) = '2013' AND monthname(closed) = 'January' AND source = '{$user}'  GROUP BY day LIMIT 30");
$count = mysql_num_rows($q);
return $count;
    }
    function test11($user)
    {
        $q = mysql_query("SELECT day(closed) AS day, COUNT( closed ) AS c FROM ost_ticket WHERE year(closed) = '2013' AND monthname(closed) = 'January' AND source = '{$user}'  GROUP BY day LIMIT 30");
$r = 0;
$a = mysql_num_rows($q);
while($row = mysql_fetch_assoc($q))
{   
    while($r < $a)
    {
    return $row['c'];
    $r++;
    }
}
}
    function getUser()
    {
        $q = mysql_query("SELECT * FROM ost_staff WHERE staff_id > 2");
        while($row = mysql_fetch_assoc($q))
        {
            $count1 = getRows($row['username']);
            $a = 0;
            echo '<div id="' . $row['username'] . '" class="stat">
            <span class="title">' . $row['username'] . '</span><br /><br />
            <div id="break"></div>';
            echo '
            <div id="data-' . $row['username'] .'">' . test11($row['username']) . '</div>';
        echo '</div>';
        }
    }

 

[Sean]

Okay, something seems to have worked, but now this is happening.

Would it be possible for you to mock up an image in photoshop or even MS Paint with a few boxes and text, to show me what exactly it's suppose to look like and what content is suppose to be in the boxes.

[EDIT]

If you are just trying to get Adam, Garry and Anthony, you will need to use a get method or re-do your query, as there is nothing in there defining just them users, and that lead me to believe that your PHP is working, but it's just adding the extra users...

 

[NSA]

So the database consists of jobs completed in a computer shop.
Every time a job is finished, the technician hit's complete and the date is put into 'closed'.

I need to run through all the days (1 - 30 (31)) for the selected month of the selected year so I can get the No. of Jobs each technician completed each day.
It should look a little like this:

 

[Sean]

Can you please post an image of your database structure?

 

[NSA]

 

[Sean]

I think you have over-complicated this, there would probably me a much easier way of going around this.. Let me get this clear:

When a technician / user clicks "finish" on a job, it gets recorded in a way, that you can re-call the number of jobs finished by that 1 technician on a specific day?


[EDIT]

Is this for a real company, and are there going to be a large number of Technicians, or a small number?

 

[NSA]

Well, when the technician records the job as being finished, "status" is set to closed and "closed" is filled with the current date.

 

[Sean]

And you use the Staff ID column to record what staff member is in doing and closes the job?

 

[NSA]

The "Source" is filled with the staff Username.
But Staff ID is filled with 2 always for some reason.
They were using this system when I joined the team, so I'm still figuring out some things.
I think the only way of knowing who closed it was the source.

 

[Sean]

I see, well TBH I can't really help you with this just now, to be able to solve a problem like this (for me) would require access to the files, structure, database etc, so I could see how it's all coded and structured, and I also think that you should start from scratch because you are missing some vital parts with selecting specific users and dates etc. I could code something that does what you are asking but I would use a totally different database structure and I am assuming you can't change the database structure if you just joined the team.

 

[NSA]

I see, well TBH I can't really help you with this just now, to be able to solve a problem like this (for me) would require access to the files, structure, database etc, so I could see how it's all coded and structured, and I also think that you should start from scratch because you are missing some vital parts with selecting specific users and dates etc. I could code something that does what you are asking but I would use a totally different database structure and I am assuming you can't change the database structure if you just joined the team.

It's not very likely.
The database is used by a job system and if I change 1 think the whole thing could collapse. :/

 

[Sean]

I'll give it ago anyhow, are you just picking the months, and years and it flicks through the dates 1-30(31) In a list?

 

[NSA]

Yes, that's exactly what I need.
It's just so my boss can compare technicians performance etc...

 

[Sean]

Yes, that's exactly what I need.
It's just so my boss can compare technicians performance etc...

I have got what you asked for but with a few limitation, with the structure of the database, I can only tell the number of jobs complete on a specific date/time.

Using this function:

would ouput this:

Using my make-shift database, with the same structure:

I will continue working on this, to see if I can narrow down the time span, and list all the jobs in the month.


Here is the code just now, if you want to try and work on it yourself:

<?php
    include('config.php');
 
 
    function jobs_complete($user, $date) {
        global $mysqli;
        $query = "SELECT * FROM `jobs` WHERE `source` = '{$user}' AND `closed_date` = '{$date}'";
        $result = $mysqli->query($query);
        $count = mysqli_num_rows($result);
        $clean_date = substr($date, 0, 7);
        if($count > 0) {
            echo "<b>" . $user . "</b><br>";
            if($count == 1) {
                echo "<b>" . $count . " </b> job was completed by " . $user . " on " . $clean_date;
            }
            else if($count > 1) {
                echo "<b>" . $count . " </b> jobs were completed by " . $user . " on " . $clean_date;
            }
            echo "<br><br>";
        }
        else {
            echo "<b>" . $user . "</b><br>";
            echo "No jobs were completed by " . $user . " on " . $clean_date; 
        }
    }
 
    jobs_complete('Anthony', '2013-06-01 10:30:55');
    jobs_complete('Garry', '2013-06-01 10:30:55');
 
?>

I can make it list all the users aswell if that is what you are going for, so you just add date in function and not a specific user.

 

[NSA]

Okay so, what if we didn't know the time it was done, but just the date.
Using this -> 2013-06-01 10:30:55 would mean we'd need to know the time... right?

 

[Sean]

Okay so, what if we didn't know the time it was done, but just the date.
Using this -> 2013-06-01 10:30:55 would mean we'd need to know the time... right?

Just now you would need to know the specific date and time (the red text) to display the number of jobs done at that time, I am going to bed just now and will work on limiting that time span down so you can simply use month and year and it will give you the data for now, you can try and work on it just now see how far you get.

I will post updates tomorrow if nobody has done so already... Off to bed o/