MYSQL :: How Decimal Works

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

Works In MYSQL 4.1 But Not In 5.0
MySQL
1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause'
SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ'

Here is the query in PHP

PHP
"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data.

I've tried removing the close() commands but that doesn't seem to work.

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM access_table WHERE at.client_id=2' at line 1

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.....

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....

Suddenly A Script No Longer Works
I always get the following message when starting a search in my database:

Search for titles in the mylibrary database.

Search for titles beginning with a:

V. 4.1.7 + Php4 Works, + Php5 Problems
I am running:

Mac OS X 10.3.7
apache 2.0.52
MySQL 4.1.7
php 4.9.3 & php 5.0.3

I have an html form that "POST"s data to a db.
I have a "GET" script to repopulate form for editing.
Everything works fine with php 4.9.3.

With php5.0.3 the data is "POST"ed just fine
(I used a different GUI app to check)
When I try to "GET" said data, form is returned empty?

I have all error reporting turned on = nothing.
I have all backwards-compatibility directives in
php.ini config file turned on.

Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems.

However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?

Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M
where J.msgid=M.msgid and M.parent!=0

When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired.
But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says
#1064 - You have an error in your SQL syntax.

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?

My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.

SQL Query Works Fine With Phpmyadmin But Gives Me No Results And EOF With ASP
I have a strange problem: when I perform a sql query on my phpmyadmin it works fine and it gives me the right results. If I perform the _same_ query through my asp application I reach immediately the EOF condition without any results!

Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin.

THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN:

$sql = "update table set color='red' where id='1';";
$sql_do = mysql_query($sql);

THIS QUERY WORKS ONLY IN PHPMYADMIN:

$sql = "update table set color='red' where id='1'; update table set color='green' where id='2';";
$sql_do = mysql_query($sql);

I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.

Quering Works Except Need To Sort Results By Other Table...
I have the following query:

SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;

This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error...

SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC

?

Is DECIMAL(3) The Same As DECIMAL(3,0)
Pretty straight forward question: Is DECIMAL(3) the same as DECIMAL(3,0)
I need to add this
store_config ADD sdiscount_val DECIMAL(3) NOT NULL default '0'
and it comes out this
store_config ADD sdiscount_val DECIMAL(3,0) NOT NULL default '0'
The script I am trying to run won't work, is this the problem?
If they are not the same how do I get the first to work?

Fix Decimal
i have this number after executed my sql, 73.4000. how to fix into 73.40?
Code:

SELECT ST.STUDENT_ID, ST.STUDENT_NAME, SB.SUBJECT_CODE, SB.SUBJECT_NAME,
(((RS.Q1 + RS.Q2 + RS.Q3 + RS.Q4 + RS.Q5) / 100) * AN.PERCENT_FM) AS FINAL_MARKS, RS.CARRY_MARKS,
((((RS.Q1 + RS.Q2 + RS.Q3 + RS.Q4 + RS.Q5) / 100) * AN.PERCENT_FM) + RS.CARRY_MARKS) AS FULL_MARKS
FROM RESULT RS
INNER JOIN STUDENT ST
ON RS.STUDENT_ID = ST.STUDENT_ID
INNER JOIN SUBJECT SB
ON RS.SUBJECT_CODE = SB.SUBJECT_CODE
INNER JOIN ASSESSMENT AN
ON SB.ASSESSMENT_ID = AN.ASSESSMENT_ID
WHERE RS.STUDENT_ID = '2006000005';

Decimal
I am having the exact same issue as here:

http://forums.devshed.com/mysql-help-4/help-with-decimal-type-356535.html?&highlight=decimal

Unfortunately I cant find a solution.
My webform is entering the data as a numeric. The database is set up to be Decimal (4,1). However anything after the decimal will only show as zero. If I go into phpMysql and enter the decimal there it works fine, so like in the other thread I am assuming that the decimal is being stripped somewhere along the way, I just cant figure out where

Decimal
I'm setting up a field in my database for a number with 3 decimal places.
Example: .264

I picked:
"type" - decimal
"length" - 3

It's not working. After I enter the data, the number reads "0".

Float (7,2) Vs Decimal (7,2)
I wanted to get some input on one vs the other. I have recently switched my Float to Decimal and I have seen a slight performance increase so this sound typical. The Column contains current and 99.5% of it is .00.

Any input on one or the other. I heard guelphdad talk about accuracy on the MySQL site any other comments?

DECIMAL Question
I have been using DECIMAL instead of FLOAT for all my prices. Today, when designing a new table I had to choose between these two types to store a percent value.

This percentage need to be exact and is user-controlled -- in fact, it is part of the price of the item (a shipping upgrade which has a fixed base price, plus costs a percentage of the shopping cart total).

Wanting precision, I decided to go with DECIMAL (4,4) -- since 2.5% will be stored as 2.5, not 0.025, I thought DECIMAL(4,4) would be appropriate.

I made the default value for the percentage field "1.0". Then to check my table creation I did a "describe table" and got the following:

mysql> describe shipping_upgrades;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
(leaving out a few fields)
| base_price | decimal(6,2) | NO | | 0.00 | |
| percentage | decimal(4,4) | NO | | 0.9999 | |
+------------+----------------------+------+-----+---------+----------------+
Why would the default value be changed to 0.9999?

I thought this was exactly the kind of thing that using DECIMAL instead of FLOAT was supposed to solve...

edit: i am running MySQL 5.0.22-standard

Decimal Type
I want to display 2 decimal places in mysql database.

Here's my query...
insert into delivery values(NULL, "IRL", "10.00");

I set data type to DECIMAL but the above is stored as 10

