using sql in hooks

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
dhoca
Posts: 11
Joined: 2020-05-14 11:10

using sql in hooks

Post by dhoca » 2020-05-27 21:58

Hi,

I'm trying to look up a User's SpendingLimit when the User clicks on the Insert button to create a new Order record.

I've been able to cobble the following code:

Code: Select all

$j(function(){
    $j('#insert').click(function(){
        $sql = "select SpendingLimit from Users where Email=getMemberInfo($memberInfo['username'])";
        $result = sqlValue($sql); 

        return show_message($result);
    });
})
For some reason, the hook ignores this code.

Further, I'm not familiar with "$j" syntax. Is this javascript? I thought javascript is written in between <script></script> tags os is that just for html files?

Thanks in advance

Newbie

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: using sql in hooks

Post by pfrumkin » 2020-05-27 22:58

It would be helpful to now where you have placed this code. It seems to be a mix of JQuery and PHP. I suggest starting with adding the code in a _before_insert hook, that's just PHP. Unfortunately that doesn't do such a friendly message so may be back to a _dv.js JQuery. In that case you will probably want to look at using AJAX to perform your query. I have a code snippet like below:

tablename_dv.js
function checkName()
{
// Function makes AJAX call to query the database
var tableName = 'buyers';
var f_name = $j( '#f_name' ).val();
var l_name = $j( '#l_name' ).val();
var SelectedID=$j('[name=SelectedID]').val();

$j.ajax(
{
url:'hooks/name_check.php',
type:'post',
dataType:'json',
data:{'full_name':l_name+f_name,'SelectedID':SelectedID,'tableName':tableName},
success:function(resp)
{
if(resp['found']==1)
{
alert('Name not unique. The entries on the form will be cleared.');
$j('#l_name,#f_name').val("");
}
},
error:function(err)
{
console.log(err);
}
});
}

My actual query is in the name_check.php file.

A lot of smart people here, they may have better insight. Also lots of good examples. Good luck!

~Paul

User avatar
D Oliveira
AppGini Super Hero
AppGini Super Hero
Posts: 347
Joined: 2018-03-04 09:30
Location: David

Re: using sql in hooks

Post by D Oliveira » 2020-05-28 05:04

dhoca wrote:
2020-05-27 21:58
Hi,

I'm trying to look up a User's SpendingLimit when the User clicks on the Insert button to create a new Order record.

I've been able to cobble the following code:

Code: Select all

$j(function(){
    $j('#insert').click(function(){
        $sql = "select SpendingLimit from Users where Email=getMemberInfo($memberInfo['username'])";
        $result = sqlValue($sql); 

        return show_message($result);
    });
})
For some reason, the hook ignores this code.

Further, I'm not familiar with "$j" syntax. Is this javascript? I thought javascript is written in between <script></script> tags os is that just for html files?

Thanks in advance

Newbie

maybe this will work:

Code: Select all


