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. Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/ 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. Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/#findComment-286358 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. Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/#findComment-286378 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 ; Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/#findComment-286490 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. Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/#findComment-286505 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); } Link to comment https://forums.phpfreaks.com/topic/57796-money-data-type-in-mysql/#findComment-286536 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.