bowen73

technical decisions come from requirements and my requirement comes from not wanting gaps. our asset tags comprise 5 numbers, when i roll the same system across our other regions i dont want the possibility of running out of numbers within a year or 2, so using blank spaces will help to aleviate this problem. i'll prob go with auto increment on another column for asset and keep ID as primary so i can guarantee a unique line for when an item is EOL'd. ive got this to 'fill the gaps' which seams to give me the right results, so just need to throw it in with my coding. <?php error_reporting(E_ALL); ini_set('display_errors', '1'); include("include/connect.php"); $sql = "SELECT MIN(t1.asset_id + 1) AS nextID FROM asset_list t1 LEFT JOIN asset_list t2 ON t1.asset_id + 1 = t2.asset_id WHERE t2.asset_id IS NULL"; $result = mysqli_query($connect, $sql); while($row = mysqli_fetch_assoc($result)) { echo "<li>" . sprintf("%05d", $row['nextID'] ). "</li>"; } $connect->close(); ?>

its just how i want it, and a little OCD too of not wanting gaps. I may even just scrap the tags we have and start again from 1 just to keep my brain happy :-)

thanks for that i'll take a look. ive just been using code examples from searches and not come across pdo. i thought the newest was mysqli instead on mysql.

HI, Thanks for the reply. I do have asset_id set as the primary key and auto increment but thought an ID couldnt be entered and needed to auto increment and didnt want to replace all the tags that we already have. so if i understand what youve said, doing it that way, if i set int(5) and autofill zero, then the codes would run 00001, 00002, 00003 etc.... then if an entry was done manually i.e 00303 then it will continue to auto increment from 00003 to 00302 then skip to 00304? how can i get it to display on the page if there is a 'generate' button, so if there isnt a current tag on it and a new one needs to be generated for it to +1 from 00003 to 00004 instead of getting the max value and showing 00303. I need this to be show as i intend to get an asset label printer, so on the 'generated' code i can 'save & print' to get a label and save the into to the DB thanks

im trying to make an asset list and the DB/php us getting a bit beyond my limited knowledge and searching online the suggestions are giving me errors or not incrementing! basically i have a column (asset_tag) that contains a code...lets say MM00001. As we already have tags not in a DB that i need to add these into the DB (which is the easy part) but im also after a 'generate' when adding an item to the DB that does not already have a tag. so then it looks at the DB from asset_tag ( MM00001 ) and increments to the next number into a variable ready to add to the DB with product info. but if a number has been manually added (lets say MM03003) then i want to generate a number up to that then skip past as it already exists. ive tried to get started with things like: SELECT * FROM asset_list WHERE asset_tag = (SELECT MAX(asset_tag) FROM asset_list) ive also tried this just to see if i get back the test number of MM00005 in the DB to get started: $row = mysql_fetch_assoc( mysql_query( "SELECT asset_tag FROM asset_list ORDER BY asset_tag DESC LIMIT 1" ) ); $number = $row['asset_tag']; echo $number but i just get this error: Fatal error: Uncaught Error: Call to undefined function mysql_fetch_assoc() in C:\xampp\htdocs\Magic\test.php:20 Stack trace: #0 {main} thrown in C:\xampp\htdocs\Magic\test.php on line 20 ive also looked at insert_id but the examples ive seen return the ID after its been added rather than generating the next one to use... ive been scratching my head at this for a while to try and get somewhere, but i have to admit defeat and request some help to get me in the right direction, thanks

I got there with some logical thinking, just moved the ,2 one ) in. Thanks for your help @barrand ->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70,2))

i got to that part after you suggested number_format :-)

ive got this far.... if i remove the ',2' it outputs the numbers without decimels.... but if i put the ,2 back in i get an error ->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70),2) Error: Fatal error: Uncaught Error: Call to undefined method PHPExcel_Cell::setCellValue() in C:\xampp\htdocs\pmc\dev\csv.php:66 Stack trace: #0 {main} thrown in C:\xampp\htdocs\pmc\dev\csv.php on line 66

