Get Correct MySql Data Before and After Certain Date

[cutielou22]

I need some help with the following code. I want it to go through the "bom_terms" table in which $money and $timeframe should be found based on the start date and end date comparisons I am having trouble with.

 

Full Transactions code:

 

/* Transactions */
echo "<h2>Transactions - <a href=\"../transactions/add.php?account=$account\">Add</a></h2>";

if($stmt = $mysqli->prepare("SELECT type, amount, reason, repayplan, repaid, transid, date FROM bom_transaction WHERE account = ? ORDER BY id"));
{
$stmt->bind_param('i', $account);
$stmt->execute();
$stmt->store_result();
$count = $stmt->num_rows;
$stmt->bind_result($type, $amount, $reason, $repayplan, $repaid, $transid, $date);

	if ($count == "0") {
     echo "<i>No transactions were found.</i><br>";
	}

while ($stmt->fetch()){
	if ($type == "0") {$type = ""; $color = "";}
	if ($type == "1") {$type = "Added"; $color = "green";}
	if ($type == "2") {$type = "Removed"; $color = "red";}
	if ($type == "3") {$type = "Repaid"; $color = "";}
	
	if ($repayplan == "0") {$repayplan = "No";}
	if ($repayplan == "1") {$repayplan = "Yes";}
	
	if ($repaid == "0") {$repaid = "No";}
	if ($repaid == "1") {$repaid = "Yes";}
	
	$stmt2 = $mysqli->prepare("SELECT money, timeframe FROM bom_terms WHERE account = ? AND startdate <= '2013-01-03 21:00:00' AND enddate >= '2013-02-20 21:00:00'");
	$stmt2->bind_param('i', $account);
	$stmt2->execute();
	$stmt2->store_result();
	$stmt2->bind_result($money, $timeframe);
	$stmt2->fetch();
	$stmt2->close();
	
	$date2 = date("M d, Y", strtotime($date));
	
    echo "<font color=\"$color\">$type $$amount</font> $money/$timeframe $reason $repayplan $repaid $date2 <a href=\"../transactions/edit.php?transid=$transid\">Edit</a><br>";

}
$stmt->close();
}

echo "<br>";

 

Part of Coding Asking About (From the above):

	$stmt2 = $mysqli->prepare("SELECT money, timeframe FROM bom_terms WHERE account = ? AND startdate <= '2013-01-03 21:00:00' AND enddate >= '2013-02-31 21:00:00'");
	$stmt2->bind_param('i', $account);
	$stmt2->execute();
	$stmt2->store_result();
	$stmt2->bind_result($money, $timeframe);
	$stmt2->fetch();
	$stmt2->close();

 

What's it's doing:

It's making all results from the "bom_terms" table the same when at least 2 of the results should be different (meaning from a different row of the "bom_terms" table). - Right now it is grabbing the last result found from the table. Not sure why however. 

 

Some tests/theories I tried made it so it did show a different row, but they were still all shown as the same.

 

Example (what it is doing now):

---Example---

Added $10.00 40.00/Every 2 Weeks No No Jan 31, 2013 Edit
Added $90.00 40.00/Every 2 Weeks No No Feb 05, 2013 Edit
Added $20.00 40.00/Every 2 Weeks No No Feb 19, 2013 Edit
Removed $46.00 40.00/Every 2 Weeks Vengenz Birthday Party/T-Shirts Yes No Feb 22, 2013 Edit
Added $100.00 40.00/Every 2 Weeks No No Mar 05, 2013 Edit

---END Example---

 

NOTE: The 40.00/Every 2 Weeks is the result from the "bom_terms" table.

 

Example (what I want it to do):

---Example---

Added $10.00 20.00/Every Week No No Jan 31, 2013 Edit
Added $90.00 20.00/Every Week No No Feb 05, 2013 Edit
Added $20.00 20.00/Every Week No No Feb 19, 2013 Edit
Removed $46.00 40.00/Every 2 Weeks Vengenz Birthday Party/T-Shirts Yes No Feb 22, 2013 Edit
Added $100.00 40.00/Every 2 Weeks No No Mar 05, 2013 Edit

---END Example---

 

NOTE: The 40.00/Every 2 Weeks and 20.00/Every Week are the results that would be from the "bom_terms" table.

 

I am hoping there is just a simple problem/error I don't see. .. . . Hopefully you can understand what I am asking and having trouble with - I had a hard time trying to come up with a good way to explain it - this is the best way I could come up with.

[JLT]

I'm not understanding what you're trying to do, but rather it is clear what you want it to be like.

 

If I'm correct in saying, the only difference I see between what it is doing and what you want it to do is $timeframe, the timeframe column in the database for the rows you are displaying, must have a value of 2. So basically, why is it 2 if you really want it to be 1? That is what I'm not understanding.

[cutielou22]

$timeframe is not the only one that is different. Both $timeframe and $money are different. Compare my examples of results more closely to get a better understanding.

 

 

By the way, there is not 1 row. Right now there is 2 rows in the table - and in the near future there will be more.

[JLT]

