Is this possible?

[Landslyde]

Is it possible to read x number of chars in a PHP string? Here is my problem:

 

I want to set MySQL's NOW() to a PHP variable:

$year = 'Select NOW()'

then read the first four chars of that variable to get the year. This is for my website that will track fiscal quarters for clients. And since this will be ongoing, year after year, I really don't want to hard-code the year in my query:

"SELECT SUM(balances) FROM fiscalTable WHERE date BETWEEN `2015-01-01` AND `2015-03-30`"

If I did it that way, I'd have to remember to change it yearly, etc.

 

What I'd rather do, if it's possible, is read the first four chars from the var $year that I set above. If I cld do that, then my code wld be once and done. Any suggestions, Forum?

[maxxd]

There are a couple different ways, right off the top of my head. The easiest would be to convert the $year variable that you set earlier into a DateTime object, then pull the year right from that. No fuss, no muss. You could always use substr(), but I do think the first option is going to be easier.

Edited May 7, 2015 by maxxd

[Barand]

Use the datetime functions

SELECT SUM(balances) as total_balance
FROM fiscalTable 
WHERE YEAR(date) = YEAR(CURDATE()) AND QUARTER(date) = 1

BTW, in your query the date values should be in single quotes and not backticks (backtickes are for column and table identifiers)

[Psycho]

Um, why do you have backticks around the dates? Use this:

SELECT SUM(balances)
FROM fiscalTable
WHERE date BETWEEN DATE_FORMAT(NOW(), '%Y-01-01') AND DATE_FORMAT(NOW(), '%Y-03-30')

EDIT: Barand beat me to it and had a better solution. Didn't realize there was a QUARTER() function.

Edited May 7, 2015 by Psycho

[Landslyde]

There are a couple different ways, right off the top of my head. The easiest would be to convert the $year variable that you set earlier into a DateTime object, then pull the year right from that. No fuss, no muss. You could always use substr(), but I do think the first option is going to be easier.

 

Thanks, maxxd. I don't understand the DateTime object, but the substr() is something I can easily wrap my mind around. And, for what I'm needing, it'll work out perfectly for me. Your answer is much appreciated.

 

UPDATE!

 

Lol...By the time I finished my response to maxxd and sent it in, I saw several of you had also responded. Best Forum on the net! You guys are great!

 

As for the backticks, I'm truly new at this and thought they were correct, even though my working query doesn't use them  I didn't want to look too rookie to all of you...that sure backfired!

 

Barand & Psycho: I like the way you guys did that. Is easy to see and understand. Best answser goes to Barand and his use of the Quarter() function, although I learned a little something from each of you. Many thanks.

Edited May 7, 2015 by Landslyde

[Landslyde]

I actually did it the way maxxd suggested, with the substr() function. It works best for my application:

require_once 'root_login.php';
 
$stmt = $db->prepare('SELECT NOW()'); 
$stmt->execute();
$row = $stmt->fetch();  
$year = substr($row[0], 0, 4);
 

$q2s = $year.'-04-01';
$q2e = $year.'-06-30';
 
