While loop not working properly

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

$testtot1 = array_sum($testarray1);

Is this the problem? That creates a 'running total' of your $testarray1 on each iteration.

$testarray1[] = ($zendinghead1['Aantal_Manden']*$zendinghead1['Aantal_Duiven']);

This is (I assume) the line that gives the number of birds for a single shipment?

So $zendinghead1['Aantal_Manden']*$zendinghead1['Aantal_Duiven'] is what you need to echo?

//EDIT

Strike that, I just noticed the loop within a loop, so my first comments are incorrect. Try indenting nested loops and conditionals.

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

Okay thanks, will research those a bit :) keeping you posted!

EDIT:

I don't get it. All it needs to do is make a difference between each shipment ID. So if shipment 1 has 50 birds it shouldn't count those for shipment 2. I have looked into the PHP manual, article on while loops but I wouldn't know what I'm doing wrong.
I have tried shifting some lines in or out the loop but it doesn't change a bit.

Does someone have an idea how to solve a situation like this? maybe even another similar problem so I can look into the code?

I'm lost!

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

You've got some strange stuff going on with your loops. But as most of the fields are in Dutch(?), I'm lost too.

I did this:

<?php include('variables/variables.php'); ?>
 
<div id="header">
 
<?php
 
include ('includes/db-zelf.php');
 
// define your database connection credentials
mysql_connect( $db_host, $db_user, $db_password ) or die(mysql_error());
mysql_select_db( $db_database ) or die(mysql_error());
 
/* KUBAGES */
 
$Luchtvr = mysql_query("SELECT * FROM Luchtvrachtpalletten WHERE ID='1'") or die(mysql_error());
 
while($Luchtvra = mysql_fetch_array( $Luchtvr )){
	$kuubid = $Luchtvra['ID'];
	$kuubvol = $Luchtvra['Volume'];
	$kuub = round(($kuubvol / 167)*2,2);
/* EINDE KUBAGES */
}
 
/* SELECT ONLY THE ACTIVE SHIPMENTS */
$zending = mysql_query("SELECT Bestemming, Datum FROM Zendingen WHERE Actief = 1 ORDER BY Datum ASC LIMIT 0,30") or die(mysql_error());
if(mysql_num_rows($zending)){
	while($lol = mysql_fetch_array( $zending )){
		$testz = $lol['ID']; //this is shipment id
		$probeerselbe = $lol['Bestemming'];
		$probeerselda = $lol['Datum'];
		$zending_da = date("d-m-Y", strtotime($probeerselda));
		echo "";
		echo "Zending ";
		echo $probeerselbe;
		echo " voor datum ";
		echo $zending_da;
		echo " heeft nog ";
		echo $kuub;
		echo " kuub over en bevat ";
		/* END DATAGET*/
		/* BEGIN BIRD COUNT */
		$zendingheader1 = mysql_query("SELECT s.Aantal_Manden, m.Aantal_Duiven FROM Shipments AS s JOIN Zendingen AS z ON s.Zending_ID = z.ID JOIN Manden AS m ON s.Mand_ID = m.ID WHERE z.ID = $testz order by s.Aantal_Manden, m.Aantal_Duiven ASC LIMIT 0,30") or die(mysql_error());
		if(mysql_num_rows($zendingheader1)){
			while($zendinghead1 = mysql_fetch_array( $zendingheader1 )){
				/*TOTAL AMOUNT OF BIRDS IN SHIPMENT*/
				$testarray1[] = ($zendinghead1['Aantal_Manden']*$zendinghead1['Aantal_Duiven']); 
			}
		}
		$testtot1 = array_sum($testarray1);
		/* END BIRD COUNT */
	 
	   echo $testtot1;
	   echo " duiven in totaal.";
	}
}
?>
 
</div> <!-- end #header -->

You seemed to be retrieving 'zendingen' then retrieivng zendingen again. I din't see much point in that so I made just one query out of it.

$zending = mysql_query("SELECT Bestemming, Datum FROM Zendingen WHERE Actief = 1 ORDER BY Datum ASC LIMIT 0,30") or die(mysql_error());

As for the rest of it, I'm a little confused. You seem to have 3 tables:
zendingen
shipments
manden

