[SOLVED] Need a fresh pair of eyes to help

elentz · April 20, 2009

I know this must be simple, I have been staring at this for a long time and can't get it figured out. I know the problem is in the result of the query, getting the right row data. I have tested the query and it does get the info I need.

$sql= "SELECT
vtiger_inventoryproductrel.quantity,
vtiger_products.productcode,
vtiger_products.productname
FROM
vtiger_inventoryproductrel
Inner Join vtiger_products ON vtiger_inventoryproductrel.productid = vtiger_products.productid
Inner Join vtiger_productcf ON vtiger_products.productid = vtiger_productcf.productid
WHERE
vtiger_inventoryproductrel.id =  '17898'";
//Select the Products you want to show in your PDF file


$result=mysql_query($sql);
$number_of_products = mysql_numrows($result);


//For each row, add the field to the corresponding column
while($row = mysql_fetch_array($result))
{
        $qnty = $row["quantity"];        $part = $row["productcode"];        $desc = $row["productname"];        $column_qnty = $column_quantity.$qnty."\n";        $column_part = $column_productcode.$part."\n";        $column_desc = $column_productname.$desc"\n";}
mysql_close();



//Create a new PDF file
$pdf=new FPDF();
$pdf->AddPage();

//Fields Name position
$Y_Fields_Name_position = 20;
//Table position, under Fields Name
$Y_Table_Position = 26;

//First create each Field Name
//Gray color filling each Field Name box
$pdf->SetFillColor(232,232,232);
//Bold Font for Column Field Name
$pdf->SetFont('Arial','B',12);
$pdf->SetY($Y_Fields_Name_position);
$pdf->SetX(45);
$pdf->Cell(20,6,'Qnty',1,0,'L',1);
$pdf->SetX(65);
$pdf->Cell(30,6,'Part #',1,0,'L',1);
$pdf->SetX(135);
$pdf->Cell(100,6,'Description',1,0,'R',1);
$pdf->Ln();

