accessing the main db via a custom page in hooks

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

accessing the main db via a custom page in hooks

Post by graham » 2022-04-07 13:44

Sorry if this is a silly question .. but I want to add a page in the hooks folder that produces a simple report from data selected from my AG db. It all works fine in my local environment where I used a standard $conn = mysqli_connect script. So when I added the page to my live AG app I thought I would simply leave the standard stuff as the top so AG would control who could access and provide the standard menu then simply amend my $conn = mysqli_connect script to reflect the correct credentials for the online version of the db. (As below.) But no, I get: "Could not connect to database: Access denied for user 'myusername'@'localhost' (using password: YES)". I am Admin and did access the app via my admin prior to navigating to my hook/report.php page.

<?php
define('PREPEND_PATH', '../');
$hooks_dir = dirname(__FILE__);
include("$hooks_dir/../lib.php");

include_once("$hooks_dir/../header.php");

/* grant access to the groups 'Admins' and 'Data entry' */
$mi = getMemberInfo();
if(!in_array($mi['group'], array('Admins', 'Group1' , 'Group2'))){
echo "Access denied";
exit;
}
?>
<?php

<?php
$conn = mysqli_connect("localhost", "user", "pass", "dbname");
//Check the connection
if (!$conn) {
printf("Could not connect to database: %s\n", mysqli_connect_error());
exit();
}
?>
(Yes, the correct credential were used.)
Thanks for any help.

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

Re: accessing the main db via a custom page in hooks

Post by pbottcher » 2022-04-07 20:29

Hi,

just a short question.

Is there any reason why you don't use the internal function sql, sqlvalue. There would be no need to connect to the db upfront?
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.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: accessing the main db via a custom page in hooks

Post by graham » 2022-04-08 08:18

And a short answer - because I don't know how!
So I have a number of SELECT statement on my custom page in the hooks folder - $abc = "SELECT ... $def = "SELECT ... $ghi = "SELECT ... and so on. So if I purely leave the standard code:
<?php
define('PREPEND_PATH', '../');
$hooks_dir = dirname(__FILE__);
include("$hooks_dir/../lib.php");

include_once("$hooks_dir/../header.php");

/* grant access to the groups 'Admins' and 'Data entry' */
$mi = getMemberInfo();
if(!in_array($mi['group'], array('Admins', 'Group1' , 'Group2'))){
echo "Access denied";
exit;
}
?>
<?php

without any attempt from any additional code to connect to the db then I just get bad query (but it works locally when I add a $conn ....)

Thanks for any help.

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

Re: accessing the main db via a custom page in hooks

Post by pbottcher » 2022-04-08 09:30

Well acutally the sql or sqlvalue function wrap all necessary to access your table.
To my view, sql shall be used if you want to retrieve more information than just one value, sqlvalue to retrieve just one value.

So as an example:

Code: Select all

<?php
define('PREPEND_PATH', '../');
$hooks_dir = dirname(__FILE__);
include("$hooks_dir/../lib.php");

include_once("$hooks_dir/../header.php");

/* grant access to the groups 'Admins' and 'Data entry' */
$mi = getMemberInfo();
if(!in_array($mi['group'], array('Admins', 'Group1' , 'Group2'))){
  echo "Access denied";
  exit;
}
$sqlstatement="SELECT ....."  // <-- put here your SQL statement
// assuming that you want to retrieve multiple information
$eo=[];
$res=sql($sqlstatement, $eo);
while ($row=db_fetch_assoc($res)) {
   $result[]=$row;
}
// Now the $result is an array that holds reach row of your retrieved record as associative array;
// output the $result array to your page:
echo "<pre>";
print_r($result);
?>
<?php
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.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: accessing the main db via a custom page in hooks

Post by graham » 2022-04-08 10:18

I have spent a while with the above and I'm sure you're right but I can't get it to work. Here's what I have that works locally when I use my separate $conn to connect:
$sql_training_workshops = "SELECT COUNT(*) FROM training_workshops WHERE date>='01/01/2021' AND date<='31/12/2021'";
$result_training_workshops = mysqli_query($conn, $sql_training_workshops) or die("Bad Query: $sql_training_workshops");
$row_training_workshops = mysqli_fetch_array($result_training_workshops);

No. of training workshops delivered (online and in-person)
<?php echo $row_training_workshops['COUNT(*)']; ?>

and I get my answer 23 (or whatever)

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

Re: accessing the main db via a custom page in hooks

Post by pbottcher » 2022-04-08 10:44

ok, maybe you can just try

Code: Select all

$sql_training_workshops = "SELECT COUNT(*)  FROM training_workshops WHERE date>='01/01/2021' AND date<='31/12/2021'";
$count=sqlvalue($sql_training_workshops);  // in this case you only retrieve one value
echo $count;
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.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: accessing the main db via a custom page in hooks

Post by graham » 2022-04-08 13:30

It works, thank you. But one odd thing if you have a brief moment ... my SELECT query "SELECT COUNT(*) FROM training_workshops WHERE date>='01/01/2021' AND date<='31/12/2021'"; doesn't work - the result is shown as the total number of training workshops in the db - it's not taking note of the date range. (This is the same on both local and live version.) BUT if I change the query for example, "SELECT COUNT(*) FROM training_workshops WHERE country='Thailand'"; then I get the correct results both locally and live. So I can see your code works - but I it's a niggle that my date range query doesn't work?

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

Re: accessing the main db via a custom page in hooks

Post by pbottcher » 2022-04-08 14:52

Hi,

glad it works.

You may need to user

Code: Select all

SELECT COUNT(*) FROM training_workshops WHERE  `date` >'2021-01-01' AND `date` <'2021-12-31'
Watch the correct date representation in MYSQL

Note: using a fieldname like DATE is also dangerous as it may conflict with internal functions, ... of MYSQL.
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.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: accessing the main db via a custom page in hooks

Post by graham » 2022-04-08 16:44

Great. All points noted and thank you very much for your time and trouble. (So it wise not to name a table field as 'date'?)

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: accessing the main db via a custom page in hooks

Post by graham » 2022-04-12 18:55

Sorry for the suggestion of clarification on using 'date' as a field name ... a quick google would have been appropriate!

Post Reply