Ah yes, sorry I did not notice $money is different. The first 3 adds are pretty much a division of two, whilst the 4th is kept fortnightly, what is puzzling me is how would you distinguish the two?

 

Is it possible you can dump the SQL for your tables and rows? My mind is stumped, I'm probably concentrating too hard just to figure out what you're doing but perhaps seeing your table structure and rows may give me a better idea - just make sure you do not display any sensitive data    I'll run it by localhost and see what's what.

[cutielou22]

Alright, here you are - I removed the first 3 id's, but that shouldn't matter:

--
-- Table structure for table 'bom_transaction'
--

CREATE TABLE bom_transaction (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  account int(7) unsigned NOT NULL,
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  amount decimal(5,2) unsigned NOT NULL,
  reason varchar(250) COLLATE latin1_general_ci NOT NULL,
  repayplan tinyint(1) unsigned NOT NULL,
  repaid tinyint(1) unsigned NOT NULL,
  transid varchar(40) COLLATE latin1_general_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;

--
-- Dumping data for table 'bom_transaction'
--

INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(4, 567892, 1, '10.00', '', 0, 0, '', '2013-01-25 02:58:59');
INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(5, 567892, 1, '10.00', '', 0, 0, '', '2013-02-01 02:59:20');
INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(6, 567892, 1, '90.00', '', 0, 0, '', '2013-02-06 02:59:38');
INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(7, 567892, 1, '20.00', '', 0, 0, '', '2013-02-20 02:59:51');
INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(8, 567892, 2, '46.00', 'Vengenz Birthday Party/T-Shirts', 1, 0, '', '2013-02-23 03:00:19');
INSERT INTO bom_transaction (id, account, `type`, amount, reason, repayplan, repaid, transid, `date`) VALUES(9, 567892, 1, '100.00', '', 0, 0, '', '2013-03-06 03:00:41');

[JLT]

Also the bom_terms table please

[cutielou22]

Opps. My bad. Here you go:

--
-- Table structure for table `bom_terms`
--

CREATE TABLE `bom_terms` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account` int(7) unsigned NOT NULL,
  `money` decimal(5,2) NOT NULL,
  `timeframe` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `startdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `enddate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bom_terms`
--

INSERT INTO `bom_terms` (`id`, `account`, `money`, `timeframe`, `startdate`, `enddate`) VALUES(1, 567892, '20.00', 'Every Week', '2013-01-04 00:44:51', '2013-02-06 12:33:26');
INSERT INTO `bom_terms` (`id`, `account`, `money`, `timeframe`, `startdate`, `enddate`) VALUES(2, 567892, '40.00', 'Every 2 Weeks', '2013-02-06 17:34:34', '0000-00-00 00:00:00');

[JLT]

Ok thanks. Is there any relationship between bom_transactions and bom_terms? All I see is that you're grabbing a single row from bom_terms and that's the end of it - from what I see on your original post, some transactions are done on a fortnightly basis and others on a weekly basis. I see nothing in the database that would distinguish the two types of transactions unless I'm overlooking it?

 

I have set up the database tables and I ran the query in the original post and it returned 0 rows, but if I reversed the bigger/less than signs so that it looked like follows:

startdate >= '2013-01-03 21:00:00' AND enddate <= '2013-02-20 21:00:00'

 

I would get both rows. This is because the startdate for the fortnightly option is greater than that in the query, and the enddate which is 0000-00-00 00:00:00 is less than that in the query. So why in the original query you posted, you are getting the fortnightly option I am not sure at all, I cannot seem to duplicate that.

 

Not to mention, if you did get two rows returned - partly as what I first mentioned in this post, you are doing a single fetch, there is no while - so of course it's only going to be the same all the time.

Edited March 14, 2013 by JLT

[JLT]

I took a wild guess, depending on the transaction date depends on what term is chosen.

 

Based on the transactions you have given me, I came up with this result:

 

Added $10.00 20.00/Every Week No No Jan 25, 2013 Edit
Added $10.00 20.00/Every Week No No Feb 01, 2013 Edit
Added $90.00 20.00/Every Week No No Feb 06, 2013 Edit
Added $20.00 40.00/Every 2 Weeks No No Feb 20, 2013 Edit
Removed $46.00 40.00/Every 2 Weeks Vengenz Birthday Party/T-Shirts Yes No Feb 23, 2013 Edit
Added $100.00 40.00/Every 2 Weeks No No Mar 06, 2013 Edit

 

The code I used for this is...

 

<?php
    try {
        $db = new PDO("mysql:dbname=testing;host=localhost", 'root', 'mypassword');
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        die($e->getMessage());
    }
    
    $account = 567892;
    
    try {
        $query = $db->prepare("SELECT type, amount, reason, repayplan, repaid, transid, date FROM bom_transaction WHERE account = ? ORDER BY id");
        $query->bindParam(1, $account, PDO::PARAM_INT);
        $query->execute();
    } catch (PDOException $e) {
        die($e->getMessage());
    }

	if ($query->rowCount() == 0) {
        echo "<i>No transactions were found.</i><br>";
        die;
	}

    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        $type = $row['type'];
        $amount = $row['amount'];
        $reason = $row['reason'];
        $repayplan = $row['repayplan'];
        $repaid = $row['repaid'];
        $transid = $row['transid'];
        $date = $row['date'];
        
        switch ($type) {
            case 0:
                $type = $color = "";
                break;
            case 1:
                $type = "Added";
                $color = "green";
                break;
            case 2:
                $type = "Removed";
                $color = "red";
                break;
            case 3:
                $type = "Repaid";
                $color = "";
                break;
        }
        
        $repayplan = $repayplan == 0 ? "No" : "Yes";
        $repaid = $repaid == 0 ? "No" : "Yes";

        try {
            $query2 = $db->prepare("SELECT money, timeframe, startdate FROM bom_terms WHERE account = ? ORDER BY startdate DESC");
            $query2->bindParam(1, $account, PDO::PARAM_INT);
            $query2->execute();
        } catch (PDOException $e) {
            die($e->getMessage());
        }
        
        $money = $timeframe = "none";
        
        while ($row2 = $query2->fetch(PDO::FETCH_ASSOC)) {
            if ($date >= $row2['startdate']) {
                $money = $row2['money'];
                $timeframe = $row2['timeframe'];
                break;
            }
        }
        
        $date2 = date("M d, Y", strtotime($date));
	
        echo "<font color=\"$color\">$type $$amount</font> $money/$timeframe $reason $repayplan $repaid $date2 <a href=\"../transactions/edit.php?transid=$transid\">Edit</a><br>";
    }

    echo "<br />";

 