Ive just looked at that, im still learning php so its trying to apply the examples without breaking stuff. i tried various ways to include it in the setCellValue but not got anywhere :-( Ive tfound ones for phpexcel too objPHPExcel->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode('#,##0'); but dont know how to implement it into my code without throwing out an error

Help Please im trying to export from mysql whereas the code adds a calculation (*100/70) to the cost and exports that (along with other info from the DB) to the csv (hopefully that makes sense) but its exporting 4 or sometimes 5 decimel places ive had a look but nothing ive seen i can make work without getting an error, <?php require ("../connect.php"); //$sql ="select * from products"; $sql = mysqli_query($connect,"Select * FROM products"); while ($row = mysqli_fetch_array($sql)) require_once '../Classes/PHPExcel/IOFactory.php'; require_once '../Classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Mark Bowen") ->setLastModifiedBy("Mark Bowen") ->setTitle("Product Export") ->setSubject("Product Export") ->setDescription("Product export from database to import into Website") ->setKeywords("office 2007 openxml php") ->setCategory("CSV result file"); // Add some data $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1',"sku") ->setCellValue('B1',"name") ->setCellValue('C1',"short_description") ->setCellValue('D1',"description") ->setCellValue('E1',"msrp") ->setCellValue('F1',"price") ->setCellValue('G1',"cost") ->setCellValue('H1',"in_in_stock") ->setCellValue('I1',"qty") ->setCellValue('J1',"visibility") ->setCellValue('K1',"status") ->setCellValue('L1',"websites") ->setCellValue('M1',"type") ->setCellValue('N1',"image") ->setCellValue('O1',"image_label") ->setCellValue('P1',"small_image") ->setCellValue('Q1',"small_image_label") ->setCellValue('R1',"thumbnail") ->setCellValue('S1',"thumbnail_label") ->setCellValue('T1',"categories") ->setCellValue('U1',"tax_class") ->setCellValue('V1',"weight") ->setCellValue('W1',"meta_title") ->setCellValue('X1',"meta_description") ->setCellValue('Y1',"meta_key") ->setCellValue('Z1',"manufacturer") ->setCellValue('AA1',"url_key"); $i = 2; foreach($sql as $row){ $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$i,$row['prod_sku']) ->setCellValue('B'.$i,$row['prod_name']) // ->setCellValue('C'.$i,$row['short desc']) ->setCellValue('D'.$i,$row['prod_long']) ->setCellValue('E'.$i,$row['prod_rrp']) ->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : $row['prod_trade']*100/70) ->setCellValue('G'.$i,$row['prod_trade']) // ->setCellValue('H'.$i,$row['is in stock']) // ->setCellValue('I'.$i,$row['qty']) // ->setCellValue('J'.$i,$row['visibility']) ->setCellValue('K'.$i,$row['prod_status']) // ->setCellValue('L'.$i,$row['websites']) // ->setCellValue('M'.$i,$row['type']) // ->setCellValue('N'.$i,$row['image']) // ->setCellValue('O'.$i,$row['image label']) // ->setCellValue('P'.$i,$row['small image']) // ->setCellValue('Q'.$i,$row['small image label']) // ->setCellValue('R'.$i,$row['thumbnail']) // ->setCellValue('S'.$i,$row['thumbnail label']) // ->setCellValue('T'.$i,$row['categories']) // ->setCellValue('U'.$i,$row['tax_class']) ->setCellValue('V'.$i,$row['prod_weight']) // ->setCellValue('W'.$i,$row['meta_title']) // ->setCellValue('X'.$i,$row['meta_description']) // ->setCellValue('Y'.$i,$row['meta_key']) // ->setCellValue('Z'.$i,$row['manufacturer']) // ->setCellValue('AA'.$i,$row['url_key']); ; $i++; } // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('export'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) // header('Content-Type: application/vnd.ms-excel'); // header('Content-Disposition: attachment;filename="export_DB_products.csv"'); // header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV'); $objWriter->save('../export/export_db_products.csv'); ?>

Ive got it.... I changed the line to this and it seams to have worked. just need to get phpexcel to export to 2 decimal places now. thanks for pointing me in the right direction, very much appreciated :-) ->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : $row['prod_trade']*100/70)

Hi, Thanks for the input. you may have guess put im still learning coding (and still a way to go haha) ive changed the code to the line you provided, its not giving an error now (fantastic), but even though the DB is NULL or 0.00 it is putting the zero value into the export rather than the cost *100/70

