Table dinamic with calculation

[fabioo7]

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

[Ch0cu3r]

Your query is most likely producing an error. What is the output of

echo mysql_error();

After running your query?

[Psycho]

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

[fabioo7]

Hello Psycho

Must be like

 

my db   (lc_movimento)

id  |  descricao  |   valor  |  data