MYSQL :: Two Digit Numbers Not Formatting Properly

Storing Numbers In 3 Digit Format
I have a table in which one column stores 3 digit numbers. Problem is, I wanna store 35 as 035 & 3 as 003. How do I do this in MySQL?

If it is not possible by having the field as INTEGER, Is it possible to convert the current values to 3 digit format in the table by changing the field type to VARCHAR? If so how?

Formatting Phone Numbers
I am storing phone numbers as integers (1234567890) in my MySQL database. I was wondering how I could format the phone numbers as (999) 999-9999 when pulling them from the database in Query Browser (I'm actually using Coldfusion with MySQL and was hoping to get back the phone number already-formatted).

Getting The First Digit Of An Input
I have built a database where the user inputs a postcode (split over two parts) and gets infromation returned.

What I would like to do is write a select statement that only extracts the first digit of the input data for the value used in the query, ie

User inputs - 4BU
The query searches on the value 4.

i have put together the following but it doen't appear to work to well (the value in question is variable $pc2).

$query = "SELECT
  DISTINCT location.postalcode,
  location.postal,
  venue.VenueType,
  venue.Name
FROM
  location LEFT JOIN venue
    ON location.RegionCode = venue.RegionCode
WHERE
  location.postalcode = '$pc1' AND
  location.postal = ('$pc2'+1) AND
  venue.VenueType = '$pc3'";

12 Digit Order Number Anyone?
Would anyone know the best datatype for a 12 digit order number? It was just a varchar before but I need to perform arithmetic on it now.

Date Stored As 10 Digit Integer
I have a vendor supplied database that I am trying to query. The database stores a list of events that includes the date and time that each event takes place. The date and time, however, is stored in the database as a 10 digit integer (for example 1137015000 represents 4:30 pm January 11, 2006 Eastern Standard Time). My assumption is that the number is a count of seconds beginnning from a certain date some time in 1969 or 1970.

What I am trying to do is query all events that happen on or after the current date. However when I use curdate() the format of the date is obviously different. So my question is is there a way to convert that integer to a standard date format or is there a way to convert the result of curdate() to an integer so I can compare to two values?

5-digit US Zip Code: MEDIUMINT(5) ZEROFILL Vs. CHAR(5)? Which Is Appropriate?
I'll never perform mathematical calculations on the zip field, but I will be using it in distance calculation joins.

Using Keys Properly
I have a couple questions on how to properly create keys. Basically i know nothing about them, how they work, how it speeds things up, and most importantly, where to create them to help speed up my tables.

I do have multiple tables that are used and joined together, i just dont know where to make keys. I heard that if you do it improperly, it can slow down your database too?

UPDATE Will Not Add Properly
I have an online comic engine I'm working on, and I just noticed that, as opposed to adding one view (like it should) it will always add two. Can anyone help me out with this? I've already determined it's just this line of code, and that it's not being called twice.

$query = "UPDATE `".SQL_COMICS."` SET comic_views = (comic_views + 1) WHERE comic_id = '".$comic_id."' LIMIT 1";
mysql_query($query) or die(mysql_die_message());
echo($query);

Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly?

The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db.

So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation).

SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb
FROM project AS P, employee AS E
WHERE E.emp_id = P.supervisor
The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p

Getting Utf8 To Work Properly
I have made a few tables in a database, and I'm trying to get my Japanese text to display properly. I have the collation set to utf8_general_ci, and I even added the mysql_query("SET NAMES 'UTF8'"); code into the php page. But even with all of that in, it still won't display properly. The characters show up as ã‚»ã‚*ãƒã‚¯ã«ã‚“ã˜ã‚ƒãŸã„ã‘ã¤ï¼ and other junk like that.
Now this was working before until I added a second mySQL connection into the page, which inputs the second table. I'm not sure if you can make 2 connections on the same page, but I did and it pretty much messed everything up. I took the second table away, and things were still messed up.
My question is how can I get the Japanese text to display properly? It does display properly on the phpMyAdmin page, but I get garbage on the actual page.