Im trying to export a csv file from a mysql DB, which works, but im trying to put an if statement in, so basically.... for a cell of a product if prod_override is zero or empty the its uses cost +30% if the override price has a value then this value is to be used. i get an error of 'unexpected if'. can anyone point me in the right direction?? This is what i have so far.... everything works until i put the if else in. <?php require ("../connect.php"); //$sql ="select * from products"; $sql = mysqli_query($connect,"Select * FROM products"); while ($row = mysqli_fetch_array($sql)) require_once '../Classes/PHPExcel/IOFactory.php'; require_once '../Classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document properties $objPHPExcel->getProperties()->setCreator("Mark Bowen") ->setLastModifiedBy("Mark Bowen") ->setTitle("Product Export") ->setSubject("Product Export") ->setDescription("Product export from database to import into Website") ->setKeywords("office 2007 openxml php") ->setCategory("CSV result file"); // Add some data $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1',"sku") ->setCellValue('B1',"name") ->setCellValue('C1',"short_description") ->setCellValue('D1',"description") ->setCellValue('E1',"msrp") ->setCellValue('F1',"price") ->setCellValue('G1',"cost") ->setCellValue('H1',"in_in_stock") ->setCellValue('I1',"qty") ->setCellValue('J1',"visibility") ->setCellValue('K1',"status") ->setCellValue('L1',"websites") ->setCellValue('M1',"type") ->setCellValue('N1',"image") ->setCellValue('O1',"image_label") ->setCellValue('P1',"small_image") ->setCellValue('Q1',"small_image_label") ->setCellValue('R1',"thumbnail") ->setCellValue('S1',"thumbnail_label") ->setCellValue('T1',"categories") ->setCellValue('U1',"tax_class") ->setCellValue('V1',"weight") ->setCellValue('W1',"meta_title") ->setCellValue('X1',"meta_description") ->setCellValue('Y1',"meta_key") ->setCellValue('Z1',"manufacturer") ->setCellValue('AA1',"url_key"); $i = 2; foreach($sql as $row){ $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$i,$row['prod_sku']) ->setCellValue('B'.$i,$row['prod_name']) // ->setCellValue('C'.$i,$row['short desc']) ->setCellValue('D'.$i,$row['prod_long']) ->setCellValue('E'.$i,$row['prod_rrp']) //start IF ELSE statement for Sale Price if ( ->setCellValue('F'.$i,$row['prod_sell_override']) == 0 ) { ->setCellValue('F'.$i,$row['prod_trade']*100/70); } else { ->setCellValue('F'.$i,$row['prod_sell_override']) } //End IF ELSE Statement ->setCellValue('G'.$i,$row['prod_trade']) // ->setCellValue('H'.$i,$row['is in stock']) // ->setCellValue('I'.$i,$row['qty']) // ->setCellValue('J'.$i,$row['visibility']) ->setCellValue('K'.$i,$row['prod_status']) // ->setCellValue('L'.$i,$row['websites']) // ->setCellValue('M'.$i,$row['type']) // ->setCellValue('N'.$i,$row['image']) // ->setCellValue('O'.$i,$row['image label']) // ->setCellValue('P'.$i,$row['small image']) // ->setCellValue('Q'.$i,$row['small image label']) // ->setCellValue('R'.$i,$row['thumbnail']) // ->setCellValue('S'.$i,$row['thumbnail label']) // ->setCellValue('T'.$i,$row['categories']) // ->setCellValue('U'.$i,$row['tax_class']) ->setCellValue('V'.$i,$row['prod_weight']) // ->setCellValue('W'.$i,$row['meta_title']) // ->setCellValue('X'.$i,$row['meta_description']) // ->setCellValue('Y'.$i,$row['meta_key']) // ->setCellValue('Z'.$i,$row['manufacturer']) // ->setCellValue('AA'.$i,$row['url_key']); ; $i++; } // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('export'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) // header('Content-Type: application/vnd.ms-excel'); // header('Content-Disposition: attachment;filename="export_DB_products.csv"'); // header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV'); $objWriter->save('../export/export_db_products.csv'); ?> Thanks in advance for any help