WHILE doesn't works!

Jan 31, 2012 at 11:16 AM

Hi

i have a problem with a while loop through the sheets in the exporting Workbook.
The first part ($abfrage0) works well and i get the correct records in all sheets. Also the name of the sheets are correctly taken from the datarecords, but the last query ($abfrage4) doesn't get the correct datarecords and i don't know why.
Here the code:

	/*------------------------------------------------------------------------------------------------------
	  Les variables de la demande par date
	-------------------------------------------------------------------------------------------------------*/
	$id_acores 		= $_GET['id'];
	$date_demande 	= $_GET['date_demande'];
	$code_acores 	= $_GET['codeAcore'];
	$codeRegate 	= $_GET['codeRegate'];
	/*------------------------------------------------------------------------------------------------------
	  La premier requete pour obtenir les enregistrements par la date de demande
	-------------------------------------------------------------------------------------------------------*/

	$abfrage0 = "SELECT DISTINCT acores.id_acores...
					
	$ergebnis0 = mysql_query($abfrage0) or die("Query failed with error: ".mysql_error());

	if( mysql_numrows($ergebnis0) < 0)
	{
		$codeRegate	 		= mysql_result($ergebnis0 , 0 , "code_regate");
		$libBureau	 		= mysql_result($ergebnis0 , 0 , "libelle");
		$acoreTelephone		= mysql_result($ergebnis0 , 0 , "telephone");
		$acoreEmail	 		= mysql_result($ergebnis0 , 0 , "mail");
		$date_demande	 	= mysql_result($ergebnis0 , 0 , "date_demande");
		$agent				= mysql_result($ergebnis0 , 0 , "agent");
		$Nom				= mysql_result($ergebnis0 , 0 , "nom_agent");
		$Prenom				= mysql_result($ergebnis0 , 0 , "prenom_agent");
	}
	else
	{
		$codeRegate	 	= "";
		$libBureau	 	= "";
		$acoreTelephone	= "";
		$acoreEmail		= "";
		$date_demande	= "";
		$agent		 	= "";
		$Nom			= "";
		$Prenom			= "";
	}
	/*------------------------------------------------------------------------------------------------------
	  Debout du boucle
	-------------------------------------------------------------------------------------------------------*/

	while($abfrage0 = mysql_fetch_assoc($ergebnis0))			 
	{

	$cr 		= 	$abfrage0['code_regate'];
	$lib 		= 	$abfrage0['libelle'];
	$tel 		= 	$abfrage0['telephone'];
	$email 		= 	$abfrage0['mail'];
	$ddem 		= 	$abfrage0['date_demande'];
	$newA		=	$abfrage0['agent'];
	$newN		= 	$abfrage0['nom_agent'];
	$newP		=	$abfrage0['prenom_agent'];


	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$abfrage1 	= "select distinct devises from details_coupures order by devises";
	$ergebnis1 	= mysql_query($abfrage1) or die("Query failed with error: ".mysql_error());

	$rows=7;
	while($row = mysql_fetch_row($ergebnis1))
	{

	$abfrage2 	= "select count(coupure) as 'ncoupures' from details_coupures where devises = '".$row[0]."'";
	$ergebnis2 	= mysql_query($abfrage2) or die("Query failed with error: ".mysql_error());

	$ncoupures	 	= mysql_result($ergebnis2 , 0 , "ncoupures");
		
		$abfrage3 	= "select coupure from details_coupures where devises = '".$row[0]."' order by coupure";
		$ergebnis3 	= mysql_query($abfrage3) or die("Query failed with error: ".mysql_error());

		while($row1 = mysql_fetch_row($ergebnis3))
		{
		$abfrage4 = " SELECT DISTINCT detail_demande_appro_devise.quantite
						FROM demande_appro_devise, acores, infos_bureau, detail_demande_appro_devise 
						WHERE acores.id_acores = '".$id_acores."' 
						AND demande_appro_devise.date_demande = '".$date_demande."' 
						AND demande_appro_devise.code_acores = '".$codeAcores."' 
						AND detail_demande_appro_devise.coupure = '".$row1[0]."' 
						AND demande_appro_devise.code_devise = '".$row[0]."' 
						AND demande_appro_devise.num_demande = detail_demande_appro_devise.num_demande";
		$ergebnis4 	= mysql_query($abfrage4) or die("Query failed with error: ".mysql_error());
			
			
			if( mysql_numrows($ergebnis4) > 0)
			{
				$nombre_coupures	 	= mysql_result($ergebnis4 ['quantite']);
			}
			else
			{
				$nombre_coupures	 	= "0";
			}
				$objWorksheet->setCellValue('A'. $rows, $row[0]);
				$objWorksheet->setCellValue('B'. $rows, $row1[0]);
				$objWorksheet->setCellValue('C'. $rows, $nombre_coupures, $row1[0], $row[0]);
				$objWorksheet->setCellValue('D'. $rows, ( $nombre_coupures * $row1[0] ));
				$rows++;	
		}
	}

If anybody has an idea?

 

Thanks in advance

Achillix

Jan 31, 2012 at 12:36 PM

Hmm, i think mysql_numrows() is not a valid function, should be mysql_num_rows(). Furthermore, have you checked that the query actually gives any results? 

Some general notes:

- fix your indentation

- why are you not using the object oriented interface for mysqli?

- if you use mysql_fetch_assoc() you can use the field aliasses from your select as array keys, this makes your code much easier to read

- write your queries as 1 query using appropriate joins/group by's

- use 1 language instead of 3 ;)

Jan 31, 2012 at 2:04 PM

Hi

thank you for reply

Yes i get some results for the column A and B but C and D stays on "0".

The same result i get with mysql_numrows() and mysql_num_rows().

I can't  take join, because i will get much more results that i need, the results must be identically.

What do you mean about this:

- if you use mysql_fetch_assoc() you can use the field aliasses from your select as array keys, this makes your code much easier to read

 

Which language? French or German ? ;-)

 

Thanks

Jan 31, 2012 at 2:41 PM

D is always going to be 0, if your query mysql_num_rows() returns 0 or false. What I mean by aliases:

$query = 'SELECT id,name FROM users'

$result = $db->query($query);
$row = $result->fetch_assoc();

print_r($row);

Array
(
    [id] => 1
    [name] => mike
)

You can then use $row['name'] in stead of $row[1], which makes your code much easier to read. Another tip, if you want to know the numer of rows in a result, you can also do:

SELECT  COUNT(*) as total FROM your_table;

Jan 31, 2012 at 3:14 PM

I have the same query for a single Sheet Export and it works great. But here it shows me on the rows C & D only '0' when i put it in a while.

It is strange.

i will try it by arrays

thx

Feb 1, 2012 at 4:42 PM

Hi

I have found the problem, it was the wrong variables in my last query.

I changed only this part:

WHERE acores.id_acores = '".$id_acores."' 
AND demande_appro_devise.date_demande = '".$date_demande."' 
AND demande_appro_devise.code_acores = '".$codeAcores."' 

with this:

WHERE acores.id_acores = '".$idA."' 
AND demande_appro_devise.date_demande = '".$ddem."' 
AND demande_appro_devise.code_acores = '".$cora."' 

But i've don't tried with an Array, i don't know how ;-)

Thank you and greetings

Achillix