CODEWITHSUNDEEP
phpsqlpostgresql
Kino
Kino

Reputation: 217

getting the previous year of a varchar data type

I have a query 

SELECT * from table1 where sy='$school_year';

And my

 $school_year='2013-2014'      //which is a varchar

How can I make 

 $school_year='2012-2013'

I've tried 

 SELECT * from table1 where  sy='$school_year'-1;

but gives me an error stating that $school_year is not an integer. How can I get the previous $school_year? or is it possible?

Upvotes: 0

Views: 63

Answers (4)

pozs
pozs

Reputation: 36234

Consider restructuring your table. Your possible options (instead of the '2013-2014' formatted varchar) are:

Upvotes: 0

Mark Miller
Mark Miller

Reputation: 7447

$year = '2013-2014';

$prev_year = substr($year, 0, 4);
$prev_year = ($prev_year-1) . '-' . $prev_year;

$q = "SELECT * FROM table1 WHERE sy='$prev_year'";

$q will be 

SELECT * FROM table1 WHERE sy='2012-2013'

Upvotes: 0

user1978142
user1978142

Reputation: 7948

Alternatively you can do something like this: Consider this example:

$school_year='2013-2014';
$school_year = explode('-', $school_year);
$school_year[0] = ((int) $school_year[0] - 1);
$school_year[1] = ((int) $school_year[1] - 1);
$school_year = implode('-', $school_year);

$statement = "SELECT * from table1 where  sy='$school_year';";
echo $statement;

// Sample
// SELECT * from table1 where sy='2012-2013';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Here is one way to get the immediately preceding value:

select *
from table1
where sy < '$school_year'
order by sy desc
limit 1;

Upvotes: 0

Related Questions