AppGini Community Forums

Posted: **2021-03-17 22:01**

Hi

Using AppGini 5.94 1132

I am having difficulty with a sqlValue statement. I am using this statement as I know that I will only receive either one result or none. I have tried loads of iterations and I always get the same result i.e bool(false). I have modelled the same statement in phpadmin and it works so I know the structure of my command is OK

The data that I am testing for is definitely on the table.

The table I am accessing is called TermDates and is structured as follows:-

TermID Integer, PK
AcademicYr YEAR
Term Integer
StartDate Date UNIQUE
EndDate Date UNIQUE

The code is inserted in hooks/tablename.php file as a before_insert hook. My latest iteration follows . I have tried dog ears('' ') around field names, I have taken out the CONCAT and tried it with just selecting Term, I have removed TermKey. I have tried "case as DATE" for $apptdatestring, StartDate and EndDate. I have even tried them as all CHAR. None worked!

Code: Select all

//		Updating Term from the Terms table using the appt date

		$apptdatestring = $data['ApptDate'];
		$appdate = strtotime($apptdatestring);
		$term = sqlValue("select CONCAT(TermDates.AcademicYr, "/", TermDates.Term) as TermKey from TermDates where '{$apptdatestring}' Between StartDate and EndDate");
		var_dump($apptdatestring, $apptdate, $term);
		exit;

The code modelled in phpadmin (that works fine) follows. At least this proves the table is not currupted in any way

Code: Select all

select CONCAT(TermDates.AcademicYr, "/", TermDates.Term) as TermKey from TermDates where  '2021-3-3' Between StartDate and EndDate

The results I get from the var_dump are always as follows:-

string(8) "2021-3-3" NULL bool(false)

I know I am doing something completely stupid but if anyone could shed any light on this I would be grateful.

Posted: **2021-03-18 06:36**

Please replace "/" by '/' (single quotes) inside your CONCAT function call. Those double quotes will work in you sql tool, but when placed inside the PHP string they will be interpreted differently by PHP.
I recommend logging the SQL command itself. Check the output of $sql variable by executing that SELECT command in your sql tool.
Also, check the error-output after the sqlValue call. The sqlValue function has a second parameter for passing additional control information and especially for returning an error message, if any
$apptdatestring must have the correct date format matching the format being used by your MySQL server (usually YYYY-MM-DD)? I guess this is not the problem here. Maybe you should try with leading zeroes (2 digit day and month), but I guess is is also not the problem here.

Code: Select all

$apptdatestring = $data['ApptDate'];
$appdate = strtotime($apptdatestring);

// preview/check sql command before executing
$sql = "SELECT CONCAT(TermDates.AcademicYr, '/', TermDates.Term) AS TermKey FROM TermDates WHERE '{$apptdatestring}' BETWEEN StartDate AND EndDate";
var_dump($sql); 

$eo = null; // variable may contain an error message
$term = sqlValue($sql, $eo);

var_dump($eo); // dump error message, if any

var_dump($apptdatestring, $apptdate, $term);
exit;

Posted: **2021-03-18 08:22**

Many thanks Jay. I will try your suggestions ASAP and report back.

Posted: **2021-03-18 09:59**

Your solution has worked a treat. I really like your diagnostic technique too. I will certainly use that again if I have similar problems

Many thanks Jan for your time and trouble.

Posted: **2021-03-18 10:06**

Thanks for your kind feedback, I'm happy it works.

I always try to isolate the error and eliminate other influences first. If you don't do that, you can end up searching for an error in the wrong place for a long time, which is a waste of time.

If something doesn't work as expected, debugging variables helps a lot. This way I don't have to guess what value a variable has at that moment, but can safely assume that value at that moment.

AppGini Community Forums

Difficulty with sqlValue statement

Difficulty with sqlValue statement

Re: Difficulty with sqlValue statement

Re: Difficulty with sqlValue statement

Re: Difficulty with sqlValue statement

Re: Difficulty with sqlValue statement