Decimal Places In DB
I'm having a problem with decimal places in an ecommerce website. I have
insterted the prices for products into the DB and set the decimal places to
2. The problem comes when I enter a number such as 1.50 which then becomes
1.5 in the database. This isn't a problem with my code as if i manually
insert the same figure, the result is the same. Not a big problem but as it
is for a shopping site it would be better to have the full figure (1.50)
displayed.

Use Of The Decimal Field
I am trying to get into MySQL. I have entered a field in a table 'Cash' Cash DECIMAL(6,2)
But I am uncertain as to the size of the value. Is it 6 digits plus 2 decimal or is it 6 total digits which would mean 4 plus 2 decimal. I have tried entering the following and here are the results

Entered Result

265.32 265.32
2265.32 2265.32
22265.32 22265.32
122265.32 99999.99

This does not make sense and no error is thrown by the database.

Decimal Vs Integer
I had two fields in a mySQL table:

cost
quantity

Both were of the type Int. I needed decimal places for the Cost field so
changed both to be Numeric

cost - numeric 6,2
quantity - numeric 11,0

Before when they were both Int I had an asp page with did cost * quantity =
total which worked fine. Now I'm getting a Type Mismatch.

Any idea on why this is happening and how to fix it?

Decimal Precision
I've a table with a column type DECIMAL(22,21) UNSIGNED

Assume that I want to store 0.02270412445068359375
When I store it in the database, stored value is 0.022704124450683593750
^
Is it possible to avoid last zero? It's not useful for me.
When I read from database returned value is 0.022704124450683593750

My target is that returned value is 0.02270412445068359375.

Decimal Field That I Need To Sum Up
I have a decimal field that I need to sum up. I need to try to get that summed field into some type of field that will show commas in the number like 12,234.99.

Using Varchar VS Decimal
I have a program I am working on in Delphi 7, and it is like a grade book for teachers. When they are running it on windows XP in English it works fine, however living in quebec, canada I also have people using XP French.

In french the decimal is a comma. So 89.9 would be changed to 89,7. I am storing the data as a varchar right now and using StrToFloat() function when I need to access the data and perform calculations. As you can imagine 89,7 is not accepted by StrToFloat.

So the question is, if I stored the data as 89,7 as a decimal rather than a varchar would this work correctly?

Decimal In Database
I'm trying to setup a new table in my database that will be a number with a decimal point. What field type should I use? And how long should the field length be?

example field1: 0.40
example field2: .358

Decimal Problem
whenever i insert data into my decimal column the value is different from what i wanted. like when i enter -17.5 the value goes in entered as -9.

My decimal type is entered as (1,0). I just want it to be one decimal place and to betered in as what i want.
Also i was wondering how auto_increment works. when i enter data, do i leave the column out.

Decimal Type
ive got a row that stores a number that sometimes needs to have a decimal. right now i have the type set to decimal(4,1)

if i insert info into the row like 8, then when i look in the database it is shown as 8.0, which works fine. but if i input 7.5 then go look in the database it is stored as 7.0. it doesnt seem to want to store the number after the decimal. its always just a 0.

Decimal Points
I have a form for real estate listings. I have a field for the price, which would never have decimal points, as prices are in whole dolloars only. I set my field in my db to both double and then to decimal.
If I enter a price without the dollar sign such as 6,000,000 the value 6 gets stored. If I enter 6000000 the value 6000000 gets stored.

Defining Decimal Numbers
I am having a problem defining decimal numbers such that MySQL will accept the syntax I give it.
I believe the correct syntax is dec(4,2) however my MySQL rejects this.
The attachment shows the ouptut from mt SQL command line.
It doesn't seem to like the ',' inside the '()'. Unless of course the error is elsewhere.
My knowledge of SQL is still very rudimentary.

Mysql Changing Value Of My Decimal
this is really weird. I tried it on both windows and linux with the same problem.

Basically, I have a float(10,2) field, and if I enter a big number with decimal of .01 the decimal gets rounded to .02
example:
222333.01 becomes 222333.02
My database is mysql 4.1.2
here is a dump of my test database:
CREATE TABLE `tblpayroll` (
`payenddate` date NOT NULL default &#55612;&#57200;-00-00',
`principal_id` int(100) unsigned NOT NULL default Ɔ',
`vessel_id` int(100) unsigned NOT NULL default Ɔ',
`vessel_allotment` float(8,2) NOT NULL default Ɔ.00',
`vessel_pesoval` float(10,2) NOT NULL default Ɔ.00',
`amt1dollar` float(8,2) NOT NULL default Ɔ.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tblpayroll`
--
INSERT INTO `tblpayroll` (`payenddate`, `principal_id`, `vessel_id`, `vessel_allotment`, `vessel_pesoval`, `amt1dollar`) VALUES (&#55612;&#57200;-00-00', 1, 1, 222333.01, 0.00, 0.00);
any thoughts??

Format Decimal As Currency
I can't believe Google hasn't hit the nail on the head. I must be asking the wrong question.

How do I query a decimal like 12345.4 as $12,345.40?

Decimal Not Defaulting To NULL
I have a 3rd grader caliber question that would have made a lot more sense to ask 9 months ago when I didn't have 9 months of full timeMySQL / php experience. Regardless, I've hit a snag and need a boost.

Anyway here goes:

Is there any reason that a table with these columns would not actually default to NULL.

PHP

`price_1m` decimal(8,2) default NULL,
  `price_3m` decimal(8,2) default NULL,
  `price_6m` decimal(8,2) default NULL,
  `price_12m` decimal(8,2) default NULL,

I'm using the following php code to insert data into the MySQL table.

PHP

$q = "INSERT INTO links ( price_1m, price_3m, price_6m, price_12m) VALUES (཮','','','')";
mysql_query($q) or die(mysql_error());