MDB2 rows of data $rowIterator?

Topics: Developer Forum, Project Management Forum, User Forum
Aug 3, 2011 at 11:32 AM

I’m trying to implement PHP Excel and have got the tests up and running no problem so I am now trying to manipulate it to do what I would like it to do – Create an Excel Spreadsheet using data from a MySQL DB using PHP.

Is this something that is possible? Within a php file I have a form with hidden values that I would like to be posted over to my excel.php page, in order to then use those $values and put them within the spreadsheet. When using your simple-download-xls.php as a template (which worked nicely), I tried adding this

$_GET['storenumber'];

$_GET['storename'];

$_GET['operatingdate'];

 This has been unsuccessful and I get an error upon opening the file – “The file you are trying to open is in a different format than specified by the file extension…”

Errors like this are displayed in the spreadsheet - Notice: Undefined index: storenumber in /var/www/html/leads/leadsexcel.php on line 24 -  followed by numerous symbols spread around.

I was hoping to also use these values to carry out a MySQL query and then use the data from the query within the spreadsheet too, is this possible?

Any response would be greatly appreciated, many thanks.

Aug 3, 2011 at 12:04 PM

Just to update, the error was my own stupid fault, still pretty new to PHP, I can use the variables by modifying my code to this:

$storenumber="$_REQUEST[storenumber]";
$storename="$_REQUEST[storename]";
$capturedate="$_REQUEST[capturedate]";

Will try the MySQL thing and reply again if needed.

Aug 3, 2011 at 12:55 PM

I would change that to the following if I were you:

 

$storenumber = $_REQUEST['storenumber'];
$storename = $_REQUEST['storename'];
$capturedate = $_REQUEST['capturedate'];
And don't forget to check if the request vars are actually present, and verify if the values make sense.... ;)

Aug 3, 2011 at 1:04 PM

Hi borft,

Thanks for your reply. Your suggestion is definietly better practise thanks for the headsup.

The values are definitely working now, I was able to use the variables in cells within the spreadsheet. I've now managed to get a query running using them, see below.

// Collect the posted details for the Leads Query
$storenumber=$_REQUEST['storenumber'];
$storename=$_REQUEST['storename'];
$operatingdate=$_REQUEST['operatingdate'];

$mdb2 =& MDB2::factory($dsn, $MDB2options);
$res =& $mdb2->query("SELECT * FROM leads WHERE storenumber='$storenumber' AND operatingdate='$operatingdate' ");
if (PEAR::isError($res)) {
				errorhandler("leads.php/003: ".$res->getMessage().' - '.$res->getUserinfo(), $a);
			}

(Nice code insert option in this forum, very impressed!)

My next challenge which I could really use some help with is inserting the results from the query into appropriate cells in the spreadsheet. There are lots of columns which I've put the headings in as required:

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
         ->setCellValue('A1', 'Lead ID')
         ->setCellValue('B1', 'Privilege Card' )
         ->setCellValue('C1', 'Wills Card' )
	->setCellValue('D1', 'Expert Card' )
	->setCellValue('E1', 'Title' )
	->setCellValue('F1', 'First Name' )
	->setCellValue('G1', 'Last Name' )
	->setCellValue('H1', 'Email' )
	->setCellValue('I1', 'Contact Number' )
	->setCellValue('J1', 'Postcode' )

There are lots more up to AF1!!

How can I use my while loop to get imput the data where it is needed?

while (($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)))
Thanks in advance for any help guys!!

Aug 3, 2011 at 1:19 PM

how about someting like:

$rowIterator = 2;
$columns = array('A', 'B', 'C');
while ( $row = $res->fetch_assoc() ){
  foreach ( $columns as $column ){
    $value = array_shift($row);    
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $rowIterator)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING) } $rowIterator++; }

Aug 3, 2011 at 1:40 PM

Thanks for another prompt response borft, I'm getting an error though - Parse error: syntax error, unexpected '}' - the one just before $rowIterator++.

Where should I be using this code? presumeably pretty much anywhere after the query? I've tried moving it around a bit and removing and adding } here and there but no luck!

