Custom php page database connection

[dlee]

I am trying to use the Appgini php functions to connect to my MySQL database.
Here is they code I am using to retrieve the connection settings for the database. I don't get any errors just nothing appears on the screen. Any help would be appreciated!
TD

Code: Select all

<?php

include('../lib.php');

$db_host = config('$dbServer');
$db_user = config('$dbUsername');
$db_pwd = config('$dbPassword');
$db_dbase = config('$dbDatabase');

echo $db_host.'<br>';
echo $db_user.'<br>';
echo $db_pwd.'<br>';
echo $db_dbase;

?>

[jsetzer]

Just use the SQL-functions AppGini provides: sql(), sqlValue().
No need for setting up an own database connection:

Fetch a single value from a single table:

Code: Select all

$value = sqlValue("SELECT count(*) FROM mytable", $err);
// TODO: handle error, if any
// use $value

Fetch multiple rows with all their field-values as array:

Code: Select all

$res = sql("SELECT * FROM mytable", $err);
// TODO: handle error, if any
while ($row = db_fetch_assoc($res)) $rows[] = $row;
// use $rows

That's it. Hope, this answers your question.

---
Off topic

$db_host = config('$dbServer');

If, in a certain situation, you really need database settings from config, you can fetch them like this:

Code: Select all

$db_host = config('dbServer');

Note there is no '$dbServer' but 'dbServer' because that is the key of the array item (without $).

[dlee]

Thank you Jan !

[dlee]

I am not new to coding php but I am new to using the Appgini php functions. With that said I would love to see how you guys would rewrite this code using the Appgini php functions. This is the best way for me to learn

TD

Code: Select all

<?php

	include('../lib.php');

	//$pid = $_POST['pid'];	
	$pid = "1";	

	$db_host = config('dbServer');
	$db_user = config('dbUsername');
	$db_pwd = config('dbPassword');
	$db_dbase = config('dbDatabase');

	//$link = db_connect([$db_host], [$db_user], [$db_pwd], [$db_dbase]);

	$link = mysqli_connect($db_host, $db_user, $db_pwd, $db_dbase);

	/* check connection */
	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}

	$query = "SELECT id, inspection_type, fld_1, fld_2, fld_3, fld_4, fld_5, fld_6, fld_7, fld_8, fld_9, fld_10, fld_11, fld_12, fld_13, fld_14, fld_15, fld_16, fld_17, fld_18, fld_19, fld_20, fld_21, fld_22, fld_23, fld_24, fld_25 FROM inspection_setup WHERE inspection_type=?";

	/* create a prepared statement */
	$stmt = mysqli_stmt_init($link);
	mysqli_stmt_prepare($stmt,$query);

	/* bind parameters for markers */	
	mysqli_stmt_bind_param($stmt, "s", $pid);

	/* execute query */
	mysqli_stmt_execute($stmt);

	/* bind result variables */
			 mysqli_stmt_bind_result($stmt,$id,$inspection_type,$fld_1,$fld_2,$fld_3,$fld_4,$fld_5,$fld_6,$fld_7,$fld_8,$fld_9,$fld_10,$fld_11,$fld_12,$fld_13,$fld_14,$fld_15,$fld_16,$fld_17,$fld_18,$fld_19,$fld_20,$fld_21,$fld_22,$fld_23,$fld_24,$fld_25);

	/* fetch value */
	mysqli_stmt_fetch($stmt);	

	/* concatenate results */
	$results = "";
	for ($x=1;$x<=25;$x++){
		$results = $results.${"fld_".$x}.',';
	}

	echo $results;

    /* close statement */
    mysqli_stmt_close($stmt);

?>

[ppfoong]

The functions are documented, take a look inside these files:
incCommon.php
admin/incFunctions.php

For codes of actual usage, you can have some tables and generate your code with AG, then look inside the generated code.

Use the search function of your editor to find them.

I am not new to coding php but I am new to using the Appgini php functions. With that said I would love to see how you guys would rewrite this code using the Appgini php functions. This is the best way for me to learn

TD

Code: Select all

