Page 1 of 1
How to Load Data from Database into Custom Files (v2022)
Posted: Thu Aug 26, 2021 8:39 am
by mobhar
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
?>
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Aug 26, 2021 1:49 pm
by WABez
Thank you, that is exactly what I'm doing:
<?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):
<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 />
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Aug 26, 2021 2:29 pm
by mobhar
Make sure you have already enabled Include common files option from your Custom File setting, after that re-generate ALL the script files again.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Aug 26, 2021 3:16 pm
by WABez
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.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Sep 16, 2021 3:08 pm
by KORahman
easy way can be --
<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>
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Sep 16, 2021 3:59 pm
by mobhar
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.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Sun Dec 04, 2022 5:36 am
by brahim2007
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
<?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 ?>
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Sun Dec 04, 2022 9:28 am
by mobhar
If you wrote: $sql = "SELECT Count(*) FROMemployees";
then to get the result, you may simply use:
$record_count = ExecuteScalar($sql);
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Sun Dec 04, 2022 7:27 pm
by brahim2007
Thank you
Everything is fine
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Tue Dec 27, 2022 11:27 pm
by craigbert
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.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Wed Dec 28, 2022 8:44 am
by mobhar
You may post your code for more discussion.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Wed Dec 28, 2022 11:24 am
by craigbert
Here is my code:
<?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
<?>
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Wed Dec 28, 2022 12:47 pm
by mobhar
Double check again your code, especially this part:
$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.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Wed Dec 28, 2022 9:52 pm
by craigbert
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:
<?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,.
Re: How to Load Data from Database into Custom Files (v2022)
Posted: Thu Dec 29, 2022 8:55 am
by mobhar
This code:
<?php>
should be:
<?php
And this code:
<?>
shoulde be:
?>