how to return a row from database and get it

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
landrea
Posts: 27
Joined: 2018-08-04 13:09

how to return a row from database and get it

Post by landrea » 2019-03-17 10:13

I have a theorical problem,
I probably will need to do a ajax call to a php file in my hooks folder

this time I probably will need to retrun a row from the database and not a single value,
I think I can't echo an array like I can echo a simpe string with a a value, I'm not sure because I sometimes confuse js with php,
so I would ask what is the best way to do it

I though to create a string with a convection like for example separe the values with a pipe line or something and in the other side create the array in js parsing the string

or pheraps I can convert easy the array row from the database into a json (I think there are methods do it automatically) and echo this json that surelly you can hanlde inside you ajax call back and then manipolate the form how you want with js

if people want suggest me something about what is better to do (I'm oriented about echo the json format of the row of database)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: how to return a row from database and get it

Post by jsetzer » 2019-03-17 11:32

I strongly recommend using JSON as de-facto standard nowadays.

On serverside (PHP) you can return data for example like this:

Code: Select all


// include lib.php
// check authorization using getMemberInfo() function
// get data from your db for example by using AppGini's sql("...", $eo) function:
// $rows = ...
// if there is only one row, this does not matter.

$result = [
    'success' => true,
    'rows' => $rows
];

echo json_encode($result);
exit();
There are different ways on clientside, for example you can use JQuery to POST your request to the server and parse the responded result like this:

Code: Select all

$j.ajax({
  method: "POST",
  url: "hooks/YOURSCRIPTNAME.php",
  data: { /* pass additional data from the client to the server */}
}).done(function (data) {
  console.log(data); // for testing purposes. Watch the console output in your browsers developer tools
  data = JSON.parse(data);
  console.log(data); // for testing purposes. Watch the console output in your browsers developer tools
  if (data.success) {
    // you can evaluate and use data.rows now
    // ...
  }
});
Related links

PHP
http://php.net/manual/de/function.json-encode.php

JQuery
https://api.jquery.com/jquery.post/

Attention:
When using JQuery in AppGini, you'll have to use "$j" instead of "$"


Best regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: how to return a row from database and get it

Post by landrea » 2019-03-17 11:56

at the moment I made this, I'm checking without the ajax, I'm sending parameter directly in the url
I dont' know if have a sense, it seems working,
i added makesafe for security reason, pheraps is not necessary in this case, I think I'm ok
at the moment I added a header to test better the data, I dont' know if this create problem in the ajax call, I dont' thnk

it's ok what I wrote? I can use or is better change something?


<?php
$currDir = dirname(__FILE__) . '/..';
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

/* PERMESSO DI VISUALIZZAZIONE - TABELLA */
$od_from = get_sql_from('dt_aule');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

// INTERCETTA FK AULA
$aula_id = makeSafe(intval($_REQUEST['aula_id']));

// CREA SQL - SELEZIONA TUTTI I CAMPI
$res = sql("select * from dt_plessi where id_plesso='{$aula_id}'", $eo);

// CREA JSON
while($row = db_fetch_assoc($res)){

header('Content-type: application/json');
echo json_encode($row, JSON_PRETTY_PRINT);

} // fine while

?>

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: how to return a row from database and get it

Post by jsetzer » 2019-03-17 12:02

You should fetch all records first, store them in an array and then return ONE result to the client. In other words: Do not echo the json-string within your while-loop.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: how to return a row from database and get it

Post by jsetzer » 2019-03-17 12:06

Code: Select all

// CREA SQL - SELEZIONA TUTTI I CAMPI
$res = sql("select * from dt_plessi where id_plesso='{$aula_id}'", $eo);
$rows = [];

while($row = db_fetch_assoc($res)) {	
    $rows[] = $row;
}

echo json_encode($rows);
exit();
or

Code: Select all

$res = sql("select * from dt_plessi where id_plesso='{$aula_id}'", $eo);
$rows = [];
while($row = db_fetch_assoc($res)) 
  $rows[] = $row;

$result = [
  'success' => true,
  'rows' => $rows
];

echo json_encode($result);
exit();

Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

landrea
Posts: 27
Joined: 2018-08-04 13:09

Re: how to return a row from database and get it

Post by landrea » 2019-03-17 12:38

ok thanks , I rewrite better,
in this way I can adapt the script also situation I can have more rows and not only a row,
I'm probably going to use the template for many works

at the moment I dont' have the real form for testing the ajax,
I still have to do it, but I dont' think I'm going to have problems following your suggestion or the offical documentation

then is simple question to use js and write in the form something, because my idea is create a separate bootstramp tab in the form with these values come from a foreign table associated are automatically filled by the ajax, that's pheraps is not the best idea, because you store more datas you already have, but my users want' to see it in the apllication and use it for search thing in this table, to export it in a csv or excel like, so I think is easy soluction instead to find a way to create view or join these tables in a separate custom page instead to do it in the table itself and the form itself

I need to do only in a specific table, so I dont' think I waste many resources storing datas two times or I violate main rules about normalization and order in a database, I don't have many experience, project is not going to have milion of data, so I try to do my best


<?php
$currDir = dirname(__FILE__) . '/..';
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

/* PERMESSO DI VISUALIZZAZIONE - TABELLA */
$od_from = get_sql_from('dt_aule');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

// INTERCETTA FK AULA
$aula_id = makeSafe(intval($_REQUEST['aula_id']));

// CREA SQL - SELEZIONA TUTTI I CAMPI - PREDISPONE ARRAY ROWS
$res = sql("select * from dt_plessi where id_plesso='{$aula_id}'", $eo);
$rows = [];

// CREA ARRAY
while($row = db_fetch_assoc($res)) {
$rows[] = $row;
} // fine while

$risultato = [
'success' => true,
'rows' => $rows
];


header('Content-type: application/json');
echo json_encode($risultato, JSON_PRETTY_PRINT);
exit();

?>

Post Reply