Although I think you called zendingen 'shipments' in your php comments

Could you explain what are the fields in each table and how are they related. I'm sure there's an easy way to do this, maybe with an aggregated SQL.

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

You're absolutely right.
I did the coding seperately. So I first coded the part where it searches the ID and then the others. I now added this together.
Explanation underneath the coding.

<?php include('variables/variables.php'); ?>

<div id="header">

<?php

include ('includes/db-zelf.php');

// define your database connection credentials
mysql_connect( $db_host, $db_user, $db_password ) or die(mysql_error());
mysql_select_db( $db_database ) or die(mysql_error());

/* KUBAGES */

$Luchtvr = mysql_query("SELECT * FROM Luchtvrachtpalletten WHERE ID='1'") or die(mysql_error());

while($Luchtvra = mysql_fetch_array( $Luchtvr ))
{

$kuubid = $Luchtvra['ID'];
$kuubvol = $Luchtvra['Volume'];
$kuub = round(($kuubvol / 167)*2,2);
} /* EINDE KUBAGES */

/* BEGIN ZENDINGEN */

$zendingheader = mysql_query("SELECT Bestemming, Datum,ID FROM Zendingen WHERE Actief = 1 order by Datum ASC LIMIT 0,30") or die(mysql_error());

$ros = mysql_num_rows($zendingheader);
$i=0;
while($i<$ros)
{

$zendinghead = mysql_fetch_array( $zendingheader );

$testz = $zendinghead['ID'];
$probeerselbe = $zendinghead['Bestemming'];
$probeerselda = $zendinghead['Datum'];
$zending_da = date("d-m-Y", strtotime($probeerselda));

   echo "";
   echo "Zending ";
   echo $probeerselbe;
   echo " voor datum ";
   echo $zending_da;
   echo " heeft nog ";
   echo $kuub;
   echo " kuub over en bevat ";

/* EINDE ZENDINGEN*/

/* BEGIN DUIVEN OPTELLEN */

$zendingheader1 = mysql_query("SELECT s.Aantal_Manden, m.Aantal_Duiven FROM Shipments AS s JOIN Zendingen AS z ON s.Zending_ID = z.ID JOIN Manden AS m ON s.Mand_ID = m.ID WHERE z.ID = $testz order by s.Aantal_Manden, m.Aantal_Duiven ASC LIMIT 0,30") or die(mysql_error());

$ros2 = mysql_num_rows($zendingheader1);

$y=0;
while($y<$ros2)
{

$zendinghead1 = mysql_fetch_array( $zendingheader1 );

/*TOTAAL AANTAL DUIVEN IN ZENDING*/
$testarray1[] = ($zendinghead1['Aantal_Manden']*$zendinghead1['Aantal_Duiven']);
   $y++;

$steg = array_sum($testarray1);

}

/* EINDE DUIVEN OPTELLEN */
   echo $steg;
   echo " duiven in totaal.";   
   $i++;
}
?>

</div> <!-- end #header -->

Explanation:

1- Find the destination, ID and date from the table Zendingen (Shipments in Dutch) where actief (Active) is 1.
So if the shipment is active (1) it should get it's ID, destination and date.

2- Count the rows (1) returns and loop.

3- Echo the Destination and date.

4- Get the amount of birds in a shipment ( baskets * birds )

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

So the problem is solved? ;)

minitauros

Junior Poster

109 posts since Apr 2011

Reputation Points: 36

Solved Threads: 17

As I asked previously, could you please provide the database table structure, explaining the fieldnames and any relationships

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

I am trying, but some stupid rule forbids me to edit the message within 30 minutes so I repost.

You're absolutely right.
I did the coding seperately. So I first coded the part where it searches the ID and then the others. I now added this together.
Explanation underneath the coding.

<?php include('variables/variables.php'); ?>

<div id="header">

<?php

include ('includes/db-zelf.php');

// define your database connection credentials
mysql_connect( $db_host, $db_user, $db_password ) or die(mysql_error());
mysql_select_db( $db_database ) or die(mysql_error());

/* KUBAGES */

$Luchtvr = mysql_query("SELECT * FROM Luchtvrachtpalletten WHERE ID='1'") or die(mysql_error());

