Code works outside AppGini but not Inside

[omackeytech]

I have a simple function that works fine if I run it outside my application. I use Eclipse to test my codeing.
When I put the same code word for word in AppGini it won't work.
I modified the select and update statements to match what AppGini uses and it will extract the data but it doesn't extract the week and year from the date.
I've tested the code inserting the year and week directly in the select statement and it works.
I used the update statement at the end to insert the $work_date, $week and $year in the Period field. and the $work_date apears but the $week and $year strings are blank.
Does anyone know what is happening or is there a special way that AppGini can extract the week and year from a date?

Code: Select all

		$recordID = makeSafe($recordID);
        sql("select Day from Time_Entry where id = '$recordID'", $eo);
    
		$work_date = $data['Day'];
		           
        $week = date_format($work_date,'W');
        $year = date_format($work_date,'Y');  

		$sql=("select Period from PayPeriods where Year = '$year' and Week = '$week'");
			$period=sqlValue($sql);

		sql("UPDATE Time_Entry set Period = '$period' Where id = '$recordID'", $eo);
	}	

[jsetzer]

Strange. Let's narrow down:

Did you check the value of $work_date?

Can you please post that value + the value of $sql.

Also please check if $eo contains an error.

I'm wondering if the column name Year may collide with the mySql function named year(). Try `Year` instead (wrapped in backticks).

Are both databases identical (testing with/without AppGini on the SAME table/database) or may there be a difference (MySQL vs MariaDB for example)?

[omackeytech]

To start with both Eclipse and AppGini are running on the same Windows 10 machine using XAMPP. They are both pulling from the same database and using PHP 7.

$work_date is the date from the entry in MySql. I changed the last line in the code substituting $work_date for $period and the date appears in the field.

When I do the same thing with $week and $year I get a blank result. If I look at the table the entry is no longer null but blank.

The PayPeriods table has 4 columns, ID, Year, Week and Period. Year is the year 2007-2034 currently. Week is the ISO week number. Period is a reference we use in our payroll system.

I tried the backticks and I get the same result both $year and $week are blank.

I left off the first line of the code, can't see it making any difference but here it is:

function updatePayPeriod($recordID, $data) {

It seems it's not evaluating:

$week = date_format($work_date,'W');
$year = date_format($work_date,'Y');

[omackeytech]

Just in case you wanted to see it here's the code I used in Eclipse IDE to test my idea. I runs exactly as expected.
I do have to enter an id for it to capture the test row. The ID in AppGini is passed with the call to the function.

Code: Select all

<?php 
$link = mysqli_connect("localhost", "root", "", "TimeTracker3");

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT * from Time_Entry where id = '4'";
    $result = mysqli_query($link,$sql);
        $row = mysqli_fetch_array($result);
        $work_date = new DateTime($row['Day']);
                  
    $week = date_format($work_date,"W");
    $year = date_format($work_date,"Y");  
   
     $sql = "SELECT * FROM payperiods WHERE Week = '$week' and Year = '$year'";
        $result = mysqli_query($link,$sql);
            $row = mysqli_fetch_array($result);
            $period = $row['Period'];
            
         echo "Period = " . $period;
     $sql="update Time_Entry set Period = '$week' where id = 4";
     mysqli_query($link, $sql);
               
  ?> 

[jsetzer]

sql("select Day from Time_Entry where id = '$recordID'", $eo);

Maybe it's just a small mistake:
Did you notice that you are using sql() instead of sqlValue()?

By using function sql() instead of function sqlValue(), you are NOT fetching and returning any value from your database? Your sql("...", $eo)-command will not "automagically" fill any $data array.

So, problably $work_date will not have the expected value because it has not been populated from your db. That was the reason for me for asking you to post and check the value of $work_date.

You are populating $work_date from $data array, not from database. We cannot see the code for initializing $data, so it's not possible to tell, if you have populated the $data-array correctly somewhere before.

If $work_date is not correct, date_format(...) cannot return a correct result, obviously.

If you need Day-value from your database table Time_Entry, you will have to use sqlValue() function instead of sql() function.

Code: Select all

$work_date = sqlValue("SELECT `Day` FROM `Time_Entry` where `id` = '{$recordID}'", $eo);
$week = date_format($work_date,'W');
$year = date_format($work_date,'Y');

// for testing purposes
// remove these lines if everything shows up on screen correctly
var_dump($recordID);
var_dump($eo);
var_dump($work_date);
var_dump($week);
var_dump($year);
exit();

[omackeytech]

Thank you for hanging in there, this has been quite frustrating.

Tried and the results are all blank.
With the sql code I would get the correct date from the database.

I went back to the original code sql() and I get the date but $week and $year are blank. I noticed that the date is formatted 'YYYY-MM-DD' (the way MySql has it in the database).
However I set the display to show MM/DD/YYYY. Could I be confusing the date_format() function?

Just grabbing at straws at this point.

[omackeytech]

Using above code - sqlValue(): (I must have copied it wrong)
string(1) "4" NULL string(10) "2021-03-18" bool(false) bool(false)
Using sql() code:
string(1) "4" NULL string(10) "2021-03-18" bool(false) bool(false)

[jsetzer]

Code: Select all

$recordID = 4;

// $day = sqlValue("SELECT `Day` FROM `Time_Entry` where `id` = '{$recordID}'", $eo);
$day = '2021-03-18';

$work_date = new DateTime($day);
$week = $work_date->format("W");
$year = $work_date->format("Y");

var_dump($week);
var_dump($year);
exit();

Output

Code: Select all

string(2) "11"
string(4) "2021"

[omackeytech]

I tried that code and it's the $work_date=new DateTime($day) that makes it work.
Thank you so much.