//Now show the 3 columns
$pdf->SetFont('Arial','',12);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(45);
$pdf->MultiCell(20,6,$column_qnty,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(65);
$pdf->MultiCell(30,6,$column_part,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(135);
$pdf->MultiCell(100,6,$column_desc,1,'R');
$pdf->SetX(135);


//Create lines (boxes) for each ROW (Product)
//If you don't use the following code, you don't create the lines separating each row
$i = 0;
$pdf->SetY($Y_Table_Position);
while ($i < $number_of_products)
{
        $pdf->SetX(45);
        $pdf->MultiCell(120,6,'',1);
        $i = $i +1;
}

$pdf->Output();
?>

When I open the page I get nothing (blank page) I got this script form the FPDF site and all I did was change the query and the variable names, and the connection to the DB. I created table in my DB that coincided with what the original script used and I got the page results I wanted. I just don't see where I went wrong.

Thanks alot for any help!

premiso · April 20, 2009

You are closing mysql in the loop.

You do not need to use mysql_close in your code as it is closed automatically upon the script finishing.

But that is probably your issue. An error is being thrown cause the resource is no longer valid once you have closed the mysql connection.

mrMarcus · April 20, 2009

mysql_numrows($result);

should be

$number_of_products = mysql_num_rows($result);

you are missing a _ between num and rows - mysql_numrows -> mysql_num_rows

not going to solve your problem, but it's the first thing i noticed so i thought i'd point it out.

premiso · April 20, 2009

mysql_numrows($result);
should be
$number_of_products = mysql_num_rows($result);
you are missing a _ between num and rows - mysql_numrows -> mysql_num_rows

Just an fyi.

mysql_numrows they both work, as it is an alias of mysql_num_rows.

mrMarcus · April 20, 2009

i stand corrected .. never knew that

elentz · April 20, 2009

Thanks for the help so far. I deleted the Mysql Close and got the same result. Any other suggestions, Does anyone know of a tutorial that explains what or the mechanics of extracting the data.

Thanks

soak · April 20, 2009

In the loop you're setting the values correctly but you're overwriting them each time.

Move your while loop (and its contents) and wrap it around the bit that prints the row. So:

// new pdf stuff

// header stuff

while () {

// set variable values

// print the pdf row

}

end pdf stuff, footer, echo the pdf etc

elentz · April 20, 2009

I am confused now. I used some code I got and changed the query and the variables around. Here is the original code

//Select the Products you want to show in your PDF file
$result=mysql_query("select Code,Name,Price from Products ORDER BY Code",$link);
$number_of_products = mysql_numrows($result);

//Initialize the 3 columns and the total
$column_code = "";
$column_name = "";
$column_price = "";
$total = 0;

//For each row, add the field to the corresponding column
while($row = mysql_fetch_array($result))
{
        $code = $row["Code"];
        $name = substr($row["Name"],0,20);
        $real_price = $row["Price"];
        $price_to_show = number_format($row["Price"],',','.','.');

        $column_code = $column_code.$code."\n";
        $column_name = $column_name.$name."\n";
        $column_price = $column_price.$price_to_show."\n";

        //Sum all the Prices (TOTAL)
        $total = $total+$real_price;
}
mysql_close();

//Convert the Total Price to a number with (.) for thousands, and (,) for decimals.
$total = number_format($total,',','.','.');

//Create a new PDF file
$pdf=new FPDF();
$pdf->AddPage();

//Fields Name position
$Y_Fields_Name_position = 20;
//Table position, under Fields Name
$Y_Table_Position = 26;

//First create each Field Name
//Gray color filling each Field Name box
$pdf->SetFillColor(232,232,232);
//Bold Font for Field Name
$pdf->SetFont('Arial','B',12);
$pdf->SetY($Y_Fields_Name_position);
$pdf->SetX(45);
$pdf->Cell(20,6,'CODE',1,0,'L',1);
$pdf->SetX(65);
$pdf->Cell(100,6,'NAME',1,0,'L',1);
$pdf->SetX(135);
$pdf->Cell(30,6,'PRICE',1,0,'R',1);
$pdf->Ln();

//Now show the 3 columns
$pdf->SetFont('Arial','',12);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(45);
$pdf->MultiCell(20,6,$column_code,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(65);
$pdf->MultiCell(100,6,$column_name,1);
$pdf->SetY($Y_Table_Position);
$pdf->SetX(135);
$pdf->MultiCell(30,6,$column_price,1,'R');
$pdf->SetX(135);
$pdf->MultiCell(30,6,'$ '.$total,1,'R');

//Create lines (boxes) for each ROW (Product)
//If you don't use the following code, you don't create the lines separating each row
$i = 0;
$pdf->SetY($Y_Table_Position);
while ($i < $number_of_products)
{
        $pdf->SetX(45);
        $pdf->MultiCell(120,6,'',1);
        $i = $i +1;
}

$pdf->Output();

If I add my DB connection and use a DB and table that matches what this script is looking for it works fine. So changing the while loop is confusing to me, if it works with this code and not the other.

soak · April 20, 2009

It looks as though it will print 1 row and that's it. Does your "working" version print more than 1 product row?

I was thinking of something like this:

<?php
//Select the Products you want to show in your PDF file
$result=mysql_query("select Code,Name,Price from Products ORDER BY Code",$link);
$number_of_products = mysql_numrows($result);

//Initialize the 3 columns and the total
$column_code = "";
$column_name = "";
$column_price = "";
$total = 0;

//Create a new PDF file
$pdf=new FPDF();
$pdf->AddPage();

//Fields Name position
$Y_Fields_Name_position = 20;
//Table position, under Fields Name
$Y_Table_Position = 26;

//First create each Field Name
//Gray color filling each Field Name box
$pdf->SetFillColor(232,232,232);
//Bold Font for Field Name
$pdf->SetFont('Arial','B',12);
$pdf->SetY($Y_Fields_Name_position);
$pdf->SetX(45);
$pdf->Cell(20,6,'CODE',1,0,'L',1);
$pdf->SetX(65);
$pdf->Cell(100,6,'NAME',1,0,'L',1);
$pdf->SetX(135);
$pdf->Cell(30,6,'PRICE',1,0,'R',1);
$pdf->Ln();

//For each row, add the field to the corresponding column
while($row = mysql_fetch_array($result))
{
        $code = $row["Code"];
        $name = substr($row["Name"],0,20);
        $real_price = $row["Price"];
        $price_to_show = number_format($row["Price"],',','.','.');

        $column_code = $column_code.$code."\n";
        $column_name = $column_name.$name."\n";
        $column_price = $column_price.$price_to_show."\n";

        //Sum all the Prices (TOTAL)
        $total = $total+$real_price;

        //Now show the 3 columns
        $pdf->SetFont('Arial','',12);
        $pdf->SetY($Y_Table_Position);
        $pdf->SetX(45);
        $pdf->MultiCell(20,6,$column_code,1);
        $pdf->SetY($Y_Table_Position);
        $pdf->SetX(65);
        $pdf->MultiCell(100,6,$column_name,1);
        $pdf->SetY($Y_Table_Position);
        $pdf->SetX(135);
        $pdf->MultiCell(30,6,$column_price,1,'R');
}

//Convert the Total Price to a number with (.) for thousands, and (,) for decimals.
$total = number_format($total,',','.','.');

$pdf->SetX(135);
$pdf->MultiCell(30,6,'$ '.$total,1,'R');

//Create lines (boxes) for each ROW (Product)
//If you don't use the following code, you don't create the lines separating each row
$i = 0;
$pdf->SetY($Y_Table_Position);
while ($i < $number_of_products)
{
        $pdf->SetX(45);
        $pdf->MultiCell(120,6,'',1);
        $i = $i +1;
}

$pdf->Output();

elentz · April 20, 2009

Yeah the working version printed all the products I created in my test (4).

soak · April 20, 2009

Ahhh, I see, you're putting them all in 1 big MultiCell, not in seperate Cell's.

In that case, ensure that error reporting is on and issue a die(); before your pdf is output.

Put this at the very start of your script:

error_reporting(E_ALL);

ini_set('display_errors', 1);

I suspect there's an error in your code somewhere.

elentz · April 20, 2009

Arghhhh! I tried the script with the error reporting and still got the blank page.

soak · April 20, 2009

Got it.... possibly:

$column_desc = $column_productname.$desc"\n";

should be

$column_desc = $column_productname.$desc."\n";

elentz · April 20, 2009

Well, That yielded ALOT!