$stmt = $db->prepare('SELECT SUM(amount_paid) as q2
FROM history
WHERE last_payment
BETWEEN :q2s and :q2e'); 
$stmt->bindValue(':q2s', $q2s, PDO::PARAM_INT); 
$stmt->bindValue(':q2e', $q2e, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();  
 
 
?>
 
<div class="row"> 
<div class="table-repsonsive">
<table class="table table-bordered table-striped table-hover" style="margin-top:30px">
<thead>
<tr>
<th style="text-transform: uppercase">Quarter 2</th>
</tr>
</thead>
 
<?php foreach($result as $row ) { $i++; ?>
 
<tbody>      
    <?php echo '<tr style="'.getbgc($i). '">' ?>
<td style="width:auto;color: #fff;"><?php echo number_format($row['q2'],2) ?></td>
    </tr>
</tbody>

In doing it this way, I'll always have the current year to produce the quarter breakdowns. This is just a small sample to demonstrate what I needed to accomplish and how I wanted to go abt it. I appreciate everyone's input.

[Psycho]

Running two queries when you only need one? Also, why you you hard-code the data to determine the period to pull records for? Create a function and pass the quarter.

 

 

function getQuarterResults($qtr)
{
    $query = "SELECT SUM(amount_paid) as qRes
              FROM history
              WHERE YEAR(last_payment) = YEAR(CURDATE()) 
                AND QUARTER(last_payment) = :q";
    $stmt = $db->prepare($query);
    $stmt->bindValue(':q', $qtr, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll();
    return $result;
}

 

 

Heck you can even run one query to get the results for the entire year by quarter with one query. Plus, no need to prepare the query

 

SELECT QUARTER(last_payment), SUM(amount_paid) as total
FROM history
WHERE YEAR(last_payment) = YEAR(CURDATE())
GROUP BY QUARTER(last_payment)

[maxxd]

@Landslyde, I gotta admit I couldn't agree more with Pyscho on this one. The way you've got it set up now is wasteful, resource-wise, and unnecessarily complex. When I typed out my answer, I'd read your question incorrectly in that I thought you had set the $year variable earlier in php - I chalk that up to lack of sleep. Psycho's query with Barand's suggestion of using the QUARTER() function is going to offer you the most flexibility and least unnecessary maintenance. As well as offering the fastest return time; as I said in my response, using substr() isn't the best idea, especially if you're not pulling the date from user input to inject into the query later.

[Landslyde]

Seems like I have a lot to learn. I mean, it's obvious I'm not very smart abt any of this. That's from inexperience. I see what you've done in the function, Psycho, and I kind of understand it. I'll work with it and see what it does. I admit mine looks cluttered and clunky, like it was done by a 3rd grader, but that's the only way I cld understand it. The stuff you guys showed me from the onset didn't make sense to me...I just didn't understand. I appreciate you guys taking the time to further explain things to me. Means a lot.

[Psycho]

If you don't understand something that is provided - by all means ask! The main point of this site, in my opinion, is helping people learn. So, in that spirit, let me break down the query Barand provided (I defer to him on all things database!) using your field/table names:

 

 

SELECT SUM(amount_paid) as qRes
FROM history
WHERE YEAR(last_payment) = YEAR(CURDATE()) 
  AND QUARTER(last_payment) = :q

 

The first line

 

SELECT SUM(amount_paid) as qRes

This add the total of all the 'amount_paid' fields of the records that will match the query

 

 

FROM history

We'll be selecting data from the history table

 

 

WHERE YEAR(last_payment) = YEAR(CURDATE()) 
  AND QUARTER(last_payment) = :q

Ok, this is the real meat of the query. There are MySQL functions used:

 - YEAR() returns the year value of a date object

 - CURDATE() returns the current date [same as NOW(), but only the date portion]

 - QUARTER() returns the numerical quarter of a date object (i.e. 1, 2, 3 or 4). No need to defined Jan1 to Mar31

 

The WHERE clause will return all the records that match two conditions:

 

1. The YEAR of the records last_payment date field is the same as the YEAR for the CURRENDATE, i.e. the current year

2. The QUARTER of the last_payment date field is the same as the quarter we specify, e.g. 1 will be Jan1 to Mar31, 2 will be Apr1 to Jun30, etc.

 

Hope that helps. But, if you need to show data for multiple quarters, then the second query I provided above would be better.

[Barand]

 Didn't realize there was a QUARTER() function.

 

It is an obscure one and one I have never used. I had a niggling feeling that I'd seen it in the function index so looked it up first just to check

[maxxd]

It's not that the code looked clunky, by any means. It's the base logic behind the data flow that I'm questioning.

 

By calling to the database to get NOW(), you've used network and system resources to get the date; this is something very easily obtainable either from php or within the query itself, both of which negate the need for the first query which cuts down on network and system resources. It's sort of akin to using a JOIN in sql as opposed to running a sub-query on every loop through the result set of a parent query.

 

Or, better yet, you wouldn't run a query to get all the user ID's from a table, then run an individual query for each user ID to get the user's name, right?

 

And don't forget - as Psycho said - the point of this board is for everyone to learn. If you have a question, ask away - you'll probably learn something from the answer, and there's a good chance the people giving the answer will learn something, either by answering the question or from another user's answer (QUARTER() function, I'm looking at you). It's a win-win situation!

[Landslyde]

Hello Forum:

 

I used the query provided by Barand & Psycho:

$stmt = $db->prepare('SELECT QUARTER(last_payment), SUM(amount_paid) as total
                      FROM history
                      WHERE YEAR(last_payment) = YEAR(CURDATE())
                      GROUP BY QUARTER(last_payment)');

The built-in MySQL functions are sweet. I'll have to figure out a workaround for the empty quarters though. All of my last_payment dates, put in by me for testing, are of the 2nd quarter. So when I ran this, with table headers for Quarter 1 through Quarter 4, the 2nd quarter 'total' fell under the Quarter 1 header. So I ran an UPDATE and changed the first 15 last_payment entries to reflect 1st Quarter dates, re-ran the query and got 1st and 2nd quarter to display correctly in the table. Hmm...I just thought of a fix for this Problem solved.

 

Barand: the QUARTER() function works like a charm. Saves on a lot of needless code. Many thanks to you for offering this information.

 

I know this is a good board with good people like you three that offer help to peeps like me. I read the board all the time, several boards, and I often see those who come here just to get others to write their code for them. That's why I feel reluctant to overstay with myriad questions, my pride foreclosing on me in midstream. But, as all of you have seen, my rookie solutions are not not not the best in town

 

I hope that one day I'll be as good as this as you guys are, being able to help others when they come to the Forum. Again, my thanks to to you guys for your time and efforts. Learning is a blast!

 

 

[Barand]

If you want the quarters with no history, create a "quarter" table with a row for each quarter and LEFT JOIN to history table

CREATE TABLE quarters (
qtr INT NOT NULL PRIMARY KEY
);

INSERT INTO quarters (qtr) VALUES (1), (2), (3), (4);

SELECT q.qtr
, SUM(h.amount_paid) as total
FROM quarters q
LEFT JOIN history h 
    ON q.qtr = QUARTER(h.last_payment)
WHERE YEAR(h.last_payment) = YEAR(CURDATE())
GROUP BY qtr

[Landslyde]

Thanks for that information, Barand. I see where this wld provide me null values for empty quarters, allowing my display of earnings to properly align with the table headers Quarters 1 through 4. At least I think that's what this will do. I'd earlier thought abt creating this table so the clients cld view year-to-year quarterly earnings. All I'd have to add to your table wld be the clientID. And I see where having a row for each quarter is necessary for the left join to work. I'll admit that it's a little confusing to me, but I'll test it out and watch how the gears turn. Much appreciated, Barand.

[Barand]

ClientId would be in the history table to get totals for each client.

[Landslyde]

And that's the way I have it. So the quarters table holds no values except 1, 2, 3 & 4, right? And is only used to provide null values for quarters with no last_payment date through the LEFT JOIN? Am I better understanding it now?

Edited May 8, 2015 by Landslyde

[Barand]

That's right.

[Landslyde]

That's powerful, Barand. Your queries are like Jaguars next to my VW Bugs