How To Install Mydsl Properly
is there a step by step guide for installing mydsl properly and creating a databse?

Search Does Not Match Properly
i had created a search function on a web application.

the problem is, i try enter some of info to it, certain text can be found but certain text can be match although they are in database.

this my query:

$keywordu = strupper($keyword);
$keywordl = strlower($keyword);

$sql = "SELECT E.* FROM employes AS E, dvi_problem AS DV WHERE AS.empId = DV.empId AND (E.emp_name LIKE '%".$keyWordl."%' OR DV.reference LIKE '".$keywordi."''%".$keywordl."%' OR E.emp_name = '%".$keywordu."%' OR DV.reference LIKE '%".$keywordu."%')"

$query = mysql_query($sql, $db) or die('Fail to search.');

i test with direct copy the data from database, some string i copy can search it but some other string i copy direct it can work wo...

i try show out the variable $sql value, it show with not error, the keywordu n keywordl aslo show out the value when i print out the $sql variable.

Mysqld Is Not Running Properly
I have installed mysql server & client version 3.23.54a from Redhat installation 9.0. I could install the RPM 's with out any problem.

I can see 'mysqld' program . But when I run it shows a message like mysqld started [ok].When I checked 'ps aux' , I could not see any process running with mysqld. Also I could not run any programs like mysqladmin etc. All of them fail. does anybody know what is the mistake ? What should I do ? Also when I try to stop mysqld it fails.

Designing Databases Properly
After what seems like far too much agonizing, I think I've decided to build my own php "shopping cart" and hack my own interface to paypal IPN, because all the other solutions I've come across are massively oversized and overdeveloped for my needs.

But I could use some help in setting up the structure. I'm not sure of the best way to handle "options" with my various products.

First of course I set up a basic database with product fields like:
sku, price, name, etc. etc.

The tricky part is that some products will have special options, some dropdowns and some text fields, and I know I won't be able to predict all these in advance, like:
item color, accessory color, font, initial, initials ...

In addition, some products will call for extra product photos and others won't ...

And then I will need some way of storing all these extra options for a given customer's order when it comes through.

Extended-insert Not Working Properly
I am having a problem with using the --extended-insert option when using mysqldump. As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data.

According to documentation I have found, this substantially speeds up the restoration process. The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine).

However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas. When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way. I am using Windows XP, 1.8 Ghz, 512 MB of RAM.

The command I am issuing is as follows:

mysqldump --opt --single-transaction --u root --all-databases > "C: estbackup.sql"

I have also issued the following command, with the same result:

mysqldump --extended-insert --single-transaction --u root --all-databases > "C: estbackup.sql"

Properly Re-starting Replication Process
I've just tried to re-start a replication process and the slave's
tables came up marked as crashed. I'm doing a repair on one of them but
there are a lot of databases/tables so I can't check them all (or is
there a table entry somewhere that flags crashed tables?) Here is the
sequence I used: Code:

Storing Large Integers Properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit
numbers and many start with 0. The data type of the field I am storing
this info in is a bigint(16) unsigned.

It appears that isbns that start
with 0 are going in as 9 digit numbers, the 0 is being ignored or
stripped. I have experienced this before with integer data types
ignoring leading 0s. I'm wondering how to address this? Should I
change the field to a varchar or char data type?

Inserting Dates Properly Formatted
I have a dataset that has all it's date formatted as MM/DD/YYYY. I want to be able to insert this into a table that is using a field set as 'date'.
How would I go about properly formatting the MM/DD/YYYY to insert into the table?
Right now when I insert I end up with all the dates storing wrong. IE: 9/6/2005 stores as 2009-06-20.

