Difficulty with sqlValue statement

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
iwilliams
Posts: 27
Joined: 2020-10-24 15:10

Difficulty with sqlValue statement

Post by iwilliams » 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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Difficulty with sqlValue statement

Post by jsetzer » 2021-03-18 06:36

  1. 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.
  2. I recommend logging the SQL command itself. Check the output of $sql variable by executing that SELECT command in your sql tool.
  3. 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
  4. $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;
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

iwilliams
Posts: 27
Joined: 2020-10-24 15:10

Re: Difficulty with sqlValue statement

Post by iwilliams » 2021-03-18 08:22

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

iwilliams
Posts: 27
Joined: 2020-10-24 15:10

Re: Difficulty with sqlValue statement

Post by iwilliams » 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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Difficulty with sqlValue statement

Post by jsetzer » 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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply