Karebac Posted June 30, 2007 Share Posted June 30, 2007 I am a total beginner. I realize there is not a Money data type in MySQL. I need to create a price list table in which there are three prices: price per box, price per case, and manufacturer's suggest list price. Should I define those fields as decimal, or integer, or float? What do you suggest. Then, I want to write a php script to list off the price list as a table. Thanks for any help or suggestions on how to define the money data type, and also, how to handle it in php, with regard to formatting. Quote Link to comment Share on other sites More sharing options...
Karebac Posted June 30, 2007 Author Share Posted June 30, 2007 OK. I think I figured out part of my answer. I went into my SQLyog mysql shareware utility, defined a table as prices, defined price1 as decimal, and put 8,2 in the Length field. Then I entered a few prices, and exported the table to csv. I opened it in PSPad, and brought it into Excel. I cant figure out how to make Excel export the table I have so that each field is surrounded with double quotes, so I could import it easily into MySQL. Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 30, 2007 Share Posted June 30, 2007 I cant figure out how to make Excel export the table I have so that each field is surrounded with double quotes, so I could import it easily into MySQL. If you have access to phpMyAdmin, you can import a CSV file with your choice of delimiter. Quotes are not needed. Quote Link to comment Share on other sites More sharing options...
Karebac Posted June 30, 2007 Author Share Posted June 30, 2007 Thanks for advice! I tried phpmyadmin on the csv, with no success But I was successful with the freeware SQLyog (www.webyog.com) which has an option for Excel friendly import. All I had to do was remove the reference to double quote. So, now I have my table of prices, and I was able to adapt the following code to list the prices (below). But NOW, my next project is to learn how to enhance this php script so that I can have pages, with column headings, and also, control the column width. <html><head> <title>Prices</title></head> <body> <? $dbHost = "mysql"; $dbUser = "YoursTruly"; $dbPass = "YeahRight!"; $dbDatabase = "company"; // names have been changed to protect the guilty $db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database."); mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database."); $query = "SELECT * FROM `prices`"; $result = mysql_query($query); echo "<table border=1>\n"; while ($myrow = mysql_fetch_row($result)) { printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow[2], $myrow[3], $myrow[4], $myrow[5], $myrow[6], $myrow[7], $myrow[8]); } echo "</table>\n"; mysql_close($db); ?> </body></html> Here is the SQL code which SQLyog produced to create the table that I use. CREATE TABLE `prices` ( `pk` INT( 6 ) NOT NULL AUTO_INCREMENT , `seq` INT( 6 ) NOT NULL , `part` VARCHAR( 12 ) NOT NULL , `grit` INT( 6 ) NOT NULL , `qpb` INT( 6 ) NOT NULL , `bpc` INT( 6 ) NOT NULL , `ppb` DECIMAL( 5, 2 ) NOT NULL , `ppc` DECIMAL( 5, 2 ) NOT NULL , `mslp` DECIMAL( 5, 2 ) NOT NULL , PRIMARY KEY ( `pk` ) ) TYPE = MYISAM ; Quote Link to comment Share on other sites More sharing options...
Karebac Posted June 30, 2007 Author Share Posted June 30, 2007 Now I have an answer for column width and justification: while ($myrow = mysql_fetch_row($result)) { printf("<tr><td width=110>%s</td><td width=50 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td></tr>\n", $myrow[2], $myrow[3], $myrow[4], $myrow[5], $myrow[6], $myrow[7], $myrow[8]); } So, next I need to find out how to make this into pages with column headings. Quote Link to comment Share on other sites More sharing options...
Karebac Posted June 30, 2007 Author Share Posted June 30, 2007 Now I have a way to print column headings every time the part number changes But I am still looking for a better way to page and format. At least what I have is useable, though primitive. $prev = ""; echo "<table border=1>\n"; while ($myrow = mysql_fetch_row($result)) { if ($prev != substr($myrow[2],1,1)) {printf("<TR><TH>Part #</TH><TH>Grit</TH><TH>Qty per Box</TH><TH>Boxes per Case</TH><TH>Price/Box</TH><TH>Price/Box(full case)</TH><TH>Mfg.Sugg.List</TH></TR>");} printf("<tr><td width=110>%s</td><td width=50 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td><td width=70 align=right>%s</td></tr>\n", $myrow[2], $myrow[3], $myrow[4], $myrow[5], $myrow[6], $myrow[7], $myrow[8]); $prev = substr($myrow[2],1,1); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.