Mysqlcheck: Clients Are Using Or Haven't Closed The Table Properly
I'm using the command mysqlcheck --all-databases --auto-repair --silent
and I see these warnings (listed below) on a daily occurrence. Is mysqlcheck
causing these warnings? I can't see how a table can close inappropriately.
How can I prevent this.

Hoping to here from MySQL users who have came across this. I'm using MySQL
4.1.11 on RHEL. Code:

Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages.

Page 1: Input 3 peices of data (Title, Date and Text)

Page 2: Display all news that has been inputted

I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on.

Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:

Formatting
I have a string of airport codes formatted like this

KWIMAACCJTRZ

Every 3 letters is a complete airport code... i would like to know how I might format this so that I can query the database with one airport code at a time and return the result... the strings vary in length from one code to over 200 airport codes so I cant put them in a field each... so how might I do this... also i need it so that I can creatte a dropdown menu with the results (just formatted in a way that I can do this via php as i already know how to do the dropddown menu)... This is really complicated for me and I cant find any help on the internet for this so you are my last hope.

Set Formatting For Prices
I'm not entirely sure how to best achieve what I want to do here.

I have a mySQL backend on a PHP driven site. I want my client to be able to set the price of a product which is stored in said database. I'm wondering what the best way to format my sql table (or php code) to ensure that a price is always in the format of 0.00 (just 2 decimal places).

Date Formatting:
If I have pulled the date from my MySQL datebase and $mydate displays as 2007-04-01 How do I format it with PHP to something more readable/

Column Formatting
Being new to mysql I have some queries about formatting a date column

1. Can I use dd-mm-yy, my website is Australian

2. Can I leave the date field in one row empty if the date is unknown.

3. If I only know the year an event took place, can I enter just the
year.

4. Would it be best if I used varchar for the column

Formatting Localtime()
this is the normal format of the localtime()
yyy-ddd-mm hh:nn:ss
*n=minutes

that is the format i need
dd/mm/yyyy hh:nn:ss

i need to format into the following format and use it inside of query
select ... from... where time < newformat( localtime() )

how can i achieve the format i need?

Formatting Dates
I've got a script that is designed to print entries in a table. This script uses a query:

$query="SELECT * FROM entries
ORDER BY time";
$result=mysql_query($query);entries: table
time: datetime field in table

And then a row count to print every entry:
$num=mysql_numrows($result);
$i=$num - 1;
while ($i > -1) {

$title=mysql_result($result,$i,"title");
$entry=mysql_result($result,$i,"entry");

echo "$title<br>$entry";

$i=$i-1;
}

Now, I would like to have the datetime field associated with every row printed as well, but I don't want it just printed as is, I can achieve that with:
$time=mysql_result($result,$i,"time");

echo "$title<br>$time<br>$entry";
But what I get looks something like:
2006-15-06 20:56:34

I'd like to get that more readable, and I understand the different symbols for each different way to display the date, but I don't know how to make this apply to the printing of the datetime field from each entry.

Formatting Text
I have a mysql database that contains guitar tabs and song lyrics (kind of
like my own personal taborama.com),anyway i have everything stored in the
database and use php to display all the records on a web page but my problem
is i can't keep any of the formatting for the tabs or lyrics, i enter
everything into the database via mysqlcc. When i enter the tabs in mysqlcc
it all looks nice and formatted but when i print it in php it all goes on
one line, is there anyway of preserving the format of the text in the mysql
database or should this question be aimed at a php newsgroup?

Formatting Text
I have selected a row from a table where the column names are:

id, money, com1, com2, com3, com4, com5, com6

I want to do a loop where I run a function on each one of the com1, com2 etc. values one at a time.

Is there any way I can format the text to be say

for ($i=1;$i<=20;$i++)
{
$answer = com$i +whatever
}

So I can run the same loop for each of the com values.

Count Formatting In 4.1
I am having a really odd problem, I just upgraded from MySQL 4.0 to MySQL 4.1 and suddenly all my ASP code that is performing math against Counts is failing with a type mismatch, I have to assume for some reason that the data being returned by the count is formatted as text instead of integer now.

