How to Load Data from Database into Custom Files (v2022)

mobhar · Post by **mobhar** » Thu Aug 26, 2021 8:39 am

To get data from database into Custom Files, basically v2022 is same with v2021.

You may try this from demo2022 project, by creating a new Custom File, for example, with the following settings:

File Name: getemployees.php
Caption: Get Employees
Include common files: enabled

<?php

    $sql = "SELECT `FirstName`, `LastName` FROM `employees`"; // define your SQL
    $stmt = ExecuteQuery($sql); // execute the query
    $displayResult = ""; // initial display result
    if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0
        while ($row = $stmt->fetch()) { // begin of loop
            $displayResult .= $row["FirstName"] . " " . $row["LastName"] . "<br>"; // in case the result returns more than one row, separated by line break
        } // end of loop
        echo "Here is the Employees list: <br>" . $displayResult; // display the result
    } else { // if there are no result
        echo "No records are found."; // display the message
    } // end of check condition

?>

WABez · Post by **WABez** » Thu Aug 26, 2021 1:49 pm

Thank you, that is exactly what I'm doing:

Code: Select all

<?php
   	// some code
   	$sql = "SELECT * FROM 'event'";
	$eventsQuery = ExecuteQuery($sql);
   	// some more code
?>

and when I run this, I get the following error" Call to undefined function ExecuteQuery() (F12 - inspecting the console output):

Code: Select all

<br />
<b>Fatal error</b>:  Uncaught Error: Call to undefined function ExecuteQuery() in C:\Apache24\htdocs\phpcal\ajaxcalls\ajaxcalendarevents.php:11
Stack trace:
#0 {main}
  thrown in <b>C:\Apache24\htdocs\phpcal\ajaxcalls\ajaxcalendarevents.php</b> on line <b>11</b><br />

mobhar · Post by **mobhar** » Thu Aug 26, 2021 2:29 pm

Make sure you have already enabled Include common files option from your Custom File setting, after that re-generate ALL the script files again.

WABez · Post by **WABez** » Thu Aug 26, 2021 3:04 pm

I have also tested with namespace PHPMaker2021\MyProject;, and still getting the same error: Call to undefined function ExecuteQuery()

However, from more "digging", I came across this post about the new Route_Action server event, and this seems to be the way going forward instead of having my own custom files for the ajax calls.

However, I'm new to the SLIM Framework and I'm trying to integrate Fullcalendar with my (test) project. How do you then call the function from the javascript code? In Fullcalendar it would be:

Code: Select all

....
events: "myPHPajaxFile.php";
...

Any ideas would be welcome and appreciated?

WABez · Post by **WABez** » Thu Aug 26, 2021 3:16 pm

mobhar wrote:

Make sure you have already enabled Include common files option from your Custom File setting, after that re-generate ALL the script files again.

Thank you. I can go and bump my head against a wall for being such an idiot. That was what I was missing, had it "checked" on all files except this one!

However I would now still try the new Route_Action and learn some more.

WABez · Post by **WABez** » Thu Aug 26, 2021 4:59 pm

I think I should just dump this idea and look into the Route_Action option. I have now hard coded a test array with the following results (F12 > Network > Select file > Response):

With Include common files unchecked, I get the desired result from my hard coded test array (using json_encode() to convert to JSON) - Response = JSON data!
-> BUT, trying to execute the query, I get Call to undefined function ExecuteQuery()
With Include common files checked, the Response is all messed up (using json_encode() to convert to JSON) - however Response = the complete HTML/PHP/... code for the page!
-> No matter what I do, it does not give me the "Call to undefined function ExecuteQuery()" error, but I don't get any desired data.

I'm stumped with this result...

WABez · Post by **WABez** » Thu Aug 26, 2021 9:10 pm

Ok, resolved. Damn naming convention that I browsed over many times and missed..... should not be doing this when overly tired!

KORahman · Post by **KORahman** » Thu Sep 16, 2021 3:08 pm

easy way can be --

Code: Select all

<div class="card">
	<div class="card-header">
		<h3 class="card-title">ANY THING U WRITE HERE AS A HEADER</h3>
	</div>
	<div class="card-body">
<?php
	$sql = "  SELECT * from YOUR_TBL_NAME"; 
	Write(ExecuteHtml($sql, ["fieldcaption" => true, "tablename" => ["YOUR_TBL_NAME" ]]));
?>	
</div>
</div>

mobhar · Post by **mobhar** » Thu Sep 16, 2021 3:59 pm

