Hello, I would like to know how to use DbHelper() or another. Search and lock the result with Custom File.
Cliente
idC - nome
1 - John
Produto
idP - produto - valor
1 - café - 1.00
2 - pão - 2.00
Venda
idV - idC - idP - valor
1 - 1 - 1 - 1.00
Has as?
I don't think Custom File support search functionality (since there is no actual table on custom file).
You can do this way:
Your layout will then looks like this:
[Master (Client), view mode]
[Detail (Sale), list mode]
You can, then, remove the unwanted-column from [Detail]. If you don't need any condition for the List page of this table, just uncheck the column from Field Setting -> List.
And...
You can do search on them BOTH.
Venda
idV - idC - idP - valor
1 - 1 - 1 - 1.00
Hello, can someone give me an example query with two tables?
This can be the example with Customers and Products tables. Cited above.
Can I use DbHelper() or LoadRecordset()
I would like to bring the result of the Sale table and not repeat the client again using the Custom file.
can someone give me an example query with two tables?
I dont speak the language in which you tables are named so my guess for what sales are might be wrong, but here goes
If I understand correctly, you want to query for a client once and then query for his sales after, if so then you can use LoadRecordset to do your own loop and put a loop indide another that way you only have to pick the cleins once and then read his sales, forexample If you want to do this in a custom file it would be like.
<?
$project_db =& DbHelper();
//loop throught all clinets
$all_clients = $project_db->LoadRecordset("select idC,nome from Cliente");
foreach( $all_clients as $a_client)
{
//read a client info
$client_idC = $a_client["idC"];
$client_nome = $a_client["nome"];
//start rendering this in a table if you want, am just ehoing it for now
echo "<br>".$client_nome;
//query and loop htrough the sales of this client
//NOTE we are going to join sales and product so as to get product name of a sale
//This is basic sql google sql joins and views for more
//we are returning only neccessary info
$all_sales = $project_db->LoadRecordset("select Produto.produto as product_name, Venda.valor as sold_quantity from Venda join Produto on Produto.idP = Venda.idP where Venda.idC = '".$client_idC."'");
foreach( $all_sales as $a_sale)
{
//read a sale info info
$product_name = $a_sale["product_name"];
$sold_quantity = $a_client["sold_quantity"];
//start rendering this in a table if you want, am just ehoing it for now
echo "<br>Sold ".$sold_quantity." of ".$product_name;
//query and loop htrough the sales of this client
}}
?>
HINT: this same type of quering can still be done using ew_LoadRecordset in any of phpmaker events if you need to
NOTE: If in the end you want to render the above in a well formatted table which can also support export sorting searching etc, then its easier if you use master/detail or just a normal list of a join like others had ealier suggested, but if a custom file works for what you want then its ok, other wise what do you want to achieve here
Very good, thank you.
I have some instructions on mysql_query which no longer work on PHPMaker 2018.
<?php
// saber ITEM
$sql_item = mysql_query("SELECT
con_rel_saldo_item_nat.AnoNome,
con_rel_saldo_item_nat.NomeItemProgramacao
FROM
con_rel_saldo_item_nat
GROUP BY
con_rel_saldo_item_nat.AnoNome,
con_rel_saldo_item_nat.NomeItemProgramacao
HAVING
con_rel_saldo_item_nat.AnoNome = $Ano_ID") or die ("Erro no ITEM");
the command: (mysql_query) no longer works?
mysql_query is depreciated, so its better ot use mysqli, BUT in phpmaker you can use thier dbhelper or ew_Execute database fucntions since they also impliment mysqli for you
what am getting is you want to get all con_rel_saldo_item_nat records where the AnoNome = the value in $Ano_ID.
So do something like.
<?php
//ENSURE YOU HAVE SET value for $Ano_ID at this point
//isolate the query in its own variable, just so we can look at it later if we want.
//NOTE:Ive added quotes around $Ano_ID such that if AnoNome is a string or not it will still work
$sql_query = "SELECT
con_rel_saldo_item_nat.AnoNome,
con_rel_saldo_item_nat.NomeItemProgramacao
FROM
con_rel_saldo_item_nat
GROUP BY
con_rel_saldo_item_nat.AnoNome,
con_rel_saldo_item_nat.NomeItemProgramacao
HAVING
con_rel_saldo_item_nat.AnoNome = '$Ano_ID'";
//you can see what the query looks like so you can run it manually in mysql and test if it works.
echo $sql_query;
//since it returns a record set we need to loop through it
$project_db =& DbHelper();
//loop throught all rows if any
$all_rows = $project_db->LoadRecordset($sql_query);
foreach( $all_rows as $a_row)
{
//read info
$AnoNome = $a_row["AnoNome"];
$NomeItemProgramacao = $a_row["NomeItemProgramacao"];
echo "<br>".$AnoNome." - ".$NomeItemProgramacao;
}
?>
Hello,
As suggested by kirondedshem. Instead of using mysql_query give a try to:
$project_db =& DbHelper();
$all_rows = $project_db->LoadRecordset("Your SQL Query goes here");
It is more in line with PHPMaker will be upgrade proof. Meaning it will not break with newer version of PHPMaker.
I created a form to grab data from one table and pass as variables to another page.
I would like to know how to stay in the same format as the HTML generated by Phpmaker2018.
example:
<form action="acao.php" method="post" name="rel_acom_credor_adm" target="_blank">
<font size="1"><b>ANO LOA </b></font></span>
<?php
$sql_ano = ew_LoadRecordset("SELECT ano.AnoID, ano.AnoNome FROM ano ORDER BY AnoNome");
$linhas = $sql_ano->GetRows();
?>
<select id="loa" name="loa">
<option value="0" selected="selected">Selecione</option>
<?php foreach($linhas AS $linha){ ?>
<option value="<?Php echo $linha["AnoID"]; ?>"><?Php echo $linha["AnoNome"]; ?></option>
<?php } ?>
</select>
<br>
<br>
<button class="btn btn-primary ewButton" name="btnAction" id="btnAction" type="submit">Gerar Relatório</button>
</form>
Almeida 3A wrote:
I would like to know how to stay in the same format as the HTML generated by Phpmaker2018.
There are some classes that you need to add to your code. Just refer to the form on Add or Edit Page. View Page Source of the page from your browser, then you should be able to adjust your code with that references.