I could use some suggestions, either a way to force the output to be numeric, or something. Was this an intended change? It may be something completely unrelated but all my troubleshooting points to that count function, any pages where I am performing math on counts is now failing.

Formatting The Time
Is there a function to convert a time like '1:30pm' to '13:30:00'?
I am taking a time input from a user using a HTML <select> tag. I want to take this change it two the TIME datatype.

Date Formatting
The problem that i am having is that i have a field called logdate which is within table opencall. This logdate field is set as a string (dd/mm/yy hh:mm:ss).
I am wanting to extract certain dates of information, for example the previous months information, but at the moment the script i have is producing false data. I have realised because the logdate is a string and the current date variable is a date that this may be the problem so i have made the logdate into a unix timestamp as you can see in the script below but i am still getting problems. It is not bringing back any syntax errors, and it is not rpoducing any information which i am sure it should be.

Select suppgroup, UNIX_TIMESTAMP(concat(substring(logdate,7,4),"-",substring(logdate,4,2),"-",substring(logdate,1,2),substring(logdate,11,9))), count(*) Number
From opencall
where logdate between UNIX_TIMESTAMP((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) and UNIX_TIMESTAMP((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-0)*100)+1)
and probcode not like 'CHNG%'
Group by suppgroup ; .

Formatting Text
I have a mysql database that contains guitar tabs and song lyrics (kind of
like my own personal taborama.com),anyway i have everything stored in the
database and use php to display all the records on a web page but my problem
is i can't keep any of the formatting for the tabs or lyrics.

i enter everything into the database via mysqlcc. When i enter the tabs in mysqlcc
it all looks nice and formatted but when i print it in php it all goes on
one line, is there anyway of preserving the format of the text in the mysql
database or should this question be aimed at a php newsgroup?

Database Formatting
I've been able to create my stat database, and all is well. What I want to do now is color code the table, highlighting the hi values, by coloring that cell. I've created css styles for these.
How do I go about applying them. What do I need to do to make this happen?

Date Formatting
i've got such values in the table: 1157012562.
how to extract rows say for current month and current year?

PS i always dealt with ISO date format before and really stuck with NULL results i'm heaving using UNIX_TIMESTAMP() and YEAR() functions...

Formatting Results
I want my results to be displayed in a table with 4 columns accross then repeat down the page. I've figured out how repeat them either across continually or down, but not 4 across then down. What code do I need to format the results the way I'm wanting?

Date Formatting
I am having to read a field that is a varchar(20) that stores dates in the following format:

01-APR-2005 06:48:19

Is there anyway to select this field and have it output in the this format:

2005-04-01

I tried this sql query, but it only returned nulls:

SELECT str_to_date(
dateFieldToConvert, '%d.%m.%Y %H.%i')
FROM myTable

Row Type To Keep Formatting
I am writing an app for our school newspaper to be posted online. I want to store everything in my MySQL db, but also want to keep the formatting when we put the articles in the DB. What should the row type be for this in order to keep the formatting (paragraphs mainly). Text puts it all together.Basically articles will be copied out of word into a <textarea> field and I would like to eliminate the need to build in a WYSIWYG editor if possible

Formatting Text
I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.
The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?

Formatting Date
i've been trying to convert a user entered date string (dd-mm-yyyy) before entering it in the database.
i need to convert it to a mysql friendly format (yyyy-mm-dd).
i tried date_format and STR_TO_DATE, but they mess up the date.
i figure these cannot be used to that, therefore my question to this honorable audience is: "is there a mysql function that allows to convert the date format upon insert or update ?"

(of course i could do it in php, but i like to keep this in mysql)

Formatting A Phone/fax Layout
Is it possible to do a mysql SELECT using If then?

i.e.
In a client PHONE table, I have

ID
Name
Phone Number
Fax Number