The main idea from the first post above is how we can get data by using loop through the recordset, so that we can format or do anything we want while looping from the recordset.

brahim2007 · Post by **brahim2007** » Sun Dec 04, 2022 5:36 am

Hey,
How can we get to count the number of employees in the database instead of the first name and last name?
Please correct the attached query
Thank you

Code: Select all

<?php $sql = "SELECT Count(*) FROM `employees`"; // define your SQL
$stmt = ExecuteQuery($sql); // execute the query
$displayResult = ""; // initial display result
if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0
    while ($row = $stmt->fetch()) { // begin of loop
        $displayResult .= $row["FirstName"] . " " . $row["LastName"] . "<br>"; // in case the result returns more than one row, separated by line break
    } // end of loop
    echo "Here is the Employees list: <br>" . $displayResult; // display the result
} else { // if there are no result
    echo "No records are found."; // display the message
} // end of check condition ?>

mobhar · Post by **mobhar** » Sun Dec 04, 2022 9:28 am

If you wrote: $sql = "SELECT Count(*) FROMemployees";

then to get the result, you may simply use:

$record_count = ExecuteScalar($sql);

brahim2007 · Post by **brahim2007** » Sun Dec 04, 2022 7:27 pm

Thank you
Everything is fine

craigbert · Post by **craigbert** » Tue Dec 27, 2022 11:27 pm

Hi All,

I am doing something wrong here.
I am putting the sample code in my app (I changed the SQL statement to pull from one of my tables) and when the page loads all I see on the page is the code.
The code was placed in the Custom Templates -> Table Specific -> Custom File -> Content
I am not sure that is the correct spot for my custom file. Will this code then be executed for ALL my custom files? I only have the one right now, but there could be others in the future.

Any idea where I am going awry here?

Thanks.

mobhar · Post by **mobhar** » Wed Dec 28, 2022 8:44 am

You may post your code for more discussion.

craigbert · Post by **craigbert** » Wed Dec 28, 2022 11:24 am

Here is my code:

Code: Select all

<?php>
$sql = "select `user_id`, `first_name`, `last_name`, `email_address`, `active_ind`, `user_level_id`, `primary_acct_ind`, `parent_user_id`
from `app_user`
where `parent_user_id` = 2
"
$stmt = ExecuteQuery($sql); // execute the query
$displayResult = ""; // initial display result

    if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0
        while ($row = $stmt->fetch()) { // begin of loop
            $displayResult .= $row["first_name"] . " " . $row["last_name"] . "<br>"; // in case the result returns more than one row, separated by line break
        } // end of loop
        echo "Here is the Account Members list: <br>" . $displayResult; // display the result
    } else { // if there are no result
        echo "No records are found."; // display the message
    } // end of check condition
<?>

mobhar · Post by **mobhar** » Wed Dec 28, 2022 12:47 pm

Double check again your code, especially this part:

Code: Select all

$sql = "select `user_id`, `first_name`, `last_name`, `email_address`, `active_ind`, `user_level_id`, `primary_acct_ind`, `parent_user_id`
from `app_user`
where `parent_user_id` = 2
"

It seems you missed semicolon character at the end after the last double quote character.

craigbert · Post by **craigbert** » Wed Dec 28, 2022 9:52 pm

Hi mobhar,

Thanks for catching that silly mistake on my part, however it is still spitting out all the code between the PHP tags instead of outputting any data. I know for a fact that there is one record meets this criteria and is returned when I run that SQL in a database session.

Here is the updated code:

Code: Select all

<?php>
$sql = "select `user_id`, `first_name`, `last_name`, `email_address`, `active_ind`, `user_level_id`, `primary_acct_ind`, `parent_user_id` from `app_user` where `parent_user_id` = 2";
$stmt = ExecuteQuery($sql); // execute the query
$displayResult = ""; // initial display result

    if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0
        while ($row = $stmt->fetch()) { // begin of loop
            $displayResult .= $row["first_name"] . " " . $row["last_name"] . "<br>"; // in case the result returns more than one row, separated by line break
        } // end of loop
        echo "Here is the Account Members list: <br>" . $displayResult; // display the result
    } else { // if there are no result
        echo "No records are found."; // display the message
    } // end of check condition
<?>

Any other thoughts?
Thanks,.

mobhar · Post by **mobhar** » Thu Dec 29, 2022 8:55 am

This code:
<?php>

should be:
<?php

And this code:
<?>

shoulde be:
?>