Resultaten weergeven in kruistabel

Status
Niet open voor verdere reacties.

Ruultje

Gebruiker
Lid geworden
16 okt 2007
Berichten
39
Ik heb de vraag al eens gesteld van de week, maar dat was alleen met een query. Ik denk dat daar weinig mee valt te doen. Het gaat hier om een voorraad database. Ik zet dit nu allemaal in een Excel file. Daar wordt ook gewerkt met een kruistabel. Maar ik wil dit dus binnen MySQL en PHP gaan doen. Daar dan enkele andere functies aan vast bouwen. In dat laatste zal ik geen problemen tegen gaan komen. Maar ik kom nu totaal niet verder. En dat begint mij nu een beetje te irriteren.

In ieder geval: ik ben nu al anderhalve week! bezig met een kruistabel binnen PHP. Hij doet in principe wel wat hij moet doen (hij zet de cijfers onder de juiste week en bij het juiste product) maar bij de indeling gaat het mis.

Als bij een product 4x (gaat per week) voorraad is ingegeven dan komt het product ook 4 x te voorschijn. Deze staan dan onder elkaar. Dat geldt dan ook voor de cijfers. Die gaan wel bij de juiste week maar die gaan ook naar 1 van die 4 producten. Ik heb dan in principe 4 regels per product. Terwijl dit allemaal in 1 regel moet staan. En de cijfers uiteraard ook (bij het juiste product).

Ik heb al geprobeerd te werken met een array maar daar kwam ik ook niet uit. Ik denk dat ik het moet gaan invullen via een loop, maar ook daar kom ik vast mee te zitten.

De code:
PHP:
<?php
// Datum herkennen
$datum =time () ;
$dag = date('d', $datum) ;
$week= date('w', $datum) ;

	echo "<table>";
	echo "<tr>";
	echo "<td>";
	echo "<b>Product</b>";
	echo "</td>";
// Weken gaan weergeven
    for ($week = 1; $week<=53; $week++) {
	echo "<td>";
	echo "<b>". $week ."</b>";
	echo "</td>";
	}
	echo "</tr>";

