Jump to content

Table dinamic with calculation


fabioo7

Recommended Posts

Hello Friends 
? Could anyone help me 
 
This is my name mysql database: lc_movimento 
| Id | data | description | value 
 
I'm using this code below is giving the error. could someone tell me what I doing wrong
 
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in
 
<?php
include"config.php";

//$pdo = new PDO("mysql:host=localhost;dbname=mrangelc_teste", "mrangelc_01", "kov6095" );


//$pdo = new PDO('pgsql:<conn-string>');


$query = mysql_query("select 
  (('month',CURRENT_DATE) + CAST(FLOOR(s.idx / 5) || ' day' as interval))::DATE AS data, 
  'descricao' as descricao,
  CASE WHEN random() < 0.5 THEN ROUND(150 + (random()*100)::numeric,2) ELSE ROUND(150 + (random()*100)::numeric,2) * -1 END as valor
from lc_movimento(1,24) as s(idx)");

$ultimaData = null;
$acumulado = 0;
$totalEntradaDia = 0;
$totalSaidaDia = 0;

echo '<table>';

while ($resultado = mysql_fetch_array($query)) {
	$date = DateTime::createFromFormat('Y-m-d', $row['data']);
	$key = $date->format('Ymd');

	if($ultimaData !== null && $ultimaData != $key) {
		printf('<tr><td colspan="2">Entrada: %s, Saida: %s, Saldo dia: %s, Saldo acumulado: %s</td></tr>',
			number_format($totalEntradaDia,2,',','.'),
			number_format($totalSaidaDia,2,',','.'),
			number_format($totalEntradaDia + $totalSaidaDia,2,',','.'),
			number_format($acumulado,2,',','.')
		);

		$totalSaidaDia = 0;
		$totalEntradaDia = 0;
	}

	$ultimaData = $key;

	printf('<tr><td>%s</td><td>%s</td><td>R$ %s</td></tr>',
		$date->format('d'),
		$row['descricao'],
		number_format($row['valor'],2,',','.')
	);

	if($row['valor'] > 0) {
		$totalEntradaDia += $row['valor'];
	} else {
		$totalSaidaDia += $row['valor'];
	}

	$acumulado += $row['valor'];
}

echo '</table>';

printf('<tr><td colspan="2">Entrada: %s, Saida: %s, Saldo dia: %s, Saldo acumulado: %s</td></tr>',
	number_format($totalEntradaDia,2,',','.'),
	number_format($totalSaidaDia,2,',','.'),
	number_format($totalEntradaDia + $totalSaidaDia,2,',','.'),
	number_format($acumulado,2,',','.')
);

Thanks to all

Link to comment
Share on other sites

There's definitely something wrong with that query. But, to be honest, I don't understand much of it:

 

 

(('month',CURRENT_DATE) + CAST(FLOOR(s.idx / 5) || ' day' as interval))::DATE AS data,

I have no clue what that is trying to retrieve. For example, what is ('month',CURRENT_DATE) supposed to do? I'm thinking you are missing a MySQL function for that parenthesized section. Also, the ::DATE is something I've never seen before.

 

 

'descricao' as descricao,

No need to alias something as the same name it already is.

 

 

 CASE
    WHEN random() < 0.5 THEN ROUND(150 + (random()*100)::numeric,2)
    ELSE ROUND(150 + (random()*100)::numeric,2) * -1
  END as valor

I don't use CASE statements in MySQL very often, so I can't say if that syntax is correct. But, a CASE statement should be used when you have many options. For that, I would just use an IF() statement. But, regardless of which one you use I see a problem. You are using random() as part of the condition and then use it within the value. I am pretty sure that each instance of random() will be different. For example, you are using a "* -1" if the first random() is less than .5, but the random()'s used in the calculation can be different values entirely. I'm guessing there is a better way to accomplish that so the random() value for each record will be the same random. But, one solution I can think of is to create a temporary table with a single random value for each record. Then run a second query to do the calculations.

 

 

 

FROM lc_movimento(1,24) as s(idx)

I have no idea what the (idx) is for

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.