Displaying MySQl results

[redbeard123]

Hi,

I am in need for some help. I made this short database:

 

id startperiod endperiod daysperiod interest daysyear

1 2004-07-01 2004-12-31 184 0.1550 366

2 2005-01-01 2005-12-31 365 0.1550 365

3 2006-01-01 2006-06-30 181 0.1350 365

4 2006-07-01 2006-12-31 184 0.1250 365

5 2007-01-01 2007-06-30 181 0.1150 365

6 2007-07-01 2007-12-31 184 0.1200 365

7 2008-01-01 2008-12-31 366 0.1200 366

8 2009-01-01 2009-06-30 181 0.1050 365

9 2009-07-01 2009-12-31 184 0.0900 365

10 2010-01-01 2010-12-31 365 0.0900 365

11 2011-01-01 2011-06-30 181 0.0900 365

12 2011-07-01 2011-12-31 184 0.0925 365

13 2012-01-01 2012-06-30 182 0.0900 366

 

 

I am trying to build a calculator that calculates interests. The user would insert the following fields in a web form: amount of debt, date overdue, date of calculation.

My problem is that interest rates are different in different time periods (as you can see from above table).

 

My approach to this is: (assume 2007-02-02 as date overdue and 2012-02-10 as date of calculation):

 

1. First, find the start period and calculate number of days between date overdue and the end date of a period  and multiply the result by interest rate:

 

My query example:

 SELECT (((SELECT DATEDIFF((SELECT endperiod from obresti WHERE startperiod <= '2007-02-02' AND endperiod >= '2007-02-02'),'2007-02-02')) * interest) / daysyear) FROM obresti WHERE startperiod <= '2007-02-02' AND endperiod >= '2007-02-02';

 

 

2. Then find the end period and calculate number of days between calculation date and the start date of a period  and multiply the result by interest rate:

 

SELECT (((SELECT DATEDIFF('2012-02-10', (SELECT startperiod from obresti WHERE startperiod <= '2012-02-10' AND	 endperiod >= '2012-02-10'))) * interest) / daysyear) FROM obresti WHERE startperiod <= '2012-02-10' AND endperiod >= '2012-02-10';

 

3. Find all periods between the dates (except first and last):

 

SELECT SUM((daysperiod * interest) / daysyear) FROM obresti WHERE startperiod >= '2007-02-02' AND endperiod <= '2012-02-10';

 

4. Then get result in PHP and SUM (1+2+3) and multiply by amount and display the end result to user.

 

All that code works in MySQL but I can't make it work with PHP, it gives back output Resource ID#4 or Array() or some syntax errors.

 

One of my attempts to solving this problem was something like the code below (it's the code just for point 1), but it didn’t work.

I am guessing it's a problem with mysql_fetch command and probably some syntax in the query.

 

 

 

$start = $_POST["start"];
$end = $_POST["end"];	

$start_ts = strtotime($start);
$end_ts = strtotime($end);
$query1="SELECT (((SELECT DATEDIFF((SELECT endperiod from obresti WHERE startperiod <= '$start_ts' AND endperiod >= '$start_ts'),'$start_ts')) * interest) / daysyear) FROM obresti WHERE startperiod <= '$start_ts' AND endperiod >= '$start_ts'";
$result=mysql_query($query1, $connection);
while($row = mysql_fetch_array($result)){
$result[]= $row;
}
echo $result;

 

 

Also as you can see I added the column “daysperiod”  with intent to use server resources as little as possible. Is there any other,cleaner solution?

 

Any help would be appreciated. Tnx.

 

R.

 

P.S. I've added my table as attachment.

 

 

 

17776_.zip

[trq]

See the examples on this page: mysql_fetch_assoc.

[cpd]

What your trying to do just wont work with your current database structure. You need to re-think how your doing it with changing interest rates if I'm interpreting your requirements correctly.

[redbeard123]

well all of the queries work in mysql. They give the expected result.

Shouldn't it work when I just try to display results using php?

[cpd]

What a numpty, I've completely misinterpreted what your trying to do.

 

Like thorpe said, use the mysql_fetch_assoc or mysql_fetch_array and in your query you can add an "AS" statement like so:

 

SELECT SUM((daysperiod * interest) / daysyear) AS `interest` FROM obresti WHERE startperiod >= '2007-02-02' AND endperiod <= '2012-02-10';

 

Although I would consider using a stored routine or procedure, depending on your DBMS, because this is a lengthy process which would make sense to have in the SQL Server itself.

[redbeard123]

Tnx CPD,

 

I've made it work,

I tried doing it different ways, even similar to the solution but it didn't work because I never had anything to call, and thats where AS xxxx helped, didn't know about that before.

 

and code:

 

 

$sqlid2=mysql_query("SELECT id AS ident2 from obresti WHERE startperiod <= '$end' AND endperiod >= '$end'");
$rowid2=mysql_fetch_array($sqlid2);

$ident2=$rowid2['ident2'];
echo $ident2;