Jump to content

Money data type in MySQL


Karebac

Recommended Posts

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

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.

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.

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 ;

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.

 

 

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);
}

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.