Basically I ignore the enddate as you should be able to work with this yourself if I am close to what you are wanting. I loop through the terms in descending order, if it was in ascending order then $date which should be fortnightly is bigger than the weekly date, thus it'll always display as weekly. Descending order would basically mean if $date is bigger than fortnightly then it's fortnightly else... try the next row which in this situation will be weekly... do I make sense?

 

Note that I did convert it all to PDO because I'm more comfortable in that environment, but providing you did a similar technique it would work in mysqli. I also tidied it up a little because I'm a bit of a tidy freak. I didn't like the if statements, switches are faster and as for the YES/NO a simple if/else would suffice instead of what you had. I did an if/else in a ternary style because it is neater.

 

Let me know the desired result is not what you wanted, I didn't have much to go on - I took a wild guess and came up with some results. If it is the desired result, I would probably be able to simplify it and use only one query

Edited March 14, 2013 by JLT

[cutielou22]

Yes, that is the result I wanted - I have yet to test your code though. I will let you know tonight or tomorrow if it works for me.

 

EDIT: Could you convert it back over to mysqli? I tried changing it myself, but I am not getting the same results as you.

Edited March 14, 2013 by cutielou22

[JLT]

I could give it my best shot - is it urgently needed or can I get some sleep for 8 to 10 hours first?

[cutielou22]

I am guessing you already went to sleep. But it is not urgent. No rush needed.

[JLT]

A little delayed, only just got around to doing it. I've tested it locally and it gives out the same result... you could of posted your attempt at converting and I could of corrected you but oh well.

 

<?php    
    $mysqli = new mysqli("localhost", "***", "***", "***");
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    
    $account = 567892;
    
    $sql = "SELECT type, amount, reason, repayplan, repaid, transid, date FROM bom_transaction WHERE account = ? ORDER BY id";
    
    if ($stmt = $mysqli->prepare($sql)) {
        $stmt->bind_param('i', $account);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($type, $amount, $reason, $repayplan, $repaid, $transid, $date);
        
        if ($stmt->num_rows == 0) {
            echo "<i>No transactions were found.</i><br />";
        }
        
        while ($stmt->fetch()) {
            switch ($type) {
                case 0:
                    $type = $color = "";
                    break;
                case 1:
                    $type = "Added";
                    $color = "green";
                    break;
                case 2:
                    $type = "Removed";
                    $color = "red";
                    break;
                case 3:
                    $type = "Repaid";
                    $color = "";
                    break;
            }
            
            $repayplan = $repayplan == 0 ? "No" : "Yes";
            $repaid = $repaid == 0 ? "No" : "Yes";
            
            $sql = "SELECT money, timeframe, startdate FROM bom_terms WHERE account = ? ORDER BY startdate DESC";
            
            $money = $timeframe = "none";
            
            if ($stmt2 = $mysqli->prepare($sql)) {
                $stmt2->bind_param('i', $account);
                $stmt2->execute();
                $stmt2->store_result();
                $stmt2->bind_result($m, $t, $startdate);
                
                while ($stmt2->fetch()) {
                    if ($date >= $startdate) {
                        $money = $m;
                        $timeframe = $t;
                        break;
                    }
                }
            }
            
            $date2 = date("M d, Y", strtotime($date));
            
            echo "<font color=\"$color\">$type $$amount</font> $money/$timeframe $reason $repayplan $repaid $date2 <a href=\"../transactions/edit.php?transid=$transid\">Edit</a><br>";
        }
    }

    echo "<br />";

 

Let me know if it's what you want.

[cutielou22]

Yes thank you. It is working perfectly now.

 

Sorry about not posting my attempt. (Noted for next time.)