Jump to content

show query result on table horizontally


gralfitox
Go to solution Solved by Barand,

Recommended Posts

I´ve reached to

    <?php require_once('Connections/conexxion.php'); ?>
    <?php
    mysql_select_db($database_conexxion, $conexxion);
    $query_consulta = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc";
    $consulta = mysql_query($query_consulta, $conexxion) or die(mysql_error());
    $row_consulta = mysql_fetch_assoc($consulta);
    $totalRows_consulta = mysql_num_rows($consulta);
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>test</title>
    </head>
     
    <body>
     
       <?php
    echo "<table align=center>";
     
    $columnes = 10; # Número de columnas (variable)
     
    if (($rows=mysql_num_rows($consulta))==0) {
      echo "<tr><td colspan=$columnes>No hay resultados en la BD.</td></tr> ";
    } else {
      echo "<tr><td colspan=$columnes>$rows Resultados </td></tr>";
    }
     
    for ($i=1; $row = mysql_fetch_row ($consulta); $i++) {
    $resto = ($i % $columnes); # Número de celda del <tr> en que nos encontramos
    if ($resto == 1) {echo "<tr>";} # Si es la primera celda, abrimos <tr>
       echo "<td>$row[1]</td>";
    if ($resto == 0) {echo "</tr>";} # Si es la última celda, cerramos </tr>
    }
    if ($resto <> 0) { # Si el resultado no es múltiple de $columnes acabamos de rellenar los huecos
    $ajust = $columnes - $resto; # Número de huecos necesarios
    for ($j = 0; $j < $ajust; $j++) {echo "<td> </td>";}
    echo "</tr>"; # Cerramos la última línea </tr>
    }
    mysql_close($connexion);
    echo "</table>";
    ?>
     
     
     
    </body>
    </html>
    <?php
    mysql_free_result($consulta);
    ?>

it "works", but results doesn´t start at number 1 but number 2

 

anyways, The intent of this table is to show the stock of a product, a lens, that has a range of Rx, we use to see this range this way, and show the stock this way would be very productive, but I can´t figure out how to achive it, any sugestion??

 

IMG_20141214_123617428.png

Edited by gralfitox
Link to comment
Share on other sites

Which field (A) provides the values for the column headings?

Which field (B) provides the values for the row headings?

Which field © provides the values for the totals?

 

You would need something like

SELECT A, B, SUM(C) as C
FROM ...
GROUP BY A, B

then a while() loop to process the results.

Output a row of totals for B when A changes

Link to comment
Share on other sites

Your images are broken. I assume you have some type of "property" to use as the column headers that specifies some type of description for the records and you want to show the quantity in stock for each item by that property. Something like this?

Name   | Blue | Green | Red | Yellow
---------------------------------------
Item 1 |   5  |   6   |  6  |  17
Item 2 |   2  |   6   |  0  |   9
Item 3 |  12  |  13   |  7  |   4
Item 4 |   4  |   6   |  0  |   3
Item 5 |   0  |   6   |  3  |  23

(with a possible "total" column at the end). If that is correct,then I assume it is possible for some records to have a 0 quantity for some columns. Are the columns "fixed" or would you be retrieving them from the database? Your current query is grouping by the item_id. To do the above we would also need to group by the column header value. What field would represent the column headers?

Edited by Psycho
Link to comment
Share on other sites

Your images are broken. I assume you have some type of "property" to use as the column headers that specifies some type of description for the records and you want to show the quantity in stock for each item by that property. Something like this?

Name   | Blue | Green | Red | Yellow---------------------------------------Item 1 |   5  |   6   |  6  |  17Item 2 |   2  |   6   |  0  |   9Item 3 |  12  |  13   |  7  |   4Item 4 |   4  |   6   |  0  |   3Item 5 |   0  |   6   |  3  |  23
(with a possible "total" column at the end). If that is correct,then I assume it is possible for some records to have a 0 quantity for some columns. Are the columns "fixed" or would you be retrieving them from the database? Your current query is grouping by the item_id. To do the above we would also need to group by the column header value. What field would represent the column headers?
Ok, so column heading is the cilindro.cilindro value and row heading is esfera.esfera value. I'm at my mobile and have no access to my db. I'll post my schema model to see what's possible to do

 

