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

Tips submitted by PHPMaker users
Post Reply
mobhar
User
Posts: 11660

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

Post 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

?>

WABez
User
Posts: 199

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

mobhar
User
Posts: 11660

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


WABez
User
Posts: 199

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


KORahman
User
Posts: 8

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

mobhar
User
Posts: 11660

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


brahim2007
User
Posts: 7

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

mobhar
User
Posts: 11660

Post by mobhar »

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

then to get the result, you may simply use:

$record_count = ExecuteScalar($sql);


brahim2007
User
Posts: 7

Post by brahim2007 »

Thank you
Everything is fine


craigbert
User
Posts: 91

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


mobhar
User
Posts: 11660

Post by mobhar »

You may post your code for more discussion.


craigbert
User
Posts: 91

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

mobhar
User
Posts: 11660

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


craigbert
User
Posts: 91

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


mobhar
User
Posts: 11660

Post by mobhar »

This code:
<?php>

should be:
<?php

And this code:
<?>

shoulde be:
?>


Post Reply