<?php

	include('../lib.php');

	//$pid = $_POST['pid'];	
	$pid = "1";	

	$db_host = config('dbServer');
	$db_user = config('dbUsername');
	$db_pwd = config('dbPassword');
	$db_dbase = config('dbDatabase');

	//$link = db_connect([$db_host], [$db_user], [$db_pwd], [$db_dbase]);

	$link = mysqli_connect($db_host, $db_user, $db_pwd, $db_dbase);

	/* check connection */
	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}

	$query = "SELECT id, inspection_type, fld_1, fld_2, fld_3, fld_4, fld_5, fld_6, fld_7, fld_8, fld_9, fld_10, fld_11, fld_12, fld_13, fld_14, fld_15, fld_16, fld_17, fld_18, fld_19, fld_20, fld_21, fld_22, fld_23, fld_24, fld_25 FROM inspection_setup WHERE inspection_type=?";

	/* create a prepared statement */
	$stmt = mysqli_stmt_init($link);
	mysqli_stmt_prepare($stmt,$query);

	/* bind parameters for markers */	
	mysqli_stmt_bind_param($stmt, "s", $pid);

	/* execute query */
	mysqli_stmt_execute($stmt);

	/* bind result variables */
			 mysqli_stmt_bind_result($stmt,$id,$inspection_type,$fld_1,$fld_2,$fld_3,$fld_4,$fld_5,$fld_6,$fld_7,$fld_8,$fld_9,$fld_10,$fld_11,$fld_12,$fld_13,$fld_14,$fld_15,$fld_16,$fld_17,$fld_18,$fld_19,$fld_20,$fld_21,$fld_22,$fld_23,$fld_24,$fld_25);

	/* fetch value */
	mysqli_stmt_fetch($stmt);	

	/* concatenate results */
	$results = "";
	for ($x=1;$x<=25;$x++){
		$results = $results.${"fld_".$x}.',';
	}

	echo $results;

    /* close statement */
    mysqli_stmt_close($stmt);

?>

[jsetzer]

I would love to see how you guys would rewrite this code

Looks like you'd like to output some data based on a custom query without any authentication. Let's see how we can give some reusable code to you for learning. I think there is a lot inside including array-functions, some Bootstrap styling of tables, AppGini custom page specifics, AppGini PHP functions for database queries and a first glance at object oriented programming in PHP.

Output

The result.

chrome_4x593JF1Vj.png (46.15 KiB) Viewed 5155 times

(1) Custom Page

Code: Select all

<?php
// file: hooks/custom.php
if (!defined('PREPEND_PATH')) define('PREPEND_PATH', '../');
include(PREPEND_PATH . 'lib.php');
include(PREPEND_PATH . 'header.php');
echo new \Renderers\TableRenderer("SELECT * FROM nodes WHERE parent_id=2 LIMIT 25");
include(PREPEND_PATH . 'footer.php');

(2) Table Renderer

Resusable code. This already handles SQL errors, but not authentication/authorization.

Code: Select all

<?php
// file: resources/lib/Renderers/TableRenderer
namespace Renderers;

class TableRenderer
{
    public function __construct(public readonly string $sql) { /* no-op */ }

    function __toString()
    {
        return $this->renderTable();
    }

    protected function getRecords(): array
    {
        $err = ['silentErrors' => true];
        $res = sql($this->sql, $err);
        if ($err['error']) throw new \Exception("SQL-Error: {$err['error']}", 1);
        $rows = [];
        while ($row = db_fetch_assoc($res)) $rows[] = $row;
        return $rows;
    }

    public function renderTable(): string
    {
        global $Translation;
        try {
            $rows = $this->getRecords();
            if (!count($rows))
                return '<div class="alert alert-info">' . $Translation['No records found'] . '</div>';

            return '<table class="table table-bordered table-hover table-striped">'
                . '<thead><tr>' . implode('', array_map([$this, 'renderTH'], [reset($rows)]))  . '</thead>'
                . '<tbody>' . implode('', array_map([$this, 'renderTR'], $rows)) . '</tbody>'
                . '</table>';
        } catch (\Throwable $th) {
            return '<div class="alert alert-danger">' . $th->getMessage() . '</div>';
        }
    }

    protected function renderTH(array $row): string
    {
        return implode('', array_map(function (string $key) {
            return '<th>' . htmlspecialchars($key) . '</th>';
        }, array_keys($row)));
    }

    protected function renderTR(array $row): string
    {
        return '<tr>' . implode('', array_map([$this, 'renderTD'], array_values($row))) . '</tr>';
    }

    protected function renderTD($value): string
    {
        return '<td>' . htmlspecialchars($value) . '</td>';
    }
}

[dlee]

Thank you ppfoong and thank you Jan !!!