What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here?
I've got a table in mysql with all 5 fields defined
as tinytext
Problem is when I export this to to a text file for notepad
each field is padded out by several blank spaces,
and i think my eamil program doesnt like this type of structue :
field1 , field2 , field3 , field4
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Non-blank Fields
Is there a way to select all non-blank fields when doing a select statement in SQL? For example, I want to run the query "Select * from <tablename> where username = <username>" The result set contains many columns that are completely blank, as the database I'm working with contains over 100 fields. Is there a way to tell it not to display those blank columns in the result set?
Blank Fields
In the table for my fields even if I pick the option "do not alow null". I noticed when I save the tables with out entering anything in the fields it let me save with out promting me and saying "null not allowed" I belive in some other databases if you have picked "not null" for a field and try to save it with out entering anything it will not let you save.
Ignoring Blank Fields
Im having a problem where im allowing the user to identify a row by 1 of 2 unique fields. The problem i have is that my current query states to either take a OR b... so my query looks like this. a = $a OR b = $b Now these unique fields can also contain a result of null. Which means that im returned with more than 1 result when only 1 of the unique fields is entered. Is there a way of escaping results that contain null in either of the fields?
Spaces In Field Name
I'm trying to design a web app that will handle comps for real estate and am trying to make it customizable so that the user can make custom fields to go with the comps. My question deals with spaces in the field name that a user types in, which is directly ADDed to the table as a new field. Right now I have backticks in the ADD query so that if there is a space in the field name it won't complain. Would it be better to leave it with the backticks or parse the field name to replace spaces with an underscore, or some other character? Or does it really make any difference? I have heard some people say that spaces in the field name can cause havoc when databases get larger, but I'm not sure why.
Table And Field Name With Spaces
I just converted an Acces 2000 database to mysql by exporting all of the tables from Access to mysql via ODBC and myODBC connection. Everything went well. Now I am having trouble using select queries in mysql with table and field names that have spaces. I have read that field and table names with spaces should never be used. Do I have to remove all the table and field spaces? I have like 50 tables with spaces and columns in them. Can I surround these name with brackets [] or something else like in Access VBA.
Blank Date Field
How do you determine if a Date field is blank. In my Sql statement I only want records who's date field is blank (i.e. checking for only open invoices where Date_Paid = '' ...still open ...it doesn't work when I use that syntax)
Enter Null In A Blank Field
How would I enter a Null in a blank field from a query. I have several fields in a table that may or maynot have information in them. I need to export all those fields, I want to have all the blank fields filled with Null so that when I export the table info I can change the Null to another value.
Does PK Field Type Have To Be Same As FK Filed Type?
I have a table with a primary key and, in another table, a foreign key to that 1st table needs to be set. should both fields have the same type i.e. where the PK is a 'int(11)' should the FK field be 'int' as well. And if so does it neeed to be limited to 11 as well?
Bit-fields Or Set-type
I'm trying to manage user privileges. My first thought was to use bit-fields. Something like: // 1 = READ // 2 = WRITE // 4 = CREATE // 8 = DELETE // Get users whit read,write,create and delete rights: SELECT userID FROM users WHERE privs = 15; And using bit operators to set/get different privileges... But then I remebered the SET-type (I allways forget about it). Which is the better to use? Personally I think the bit-field is pretty easy to manage, but which has the best performance? My guess is that the SET-type uses a bitfield anyway.... Anyone that knows what's happening behind the scene?
Array Type Fields
I'm quite new to MySQL and quite impressed by its feature set. I've also been looking at Interbase and it has a feature that allows a multidimensional array to be stored in a single field. Does MySQL also have this feature? I was thinking about using MySQLs GIS data types but it only supports a two dimensional point, where as I need a three dimensional point to be stored.
How To Remove Multiple Spaces From A "data" Field?
I need to remove multiple spaces from random pieces of text in a data field. Can someone tell me how to do this? I've tried using this but it doesn't do anything: update posts set body = replace(body,' href',' href'); *Note the double spaces before the first href and the single space before the second href.
What Field Type
I'm using a credit system in my database what is the best field type to use for numbers which will have values added and deducted.
Type Field
At this moment I have a field with the type DATE using by default 0000-00-00. I want modify to correspond to the next example: 2006-06-06 15:30 PM. Can I register in the same field or I want to separate by other fields?
Field Type
what the field type should be set to so that if a user keys .5 into a field it will get saved into the database as .5 and not 0, that's what is happening now. I had it set to integer, and all of those that where keyed as a decimal are showing 0 in the database.
Field Type
i create a table: mysql>create table a(a float); mysql>insert into a values('39923.56'); mysql> select * from a; +---------+ | a | +---------+ | 39923.6 | +---------+ 1 row in set (0.00 sec) ////////////////////////////// i do not want the round result! i want get the values like *.?? i do not want use the DECIMAL(5,2) because it can not caculated by the cpu. one way maybe i can use bigint then /100 get the float num! any other ways?
Field Type
I want to add times into a field in the form mm:ss.d such as 03:33.5 but when I use the time type it removes the decimal but I really need it to keep it in. At the moment I am using a char(10) type and converting it in my scripts. Is there a way I can add it in using the correct type? I couldn't see anything in the documentation on the time type except that you could enter it in the form I want but it will convert it.
Field Type
You have DATE, DATETIME and TIMESTAMP. I want to be able to retrieve records by saying, "Select all records from 4 days ago." I dont care so much for the time, but the date is very important.
Field Type
I'm trying to set up a database with a unique name as the primary key but when i was using varchar i was running into problems as it couldn't store enough characters (need approx 70 chars maybe more). I've tried "longtext" and "blob" but while these allow enough characters they wont let me set them to primary key!
Which Field Type Question...
I was wondering which column type to use to contain a list of categories (1 cat per row). If the data is probably not going to be longer than about 10 characters for each item would varchar(10) be sufficient or should I include a larger number? How much space do you normally give yourself? Or would char be better?
Field Type Question
If I were to store a monetary amount into a database using MySQL, what field woulld I use? The num(length) field? Thanks!
Changing Field Type?
If I have a field that is all numerical, but is set up as varchar, will it cause any problems in the already-entered data if I alter it to make that field an int field?
How Sum A Field Type 'time' ?
I got a field type 'time'. There, i got values like: 01:30, 02:30. What is the problem ? Whem i run the query 'select time(sum(field)) from xx'. I got the right value. But if i got values with minutes like 01:39 or 01:45. The Mysql dont SUM the minutes.
TIME Field Type
Within PHP I have the time data of 01:32 or 1:32 - this refers to a mm:ss / m:ss format. My MySQL Field type is a TIME field - I require it to be a time format so I can perform functions upon the row. The time format is 00:00:00 (hh:mm:ss) - is there any way of having the field correspond with my format, or is there a way of converting it upon entry. If not it means I'll have to use string functions in PHP to convert the data into the neccessary format - which frankly is a pain the ***. Then again, I'm confident there's a solution available in MySQL, and I'm uber-confident that you guys will be the people to ask too.
Year Field Type
My question is, how do I prevent default data being entered into this field when I don't want to enter a year for the given row?
TEXT Field Type
Is there a way to limit the field type TEXT to only store up to 1000 chars? or is it something that you can limit in your script when storing data?
What Data Type For Currency Field?
I am on MySQL 4.1.20, and need to create a table that will have fields containing US currency amounts, up to $999,999,999.99. My boss wants a webpage displaying the table, and he wants the fields to be formatted to have a dollar sign, commas, and decimals. Am I better off making the fields varchar so the $ and , can be added -- or should I make the MySQL fields as float or decimal and then format the display on the webpage with (PHP, Javascript, ?)?
Default With Field Type Of 'text'
When i try to put a default value on my field in phpMyAdmin, it does not work. The field was text. When i change it to VARCHAR it accepts the default value.
Date/time Field Type
I want a field to automatically record the date/time each time a record is written to the db. Do I want that field to be of type DATE, DATETIME, TIMESTAMP…? My book is less than clear.
Field Type To Store Text With Commas
I need to change my field type to allow for commas in the field name. So I tried to change my text from "varchar" to "text". However, I am getting this error: #1170 - BLOB/TEXT column 'title' used in key specification without a key length Can anybody tell me if text is the right type to use? If so why isn't my database allowing me to make this change?
Most Efficient Date Field Type For Selects?
I have searched this forum seeking the answer to this question, to no avail. I am making an events diary that will have a lot of entries. Entries will be entered in all kinds of order, including multiple events spanning a year or so. Even multiple events of the same type will be individual entries. (e.g. weekly chess club meeting every weds at 7pm will be 52 entries) My question is : Which is the best format to store dates? Native mysql date type Integer timestamp A.n.other? I suppose a worst case scenario is imagine I want to generate a calendar for July (31 days) and I want to show how many events there are for each day. 1st July (13 events) 2nd July (22 events) and so on ... it is possible that any given day could have up to 100 entries. What in your opinion is the best column type to use and ss there any kind of Indexing I can use to speed up select by dates? Or any other tip or trick you'd be prepared to share with me?
Select * From Table Where A Field Is Of The Time Type
I have a mysql database which has been in use for a while. I recently installing it onto a new windows xp pro machine. The prevous computers have been both xp pro and 2000 server. Since this new installation the ASP application I have been using along with mysqladmin both fail to list any table that contains a time field. This includes creating the field and inserting to it as a new record. The code is as simple as CODESELECT * FROM
Converting Field Type In Active Database
When I designed my db in several tables I used unsigned integer fields to hold an integer that each bit was a 'flag'. Now I realize that the set field type does just what I was trying to accomplish. Is it safe to convert the field(s) from uint to set and keep their current values? Obviously all external programming (queries) will have to be adjusted, but it seems to make more sense to use the set type.
Problem Updating Time Type Field
Here's a query I tried and doesn't work for me. UPDATE activities SET ljTime=19:00:00 WHERE actID=05052003 I have also tried this: UPDATE activities SET ljTime=07:00:00 [PM] WHERE actID=05052003 But I still receive the error: [txoov] ERROR 1064: You have an error in your SQL syntax near ':00:00 WHERE actID=05052003' at line 1.
Sort By 2 Fields Unless One Field = 0
Quick recap - I know very little about databases. I'm trying to produce a query for a MySQL table. The table has 3 columns of interest: itemid - a unique integer representing this item lastcommentid - a unique integer which is the unique id of a row in another table itemname - free text name I want to select all the items in this table, ordered by the following logic: Quote: - If the lastcommentid is not integer 0, order the results ascending by lastcommentid (lowest lastcommentid first) - However, if the lastcommentid is integer 0, order the results ascending by itemid - Regardless of the above 2 points, only return items with an itemid higher than N. When done correctly this should return records ordered by their last activity, because either: A comment was made on the item in which case its lastcommentid will be greater than other items' lastcommentid, or, No comment was made on the item but the itemid field is greater because the item was added after other items.
Adding A Field Between Fields
If i had the following list of fields in my database: MemberID Surname DatofBirth How would I add FirstName between Surname and DateofBirth?
Multible Fields In A Field?
I am making a gaming site that is PHP/MySQL drivin. I put all the codes, games, basically any informaton in the database, and use PHP to take it out. Well, I've been workking on this little project for about a week now, and it just dawned on me. If I can only get one cheat code in per field, how am I going to add multiple cheats. I use one row for easch game title that I am going to use for my site, so, that means that if I wanted cheat codes, I would have one colomn for each cheat. But I can't do that, because I want to type in one code for the "code page" (where the cheats are viewable after the user clicks on the the game title), so that all the codes show up. Code:
How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
Update One Field With More Fields From Another Table
I have a select count() that seams be ignoring one clause. Data: Table: ID CA-Char CB-Int 1 dsfsd 6 2 dsfsd 0 3 dsfsd 1 4 sdrtt 1 SQL is: "SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0" Returns 3 - Should be 2
Display All Fields By Calling One Field
I created a testing db with three fields: id, title, text, where id is auto increment. I have this on my .php page: $dbh=mysql_connect ("localhost", "username", "password"); mysql_select_db ("testing_db"); $result = mysql_query("SELECT id FROM demoTable"); while ( $row = mysql_fetch_array($result) ) { echo("<P>" . $row["id"] . "</P>"); } this only displays all the id of each row of data. the way i want it to work is to call the id number (eg. page.php?id=1) and then display both the title and text defined for that id. i reckon this is a php syntax problem...would i hv more luck posting in the mysql forum?
Adding A Date Field, Need To Populate The Fields
I have a table and until now there is was date entry for the fields. I have added a timestamp (column "time") and from now on, every time I insert a new entry, the date/time will be recorded. The thing is, I want a date/time to exist for the old fields. I have about 250 fields and I want each field to be assigned a timestamp based on the fields alphabetical order (ordered by "title"). So, field with title "aaaaa title" will have a timestamp of today. A field with a title "zzzz title" will have a timestamp of 250 days ago. It doesn't have to be exactly like this. My main goal is to assign dates to my old fields. Anyone know how to do this?
Combining Date And Time Fields Into A Datetime Field
I have a table with separate Date and Time fields. Is there a way to either: 1. Convert the date and time fields into a combined datetime field? or 2. Create a datetime value in the select statement so I can select all records before a given date and time?
Avoid Adding More Than Once
im trying to do what i think is a fairly easy query but im having some problems. basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.
|
TRACKER