I want to customize the output of the phone and fax number with a format that looks like this..

(xxx)-xxx xxxx

I know I can use this format for the Phone Number and Fax Number ....

Calculations / Formatting With MySql Or PHP ?
When I first started programming, I kept hearing that when doing queries and needing to do calulcations and formatting the data, you should always let MySQL do the work instead of PHP. Is this true and why or why not?

MySQL Style/formatting
I'm fairly to new to MySQL and have been reading a lot of posts here recently. I am totally blown away by how much difference good formatting makes -- I guess I should have realized this, coming from C/C++ and Lisp, where spacing & indentation make a whole WORLD of difference... But somehow it seems most beginners (myself included) write hard-to-read, hard-to-debug queries on one or two lines with no clear capitalization conventions.

And then I see query examples by, say, r937 or longneck. And they both seem to follow almost exactly the same indentation and line break principles. And they're absolutely marvels of legibility and clarity.

What are you guys using to author your queries? I have tried to imitate in a couple code editors, and it really doesn't want to help me line up everything the way you have it lined up. Certainly requires a lot of meticulousness, which I'm not ready to commit to doing manually.

Also, are you guys using another language to get at MySQL, e.g., PHP? Do you usually write such multi-line queries in PHP? If so, what conventions do you use for good formatting and readability?

Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now:

ProductLine=`mysql -u www -s support <<EOF
SELECT ProductLine FROM Documents WHERE ID = '$ID';
EOF`

DocType=`mysql -u www -s support <<EOF
SELECT DocType FROM Documents WHERE ID = '$ID';
EOF`

DocName=`mysql -u www -s support <<EOF
SELECT DocName FROM Documents WHERE ID = '$ID';
EOF`

FileName=`mysql -u www -s support <<EOF
SELECT FileName FROM Documents WHERE ID = '$ID';
EOF`

Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this:

SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID';

I don't know how to take that output and put it into my shell variables.

Text Field Formatting
I am on OS X, Apache, using MYSQL and PHP developing a database for my community theatre that includes a table for Board meeting minutes with the minutes being stored in a Text Field. I donâ€™t need a whole lot of fancy formatting, but I would like to include a blank line between paragraphs. This does not seem to be a problem when the text is displayed within a text field within a form. I can include any number of lines between paragraphs and MySQL stores them and redisplays within the form.

The problem occurs when I bind the field to a page to display the minutes without a form field (and scroll bars) for printing. All the lines will run together with no breaks unless I use the php nl2br formatting command. Even then, only one <br > is inserted, regardless of how many blank lines are in the text field. Any suggestion on how I can maintain a blank line between paragraphs?

I know...the best way is to store the text as html and then display it via echo, but this is being designed for people who do not know html and will be typing up the minutes on their home computer.

Mysql Select Formatting
I am a total newbie to mysql and I have been trying to educate myself.

I created a database and a table within it called "simple", see below.

I loaded data in to "simple" from a tab separated file.

The table simple has the following types -

mysql> describe simple;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| joined | date        | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | varchar(20) | YES  |     | NULL    |       |
| sex    | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

If I enter one record in my .txt (tab separated file) and then try and retrieve all records from that table I can see all the data with no problem, see directly below

mysql> select * from simple;
+------------+------+------+------+
| joined     | name | age  | sex  |
+------------+------+------+------+
| 2006-01-01 | bob  | 10   | male |
+------------+------+------+------+
1 row in set (0.00 sec)

If I enter more than one record and then try to retrieve all the data, the formatting goes wrong, where I can't see the whole of the first date and the end of the last field in the first record.

mysql> select * from simple;
+------------+------+------+-------+
| joined     | name | age  | sex   |
+------------+------+------+-------+
| 006-01-01 | bob  | 10   | male
| 2006-01-02 | tim  | 12   | male  |
+------------+------+------+-------+

If I add more records, the formatting is worse and the joined column makes no sense all the way down.