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

PHPMaker User Submitted Tips (PHPMaker)
1

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

?>
2

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 />
3

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

4

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.

5

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>
6

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.

7

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 ?>
8

If you wrote: $sql = “SELECT Count(*) FROMemployees”;then to get the result, you may simply use:$record_count = ExecuteScalar($sql);

9

Thank you
Everything is fine

10

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.

11

You may post your code for more discussion.

12

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
<?>
13

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.

14

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,.

15

This code:

<?php>should be: <?phpAnd this code: <?>shoulde be:

?>