while($Luchtvra = mysql_fetch_array( $Luchtvr ))
{

$kuubid = $Luchtvra['ID'];
$kuubvol = $Luchtvra['Volume'];
$kuub = round(($kuubvol / 167)*2,2);
} /* EINDE KUBAGES */

/* BEGIN ZENDINGEN */

$zendingheader = mysql_query("SELECT Bestemming, Datum,ID FROM Zendingen WHERE Actief = 1 order by Datum ASC LIMIT 0,30") or die(mysql_error());

$ros = mysql_num_rows($zendingheader);
$i=0;
while($i<$ros)
{

$zendinghead = mysql_fetch_array( $zendingheader );

$testz = $zendinghead['ID'];
$probeerselbe = $zendinghead['Bestemming'];
$probeerselda = $zendinghead['Datum'];
$zending_da = date("d-m-Y", strtotime($probeerselda));

   echo "";
   echo "Zending ";
   echo $probeerselbe;
   echo " voor datum ";
   echo $zending_da;
   echo " heeft nog ";
   echo $kuub;
   echo " kuub over en bevat ";

/* EINDE ZENDINGEN*/

/* BEGIN DUIVEN OPTELLEN */

$zendingheader1 = mysql_query("SELECT s.Aantal_Manden, m.Aantal_Duiven FROM Shipments AS s JOIN Zendingen AS z ON s.Zending_ID = z.ID JOIN Manden AS m ON s.Mand_ID = m.ID WHERE z.ID = $testz order by s.Aantal_Manden, m.Aantal_Duiven ASC LIMIT 0,30") or die(mysql_error());

$ros2 = mysql_num_rows($zendingheader1);

$y=0;
while($y<$ros2)
{

$zendinghead1 = mysql_fetch_array( $zendingheader1 );

/*TOTAAL AANTAL DUIVEN IN ZENDING*/
$testarray1[] = ($zendinghead1['Aantal_Manden']*$zendinghead1['Aantal_Duiven']);
   $y++;

$steg = array_sum($testarray1);

}

/* EINDE DUIVEN OPTELLEN */
   echo $steg;
   echo " duiven in totaal.";   
   $i++;
}
?>

</div> <!-- end #header -->

Explanation:

1- Find the destination, ID and date from the table Zendingen (Shipments in Dutch) where actief (Active) is 1.
So if the shipment is active (1) it should get it's ID, destination and date.

2- Count the rows (1) returns and loop.

3- Echo the Destination and date.

4- Get the amount of birds in a shipment ( baskets * birds ).

5- Echo this amount.

There are 3 tables.

Zendingen--|-Manden--------|-Shipments
--------------------------------------------------
Datum-------|-Aantal_Duiven|-Aantal_Manden
ID-------------|-ID----------------|-Mand_ID
Bestemming|--------------------|-Zending_ID

So the "Zendingen" table contains the "date", "ID" and "destination" of the shipment
The "Manden" table contains the "basket_capacity" and "ID".
The "Shipments" table is the one holding the ID's of both "Zendingen" and "Manden" and the amount of birds in the basket.
So it actually holds the ID of each client and basket and then the amount of birds.
I need the query to return for each shipment (Zending_ID) that is active (Active=1) the total amount of birds.
Hope you get it now.

Any more info needed, just ask. Thanks for looking into this btw.

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

No I understand, it's just I can't help but feel that there's a really easy way to do what you need with just the one SQL statement, using aggregeated functions and calculated fields. It seems to be that simple, but I'm still getting trouble with your table structure (my fault - I'm not that quick!). I'll get back to you.

BTW - 30 minute timeout on edit? It's a bitch ain't it? :)

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

No I understand, it's just I can't help but feel that there's a really easy way to do what you need with just the one SQL statement, using aggregeated functions and calculated fields. It seems to be that simple, but I'm still getting trouble with your table structure (my fault - I'm not that quick!). I'll get back to you.

BTW - 30 minute timeout on edit? It's a bitch ain't it? :)

Yup, pissed me off, had typed this whole text and then it tells me it's not possible to edit...

Anyway. you want me to print out an information scheme, mark the data needed and scan it so i can post a picture here?

