MySQL Sort by Array
Hi Guys, I'm trying to sort by a particular order: SELECT * FROM tablename WHERE id='5' OR id='9' OR id='25' OR id='34' ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? Thanks for your help. Keith
Time Zone Support
Hi, We need to add support for time zone to our existing PHP Board Member Extranet applications. Could those of you with experience supporting a time zone field in your MySQL data tables offer some advice? Would you use a single field? Would you use just an offset value? How would you represent the information on presentation data? How would you list the time zone options in your form dropdowns? Would you use a separate time zone table to support the offset values, an abbreviated time zone, a full time zone name and a daylight savings time value? We greatly appreciate any advice you can offer. Thank you for taking the time to share your knowledge... Keith
Zip Codes with Leading Zeros
Hi, RE: Zip Codes with Leading Zeros We need to export a MySQL table with a zip code field to Excel. We currently use PhpMyAdmin to export to CSV or Excel files. We have had problems with zip codes with leading zeros. The leading zeros are removed so that we are left with incomplete codes. Can you help us learn the correct procedure for dealing with, exporting and importing zip codes? Thank you very much for all your help. Keith
Re: Zip Codes with Leading Zeros
Hi Johnny, Yeah. Sadly I missed the fact that the zip codes were hacked in our original Works to MySQL conversion until long after I had imported the data into our current MySQL table. Now we have to fix the numbers in our MySQL table and guarantee that we can export them out to Excel for the printer. I'm working on either finding or producing a script that will repair the 12,000 rows of mixed zip (5 digit) and zip+4 data. I appreciate your taking the time to help us Johnny. Keith - Original Message - From: Johnny Withers To: Keith Spiller Sent: Thursday, August 21, 2008 8:15 PM Subject: Re: Zip Codes with Leading Zeros I think the problem is with Excel. When it opens the CSV, it trys to help you and automatically sets the field type to Number. You may be able to solve your problem by selecting that column, selecting format fields and changing their format to General. -johnny On 8/21/08, Keith Spiller [EMAIL PROTECTED] wrote: Hi, RE: Zip Codes with Leading Zeros We need to export a MySQL table with a zip code field to Excel. We currently use PhpMyAdmin to export to CSV or Excel files. We have had problems with zip codes with leading zeros. The leading zeros are removed so that we are left with incomplete codes. Can you help us learn the correct procedure for dealing with, exporting and importing zip codes? Thank you very much for all your help. Keith -- - Johnny Withers 601.209.4985 [EMAIL PROTECTED]
Re: Zip Codes with Leading Zeros
Thanks for all your suggestions Tom. The mixed 5 digit zip code and 10 digit zip+4 code data set are in a varchar(20) field. I don't recall if the data was identical in both the CSV and Excel files, but I do remember I had the same problem. It's been many months since I imported the original data into MySQL and I still need to repair the damage zip codes before we attempt another export to Excel. I believe your final suggestion is my necessary route. Thanks again for your help Tom. Keith - Original Message - From: Kralidis,Tom [Burlington] [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, August 21, 2008 8:28 PM Subject: RE: Zip Codes with Leading Zeros Keith (I'm not very familiar with phpMyAdmin): what is the underlying datatype of your zip code field? On the command line, if I use: mysql select * into outfile '/tmp/file.txt' fields terminated by ',' optionally enclosed by '' lines terminated by '\n' from tmp; (note that the default output, if not specified is tab-separated) ...I get csv style output in the output file for columns of type varchar(100), for example, with records containing leading zeros. Are the outputs identical in CSV and Excel (not sure whether Excel is not showing the leading zeroes as part of the column formatting defaults). Of course, you could write a post-processing script that adds the leading zeros to records not long enough. ..Tom -Original Message- From: Keith Spiller [mailto:[EMAIL PROTECTED] Sent: Thu 21-Aug-08 22:11 To: mysql@lists.mysql.com Subject: Zip Codes with Leading Zeros Hi, RE: Zip Codes with Leading Zeros We need to export a MySQL table with a zip code field to Excel. We currently use PhpMyAdmin to export to CSV or Excel files. We have had problems with zip codes with leading zeros. The leading zeros are removed so that we are left with incomplete codes. Can you help us learn the correct procedure for dealing with, exporting and importing zip codes? Thank you very much for all your help. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Application Builder
Hi, I've been building my PHP/MySQL applications by hand for years. Now I am wondering after seeing a Flex demo if some sort of instant or super easy mysql application builder existings. I want something for rapid development with a graphical user interface. Maybe drag and drop table query creation etc. Something to dramatically reduce the amount of time it takes to build a simple web application that reads from MySQL tables. Any recommendations? Thank you for your help... Keith
Re: Sort Select by List
Thanks Dan, It is a very small table. I really appreciate you help. Keith - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Mark Leith [EMAIL PROTECTED] Cc: Pintér Tibor [EMAIL PROTECTED]; [MySQL] mysql@lists.mysql.com Sent: Saturday, December 16, 2006 9:48 PM Subject: Re: Sort Select by List In the last episode (Dec 16), Dan Nelson said: In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort Select by List
Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith
Re: Sort Select by List
Hi Hal, I appreciate your help, but the order can not be alphabetical. It must be explicitly Exec, VP, Dir... Keith - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED] Sent: Saturday, December 16, 2006 2:28 PM Subject: Re: Sort Select by List order by office On Dec 16, 2006, at 3:25 PM, Keith Spiller wrote: Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Manually Inserted Data
Hi, I'm trying to manually insert data into a mysql query so that the data will be sorted by the query but not inserted into the table. Here's what I have so far: ( SELECT ID, Title, Label, Location, Start, End, Time, Description, Organization, Department, Contact, Phone, Email, Global, Board, Committee, Status, TBD_Time , TO_DAYS(End) - TO_DAYS(Start) + 1 AS Days FROM site_calendar WHERE Global='1' ) UNION ( SELECT '9', 'No events exist for this month...', '', '', '', '-00-00', '00:00:00', '', '', '', '', '', '', '1', '', '', '', '', as z ) ORDER BY z, Start ASC, Status DESC, Time ASC I just don't know what I am doing wrong her, or how to accomplish this task. Please help. Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Manually Inserted Data
Hi Rolando, Thanks for your help. I have reduced the query to the bare essensials to try to test the concept, but it still fails... ( SELECT ID, Start, End FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01', '-00-00' as z ) ORDER BY z, Start ASC, Status DESC, Time ASC a Does anyone see my mistake? Keith - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED] Cc: MySQL mysql@lists.mysql.com Sent: Wednesday, November 01, 2006 2:11 PM Subject: Re: Manually Inserted Data The first UNION part has 19 columns the other UNION part has 18 columns - Original Message - From: Keith Spiller [EMAIL PROTECTED] To: [MySQL] mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 3:31:24 PM GMT-0500 US/Eastern Subject: Manually Inserted Data Hi, I'm trying to manually insert data into a mysql query so that the data will be sorted by the query but not inserted into the table. Here's what I have so far: ( SELECT ID, Title, Label, Location, Start, End, Time, Description, Organization, Department, Contact, Phone, Email, Global, Board, Committee, Status, TBD_Time , TO_DAYS(End) - TO_DAYS(Start) + 1 AS Days FROM site_calendar WHERE Global='1' ) UNION ( SELECT '9', 'No events exist for this month...', '', '', '', '-00-00', '00:00:00', '', '', '', '', '', '', '1', '', '', '', '', as z ) ORDER BY z, Start ASC, Status DESC, Time ASC I just don't know what I am doing wrong her, or how to accomplish this task. Please help. Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ftp of Mysql Files
Hello, I already use phpmyadmin to dump the mysql data into sql files, but I would like to back up the original database files as well. I found the actual database files on my server in var/lib/mysql and want to download copies, but I do not know wether I should use binary or ascii format. What would be best? Will downloading in the wrong format corrupt the database files? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Now() and Timestamp
Hello,
I am trying to figure out how to use the NOW() function with a MySQL Insert
command on a Timestamp field.
Here is my mysql command:
INSERT INTO $tablename
(sender, recipient, whenread, whensent, subject,
messagetext, folder, priority, condition)
VALUES
('$directorid', '$SendTo', '00', '$whensent', '$SetSubject',
'$MessageText', 'Inbox', '$SetPriority', 'TO')
I want to use the NOW() function to set the current time in place of the
$whensent variable.
Can anyone help me?
Keith
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlimport
Hello, mysqlimport --replace --fields-terminated-by=\| --verbose --user=user --password=mysql database tables.sql I'm trying to use the mysqlimport command. I have multiple tables listed in my table.sql file. But the command requires that the filesname be the same as the table you try to import. Is there anyway to import all of the tables at once? Keith - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Proper Case Conversion
Hello, I'm reading data from a mysql table that is entirely in uppercase letters. I'd very much like to convert them to lowercase. After searching a while, I found that there is are php commands to convert text strings between upper and lower case, but I was unable to find anything that can convert to proper case. Does any one know of a way to do this, or a script that will accomplish it? Or, is there a mysql command that can handle the matter for me? Thanks, Keith
Opps: Proper Case NOT lower case
-Opps: I meant to say I would like to convert to Proper Case, rather than lower case or UPPER CASE. I'm reading data from a mysql table that is entirely in uppercase letters. I'd very much like to convert them to Proper Case. After searching a while, I found that there is are php commands to convert text strings between upper and lower case, but I was unable to find anything that can convert to proper case. Does any one know of a way to do this, or a script that will accomplish it? Or, is there a mysql command that can handle the matter for me? Thanks, Keith
LIMIT with mysql_num_rows
Hello, I want to know if there is some way to return the number of rows in a query, the complete query, while using a LIMIT $start, $end command. Unfortunately, so far, using $result = mysql_query($SELECT); $qct = mysql_num_rows($result); Results in $qct being equal to $end, which I already knew. Is it possible to get this figure without doing a separate query? Thanks for any help... Keith Spiller [EMAIL PROTECTED]
LIMIT with mysql_num_rows
Hello, I want to know if there is some way to return the number of rows in a query, the complete query, while using a LIMIT $start, $end command. Unfortunately, so far, using $result = mysql_query($SELECT); $qct = mysql_num_rows($result); Results in $qct being equal to $end, which I already knew. Is it possible to get this figure without doing a separate query? Thanks for any help... Keith Spiller [EMAIL PROTECTED]
array awry
This associative array embedded within a function and declared as a global at the
start of the function, is meant to be a multidimensional array, but with every loop of
the while ($myrow = mysql_fetch_row($result)) statement, it's previous values are
replaced by the new ones. Any ideas on how I can fix this?
If ($Selection == "3")
{
$tabledata[catid]= $myrow[0];
$tabledata[category] = "$myrow[1]";
$tabledata[under]= $myrow[2];
$tabledata[corder] = $myrow[3];
$tabledata[active] = $myrow[4];
}
Keith
aka Larentium
Join causing Error?
Can anyone tell me why this:
Line 282mysql_select_db("centraldb",$db);
Line 283$qorder++;
Line 284$result = mysql_query("SELECT q.questid, q.question, q.answer, q.qorder,
q.depart, q.catid,
q.active, q.global, q.adate, q.author, q.authoremail, q.askemail,
c.catid, c.category, c.under,
c.corder, c.active FROM central_groupfaqq q, central_groupfaqcat c
WHERE q.active = '1' AND
q.global = '1' AND c.active = '1' ORDER BY c.under, c.order,
q.qorder",$db);
Line 285while ($myrow = mysql_fetch_row($result))
Would cause this error:
Warning: Supplied argument is not a valid MySQL result resource in faqbody.php3 on
line 285
When changing the same SELECT statement to:
Line 284$result = mysql_query("SELECT * FROM central_groupfaqq WHERE active =
'1' ORDER BY
qorder",$db);
Works perfectly?
Keith Spiller
a.k.a. Larentium