$j(function(){

    $j('#insert').click(function(){
    
        var x = '<?php $mi = getMemberInfo(); $sql = "select SpendingLimit from Users where Email = '{$mi['username]}' )"; $result = sqlValue($sql); echo $result;?>';

	console.log('data: '+x);
	alert(x);

    });
    
});


dhoca
Posts: 11
Joined: 2020-05-14 11:10

Re: using sql in hooks

Post by dhoca » 2020-05-28 19:38

Thanks for the suggestion. Unfortunately, it turns out '<?php $mi = getMemberInfo(); $sql = "select SpendingLimit from Users where Email = '{$mi['username]}' )"; $result = sqlValue($sql); echo $result;?>'; is merely treated as text in the alert.

I now understand that Javascript is client side and not easily used to extract data from the database and hence the attempt to use php to query the database. I guess that's where I'm stumbling in getting the server side to talk to the client side.

In my research, I've come across AJAX. Can AJAX be used in appgini? What's the prefered way to query the database in appgini?

Thanks again for the suggestion.

User avatar
D Oliveira
AppGini Super Hero
AppGini Super Hero
Posts: 347
Joined: 2018-03-04 09:30
Location: David

Re: using sql in hooks

Post by D Oliveira » 2020-05-28 21:49

dhoca wrote:
2020-05-28 19:38
Thanks for the suggestion. Unfortunately, it turns out '<?php $mi = getMemberInfo(); $sql = "select SpendingLimit from Users where Email = '{$mi['username]}' )"; $result = sqlValue($sql); echo $result;?>'; is merely treated as text in the alert.

I now understand that Javascript is client side and not easily used to extract data from the database and hence the attempt to use php to query the database. I guess that's where I'm stumbling in getting the server side to talk to the client side.

In my research, I've come across AJAX. Can AJAX be used in appgini? What's the prefered way to query the database in appgini?

Thanks again for the suggestion.
yes you're right, ideally you wanna use AJAX calls, make sure to use function makeSafe() when passing your variables!! that prevents sql injection vulnerabilities, heres how I do it, hope it helps:

js code

Code: Select all

					var xds =  var_to_be_passed; 

								$j.ajax({
									url: 'hooks/ajax-file.php',
									data: { xds_var: xds },
									success: function(data){
										if (data) {
											console.log(data);
										}else{
											console.log('no data');
										}
									}
								});

ajax-file.php

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..'; // assuming file inside hooks folder
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	$con = mysqli_connect($dbServer, $dbUsername, $dbPassword);
	mysqli_select_db($con,$dbDatabase);


	$mi = getMemberInfo();

	$var_coming = makesafe($_REQUEST['xds_var']);

	$sql = "SELECT field FROM table WHERE user = '{$mi['username']}' AND whatever = '{$var_coming}' ";

	$data_returning = sqlValue($sql);

	echo $data_returning;





?>

above example was for single variables returning, to return a table use:


js code

Code: Select all

var xds =  var_to_be_passed; 

								$j.ajax({
									url: 'hooks/ajax-file-table.php',
									data: { xds_var: xds },
									success: function(data){
										if (data) {
											console.log(data);
											
											var tbl = jQuery.parseJSON(data);
											
											if (tbl) {
												console.log(tbl.field_name);
											}
											
										}else{
											console.log('no data');
										}
									}
								});
ajax-file-table.php

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..'; // assuming file inside hooks folder
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	$con = mysqli_connect($dbServer, $dbUsername, $dbPassword);
	mysqli_select_db($con,$dbDatabase);


	$mi = getMemberInfo();

	$xds_var = makesafe($_REQUEST['xds_var']);



	$query = mysqli_query($con,"select * from table where ID= '{$xds_var}'");
	$table_data = mysqli_fetch_array($query);

    echo json_encode($table_data);


?>

dhoca
Posts: 11
Joined: 2020-05-14 11:10

Re: using sql in hooks

Post by dhoca » 2020-05-29 21:34

ideally you wanna use AJAX calls, make sure to use function makeSafe() when passing your variables!! that prevents sql injection vulnerabilities, heres how I do it, hope it helps:

js code
CODE: SELECT ALL

var xds = var_to_be_passed;

$j.ajax({
url: 'hooks/ajax-file.php',
data: { xds_var: xds },
success: function(data){
if (data) {
console.log(data);
}else{
console.log('no data');
}
}
});

ajax-file.php
CODE: SELECT ALL

<?php
$currDir = dirname(__FILE__) . '/..'; // assuming file inside hooks folder
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

$con = mysqli_connect($dbServer, $dbUsername, $dbPassword);
mysqli_select_db($con,$dbDatabase);


$mi = getMemberInfo();

$var_coming = makesafe($_REQUEST['xds_var']);

$sql = "SELECT field FROM table WHERE user = '{$mi['username']}' AND whatever = '{$var_coming}' ";

$data_returning = sqlValue($sql);

echo $data_returning;





?>
This code is really helpful in advancing my education into AJAX. I amended the code to match the database schema:

Code: Select all

$sql = "SELECT SpendingLimit FROM Users WHERE Email = '{$mi['username']}' AND whatever = '{$var_coming}' ";
I think my difficulty now is that whatever information is retrieved from the ajax-file.php is not reaching the js code in the hook because I get a console error when I run the code stating "var_to_be_passed" is not defined. I've even amended the code to state:

Code: Select all

var xds = $data_returning;


but I get the same console error stating $data_returning is not defined.

Any suggestion would be helpful. Thanks.

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: using sql in hooks

Post by pfrumkin » 2020-05-29 23:00

Rather than
echo $data_returning;

try
echo json_encode($data_returning);

Also, I did not need the mysqli_connect() and mysqli_select_db(). If you are dealing with multiple databases, then ok. I am not so I think I get this for free.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: using sql in hooks

Post by pbottcher » 2020-05-30 06:43

Hi,
I think you mix a little bit the JS and PHP area.

The echo $data_returning is the output from the PHP code (called via AjAX).

The calling JS (AJAX Path) is receiving that output in the data variable (in that sample). So you can access that variable and handle it to your needs
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

dhoca
Posts: 11
Joined: 2020-05-14 11:10

Re: using sql in hooks

Post by dhoca » 2020-05-31 18:14

Re: using sql in hooks
Unread post by pfrumkin » 2020-05-29 23:00

Rather than
echo $data_returning;

try
echo json_encode($data_returning);

Also, I did not need the mysqli_connect() and mysqli_select_db(). If you are dealing with multiple databases, then ok. I am not so I think I get this for free.
Here's the revised js code:

Code: Select all

var xds = var_to_be_passed;

$j.ajax({
    url: 'hooks/ajax-file.php',
    data: { xds_var: xds },
    success: function(data){
        if (data) {
            console.log(data);
        }else{
            console.log('no data');
        }
    }
});
Here's the ajax-file.php code:

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..'; // assuming file inside hooks folder
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	//$con = mysqli_connect($dbServer, $dbUsername, $dbPassword);
	//mysqli_select_db($con,$dbDatabase);


	$mi = getMemberInfo();

	$var_coming = makesafe($_REQUEST['xds_var']);

	$sql = "SELECT SpendingLimit FROM Users WHERE Email = '{$mi['username']}' AND whatever = '{$var_coming}' ";

	$data_returning = sqlValue($sql);

	echo json_encode($data_returning);





?>
In the console, it states: "Uncaught ReferenceError: var_to_be_passed is not defined"

If I comment out the line "var xds = var_to_be_passed;" in the js code, then the console states: "Uncaught ReferenceError: xds is not defined"

User avatar
D Oliveira
AppGini Super Hero
AppGini Super Hero
Posts: 347
Joined: 2018-03-04 09:30
Location: David

Re: using sql in hooks

Post by D Oliveira » 2020-06-01 02:14

dhoca wrote:
2020-05-31 18:14
Re: using sql in hooks
Unread post by pfrumkin » 2020-05-29 23:00

Rather than
echo $data_returning;

try
echo json_encode($data_returning);

Also, I did not need the mysqli_connect() and mysqli_select_db(). If you are dealing with multiple databases, then ok. I am not so I think I get this for free.
Here's the revised js code:

Code: Select all

var xds = var_to_be_passed;

$j.ajax({
    url: 'hooks/ajax-file.php',
    data: { xds_var: xds },
    success: function(data){
        if (data) {
            console.log(data);
        }else{
            console.log('no data');
        }
    }
});
Here's the ajax-file.php code:

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..'; // assuming file inside hooks folder
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	//$con = mysqli_connect($dbServer, $dbUsername, $dbPassword);
	//mysqli_select_db($con,$dbDatabase);


	$mi = getMemberInfo();

	$var_coming = makesafe($_REQUEST['xds_var']);

	$sql = "SELECT SpendingLimit FROM Users WHERE Email = '{$mi['username']}' AND whatever = '{$var_coming}' ";

	$data_returning = sqlValue($sql);

	echo json_encode($data_returning);





?>
In the console, it states: "Uncaught ReferenceError: var_to_be_passed is not defined"

If I comment out the line "var xds = var_to_be_passed;" in the js code, then the console states: "Uncaught ReferenceError: xds is not defined"
if you just wanna trigger the AJAX without passing variables then pass '0' as your var_to_be_passed and remove its reference in the SQL statement

Code: Select all

var xds = '0';

Post Reply