Let me try to explain myself again, I'm not english speaker so sorry about it.

 

I have filled the movimiento table with all possible rx combinations for testing pourposes. So i have 1 item id 1 in stock with rx 4.00 esf 0.00 cil

2 item id 1 in stock with rx 3.75 esf 0.00 cil

3 item id 1 in stock with rx 3.75 esf -0.25 cil

4 item id 1 in stock with rx 3.50 esf 0.00 cil

 

An so on, is clear this way?

Edited by gralfitox
Link to comment
Share on other sites

 

 

Are the columns "fixed" or would you be retrieving them from the database?
This is important. Do you have a table with all the possible values of "cilindro"? If not, and there is any value where the quantity for all products is 0 - there would be no records in the result set.
Link to comment
Share on other sites

well, studying a little I´ve written this

	<?php require_once('Connections/conexxion.php'); ?>
		<?php
	if (!function_exists("GetSQLValueString")) {
	function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
	{
	  if (PHP_VERSION < 6) {
		$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
	  }
	
	  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
	
	  switch ($theType) {
		case "text":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;    
		case "long":
		case "int":
		  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
		  break;
		case "double":
		  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
		  break;
		case "date":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;
		case "defined":
		  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
		  break;
	  }
	  return $theValue;
	}
	}
	
		if (!function_exists("GetSQLValueString")) {
		function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
		{
		  if (PHP_VERSION < 6) {
			$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
		  }
		
		  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
		
		  switch ($theType) {
			case "text":
			  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
			  break;    
			case "long":
			case "int":
			  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
			  break;
			case "double":
			  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
			  break;
			case "date":
			  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
			  break;
			case "defined":
			  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
			  break;
		  }
		  return $theValue;
		}
		}
		
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_Recordset1 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 1";
		$Recordset1 = mysql_query($query_Recordset1, $conexxion) or die(mysql_error());
		$row_Recordset1 = mysql_fetch_assoc($Recordset1);
		$totalRows_Recordset1 = mysql_num_rows($Recordset1);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila2 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 1,2";
		$fila2 = mysql_query($query_fila2, $conexxion) or die(mysql_error());
		$row_fila2 = mysql_fetch_assoc($fila2);
		$totalRows_fila2 = mysql_num_rows($fila2);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila3 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 3,3";
		$fila3 = mysql_query($query_fila3, $conexxion) or die(mysql_error());
		$row_fila3 = mysql_fetch_assoc($fila3);
		$totalRows_fila3 = mysql_num_rows($fila3);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila4 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 6,4";
		$fila4 = mysql_query($query_fila4, $conexxion) or die(mysql_error());
		$row_fila4 = mysql_fetch_assoc($fila4);
		$totalRows_fila4 = mysql_num_rows($fila4);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila5 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 10,5";
		$fila5 = mysql_query($query_fila5, $conexxion) or die(mysql_error());
		$row_fila5 = mysql_fetch_assoc($fila5);
		$totalRows_fila5 = mysql_num_rows($fila5);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila6 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 15,6";
		$fila6 = mysql_query($query_fila6, $conexxion) or die(mysql_error());
		$row_fila6 = mysql_fetch_assoc($fila6);
		$totalRows_fila6 = mysql_num_rows($fila6);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila7 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 21,7";
		$fila7 = mysql_query($query_fila7, $conexxion) or die(mysql_error());
		$row_fila7 = mysql_fetch_assoc($fila7);
		$totalRows_fila7 = mysql_num_rows($fila7);
		
		mysql_select_db($database_conexxion, $conexxion);
		$query_fila8 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 28,8";
		$fila8 = mysql_query($query_fila8, $conexxion) or die(mysql_error());
		$row_fila8 = mysql_fetch_assoc($fila8);
		$totalRows_fila8 = mysql_num_rows($fila8);
		
		mysql_select_db($database_conexxion, $conexxion);
	$query_fila9 = "SELECT venta,compra,taller,regula_mas,regula_menos,movimiento.id_item,sum(compra+regula_mas-venta-taller-regula_menos)  as stock, cilindro, esfera FROM movimiento  join item on item.id_item=movimiento.id_item join rx on rx.id_rx=item.id_rx join cilindro  on cilindro.id_cil=rx.id_cil  join esfera  on esfera.id_esf=rx.id_esf GROUP BY movimiento.id_item ORDER BY esfera desc, cilindro desc limit 36,25";
	$fila9 = mysql_query($query_fila9, $conexxion) or die(mysql_error());
	$row_fila9 = mysql_fetch_assoc($fila9);
	$totalRows_fila9 = mysql_num_rows($fila9);
		
		?>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>test</title>
	</head>
	
	<body>
	<table align=center>
	<tr>
	  <td width="40px;" align="center"> </td>
		<td width="40px;" align="center">0.00</td>
		<td width="40px;" align="center">-0.25</td>
		<td width="40px;" align="center">-0.50</td>
		<td width="40px;" align="center">-0.75</td>
		<td width="40px;" align="center">-1.00</td>
		<td width="40px;" align="center">-1.25</td>
		<td width="40px;" align="center">-1.50</td>
		<td width="40px;" align="center">-1.75</td>
		<td width="40px;" align="center">-2.00</td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_Recordset1['esfera']; ?></td>
	  <td align="center"><?php echo $row_Recordset1['stock']; ?></td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila2['esfera']; ?></td>
	  <?php do { ?><td align="center">
		  <?php echo $row_fila2['stock']; ?>
		  </td><?php } while ($row_fila2 = mysql_fetch_assoc($fila2)); ?>
		<td align="center" bgcolor="#0000FF"> </td>
		
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila3['esfera']; ?></td>
	  <?php do { ?><td align="center">
		  <?php echo $row_fila3['stock']; ?>
		  </td><?php } while ($row_fila3 = mysql_fetch_assoc($fila3)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila4['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila4['stock']; ?></td>
		<?php } while ($row_fila4 = mysql_fetch_assoc($fila4)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila5['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila5['stock']; ?></td>
		<?php } while ($row_fila5 = mysql_fetch_assoc($fila5)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila6['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila6['stock']; ?></td>
		<?php } while ($row_fila6 = mysql_fetch_assoc($fila6)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila7['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila7['stock']; ?></td>
		<?php } while ($row_fila7 = mysql_fetch_assoc($fila7)); ?>
	  <td align="center" bgcolor="#0000FF"> </td>
	  <td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<tr>
	  <td align="center"><?php echo $row_fila8['esfera']; ?></td>
	  <?php do { ?>
		<td align="center"><?php echo $row_fila8['stock']; ?></td>
		<?php } while ($row_fila8 = mysql_fetch_assoc($fila8)); ?>
	<td align="center" bgcolor="#0000FF"> </td>
	</tr>
	<?php do { ?>
	  <tr>
		<td align="center"><?php echo $row_fila9['esfera']; ?></td>
		<td align="center"><?php echo $row_fila9['stock']; ?></td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
		<td align="center"> </td>
	  </tr>
	  <?php } while ($row_fila9 = mysql_fetch_assoc($fila9)); ?>
	<tr>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	  <td align="center"> </td>
	</tr>
	</table>
	   <?php
	
	
	$columnas = 9; # Número de columnas (variable)
	
	
	for ($i=1; $row = mysql_fetch_row ($Recordset1); $i++) {
	$resto = ($i % $columnas); # Número de celda del <tr> en que nos encontramos
	if ($resto == 1) {echo "<tr>";} # Si es la primera celda, abrimos <tr>
		echo "<td width=\"40px;\" align=\"center\">$row[1]</td>"; 
	if ($resto == 0) {echo "</tr>";} # Si es la última celda, cerramos </tr>
	}
	if ($resto <> 0) { # Si el resultado no es múltiple de $columnes acabamos de rellenar los huecos
	$ajust = $columnas - $resto; # Número de huecos necesarios
	for ($j = 0; $j < $ajust; $j++) {echo "<td> </td>";}
	echo "</tr>"; # Cerramos la última línea </tr>
	}
	mysql_close($connexion);
	echo "</table>";
	?> 
	  
	
	
	</body>
	</html>
	<?php
	mysql_free_result($fila1);
	
	mysql_free_result($Recordset1);
	
	mysql_free_result($fila2);
	
	mysql_free_result($fila3);
	
	mysql_free_result($fila4);
	
	mysql_free_result($fila5);
	
	mysql_free_result($fila6);
	
	mysql_free_result($fila7);
	
	mysql_free_result($fila8);
	
	mysql_free_result($fila9);
	
	mysql_free_result($consulta);
	?>

with this result

 

 

 

 

trabla.png

now I have 25 rows in the table that can be filled up to 9 results, you can see that there are 9 results for 2.00, 9 for 1.75 and so on but I am unable to achive this.

 

I can see that creating a new query for every row is a solution, but 25 + 9 more querys.... maybe too many queries?

Edited by gralfitox
Link to comment
Share on other sites

try something like this with a single query

<?php
//
// GET CILINDRO VALUES
//
$sql = "SELECT cilindro FROM cilindro
        ORDER BY cilindro";
$cilindro = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_row()) {
    $cilindro[] = $row[0];
}
$thead = "<tr><th></th><th>" . join('</th><th>', $cilindro) . "</th></tr>\n";
$initial = array_fill_keys($cilindro, ''); // empty initial array for each row

//
// GET STOCK VALUES
//                
$sql = "SELECT 
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera 
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item 
  JOIN rx ON rx.id_rx=item.id_rx 
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf 
GROUP BY movimiento.id_item 
ORDER BY esfera DESC";

$tdata = '';
$currEsfera = '';
$esfdata = $initial;
$res = $mysqli->query($sql);
while (list($stock,$cil, $esf) = $res->fetch_row()) {
    if ($esf != $currEsfera) {  // change of esfera?
        if ($currEsfera) {  // don't want initial balank values
            $tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
        }
        $currEsfera = $esf;
        $esfdata = $initial;
    }
    $esfdata[$cil] = $stock;
}
// output row for final esfera value
$tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
?>
<table border='1'>
<?php echo $thead, $tdata?>
</table>
Link to comment
Share on other sites

Correction to above, main query should be GROUP BY esfera, cilindro

SELECT
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item
  JOIN rx ON rx.id_rx=item.id_rx
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf
GROUP BY esfera, cilindro
ORDER BY esfera DESC
Link to comment
Share on other sites

hi again, so with this code

<?php require_once('Connections/conexxion.php'); 

mysql_select_db($database_conexxion, $conexxion);

?>
<html>
<body>
<?php
//
// GET CILINDRO VALUES
//
$sql = "SELECT cilindro FROM cilindro
        ORDER BY cilindro";
$cilindro = array();
$res = $mysqli->query($sql);
while ($row = $res->fetch_row()) {
    $cilindro[] = $row[0];
}
$thead = "<tr><th></th><th>" . join('</th><th>', $cilindro) . "</th></tr>\n";
$initial = array_fill_keys($cilindro, ''); // empty initial array for each row

//
// GET STOCK VALUES
//                
$sql = "SELECT
   sum(compra+regula_mas-venta-taller-regula_menos)  as stock
 , cilindro
 , esfera
FROM movimiento  
  JOIN item ON item.id_item=movimiento.id_item
  JOIN rx ON rx.id_rx=item.id_rx
  JOIN cilindro ON cilindro.id_cil=rx.id_cil  
  JOIN esfera ON esfera.id_esf=rx.id_esf
GROUP BY esfera, cilindro
ORDER BY esfera DESC";

$tdata = '';
$currEsfera = '';
$esfdata = $initial;
$res = $mysqli->query($sql);
while (list($stock,$cil, $esf) = $res->fetch_row()) {
    if ($esf != $currEsfera) {  // change of esfera?
        if ($currEsfera) {  // don't want initial balank values
            $tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
        }
        $currEsfera = $esf;
        $esfdata = $initial;
    }
    $esfdata[$cil] = $stock;
}
// output row for final esfera value
$tdata .= "<tr><td>$currEsfera</td><td>".join('</td><td>', $esfdata)."</td></tr>\n";
?>
<table border='1'>
<?php echo $thead, $tdata?>
</table>

    
</body>
</html>

I got this

 

asdasdad.png

 

:

Link to comment
Share on other sites

  • Solution

You should notice that my code uses mysqli (as you should, or PDO)

 

Instead of :

mysql_select_db($database_conexxion, $conexxion);

 

use this to create a mysqli connection:

$mysqli = new mysqli (host, username, password, databasename);    

 

using your credentials.

Edited by Barand
  • Like 1
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.