Aug 3, 2011 at 1:56 PM

after your query ;)

 

it seems I forgot a semicolon (;) after setting the cell value.

Aug 3, 2011 at 3:08 PM

Well that was a hectic hour or so! Had hundres of calls and emails all of a sudden.

 

Sorry borft I don;t quite follow, could you please repaste the code with the semicolon I can't figure it out lol!

Aug 3, 2011 at 3:20 PM

$rowIterator = 2;
$columns = array('A', 'B', 'C');
while ( $row = $res->fetchRow(MDB2_FETCHMODE_ASSOC) ){
  foreach ( $columns as $column ){
    $value = array_shift($row);    
    $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $rowIterator)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
  }
  $rowIterator++;
}

Aug 3, 2011 at 4:05 PM

Thanks borft, that's sorted the } issue out. I have a new one now though (can you believe it?!).

I now have an error upon opening the file - “The file you are trying to open is in a different format than specified by the file extension…”

And within the file this error - Deprecated: Assigning the return value of new by reference is deprecated in /usr/share/pear/MDB2/Driver/mysql.php on line 924

Aug 3, 2011 at 4:13 PM
Edited Aug 3, 2011 at 4:14 PM

it is a waring, so not fatal, but it can be solved by not assigining the return value  (of your query) by reference. This means you have to move the & (ampersand) from the line in which you assign the query result:

 

 

$res = $mdb2->query("SELECT * FROM leads WHERE storenumber='$storenumber' AND operatingdate='$operatingdate' ");

 

furthermore wouldn't recommend inserting variables into queries this way. Prepared statements are the way to go, or atleast use something like sprintf:

 

 

$query = 'SELECT * FROM leads WHERE storenumber=%d AND operatingdate="%s"';

$res = $mdb2->query(sprintf($query, $storenumber, $operatingdate));

 

I have no experience with mdb2, but I cannot imagine machanisms like prepared statements are not available. If you can format your date as a number (eg. 20110803) you can safely use %d instead of %s for the date.

Aug 3, 2011 at 4:16 PM

hmm, upon rereading your error, I guess it is not the assignment of the result, but the assignment of the initialisation of the mdb2 object that is causing the error. Remove the & there and the warning will be gone ;)

Aug 3, 2011 at 4:24 PM

Thanks for the suggestion of removing the & borft but I still have exactly the same errors coming up. Thanks also for the headsup on the sprintf I shall see to that.

 

// Collect the posted details for the Leads Query
$storenumber=$_REQUEST['storenumber'];
$storename=$_REQUEST['storename'];
$operatingdate=$_REQUEST['operatingdate'];

$mdb2 = MDB2::factory($dsn, $MDB2options);

$res = $mdb2->query("SELECT * FROM leads WHERE storenumber='$storenumber' AND operatingdate='$operatingdate' ");
if (PEAR::isError($res)) {
errorhandler("leads.php/003: ".$res->getMessage().' - '.$res->getUserinfo(), $a);
			}
$rowIterator = 2;
$columns = array('A', 'B', 'C');
while ( $row = $res->fetchRow(MDB2_FETCHMODE_ASSOC) ){
foreach ( $columns as $column ){
$value = array_shift($row);    
 $objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($column, $rowIterator)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
  }
  $rowIterator++;
}

Aug 3, 2011 at 4:55 PM

Changing error reporting to 0 has got rid of both errors, only downside is, the data I want isn't displaye

 

error_reporting(0);

Aug 5, 2011 at 7:54 AM

I've still not resolved this, can anyone help? All I could think of was to change this to my column headings and also my table columns but not luck:

$columns = array ('id','privilegecard'.......

Aug 5, 2011 at 3:14 PM

Nevermind, sorted it.

Jan 21, 2012 at 2:58 PM

hey can you explain how did you sort the issue

I have the same issue in my project

 

Jan 23, 2012 at 10:50 AM

Hi,

I'm really sorry but I can't remember what it was, it's a few months ago now.

 

I've tried reviewing the code but can't figure it out I'm afraid. I'll have another look shortly to see if I can spot it for you.