Re: Help with PHP to MySQL db connection
This is really just a PHP question, I think.
Are MySQL and Apache running on the same machine?
What version of MySQL are you running?
Typically, you need a PHP mysql_connect() call including the MySQL
host, username, and password. You only list the host above.
With a default installation of MySQL, which gives you a root user with
no password, you could connect like:
$db = mysql_connect( "localhost", "root", "" );
but hopefully you've already given your root user a password, so put
whatever password that was in there.
Instead of creating the "[EMAIL PROTECTED]" user (I think that PHP will
often run as "nobody", perhaps Apache is set up that way also), create
a user with a username/password and privileges that are sufficient for
what you want to do. Then connect using that information.
Wes
On Wed, 29 Sep 2004 12:43:57 -0500, Andrew Lietzow
<[EMAIL PROTECTED]> wrote:
> Hola, MySQL wizards.
>
> I'm using LAMP on Fedora Core 2.
> Some kind soul produced a tutorial on the net and the first script,
> , I am trying to use yields an error in
> the browser of: < Couldn't connect to MySQL > Here is the script.
> ---
> Birthdays Create Database
>
> $dbhost = 'localhost';
> $link = mysql_connect($dbhost);
> if (! $link)
> die("Couldn't connect to MySQL");
> //create database
> mysql_create_db("mydatabase")or die("Create Error: ".mysql_error());
> mysql_close($link);
> ?>
>
>
> ---
> I can access all of my databases and tables with phpMyAdmin just fine,
> and I can access them at the command line. Of course I didn't know
> which user I was when was attempting to execute this php script through
> the browser, so I check my mysqld.log file and it claims I should be
> '[EMAIL PROTECTED]', so I setup this user in the mysql database.
>
> I tried adding
> $dbuser = 'nobody';
> $dbpass = '';
> to the script but to no avail even though I can access through the
> command prompt with with a password.
>
> When I do a the results show a --port=3306 as
> well as 10 connection PID's. My hello.php script works fine, as do
> other php scripts.
>
> When I list however, the number that gets
> displayed in the column after and before
> is 999269. I was expecting it to be 3306?
>
> How do I troubleshoot this error message? My
> mysqld.log looks "normal" other than a "Warning: Asked for 196608 thread
> stack, but got 126976>. The mysql.sock line shows .
>
> Any ideas on what to check next? I'd at a loss.
>
> TIA
> Andrew L. in the Heartland (not the "Hinterlands")
>
> --
> 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]
Re: full text search question
Laura:
Perhaps the "-" is acting like a Boolean operator. What if you put
double quotes around your search phrase:
SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+"XY-11443"' IN
BOOLEAN MODE );
Wes
On Wed, 29 Sep 2004 13:22:54 -0400, Laura Scott <[EMAIL PROTECTED]> wrote:
>
>
> Hello,
>
> I have a questions with limitations/restrictions that are around for
> full text search.
>
> I have a field with data like "XY-11443;." and I need to find the
> record. The original developer was using full text search and says that
> all was working before the task switched hands.
>
> The basic query is
> select * from metadata where match(type) against ('+XY-11443' in boolean
> mode);
>
> This query spins through all of my records and gives no results.
> However, if I remove the "XY-" and just do ('+11443' in boolean mode) I
> get an immediate and correct result.
>
> I believe there is something going on with the '-' in the string that is
> causing trouble - like maybe a stop word or something - but can't find
> exactly what is going on and more importantly "HOW TO FIX IT"
>
> Any help would be awesome!
>
> Laura
>
> --
> 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]
Re: Get lines matching a select / group by query
Use COUNT(): SELECT COUNT( id ) FROM test WHERE LEFT( id, 5 ) = '12345' GROUP BY value; Wes On Tue, 21 Sep 2004 00:24:33 +0200, Alexander Newald <[EMAIL PROTECTED]> wrote: > Hello, > > I like to get the number of lines returned by a select ... group by query: > > Example: > > SELECT id FROM test WHERE LEFT(id,5) = "12345" GROUP BY value; > > Perhaps I will get > > +---+ > | id| > +---+ > | 2 | > | 1 | > | 5 | > +---+ > 3 rows in set (0.02 sec) > > But I like to simply get 3 > > As I use mySQL 3.x and can't change it for now I'm not able to use > "FOUND_ROWS()" > > Thanks, > > Alexander Newald > > -- > 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]
Is there any performance reason to use "unique index"
Hi: I was wondering if there is any performance-related reason to use a unique index versus a standard index? Is the only benefit of a unique index that it will prevent duplicate values from being inserted into a table unless explicitly allowed? I have a column, colA, that I know contains only unique values because I create the table using a 'GROUP BY colA' clause. Before I use the table for any more work, I want to index colA. This table will have no further rows added to it, so I don't need to worry about a potential duplicate value being inserted. I was just wondering if it was a performance gain/hit to use a unique index, or if the difference was negligible. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN help
Robb: http://dev.mysql.com/doc/mysql/en/JOIN.html I am assuming all the information you need is student name + city name. SELECT * FROM StudentTable AS s, CityTable AS c WHERE s.CityID = c.CityID Wes On Wed, 8 Sep 2004 19:55:29 -0500, Robb Kerr <[EMAIL PROTECTED]> wrote: > Trying to get my mind around JOINs. Please help. > > Scenario... > StudentTable > Fields: > StudentID > StudentName > CityID > > CityTable > Fields: > CityID > CityName > > I will store personal information about the student in the first table. The > second table contains information about the city. I relate the tables by > entering the CityID from the CityTable into the CityID field in the > StudentTable. > > I can write the following SELECTs... > > SELECT * FROM StudentTable > SELECT * FROM CityTable > > But, what's the correct syntax for using the JOIN statement to create a > recordset with all of the information I need. Of course, this example is > very simple and my "real" SELECTs will include WHEREs and ORDER BYs. > > Thanx, > -- > Robb Kerr > Digital IGUANA > Helping Digital Artists Achieve their Dreams > > http://www.digitaliguana.com > http://www.cancerreallysucks.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load non-fixed formatted text files
Christian: Change your readme and hostname columns to BLOB and use the LOAD_FILE() function to add your record. INSERT INTO test NULL, LOAD_FILE( '/path/to/readme/file' ), LOAD_FILE( '/path/to/hostname/file'); More on the LOAD_FILE() function: http://dev.mysql.com/doc/mysql/en/String_functions.html Wes On Wed, 8 Sep 2004 16:59:55 +0200 (CEST), Christian Gielstrup <[EMAIL PROTECTED]> wrote: > Hi gurus, > > can you pls. help a mysql newbee? > > I need to load data into the following table > > mysql> describe test; > +--++--+-+-++ > | Field| Type | Null | Key | Default | Extra | > +--++--+-+-++ > | id | tinyint(4) | | PRI | NULL| auto_increment | > | readme | text | YES | | NULL|| > | hostname | text | YES | | NULL|| > +--++--+-+-++ > 3 rows in set (0.00 sec) > > The fields "readme" and "hostname" are to contain text from two local > files with no fixed format. > They could look like this: > > README start > --- > This directory contains system configuration information. > Information was gathered on Wed Jan 21 08:54:16 MET 2004. > > Synopsis > > >Customer Name: > Contract ID: > Email results to: > Modules selected: default > --- > README end > > HOSTNAME start > --- > hostname: legolas > location: the moon > owner: gandalf > desc: this is system legolas,running X and Y service for Z customer > > HOSTNAME stop > --- > > Do I have to create a row first only containing the auto incremented id > field, and then do two updates to the table, controlled by matching the > id. > Or can this be done in one statement, with out changing the format of the > text files? > > Thank you for your time! > > /Christian > > -- > 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]
Re: Substring functions in mySQL
Assuming there are no parts of a name that include more than one word (e.g., "Mary Jo" being someone's first name), an easy way would be to use SUBSTRING_INDEX. First Name = SUBSTRING_INDEX( namefield, ' ', 1 ); Middle Name = SUBSTRING_INDEX( SUBSTRING_INDEX( namefield, ' ', 2 ), ' ', -1 ); Last Name = SUBSTRING_INDEX ( namefield, ' ', -1 ); http://dev.mysql.com/doc/mysql/en/String_functions.html Wes On Wed, 08 Sep 2004 13:35:07 +, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > How can I extract a middle segment of text in mySQL. For example a column contains > this string: 'William Walker Jones'. I need to split this into the first, middle, > and last names. I can easily extract the first and last names but how do I extract > the Middle name? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote Connecting
It might just be that your connect line isn't working properly. Try this form instead: mysql -u root -h MyLinuxSystem -p You want to connect as user "root" to host "MyLinuxSystem" using a password for which you will be prompted. If that doesn't wok, you need to make sure that on your server, on your Linux system, you have the permissions set up so root is allowed to connect from your Windows XP system. You may need to add a user to do so (http://dev.mysql.com/doc/mysql/en/Adding_users.html). GRANT ALL PRIVILEGES ON *.* TO 'root'@'MyWindowsSystem' IDENTIFIED BY 'password' WITH GRANT OPTION; Wes On Sun, 5 Sep 2004 18:46:50 +0200, Danesh Daroui <[EMAIL PROTECTED]> wrote: > Hi all, > > I have installed Mysql-4.1 successfully on a Fedora Core 2 Linux system. Now, I like > to connect to my Linux system from my other system which as Windows XP and of course > Mysql 4.1 installed too. In fact, I like to connect to my Linux system as root and > do all console tasks just like I am working with my Linux system. I tried to do that > like below on my Windows XP system: > > mysql -u [EMAIL PROTECTED] -p > > but it was failed. How can I do this ? Please help me. > > Regards, > > Danesh Daroui > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to have Undeletable Records?
I don't think so. I think your current method of either storing it in the query or with a boolean field is your best bet. What I've seen done in the past is that records get marked with a user level and there is either one or a group of users who are allowed to delete or modify those otherwise permanent records. Wes On Fri, 03 Sep 2004 11:38:35 +0100, zzapper <[EMAIL PROTECTED]> wrote: > Hi Y'All > > Is it possible to have undeletable/unmodifiable records in a table of otherwise > modifiable & > deleteable & createable records? > > At present I do it at the Update/Delete level where I have clauses which prevent > certain records > being changed. I suppose I could also have an extra boolean field "Record Read > only", but is there > anyway to specify at the data level? > > zzapper (vim, cygwin, wiki & zsh) > -- > > vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?" > > http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating a one-to-many with a MIN result from 'many' for each 'one' record?
I have a common update that I need to run that is a bottleneck in a lot of the reports that are being requested. If anybody else has dealt with a similar situation but with more success, any tips would be appreciated. Basically, I have a one-to-many relationship and I want to update every record in the 'one' table with a MIN result from the 'many' table. Although I realize this doesn't work, the query I wish I could run is this: UPDATE Table2, Table1 SET Table2.Open_Date = MIN( Table1.Open_Date ) WHERE Table2.Account_Number = Table1.Account_Number Table1 Account_Number CHAR( 10 ) Asset_Number INT( 5 ) Open_Date DATE Table2 Account_Number CHAR( 10 ) Open_Date DATE Sample Data Table1 Account Number AssetOpen Date 012345 000502003-01-01 012345 006012002-08-10 0001234567 006012002-06-15 9003004325 006012002-11-16 Table2 Account Number Open Date 012345 2002-08-10 0001234567 2002-06-15 9003004325 2002-11-16 What is going on is that Table2 is just a unique list of accounts and I would like to update it with the earliest open date for any asset owned by that account number in Table1. Currently, I am using PHP to issue a set of queries and it can take 1-2 minutes to run through it. First I get all the account numbers in Table2 (SELECT Account_Number FROM Table2). Then I loop through them, one at a time, getting the minimum Open_Date from Table1 for each account number (SELECT Open_Date FROM Table1 WHERE Account_Number = $account_number ORDER BY Open_Date ASC LIMIT 1). Before moving to the next account number, I run another query to update Table2 with the Open_Date acquired in the previous query (UPDATE Table2 SET Open_Date = $open_date WHERE Account_Number = $account_number). Is there a better way to do this, either by having MySQL do all the work, instead of using PHP to loop through? Or is there no way to avoid looping? Thanks again for any advice. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: root without permissions...
Jose: It looks like you can log in to the mysql client as root. Do you have permission to change the password once you're logged in? [EMAIL PROTECTED] jusoz]$ mysql -u root -p Enter password: Welcome to the MySQL monitor... mysql>USE mysql; Database changed. mysql>UPDATE user SET Password = PASSWORD( '' ) WHERE User = 'root'; Query OK, 0 rows affected (0.03 sec) Rows matched: 2 Changed: 0 Warnings: 0 mysql>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) mysql> quit; Then see if logging in as expected works: [EMAIL PROTECTED] jusoz]$ mysql -u root -p Wes On Sun, 29 Aug 2004 14:26:10 +0200, jose usoz <[EMAIL PROTECTED]> wrote: > Hello, > these are my first days with mySQL. Yesterday all is well. Today i > changed some permissions in the databases and groups. I think i have > returned to the original permissions, but i not have any permission with > the user root. Is possible to restore this user?: > > **login with my passw -> > [EMAIL PROTECTED] jusoz]$ mysql -u root -p > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: > YES) > > **login without passw-> > [EMAIL PROTECTED] jusoz]$ mysql -u root -p > Enter password: > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 15 to server version: 4.0.18 > > **changing the passw -> > [EMAIL PROTECTED] jusoz]$ mysqladmin -u root -h localhost password "" > mysqladmin: unable to change password; error: 'Access denied for user: > '@localhost' to database 'mysql'' > > Thanks, > jose. > > -- > Jose Usoz / Cromosfera > http://www.cromosfera.com > > -- > 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]
Re: select rows by compare on datetime column - more on time decrement
What version of MySQL are you using? You have different options. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Look at DATE_SUB() and SUBDATE()/SUBTIME(). The time part of the query would look something like DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 4 HOUR ), '%H:%i:%s' ), or something like that. Or, this might be better handled in Perl, just using Perl to control the time value supplied in the query -- I'm not sure about your "and so on". I wouldn't know how to handle a series of t1, t2, t3, t4, ..., tn without use of a scripting/programming language. Wes On Aug 18, 2004, at 3:44 PM, Viswanatha Rao wrote: I have to select mysql rows based on the following condition. Time t1 = (current time - 4 hours); Time t2 = (current time - 8 hours); In my case, I want to select all those rows that have start times (1) between current time and t1 (2) between time t1 and t2 ... and so on So I have to somehow subtract hours from the current time to get t1, t2, and so on. In some other cases, I may have to subtract specific number of minutes. So my question is how do I selectively subtract hours or minutes from current time in mysql. I need these values to plot a chart. Please let me know? -----Original Message- From: Wesley Furgiuele [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:36 AM To: Viswanatha Rao Cc: [EMAIL PROTECTED] List Subject: Re: select rows by compare on datetime column Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) <= CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): --> If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) >= '10:00:00' AND TIME( end_time ) >= '18:00:00' --> Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) = '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) <= '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- 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]
Re: select rows by compare on datetime column
Vishwa: Sorry -- I used a specific time value as an example, but since you were asking about current time, you would use CURTIME(): SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) >= CURTIME() AND DATE_FORMAT( end_time, '%H:%i:%s' ) <= CURTIME(); Wes On Aug 18, 2004, at 12:24 PM, Wesley Furgiuele wrote: Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): --> If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) >= '10:00:00' AND TIME( end_time ) >= '18:00:00' --> Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) >= '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) <= '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- 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]
Re: select rows by compare on datetime column
Does the date matter, or only the time? If date matters (you want to find all records between 2004-06-01 10:00:00 AND 2004-06-03 18:00:00): SELECT columns FROM table WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00' If only time matters( you want to find all records between 10:00:00 AND 18:00:00 on any day): --> If you're using version 4.1.1 or greater: SELECT columns FROM table WHERE TIME( start_time ) >= '10:00:00' AND TIME( end_time ) >= '18:00:00' --> Otherwise: SELECT columns FROM table WHERE DATE_FORMAT( start_time, '%H:%i:%s' ) >= '10:00:00' AND DATE_FORMAT( end_time, '%H:%i:%s' ) <= '18:00:00' Wes On Aug 18, 2004, at 11:39 AM, Viswanatha Rao wrote: I have two columns in a table: start_time and end_time. Both are of types DATETIME When I select rows from table, I want to select only those rows where the current time falls between start_time and end_time Can someone help with the SELECT syntax for this? Best Regards Vishwa Rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can connect with PHP to MYSQL
What error are you getting? My problem when moving to 4.1 was
forgetting that I was using an older MySQL client.
I'm not positive, but I think that in order to connect to MySQL 4.1
with PHP you need to use the mysqli functions, not mysql, and mysqli
requires PHP 5.
If you want to keep using MySQL 4.1 with PHP 4, then try changing your
user's password with the OLD_PASSWORD() function.
http://dev.mysql.com/doc/mysql/en/Encryption_functions.html
Wes
On Aug 13, 2004, at 9:48 AM, leegold wrote:
I can connect in the php by using 'root' and the root pw. But when I
tried adding a user ( sarah ) with GRANT I cannot connect from php. The
php is simple, what am I overlooking? Again putting the root user name
and pw it'll work in php but not for sarah:
the php
mysql_pconnect("localhost","sarah","camera")
or die("ERROR: Could not connect to database!");
mysql_select_db("howto");
*But* on the command line the sarah user works OK:
C:\Documents and Settings\Administrator>mysql -u sarah -p
Enter password: **
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 4.1.3a-beta-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from howto.page;
+-+-+
| page_id | page_url|
+-+-+
| 1 | http://www.lg.netfarms.org |
+-+-+
1 row in set (0.00 sec)
mysql> show grants for [EMAIL PROTECTED];
+--
-+
| Grants for [EMAIL PROTECTED]
|
+--
-+
| GRANT ALL PRIVILEGES ON *.* TO 'sarah'@'localhost' IDENTIFIED BY
PASSWORD '*6043233C67ADBE7E9242089D718763C3C5E1A1E2' |
+--
-+
1 row in set (0.00 sec)
--
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]
Re: BLOB columns
I tend to use LOAD_FILE(), but your file has to be on the server and
readable by MySQL ("INSERT INTO table ( field1, field2 ) VALUES ( 'a',
LOAD_FILE( 'filepath' )" ). If you're using a programming language
rather than just the MySQL client, you can also read the file into a
variable and insert the contents of the file like any other value.
If you Google for something like "storing images in MySQL", you will
come up with a bunch of different tutorials that can guide you (storing
images seems to be a popular demonstration of working with files).
Wes
On Aug 5, 2004, at 2:25 PM, Nathan Mealey wrote:
I am at a loss as to how to insert files into a BLOB column in a
database, and how to extract them. Basically, I want to store files
in the database itself, but do not know how to go about doing this.
What sort of queries do you run to do this? Is it the same as regular
"insert" and "select" syntax?
Any help would be much appreciated.
Thanks,
Nathan
--
Nathan Mealey
Director of Operations
Cycle-Smart, Inc.
P.O. Box 1482
Northampton, MA
01061-1482
[EMAIL PROTECTED]
(413) 587-3133
(413) 210-7984 Mobile
(512) 681-7043 Fax
--
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]
Re: Login question
Try
SET PASSWORD FOR 'root'@'localhost' = PASSWORD( 'newpwd' );
SET PASSWORD FOR 'root'@'%' = PASSWORD( 'newpwd' );
There was no username in your SET PASSWORD command.
Wes
On Jul 30, 2004, at 4:38 PM, aspsa wrote:
Per the MySQL documentation I used the following commands to establish
login
both at the local host and remotely.
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');
When I attempt the following locally from the Command Prompt (with
Win2K,
SP4 administrator privileges),
mysql -u root -p
, I am prompted for the password. Yet, when I enter the password set
above,
I receive the following error message.
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Argh... your help is appreciated.
Respectfully,
ASP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.17 to 4.1.3 connection problem
Keith: I don't know if it's the same problem, but I recently had issues where I had a similar setup with only two MySQL servers, one 4.0.20, the other 4.1.3, same usernames/passwords on each. My solution, and I don't remember where in the manual I saw this (possibly the FAQ), was to do an update to the 4.1.3 mysql user table: UPDATE user SET Password = OLD_PASSWORD( Password ) WHERE ... I believe this was because I was trying to connect to MySQL 4.1.x with a 4.0.x client. I don't know if that's your case as well. http://dev.mysql.com/doc/mysql/en/Old_client.html Wes On Jul 28, 2004, at 6:21 PM, Keith Thompson wrote: I have a mysql connection problem that I'm trying to understand. The three servers and the version of mysql they are running (all under Solaris9) are: db1 - 4.0.16 db2 - 4.0.17 db3 - 4.1.3 All three systems have the same mysql user and passwords setup. I'll use the mythical user "xx" with password "yy" to explain here. Connections with the mysql client (using "-uxx -pyy") from system to system all work except this one on db2: mysql -hdb3 -uxx -pyy ERROR 1045: Access denied for user 'xx'@'db2' (using password: YES) The mysql.user table entry has host=% and user=xx, so it's not simply an issue of a system-specific entry allowing one and not the other. Since db1 has no problem getting to db3, I wouldn't expect db2 to struggle. This same problem occurs with all users, so it' is also not something specific to how this user is setup. Does anyone know why this would be happening? Is there something different in 4.0.17 (compared to 4.0.16) that prevents it from connecting to the 4.1.3 server? I don't see anything in the 4.0.17 change list specific to this. 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]
query to select only numeric portion of string
Using MySQL 4.0.20, I need to extract just the numeric portion of a string in a field and move it to another field in the same table, but I'd only like to do this if the value actually starts with a number. So, what I'm looking to accomplish is: UPDATE table SET field2 = VOODOO( field1 ) The fields are currently defined as: field2 INT( 10 ) field1 CHAR( 19 ) The table is relatively small, with about 55,000 records in it. Here is the type of data I find in field1 and next to it, what I'd like to wind up with in field2: 1234 -> 1234 12345 -> 12345 123456 -> 123456 1234567 ->1234567 1234NN -> 1234 12345NN ->12345 123456N -> 123456 1234567 -> 1234567 WWW -> WWW NC -> NC There is other data, but most of it follows that rule. If I can do this with MySQL, I would prefer it. If it's something I should farm out to PHP or Perl, I can do that, but I was trying to think of how to accomplish this just within MySQL and don't really know how to approach it. Any pointers would be appreciated. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: str_to_date problem
Danny:
What if you just pad the string, does it work then?
str_to_date( LPAD( '901', 4, '0' ), '%k%i' )
Wes
On Jul 22, 2004, at 2:05 PM, [EMAIL PROTECTED] wrote:
I have a "time" field formatted as hhmm however the hours field does
not
add leading zeros. eg 09:01 is simple 901.
I want to get a standard format time out but,
mysql> str_to_date('901','%k%i')
gives a NULL result.
A further test reveals that I can covert into the desired format but
not
back.
mysql> select TIME_FORMAT('8:53:00', '%k%i') \G
*** 1. row ***
TIME_FORMAT('8:53:00', '%k%i'): 853
1 row in set (0.00 sec)
mysql> SELECT str_to_date(TIME_FORMAT('8:53:00', '%k%i'), '%k%i') \G
*** 1. row ***
str_to_date(TIME_FORMAT('8:53:00', '%k%i'), '%k%i'): NULL
1 row in set (0.00 sec)
Is there any way out?
Danny
mysql 4.1.3-beta-standard
Magdalena Ridge Observatory
--
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]
Re: FULLTEXT search
"OR" From the manual: "Every correct word in the collection and in the query is weighted according to its significance in the collection or query." http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html If you want "AND", look at the boolean full-text searches format: http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html On Jul 22, 2004, at 10:17 AM, Schalk Neethling wrote: When doing a MATCH() AGAINST() search. Does a result get returned only when all of the words in the AGAINST() 'tag' matches a document or if any words match. Basically is this an AND or OR type of result that is returned? Thanks! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- 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]
Re: Need help with a select. Regex?
Just a follow-up "oops"...
I misread the manual page when verifying the SUBSTRING_INDEX() syntax.
It states that it returns everything before _count_ instances of the
delimiter, so naturally if you feed it a value that exceeds the actual
instances of the delimiter, you get back the whole string. Sorry for
any confusion.
Wes
On Jul 21, 2004, at 1:01 AM, Wesley Furgiuele wrote:
To get "http://www.google.com/"; out of the URL, you can do this:
LEFT( referer, LENGTH( SUBSTRING_INDEX( referer, '/', 3 ) ) + 1 )
If you don't care about the trailing slash, you can use just the
SUBSTRING_INDEX() portion:
SUBSTRING_INDEX( referer, '/', 3 )
Using the LENGTH() function just helps guarantee that you'll get the
trailing slash if you want it. The MySQL manual doesn't specify what
happens if the count value you feed SUBSTRING_INDEX() exceed the count
of the delimiter, but it looks kind of like it just returns back the
whole string.
Wes
On Jul 20, 2004, at 10:11 PM, <[EMAIL PROTECTED]> wrote:
What I am trying to do is select the hostname out of a refering url.
Such as:
http://www.google.com/search?hl=en&ie=UTF
-8&q=%22Third+Park+Point+Beach+Water+Contact+Advisory+Posted%3B+May+In
dicate+Long-Term+Problem%22&btnG=Google+Search
All I really want to get is:
http://www.google.com/
So I have:
Select DISTINCT LEFT(referer, LOCATE('/',referer)-1) referer ,
count(*) as refCount
FROM NNtracking
WHERE referer != ''
GROUP BY referer
Limit 10
but that only selects me:
http:/
I have tried a couple of REGEXP ^/ variations but either I am on the
wrong track or I can't get the syntax right.
Advice?
--ja
--
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]
Re: Removing a specific set of duplicates
Scott: Yeah, exactly. The ON DUPLICATE KEY is useful if your table has something like a "last modified" or "last accessed" column that is relevant for what you're doing. It is just an easier way to accomplish INSERT IGNORE plus then doing an UPDATE on all the rows that got ignored, in case you need to document that there was an attempt to insert the data. Wes On Jul 21, 2004, at 12:58 AM, Scott Haneda wrote: on 7/20/04 9:44 PM, Wesley Furgiuele at [EMAIL PROTECTED] wrote: First off, the unique index is something you define for the table once. Being unique, you won't be allowed to add in another record with the same values as an record that already exists in the table. I thought so, thanks. And yes, once you set it up, INSERT IGNORE would allow your query to simply skip the insertion of any records that already exist in the table. Something else to look at would be the INSERT ... ON DUPLICATE KEY UPDATE syntax, depending on your version of MySQL ( >= 4.1 ) http://dev.mysql.com/doc/mysql/en/INSERT.html Super, so the INSERT IGNORE is gonna work. Curious why you pointed me to the ON DUPLICATE KEY link. Since I want to just gracefully exit from the insert, I assume you were just pointing this out as a FYI? This feature is more or less if I wanted to make some update to a row when the duplicate was hit? I'm not yet sure yet what to make of your last situation, where you are merging addresses into one group. About the bounce count, presumably that is not necessarily the same value for each instance of an email address across different groups? Is the bounce count the only field that would differ between the two duplicate records? To be honest, I am not entirely sure, yet, this will require me to ponder some more about how this is going to work. Thanks for all your help so far, this is a great solution to a otherwise complicated to me issue :-) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
Re: Need help with a select. Regex?
To get "http://www.google.com/"; out of the URL, you can do this:
LEFT( referer, LENGTH( SUBSTRING_INDEX( referer, '/', 3 ) ) + 1 )
If you don't care about the trailing slash, you can use just the
SUBSTRING_INDEX() portion:
SUBSTRING_INDEX( referer, '/', 3 )
Using the LENGTH() function just helps guarantee that you'll get the
trailing slash if you want it. The MySQL manual doesn't specify what
happens if the count value you feed SUBSTRING_INDEX() exceed the count
of the delimiter, but it looks kind of like it just returns back the
whole string.
Wes
On Jul 20, 2004, at 10:11 PM, <[EMAIL PROTECTED]> wrote:
What I am trying to do is select the hostname out of a refering url.
Such as:
http://www.google.com/search?hl=en&ie=UTF
-8&q=%22Third+Park+Point+Beach+Water+Contact+Advisory+Posted%3B+May+Ind
icate+Long-Term+Problem%22&btnG=Google+Search
All I really want to get is:
http://www.google.com/
So I have:
Select DISTINCT LEFT(referer, LOCATE('/',referer)-1) referer ,
count(*) as refCount
FROM NNtracking
WHERE referer != ''
GROUP BY referer
Limit 10
but that only selects me:
http:/
I have tried a couple of REGEXP ^/ variations but either I am on the
wrong track or I can't get the syntax right.
Advice?
--ja
--
--
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]
Re: Removing a specific set of duplicates
First off, the unique index is something you define for the table once. Being unique, you won't be allowed to add in another record with the same values as an record that already exists in the table. And yes, once you set it up, INSERT IGNORE would allow your query to simply skip the insertion of any records that already exist in the table. Something else to look at would be the INSERT ... ON DUPLICATE KEY UPDATE syntax, depending on your version of MySQL ( >= 4.1 ) http://dev.mysql.com/doc/mysql/en/INSERT.html I'm not yet sure yet what to make of your last situation, where you are merging addresses into one group. About the bounce count, presumably that is not necessarily the same value for each instance of an email address across different groups? Is the bounce count the only field that would differ between the two duplicate records? Wes On Jul 20, 2004, at 10:36 PM, Scott Haneda wrote: on 7/20/04 4:10 PM, Wesley Furgiuele at [EMAIL PROTECTED] wrote: Rather than make the email address column unique, since you want to allow multiple email address instances, you can make a unique index based on email_address+group. Wouldn't that help, or is that still too simple for your situation? I don't fully understand here, hope you can help. Is making a unique index on 2 columns something I would run perhaps on a schedule to deal with this, or is this something I set once and it is just maintained? If it is something that is on schedule, or triggered say after a bulk import by the user, when I send in that ALTER how do I also tell mysql to keep the one dupe email address with the highest bounce count? Or, perhaps this is something I set once, then I would use INSERT IGNORE and the dupes would not be allowed and would simply gracefully fail? Can this work In a transaction environment, where I would START TRANSACTION; repeat with aEmail in uploaded file INSERT IGNORE into addresses etc etc etc end repeat COMMIT; I will also have one more case that needs this treatment as well, say there are 2 groups of emails, lets call them "family" and work". I will be allowing the user to merge those into one group, something like: UPDATE addresses SET group='family' WHERE group='work' AND user_id ='123', would I still be able to get the duplicates out in this scenario as well? (note: group is not a string, I just used it as one in this example) Maybe this would be a better case to use a temp table, select both the "family" and "work" into a temp table, then somehow remove only those that have a bounce count of zero, or in the case all dupes bounce count are zero, simply remove the newest entry. Ugh, this is making my head spin :-) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing a specific set of duplicates
Scott: Sorry, should have included it... http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html The basic syntax you're looking to use is ALTER TABLE tablename ADD UNIQUE `index_name` ( email_address, group ) Wes On Jul 20, 2004, at 7:45 PM, Scott Haneda wrote: on 7/20/04 4:10 PM, Wesley Furgiuele at [EMAIL PROTECTED] wrote: Rather than make the email address column unique, since you want to allow multiple email address instances, you can make a unique index based on email_address+group. Wouldn't that help, or is that still too simple for your situation? I think you nailed it, I was not aware of this feature, any links that tell me more? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
Re: Removing a specific set of duplicates
Scott: Rather than make the email address column unique, since you want to allow multiple email address instances, you can make a unique index based on email_address+group. Wouldn't that help, or is that still too simple for your situation? Regarding temporary tables, from the MySQL manual: From MySQL 3.23 on, you can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables. I don't understand well enough how the group by function works, or select distinct. Would altering the table order to be ordered by bounce count DESC be enough to mean that when you do select distinct records, the record that comes first is the record that is selected? I don't know if it works reliably like that or if the selection is more random. Wes On Jul 20, 2004, at 6:37 PM, Scott Haneda wrote: Mysql 4 I have a slightly tougher than usual condition in which I want to remove duplicates. I am not sure how to deal with this and am looking for your suggestions. The table that stores my data is called addresses, it contains among others, a field called email_address. Within this table emails are grouped on a unique id, so for example, select email_address from addresses where group_id = '5' AND user_id = '2' would show me all the addresses that I want to work on. The case is that users will always be adding more addresses to this group, this is for a mailing list manager I am working on. The trouble is that I suspect users will upload a batch of addresses, then a few weeks later, they will upload a new set, but they will contain the old set as well. This would pile up the duplicates in short order, and I don't want multiple emails sent to the same person over and over again. My first option is when they upload new addresses, to select and test for the existence of that address. If it exists, do not add it, otherwise I will add it. The size of some of these lists are large, in the 10's of thousands. I suspect this will add too much overhead to the import time. I can not make the column unique as there is good reason to have the email address in the column more than once, since they are "groups" of email addresses. My thought is to allow the import of all the addresses, allow all the dupes, then take out the dupes, I suspect this will be faster than a select for every email address I want to import. I think this involves selecting distinct() into a temp table, deleting the addresses from the main table, then selecting into the old table from the temp table and then destroying the temp table. If anyone can suggest a tricky way to do this with perhaps a group by clause to simpy remove the dupes in one go, I would love to hear it. In regards to the temp table, is it up to me to maintain a unique temp table name to not collide in the event 2 users were to hit the page at the same time? And now, the other "rub" Another field in the addresses table I used to track bounced emails, lets call it 'bounces', which I increment by 1 on every bounce. Here is the other issue, I can not simply remove the dupes without first determining which dupe to remove. Basically, I want to remove all dupes where the bounce count is 0 (default), but if there are more than 2 dupes, I want to keep the one with the highest bounce count. Any idea how I should be approaching this? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Composite Index
David: I believe you mentioned it in a previous thread, but I forget what kind of field NameMam is. It's important because you might be required to give that key part in your index a length. Also, I'm assuming that this index is on a unique identifier field. If it's not, then just substitute the word "INDEX" where you see the word "UNIQUE" below. Otherwise, here's the syntax I find myself using most often: ALTER TABLE fammammals CREATE UNIQUE `NameForYourIndex` ( NameMam, IDMam ); If you want/need to assign a length to one or both key parts: ALTER TABLE fammammals CREATE UNIQUE `NameForYourIndex` ( NameMam( WhateverLengthIsReasonable ), IDMam ( WhateverLengthIsReasonable ) ); Even if your NameMam field doesn't require a length on the index, you might want to add a limitation because it would result in a shorter index, which would save you some space. Wes On Jul 18, 2004, at 11:31 AM, David Blomstrom wrote: I'm trying to create my first composite index and need some help. The MySQL Manual at http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html offers these directions: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC] * * * * * * * * * * So I'm trying to plug that into my situation. If my table is named fammammals, and I want to put a composite index on two fields named NameMam and IDMam, would it look something like this?: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON fammammals (index_NameMam,...) index_col_name: IDMam [(length)] [ASC | DESC] And can I just paste this code into phpMyAdmin, where it says, "Run SQL query/queries on database [MY DATABASE]"? Thanks. __ Do you Yahoo!? Vote for the stars of Yahoo!'s next ad campaign! http://advision.webevents.yahoo.com/yahoo/votelifeengine/ -- 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]
Re: Displaying Values With Commas.
Someone else might be able to provide a SQL query for formatting with commas, but otherwise just let PHP do all your formatting. PHP's number_format() function is probably your best bet. You might want to look at PHP's money_format() function also, since it might save you some time depending on what you're doing. Wes On Jul 16, 2004, at 8:04 PM, Braulio Lumbreras wrote: Hello everyone, I have a table with a column for real estate property listing values. The column is set up as float(8,2). How do I go about setting up so that the display on the web page includes a comma ? I get $155000.00 but would like to see $155,000.00. You may go here to view the results, http://swishermartinrealty.com/residentialhouseforsalelistings.php. Any help will be greatly apreciated. Thanks in advance, Braulio. -- 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]
Re: deleting duplicates from table
I think one way to solve it would be to move your data to a new copy of your table. Assuming record_ref and keyword are separate fields... CREATE TABLE newtable SELECT * FROM oldtable GROUP BY CONCAT( record_ref, keyword ); Wes On Jul 16, 2004, at 7:08 PM, L a n a wrote: Hello, I'm trying to solve a task in Mysql 4: I have a table rec_id | record_ref | keyword ** 1| 2| apple 2| 2|orange 3| 2|apple 4| 3|mango ** How can I delete records where pair (record_ref and keyword) are identical? In this case either record #1 or #3, but not both. Thank you, Lana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql function for timestamp
JS:
When I use DATE_FORMAT(), if I have a space character between
DATE_FORMAT and the ( I get an error. Try writing it like:
SELECT DATE_FORMAT( "20040601123456", '%Y-%m-%d' );
MySQL v4.1.2-alpha-standard
Mac OS X
Wes
On Jul 14, 2004, at 12:20 PM, J S wrote:
Thanks for your help. I would like to do it with the SQL. However I'm
still having problems with the syntax below.
Is this wrong?
mysql> SELECT DATE_FORMAT ("20040601123456",'%Y-%m-%d');
ERROR 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 '("20040601123456",'%Y-%m-%d')' at line 1
Also I tried:
mysql> SELECT DATE_FORMAT (time,'%Y-%m-%d %H:%i')
-> from internet_usage
-> where time>2004060112;
ERROR 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 '(time,'%Y-%m-%d %H:%i')
from internet_usage
where time=20040601
While in PHP you can try something like this...
$date_added = $row['date_added'];
$date = date("M d, Y", strtotime($date_added));
echo $date;
H and i do work in PHP as well, I believe, if you want to add the
hour and
seconds. Check out the 'date' function in PHP for info.
- Eve
In MySQL you can do that with: DATE_FORMAT
DATE_FORMAT (column_name,'%Y-%m-%d %H:%i')
-Mensaje original-
De: J S [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m.
Para: [EMAIL PROTECTED]
Asunto: sql function for timestamp
Hi,
What SQL function do I need to convert timestamp 200406011403 into
"2004-06-01 14:03" ?
Thanks,
JS.
_
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql function for timestamp
If you have 4.1.1 or greater, you can use GET_FORMAT( timestamp, 'ISO' ). Otherwise, I think if you just use DATE_FORMAT( timestamp, '%Y-%m-%d %H:%i ) you will get weird values if your timestamp field doesn't include seconds. So, either alter your field to be CONCAT( timestamp, '00' ), or, as far as I can tell, you will probably be better off just using string functions. Wes On Jul 14, 2004, at 10:25 AM, J S wrote: Hi, What SQL function do I need to convert timestamp 200406011403 into "2004-06-01 14:03" ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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]
Re: Why this query doesn't group the email addresses?
Justin: Interesting. So, if "GROUP BY t1.email" doesn't group simply by t1.email, then how would the query be written to accomplish that? And how is it occurring that Aaron is implicitly grouping by email, first, last instead of just grouping by email? Is it because of the ORDER BY clause? Wes On Jul 13, 2004, at 2:13 PM, Justin Swanhart wrote: You are doing an implicit group by of first, last as well as your explicit group by of email. So you could have two records with the same e-mail address generate two records with your group by Justin Time [EMAIL PROTECTED] Justin Credible [EMAIL PROTECTED] --DUPE-- Case differences between the records could also cause dupes. If case differences are causing it then do select lower(first), lower(last), lower(email) ... group by lower(first), lower(last), lower(email) --- Wesley Furgiuele <[EMAIL PROTECTED]> wrote: What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity>0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- 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]
Re: Why this query doesn't group the email addresses?
What type of field is the email field? Wes On Jul 13, 2004, at 11:04 AM, Aaron Wolski wrote: Hey all, Got this query: SELECT first,last,email FROM CustomerTable AS t1, OrderTable AS t2, CartTable AS t3 WHERE t2.cart_id=t3.cart_id AND t1.id=t2.customer_index AND t3.submitted='1' AND t3.product_index='1' AND t3.quantity>0 GROUP BY t1.email ORDER BY t1.first,t1.last For some strange reason it doesn't seem to group the email addresses. I'd be hard pressed to find every occurrence out of 1000 records, but I DID quickly spot two exact same records which means the email address was not grouped. What can I do or where did I go wrong? Thanks! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: all upper case records.. Keeping first char upper and rest lower?
SELECT CONCAT( UPPER( LEFT( first, 1 ) ), LOWER( RIGHT( first, LENGTH( first ) - 1 ) ) ) AS `first` FROM table On Jul 13, 2004, at 12:51 PM, Aaron Wolski wrote: Hey guys, I have a column in a table called 'first'. Currently all records are upper case. Is it possible for me to do a select statement that makes all chars after the first char lower case? Example: Current: AARON After: Aaron I think this is possible.. just don't know how to execute the functions together to make it happen. Thanks! Aaron -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting timestamps to US Date format
Gary:
Am I looking at PHP's "date()" function? You are taking a timestamp
from a MySQL table and wanting to convert it into a US format. Is the
timestamp stored in your MySQL table a Unix timestamp or a MySQL
timestamp?
It's important to note that you can't take a MySQL timestamp and use
PHP's date functions on it, because PHP is expecting a Unix timestamp.
You can either, as Daniel suggested, format the date with MySQL's
date_format() function, or when selecting the date field in your MySQL
query, use MySQL's UNIX_TIMESTAMP() function.
MySQL timestamp format:
http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html
PHP timestamp format: http://us2.php.net/manual/en/function.time.php
Wes
On Jul 5, 2004, at 7:07 PM, Gary Mack wrote:
Hi there,
I am having trouble converting a timestamp from mySQL to the US Format
mm/dd/. Can someone please help. I am also having trouble
converting -mm-dd into a normal mm/dd/ format as well.
I am using the following line of code:
date('m/d/Y', $row10['modified']);
$row10['modified'] is the timestamp from the mySQL database I want to
convert from. Thanks in advance.
Gary
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic select question...
Possibly because, if there is no typo, your table's name field is: dr. smith1 (note the period after "dr") But your query is looking for dr smith1 Does that fix it for you? Wes On Jul 5, 2004, at 4:18 PM, bruce wrote: hi... a basic select question/issue that should work mysql> select * from instructorTBL; ++--++-+---++ | name | universityID | deptID | email | phone | ID | ++--++-+---++ | dr. smith1 |1 | 2 | [EMAIL PROTECTED] | | 1 | ++--++-+---++ 1 row in set (0.00 sec) why don't any of the following work...??? as you can see, there is information in the tbl... mysql indicates it should work... what simple mistake am i missing..?? this is getting embarrassing... mysql> select instructorTBL.ID -> from instructorTBL -> where instructorTBL.name = "dr smith1"; Empty set (0.00 sec) mysql> select ID -> from instructorTBL -> where instructorTBL.name = "dr smith1"; Empty set (0.01 sec) mysql> select * -> from instructorTBL -> where instructorTBL.name = "dr smith1"; Empty set (0.00 sec) thanks... -bruce -- 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]
Re: basic question about joins....
Bruce: The problem is that you have universityTBL listed twice in your list of tables. Here is your statement, with numbers for each of the sources. SELECT schoolTBL.name, universityTBL.name FROM (1) universityTBL, (2) schoolTBL LEFT JOIN (3) universityTBL ON schoolTBL.universityID = universityTBL.ID WHERE universityTBL.name = 'sam' The syntax I think you want is: SELECT schoolTBL.name, universityTBL.name FROM schoolTBL LEFT JOIN universityTBL ON schoolTBL.universityID = universityTBL.ID WHERE universityTBL.name = 'sam' Wes On Jul 4, 2004, at 11:36 PM, bruce wrote: hi... i'm trying to figure out how to deal with joins (left/right).. i have the following test tbls... create table universityTBL( name varchar(50) not null default '', ID int(10) not null auto_increment, primary key (ID), unique key (name) )type =MyISAM; create table schoolTBL( name varchar(50) not null default '', universityID int(10) not null, ID int(10) not null auto_increment, primary key (ID), unique key (name, universityID) )type =MyISAM; mysql> describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(50) | | UNI | || | ID| int(10) | | PRI | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0.00 sec) mysql> describe schoolTBL; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | name | varchar(50) | | MUL | || | universityID | int(10) | | | 0 || | ID | int(10) | | PRI | NULL| auto_increment | +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql> select * from universityTBL; +--++ | name | ID | +--++ | sam | 1 | | bed | 2 | +--++ 2 rows in set (0.00 sec) mysql> select * from schoolTBL; +--+--++ | name | universityID | ID | +--+--++ | medicine |1 | 1 | +--+--++ i want to be able to produce a select where schoolTBL.universityID = university.ID. i can get the results using a straight select with a where" and a "and" clause: this works... mysql> select s1.name,u1.name -> from universityTBL as u1, schoolTBL as s1 -> where u1.ID=s1.universityID -> and u1.name='sam'; +--+--+ | name | name | +--+--+ | medicine | sam | +--+--+ 1 row in set (0.00 sec) however, i'm trying to get the results using a join. i've tried the follwoing with no luck.. mysql> select schoolTBL.name, universityTBL.name -> from universityTBL , schoolTBL -> left join universityTBL on universityTBL.ID=schoolTBL.universityID -> where universityTBL.name='sam'; ERROR 1066: Not unique table/alias: 'universityTBL' any thoughts/comments as to what's wrong... it's got to be something basic... perusing through google/mysql/etc.. hasn't shed any light on where the issue is... thanks for any comments/criticisms/etc... =bruce -- 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]
Re: How to install MySQL
After you finish installing MySQL, you need to start it up before connecting to it -- the installation won't do that automatically for you. http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html That manual page will give you the right commands. Basically, after installation, run the mysql_install_db script, then start up MySQL with bin/mysqld_safe. After that, you want to follow the steps that instruct you on how to give your MySQL root user a password. Wes On Jul 3, 2004, at 2:51 PM, Arkadi Kagan wrote: Description: MySQL is not properly configured for first run. How-To-Repeat: Run standard installation: ./configure ./make su ./make install exit # (exit su) mysql -- mysql failed: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) /usr/local/libexec/mysqld -- mysqld failed: Warning: Can't create test file /usr/local/var/arkadi.lower-test /usr/local/libexec/mysqld: Can't change dir to '/usr/local/var/' (Errcode: 2) Aborting /usr/local/libexec/mysqld: Shutdown Complete # Indeed /usr/local/var does not exist and my user should not have # a write permission to /usr Fix: Send me to the right part of manual :) Any suggestions are welcome. Originator: Arkadi MySQL support: email support Category: mysql Class: support Release:mysql-5.0.0-alpha (Source distribution) C compiler:gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) C++ compiler: g++ (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Environment: System: Linux arkadi 2.4.20-8 #1 Thu Mar 13 17:18:24 EST 2003 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 22 00:50 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x1 root root 1549556 Mar 14 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2321376 Mar 14 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 14 2003 /usr/lib/libc.so Configure command: ./configure _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- 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]
Re: Auto Date selection and format
I can help easily enough on formatting the dates... The DATE_FORMAT( date, format ) function is what you want: DATE_FORMAT( date, "%m/%d/%Y" ) Wes On Jun 30, 2004, at 9:40 PM, Mike Koponick wrote: Hello all, I would like to be able to select the certain dates within my script. select created_date, status, user, comment1, comment7, comment8, action from users WHERE customerid = 'Customer' AND created_date BETWEEN '2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile 'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'; I would like the first date to be the last day of the previous month and the second date to be the first day of the current month. What is the most effecient way to do this in my script rather than hard coding? Also, I looked for a way to format the output date to MM/DD/YY rather than -MM-DD. Any suggestions? Thanks in advance. Mike -- 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]
Re: return substrings out of a string
Lana: The easiest thing would be if your field always contained the same number of comma-separated strings. Then you could just do something like a SUBSTRING_INDEX() function to break out the field into separate values. But, assuming your field does not always contain the same number of strings... I'm imagining the contents of look like "abc,def,ghi". You want to do: SELECT FUNCTION_NAME( field1 ) and get back Row1: abc Row2: def Row3: ghi If that's right, off the top of my head you have two options. The first option I can think of would be to break out these values into another table. Instead of a comma-separated list inside a field, move that field to another table and break each value into it's own record, linked back to the parent table by ID. A quick search of the list archives will bring up better descriptions on how to normalize than I could give. The other, possibly simpler, option is to handle it in PHP. You could just grab the field, containing a list of strings delimited by commas, and use the explode() function to get your results. The PHP site has plenty of examples on the usage of explode. http://us2.php.net/explode Wes On Jun 30, 2004, at 5:35 PM, L a n a wrote: Hello, Im working on a search function using PHP4 and MYSQL4. Im looking for a function that allow to take a string value (consisted of several substrings separated by coma) out of db field and return each substring one by one. Could you please tell me if there is a function that can do something similar to SELECT function_name (field_name, ,) (return)-> substr1, substr2, substr3 Thank you for your help, Lana _ Add photos to your messages with MSN Premium. Get 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/ prem&xAPID=1994&DI=1034&SU=http://hotmail.com/ enca&HL=Market_MSNIS_Taglines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making lower case then first char to upper case?
Someone else hopefully has something more efficient: UPDATE table SET field = CONCAT( UPPER( LEFT( field, 1 ) ), LOWER( SUBSTRING( field, 2 ) ) ) Wes On Jun 30, 2004, at 12:46 PM, Aaron Wolski wrote: Hi Guys, I'm trying to figure out of this is possible. I know I could do it in PHP but I am dealing with a ton of records and would rather put the processing on the DB than PHP/client side. Question is. can I do a SELECT query on a column that changes all the results to lower case and THEN changes the first character of each result to an upper case? Example: Currently in DB: AARON to Lowercase: aaron to Uppercase: Aaron Any idea on if I can do this and how I might approach it? Thanks so much Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
"^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1}" in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of "short" postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki & zsh)
--
vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
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]
Re: Migrating Access Tables -- Empty Columns, Date and Time
Bob My versions are Access 2000 and MySQL 4.0.20. Here's what I do. When exporting my Access table, I choose the file type Text Files. Then, on the next dialog box, make sure to click the Advanced button to get to the formatting information. Change the date order to YMD and the date delimiter to "-". That will take care of the date. As for the NULL value. The table into which I'm importing has columns defined to allow NULLs, but when I import the Access table with empty columns, like you describe, it doesn't force a null -- it just leaves the value blank. It doesn't cause any problems, but if you want to force a NULL then I guess you could rework the exported file to state NULL for the empty fields you want to read as such. Wes On Jun 28, 2004, at 10:20 PM, Robert L Cochran wrote: 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Displaying Numerals & Dates
David: Sorry. I wrote that out poorly. I missed the closing parenthesis on the foreach(). Here it is in a cleaned-up form: Oh, also, you could/should zero out your mktime( ) function so looks like this (although it's not terribly important): mktime( 0, 0, 0, $month, $day, $year ); Apparently I had included the hours/minutes/seconds roughly corresponding to when I wrote the response. Wes On Jun 26, 2004, at 10:18 PM, David Blomstrom wrote: --- Wesley Furgiuele <[EMAIL PROTECTED]> wrote: Otherwise, you could use PHP's date formatting functions as well (assuming you are getting the date in -MM-DD format: http://us2.php.net/manual/en/ref.datetime.php Wow, that must be a record - getting three or four questions answered in one post. Thanks! The only thing that I'm having trouble with is this: I get a parse error that apparently derives from this piece of code: = explode( "-", $r['Dates'] ); Do you have any idea what the problem might be? I've tried several variations without success. Thanks. __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- 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]
Re: Displaying Numerals & Dates
David:
For number formatting, in your below example, use:
http://us2.php.net/manual/en/function.number-format.php
For date formatting, try grabbing the date from MySQL in the format you
want to use:
SELECT DATE_FORMAT( date_field, '%M %D, %Y' ) AS `Dates` FROM ...
and then just print $r['Dates'] would work.
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
Otherwise, you could use PHP's date formatting functions as well
(assuming you are getting the date in -MM-DD format:
foreach( $data as $r {
list( $year, $month, $day ) = explode( "-", $r['Dates'] );
$formatted_date = date( "F jS, Y", mktime( 21, 39, 0, $month, $day,
$year ) );
print date( "F jS, Y", $timestamp );
}
?>
http://us2.php.net/manual/en/ref.datetime.php
As for the imported numbers showing up as they are, perhaps you are
importing them into SMALLINT field, which has an upper signed limit of
32767. Try changing your field type to INT or MEDIUMINT, depending on
the range of numbers imported, and whether or not there are any
negatives.
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
Wes
On Jun 26, 2004, at 8:47 PM, David Blomstrom wrote:
I asked some questions about displaying numerals and
dates recently and got some good feedback. Now I'm
ready to do it - but I'm doing something wrong.
Consider the table below, where the field "Numerals"
displays regular numerals (e.g. 2500) and "Dates"
displays dates.
Exactly how would I rewrite this so that commas are
inserted in numerals (2,500 vs 2500), and dates (e.g.
2004-12-28) are displayed like "December 28th, 2004,"
or "Dec. 28, 2004"?
Campaign
2000
Also, there's something really weird going on with my
numerals. I saved a Microsoft spreadsheet as a CSV
file, which I then imported into MySQL.
But many of the numerals were transformed to 32767 in
my database. Here's a sample line from the CSV file,
which illustrates that just one numeral is surrounded
by quotes:
921781,"472940",422768,13421,7358,5294
In this particular example, the last two numerals
display correctly in the database, but the other four
don't - including the numeral surrounded by quotes.
Any idea what's going on here? Thanks.
__
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail
--
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]
CAST( string AS UNSIGNED ) question about reliability
Does anybody have any feedback, particularly problems, using CAST to strip the trailing alpha characters from a string in order to convert the string to an integer? Is there a better way to do it with MySQL? (MySQL 4.0.20, Mac OS X 10.3) Here's my situation: TABLE_1 id CHAR( 8 ) TABLE_2 id INT( 8 ) suffix CHAR( 2 ) Sample data would be: TABLE_1 id 123456NN 2345678M 135792 12345678 TABLE_2 id suffix 123456 NN 2345678 MM 135792 12345678 The suffix is not part of the key, but it is included automatically in the ID field in TABLE_1. In order to join TABLE_1 to TABLE_2 by id, I need to remove any part of the suffix that shows up (since, because of the field size limitations, sometimes the whole suffix shows up, sometimes part, sometimes none). So what I am doing is: "SELECT t1.field, t2.field FROM TABLE_1 t1, TABLE_2 t2 WHERE CAST( t1.id AS UNSIGNED ) = t2.id". If anyone knows of a more reliable or elegant method for joining the tables based on these fields, any comments would be appreciated. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More appropriate way to join tables?
I am guessing this is a pretty basic question... There must be a better way for me to do this, but I'm still too new at this to know what it is. I've gone through some JOIN tutorials and the documentation, but I don't think I'm quite getting it. (Using MySQL v3.23.38) I have three tables: my_clients, my_users, my_projects TABLE my_company: +---+ | company_id | company_name | +---+ | 1 | SuperCo| | 2 | Mega Inc. | +---+ TABLE my_users: +--+ | user_id | company_id | user_name | +--+ |1| 1 | Bob | |2| 1 | Jane| |3| 2 | Steve | +--+ TABLE my_projects: +--+ | project_id | user_id | company_id | project_name | +--+ | 1 |1| 1 | Put on socks | | 2 |2| 1 | Eat lunch| | 3 |3| 2 | Sing songs | +--+ I know the company_id and I want to see a list of all projects for that company. I can only image that there should be a way that I can make this work without having to create a company_id column in my_projects, but I can't get the query to work. I would like to just get a list of all users with company_id=1 and then show all projects for that company_id, in the end returning a list of project_name/user_name. I can get rid of the company_id if I'm performing a full join, but then I wind up with duplicates of the project_name (Project1/User1, Project1/User2, ...). Here is the query I am using with the tables as they are: SELECT my_projects.project_name, my_users.user_name FROM my_projects LEFT JOIN my_users ON (my_projects.user_id=my_users.user_id) WHERE my_projects.company_id = '$company_id' ORDER BY my_projects.user_id; This seems to give me a list of unique project names with the associated user's name. But if anyone has any corrections/pointers, they would be most appreciated. Thanks for any help. Wesley - 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