// Query
$query = mysql_query("
SELECT *, COUNT(productid) AS aantal, EXTRACT(week FROM stock_datum) AS week,
EXTRACT(year FROM stock_datum) AS year 
FROM voorraad
INNER JOIN voorraad_product ON voorraad_product.stockid = voorraad.stock_id 
WHERE groepid = '3'
GROUP BY week, year, productid
ORDER BY productid
	  ");
	  
	  while($result = mysql_fetch_array($query))
	  {
	  
	  $aantal = $result['aantal'];
	  $product = $result['productid'];
	  $weeks = $result['week'];
		
           
	echo "<tr>";
// Product ophalen		
	echo "<td>";
	echo "". $result['productid'] ."";
	echo "</td>";
// Aantal weken creëren 	
    for ($week = 1; $week<=53; $week++) {
// Kijken of er resultaat is in die week. Is dit niet dan cijfer 0 weergeven
	if ($week != $weeks) {
	echo "<td>";
	echo "0";
	echo "</td>";
	}
	else
	{
	echo "<td>";
	echo "". $result['stock_amount'] ."";
	echo "</td>";
	}
	}
    echo "</tr>";
	}
	echo "</table>";
?>

De Database tabellen (misschien gaat het hier mis):
HTML:
Producten:
`productcode` varchar(250) NOT NULL,
`omschrijving` varchar(350) NOT NULL,
PRIMARY KEY (`productcode`)

Voorraad
  `stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `groepid` int(11) NOT NULL,
  `stock_amount` int(11) NOT NULL,
  `stock_datum` datetime NOT NULL,
  PRIMARY KEY (`stock_id`)

Voorraad Product
`productcode` varchar(200) NOT NULL,
`stockid` int(11) NOT NULL

Het zou dus zo moeten uitzien:

Product/Week 1 - 2 - 3
Product 1 11 - 22 - 31
Product 2 8 - 11 - 121


Weet iemand wat ik nog anders moet doen? Ik kom er maar niet op. Heb op verschillende trefwoorden gezocht (Matrix, kruistabel, crosstable, y-as, x-as en nog enkele) maar kan totaal niks vinden. Begin er nu aardig moedeloos van te worden. Wanneer mij iemand een stap in de goede richting kan geven dan ben ik deze persoon zeer dankbaar.
 
Ik denk dat je om te beginnen een tabel teveel hebt: de koppeltabel kun je achterwege laten als je in de voorraad tabel een extra kolom maakt voor productcode (waarmee je dus direct kunt linken aan je "producten" tabel). Verder vergeet je het belangrijkste: namelijk het optellen van de stock_amount[sql]sum(stock_amount) as hoeveelheid[/sql]Tot slot staat er in je php code een verwijzing naar "productid" terwijl de primary key in je tabellen overzicht "productcode" heet.

Een eerste stap zou zijn om het data model kloppend te maken.
 
Klopt. Ik had eerst productid ipv productcode. Had deze nog niet aangepast.

Datamodel heb ik inmiddels aangepast. Datamodel dacht ik toch al aan veranderingen. In eerste instantie had ik ook maar één tabel met de productcode daarbij. Maar dacht dat het misschien zou helpen wanneer ik deze zou loskoppelen. Maar in mijn opzet stonden ze dus wel in een tabel. Ik heb het nu dus weer hersteld.

Ook de SUM heb ik toegevoegd.

In ieder geval al bedankt. Maar ik blijf nog die twee rijen of meer houden wanneer er product vaker dan 1x is ingevoerd.

Zie de bijlage.
 

Bijlagen

  • voorbeeld.jpg
    voorbeeld.jpg
    13,5 KB · Weergaven: 78
Even bumpen. Mijn excuses daarvoor. Nadat ik ging zoeken op Cross Table (dus los van elkaar) kwam ik terecht bij Pivot Table. Ik wist niet wat dit was dus heb voorbeeld tabellen bekeken. Toen bleek dat dit kruistabellen waren. Ik ben daardoor verder gaan zoeken op Pivot Table. Met enig resultaat. Maar goed nu werkt het niet efficiënt. Nu kan ik alles los gaan aanmaken. Dat lijkt mij ook weer niet de bedoeling.

In ieder geval is dit de code (ik ben weer veel te lang wakker maar wil dit hoe dan ook werkende krijgen, want na anderhalve week begint het aardig te irriteren). Aangezien ik ook hieraan verder wil gaan bouwen (invoer etc. berekeningen e.d.). Dat gaat allemaal geen probleem opleveren. Maar het blijft allemaal liggen. Dus dan maar ietsjes korter slapen ;)

Ik heb nu in ieder geval dit. Maar zoals gezegd is het niet efficiënt. Verder ken ik ook abs niet echt. Wist niet eens dat dit ook bij MySQL gebruikt werd. Ik zal dus nog veel aan kennis missen.

PHP:
SELECT stock_amount, productcode ,EXTRACT(week FROM stock_datum) AS week,
SUM(stock_amount) AS hoeveelheid, COUNT(productcode) AS aantal,
SUM(stock_amount*(1-abs(sign(WEEK(stock_datum)-11)))) AS week11,
SUM(stock_amount*(1-abs(sign(WEEK(stock_datum)-12)))) AS week12
FROM stock GROUP BY productcode
ORDER BY productcode

Maar goed nu moet ik iedere week los aanmaken. En bovendien ook overal het volgende zetten voor elke week apart:

PHP:
echo "<td>";
	echo "". $result['week11'] ."";
	echo "</td>";
	echo "<td>";
	echo "". $result['week12'] ."";
	echo "</td>";

Ik hoop dat iemand een betere oplossing heeft. Ik blijf zelf ook verder zoeken uiteraard.
 
Voor elke week aparte data verzamelen zou ik niet doen, dan krijg je een enorme query voor een heel jaar. Ik zou het bij "group by productcode, week" houden.

Verder is het erg moeilijk om dit soort problemen op te lossen wanneer de data structuur niet duidelijk is. Kun je een phpMyAdmin SQL Dump plaatsen (structuur+voorbeeld data) zodat ik lokaal queries/code kan testen?
 
Voor elke week aparte data verzamelen zou ik niet doen, dan krijg je een enorme query voor een heel jaar. Ik zou het bij "group by productcode, week" houden.

Verder is het erg moeilijk om dit soort problemen op te lossen wanneer de data structuur niet duidelijk is. Kun je een phpMyAdmin SQL Dump plaatsen (structuur+voorbeeld data) zodat ik lokaal queries/code kan testen?

Dat klopt. Ook omdat de tabellen zelf allemaal apart moeten worden aangemaakt in de php code.


Dit is in ieder geval de SQL (deze is wel een test)

PHP:
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Machine: localhost
-- Genereertijd: 26 Mar 2010 om 12:47
-- Serverversie: 5.1.36
-- PHP-Versie: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `voorraaddb`
--

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `gebruikers`
--

CREATE TABLE IF NOT EXISTS `gebruikers` (
  `gebruikers_id` int(11) NOT NULL AUTO_INCREMENT,
  `naam` varchar(200) NOT NULL,
  `gebruikersnaam` varchar(200) NOT NULL,
  `wachtwoord` varchar(200) NOT NULL,
  `rang` int(11) NOT NULL,
  `actief` datetime NOT NULL,
  PRIMARY KEY (`gebruikers_id`),
  UNIQUE KEY `gebruikersnaam` (`gebruikersnaam`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Gegevens worden uitgevoerd voor tabel `gebruikers`
--

INSERT INTO `gebruikers` (`gebruikers_id`, `naam`, `gebruikersnaam`, `wachtwoord`, `rang`, `actief`) VALUES
(1, 'Annemarie', 'anne', '7288edd0fc3ffcbe93a0cf06e3568e28521687bc', 2, '2010-03-26 02:50:24');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `groepen`
--

CREATE TABLE IF NOT EXISTS `groepen` (
  `groep_id` int(11) NOT NULL AUTO_INCREMENT,
  `groepnaam` varchar(200) NOT NULL,
  PRIMARY KEY (`groep_id`),
  UNIQUE KEY `sub` (`groepnaam`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Gegevens worden uitgevoerd voor tabel `groepen`
--

INSERT INTO `groepen` (`groep_id`, `groepnaam`) VALUES
(1, 'Groep 1'),
(3, 'Groep 2');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `producten`
--

CREATE TABLE IF NOT EXISTS `producten` (
  `productcode` varchar(250) NOT NULL,
  `omschrijving` varchar(350) NOT NULL,
  PRIMARY KEY (`productcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Gegevens worden uitgevoerd voor tabel `producten`
--

INSERT INTO `producten` (`productcode`, `omschrijving`) VALUES
('Test1001', 'Product 1'),
('Test1002', 'Product 2');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `sales_id` int(11) NOT NULL AUTO_INCREMENT,
  `groepid` int(11) NOT NULL,
  `productcode` varchar(250) NOT NULL,
  `sales_amount` int(11) NOT NULL,
  `sales_datum` datetime NOT NULL,
  PRIMARY KEY (`sales_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Gegevens worden uitgevoerd voor tabel `sales`
--


-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `stock`
--

CREATE TABLE IF NOT EXISTS `stock` (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `groepid` int(11) NOT NULL,
  `productcode` varchar(300) NOT NULL,
  `stock_amount` int(11) NOT NULL,
  `stock_datum` datetime NOT NULL,
  PRIMARY KEY (`stock_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Gegevens worden uitgevoerd voor tabel `stock`
--

INSERT INTO `stock` (`stock_id`, `groepid`, `productcode`, `stock_amount`, `stock_datum`) VALUES
(1, 3, 'Test1001', 20, '2010-03-25 00:00:00'),
(2, 3, 'Test1001', 5, '2010-03-18 00:00:00'),
(3, 3, 'Test1002', 34, '2010-03-18 00:00:00');

Gebruikergegevens (wachtwoord e.d.) worden aangepast. Maar deze is nu vooral om te testen.
 
Dat maakt het uitproberen van code ineens een stuk makkelijker :). Ik denk dat de makkelijkste optie is om de data tijdelijk in een array onder te brengen (zodat je de data per product bij elkaar kunt houden) en vervolgens voor elk product te kijken of er voor die week een waarde bestaat:
PHP:
<?php
	mysql_connect("localhost", "root", "");
	mysql_select_db("test");
	
	$sql = "SELECT productcode ,EXTRACT(week FROM stock_datum) AS week, SUM(stock_amount) AS hoeveelheid, COUNT(productcode) AS aantal FROM stock GROUP BY productcode, week ORDER BY productcode";
	$result = mysql_query($sql);
	$gegevens = array();
	while ($row = mysql_fetch_assoc($result)) {
		$gegevens[$row["productcode"]][$row["week"]] = array("hoeveelheid"=>$row["hoeveelheid"],"aantal"=>$row["aantal"]);
	}
	echo "<table border='1' cellspacing='0' cellpadding='4'><tr><td>Week:</td>";
	for ($i=1;$i<=52;$i++) {
		echo "<th>".$i."</th>";
	}
	echo "</tr>\n";
	foreach($gegevens as $productcode => $data) {
		echo "<tr><td>".$productcode."</td>";
		for ($i=1;$i<=52;$i++) {
			if (array_key_exists($i,$data)) {
				echo "<td>".$data[$i]["hoeveelheid"]."</td>";
			} else {
				echo "<td>&nbsp;</td>";
			}
		}
		echo "</tr>\n";
	}
	echo "</table>";
?>
Alle inline html maakt de code er niet mooier op, maar ik denk dat je zo wel het resultaat krijgt waar je naar op zoek bent.
 
Dit is het helemaal. Ik moet mij dan toch nog eens gaan verdiepen in arrays wat mij was het eerder niet gelukt. Bleef alles bij hetzelfde zoals het was.

In ieder geval ontzettend bedankt. Met die pivot zou het inderdaad heel erg weinig opschieten. Code van 1000 regels volgens mij.

Maar nogmaals bedankt. Nu kan ik het tenminste verder uitbouwen en daarmee zal ik wel verder gaan komen ;-).
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan