How to combine SQL query with a PHP script

Question

I have this query to count the number of forms that are submitted by a certain user

$sql="SELECT `ID` FROM `kform` WHERE `ID`='$ID'";                               
$result = $conn->query($sql);
$k=0;
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {                                                                                                 
      $k++;
   }
}

plus I have this php script that is basically check a certain date (which is included in each form) and prints valid if it is within this fiscal year

$endYear = 2017;
while($endYear <= 2025) {
    $end = $endYear.'/06/30';
    $endDate = DateTime::createFromFormat('Y/m/d', $end);
    $initDate = DateTime::createFromFormat('Y/m/d', $end);
    $initDate = $initDate->sub(new DateInterval('P1Y')) -> add(new DateInterval('P1D'));
    $ddb =  $row2['Date'];
    $dateFromDB = DateTime::createFromFormat('Y-m-d', $ddb);
    if ($dateFromDB >= $initDate && $dateFromDB <= $endDate) { 
        echo "valid\n";
        echo "\tStartDate->\"".$initDate->format("Y-m-d")."\"\n";
        echo "\tEndDate->\"".$endDate->format("Y-m-d")."\"\n";
        echo "\tDateFromDatabase->\"".$dateFromDB->format("Y-m-d")."\"\n";
    }

    $endYear++;
}

Now what I was trying to do is to combine these two functions together, the idea behind that is to count only the forms that are in this fiscal year, any older forms should not be counted. I tried different ways to combine them but it gave me different errors every time, So is it even possible to do so?

Did my best to make the example code readable, with the block structure visible and some spaces around things like >= so that you can see the intention more clearly. Also made use of {} consistent, starting on same line as if/while etc. not sometimes one place and sometimes another. — Ghoti, Mar 06 '17 at 15:22
When... WHEN will people learn they should use prepared statements. Where does all this outdated and horribly secure code examples originate from? Please read http://stackoverflow.com/questions/24988867/when-should-i-use-prepared-statements — Tschallacka, Mar 06 '17 at 15:29

JustOnUnderMillions · Accepted Answer · 2017-03-06T11:22:17.220

0

Go on from that:

 function isValidYear($dategiven){
   $endYear=2017;
   while($endYear<=2025) {
     $end = $endYear.'/06/30';
     $endDate = DateTime::createFromFormat('Y/m/d', $end);
     $initDate = DateTime::createFromFormat('Y/m/d', $end);
     $initDate = $initDate->sub(new DateInterval('P1Y'))->add(new DateInterval('P1D'));
     $ddb =  $dategiven;
     $dateFromDB = DateTime::createFromFormat('Y-m-d', $ddb);
     if ($dateFromDB>=$initDate && $dateFromDB<=$endDate) { 
       return true;
     }
     $endYear++;
    }
    return false;
 }

And

$sql="SELECT `ID` FROM `kform` WHERE `ID`='$ID'";                               
$result = $conn->query($sql);
$k=0;
if ($result->num_rows > 0) {
 while($row = $result->fetch_assoc()) {
   if(isValidYear($row['datefield'])){
      $k++;
   }
}

Note: Your $sql selects only the ID field. You have to at Date

$sql="SELECT * FROM `kform` WHERE `ID`='$ID'";

edited Mar 06 '17 at 11:22

answered Mar 06 '17 at 10:33

JustOnUnderMillions

3,741
9
12

it works! thank you so much, i can use `$dategiven` in `'datefield'`, right? – Rawabi.a Mar 06 '17 at 11:18
and `isValidDate` isn't supposed to be `isValidYear`? – Rawabi.a Mar 06 '17 at 11:19
You have to set up `'datefield'` to the right field from your database ( you have used `$row2['Date']`). and yes `isValidYear` is better i change it now. But you can call it like you want. – JustOnUnderMillions Mar 06 '17 at 11:22

How to combine SQL query with a PHP script

1 Answers1