I too have the feeling this can be much easier, but the lack of knowledge is bashing my skull.

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

SELECT z.ID, z.Bestemming, z.Datum, SUM(s.Aantal_Manden * m.Aantal_Duiven) AS total FROM Zendingen AS z INNER JOIN Shipments AS s ON z.ID = s.Zending_ID INNER JOIN Manden AS m ON m.ID = s.Mand_ID GROUP BY z.ID

That works for me giving the follwoing:

You'll notice that the fieldnames are different to yours, but that was just for my benefit.

Attachments

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

http://pdfcast.org/pdf/database-2

Show Comments

This is an overview of the tables, might help you a lot.

I will try your method in the meantime.

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

Yep, seems consistent with what I thought. Nice site (pdfcast) - haven't seen that one before (+1 to you).

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

lol thanks.
So i've been trying out your code:

$ardav = mysql_query("SELECT z.ID, z.Bestemming, z.Datum, SUM(s.Aantal_Manden * m.Aantal_Duiven) AS total FROM Zendingen AS z INNER JOIN Shipments AS s ON z.ID = s.Zending_ID INNER JOIN Manden AS m ON m.ID = s.Mand_ID WHERE z.Actief = 1 GROUP BY z.ID") or die (mysql_error());
echo $ardav['total'];
echo "works";

It only echo's the work. or do I also need to loop this one then? As you can see I've added the "Where Active=1" to the query, for this is really required, old shipments do not need to pop up.

EDIT:

Thank you for your help. You are today's hero.

Working code:

$ardav = mysql_query("SELECT z.ID, z.Bestemming, z.Datum, SUM(s.Aantal_Manden * m.Aantal_Duiven) AS total FROM Zendingen AS z INNER JOIN Shipments AS s ON z.ID = s.Zending_ID INNER JOIN Manden AS m ON m.ID = s.Mand_ID WHERE z.Actief = 1 GROUP BY z.ID") or die (mysql_error());

while($arda = mysql_fetch_array( $ardav ))
{
echo $arda['total'];
echo "works";
}

Thanks man. I'm going to try some tests before i put this one solved, but I'm sure you did the trick!
You've learned me that it's possible to group per attribute, never heard of it before.

EDIT 2:

I now noticed that the inner join actually does the work of "WHERE z.Actief = 1" so I removed it now.
What it doesn't do is give me the destination and date. trying to figure that out...

$ardav = mysql_query("SELECT z.ID, z.Bestemming, z.Datum, SUM(s.Aantal_Manden * m.Aantal_Duiven) AS total FROM Zendingen AS z INNER JOIN Shipments AS s ON z.ID = s.Zending_ID INNER JOIN Manden AS m ON m.ID = s.Mand_ID GROUP BY z.ID") or die (mysql_error());

while($arda = mysql_fetch_array( $ardav ))
{
   $ardabe = $arda['z.Bestemming'];
   $ardada = $arda['z.Datum'];
   echo "";
   echo "Zending ";
   echo $ardabe;
   echo " voor datum ";
   echo $ardada;
   echo " heeft nog ";
   echo $kuub;
   echo " kuub over en bevat ";
   echo $arda['total'];
   echo " duiven.";
}

Gives me:

Zending voor datum heeft nog 36.26 kuub over en bevat 71 duiven.
Zending voor datum heeft nog 36.26 kuub over en bevat 18 duiven.

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

Post edited*

Light Poster

28 posts since Dec 2011

Reputation Points: 10

Solved Threads: 0

Show Comments

Try this:

$ardabe = $arda['Bestemming'];
   $ardada = $arda['Datum'];

You don't need the table qualifiers in the output array.

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

some thing like this maybe

select zen.ID, Bestemming, Datum, sum(Aantal_Duiven*Aantal_Manden) as total_birds
  from Zendigen zen
  inner join Shipments ship on ship.Zending_ID = zen.ID
  inner join Manden man on ship.Mand_ID = man.ID
  where Actief = 1 group by(zen.ID) order by Datum

jstfsklh211

Light Poster

48 posts since Apr 2011

Reputation Points: 10

Solved Threads: 10

Solved Thanks to Ardav, some fine coding man! Works like a charm :)