RE: SQL book recommendation?
[snip] I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? [/snip] http://www.peachpit.com/store/product.aspx?isbn=0321375734 MySQL, Second Edition: Visual QuickStart Guide, 2nd Edition - Larry Ullman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: harmonic mean in SQL
[snip] The mathematical way to add two partial harmonic means to generate new harmonic mean is: Let X1 , X2 be two harmonic means on different rollup rows, generated using n1 and n2 # of facts respectively. The combined harmonic mean would be: (n1 + n2)/( n1/x1 + n2/x2) If you have experience with computing harmonic mean in SQL, please share. [/snip] Unless I am misunderstanding your question the simplest method would be; SELECT ((n1 + n2)/(n1/x1 + n2/x2)) AS Harmonic Mean ...without knowing the structure of the data. If X1 and X2 live on 2 different rows you could write a stored procedure to retrieve the relevant data and produce an output. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Even or Odds numbers
[snip] is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number [/snip] You can use modulus http://www.roseindia.net/sql/mysql-example/mysql-modulus.shtml -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
[snip] Jay, PHP is a WEB based Server Side scripting Language. Do not compare it with C. C is a middle -level System programming language. Please stop comparing. [/snip] The statements about C were not mine, it was just the way that the thread was snipped together. Believe me when I say that I know my languages, I have been in this business for 30 years and up until recently even maintained legacy Fortran code. But you are incorrect, PHP is not just a WEB based Server Side scripting language and like most languages it shares, and therefore can be compared to, traits with other languages like C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to get last record for each product
[snip] I have a list of product orders in a table with the following structure : OrderID ProductID OrderDate OrderCost What query would I need to get the last order for each productID ? [/snip] MAX(OrderDate) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to Php?
[snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
[snip] I merely wished to dispel the common, newb impression that PHP is the only realistic choice. [/snip] I don't think that is the impression but I think that the low barrier to entry and extensive support community make PHP a widely acceptable choice. I have seen many a newb turned off by the communities surrounding other languages (I have seen it in PHP too) but by and large the PHP community is pretty accepting and willing to teach young programmers how to fish. You always have to use the right tool for the job though. The question is are we teaching the inexperienced programmers what the right tools are? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Query Help
[snip] For the life of me I cannot remember how to make a query like this and what it is called. I know it is fairly basic though. Table 1 Product_id Product_Name Table 2 Category_id, Category_name Table 3 Product_id, Category_id Each product can have one or more categories. So I want a result that has Product A one category other category Product B other category [/snip] Can you give us an example of how you would like the output to be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creating a Data Dictionary
[snip]
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value
pairs
into one data and create an enum like that, then parse them later. I'm
just
wondering if there's a more elegant way to do this.
[/snip]
There is an enumerated type
http://dev.mysql.com/doc/refman/5.1/en/enum.html and there are data
dictionaries
http://dev.mysql.com/tech-resources/articles/mysql-datadictionary.html
but neither are really what you want.
Actually what you're describing the reason that we have databases in the
first place - the ability to have data in one column ('1') related to
data in another column ('one') in a record. The most elegant way of
using a database is to use it as it was designed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: query help
[snip] I have a table similar to this: - |transactions | |ID |DATE |EMPLOYEE| |234 |2010-01-05| 345| |328 |2010-04-05| 344| |239 |2010-01-10| 344| Is there a way to query such a table to give the days of the year that employee 344 did not have a transaction? [/snip] SELECT DATE FROM transactions WHERE EMPLOYEE != '344' GROUP BY DATE; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip]
I am looking for some guidance on creating a substring query. I have
a column that stores a path to a file. I would like to extract that
file extension and that is it and display it on my results. However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'. The only common they all have is the period
before the extension. Anyone created a nested substring query that
can do what I am looking to do?
[/snip]
From the manual -
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_su
bstr
SELECT SUBSTRING('myString', -3)
The result would be 'ing' in this case. Sub your string for myString
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip]
It may be a little more complicated then I made it out to be.
I am just trying to pull out the file extension but there were some
conditions I did not list.
[/snip]
Thank you for that update, would have been good to have from the start.
SELECT SUBSTRING_INDEX('my.doc','.',-1)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip] Here is what I came up with. select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from mydatabase group by MyColumn; That appears to yield what I need. I just need to filter out the results that do not have an extension. [/snip] You can exclude results that do not have a period in them if this is the only period -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Pivot Query in
[snip] Date, ProjectCode Building, Number of Copies I want to get a Connsolidate Report of Project Code RD STP 1007304--04---04 (Group by Project Code)(Sumtotal Building wise). I have tried to execute the following code : select pcode, building, sum(ncopies) from request group by pcode,building It is giving the following repott 1007304--RD--04 1007304-STP--05 Here there is a repetition of pcode, How can I avoid this. I want to get the Building Name on top as Column Names and bottom I should get the Count. [/snip] We would need to see some of the raw data to help you but this older article may point you in the right direction with crosstab (pivot) queries; http://www.evolt.org/node/26896 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: compare column value to anything in a list of values
[snip]
IN('value1','value2') should work for exact matches, also works for
integer values.
[/snip]
IN will not open and read his CSV file...
[snip]
Is there a simple function or method to compare a value in a column to
one or more items in a comma separated list?
[/snip]
In order to do this you are going to use a programming language or
scripting language. For PHP you could put the values from the CSV list
in an array and the use IN to compare against that array.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why doesn't mySQL stop a query when the browser tab is closedL
[snip] I just noticed a horrible thing. [/snip] Keep in mind that the query event is server side and is not tied to the browser (client side) once it has begun because of the statelessness of the connection. You would have to have some sort of onClose() event from the browser that would trigger a query cancellation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Virtualizing MySQL
[snip] Virtualization includes overhead. It is fine as long as your application can tolerate that, but if your performance demands grow there will be a point where a DB server in a virtual machine will cause trouble but the same HW as a real machine would still suffice. [/snip] We run MySQL in virtualized environments processing millions of records a day (virtual servers interact with our SAN for storage) and have actually enjoyed performance increases. We are also able to take advantage of advanced disaster recovery/business continuity options available to us in this kind of environment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex conditional statement during select
SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) = '20080101' AND SUBSTRING(myDate,1,10) = '20080131'), 1, 0) AS `January` FROM theTable GROUP BY theOther Throws this error... ERROR 1064 (42000): 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 ')), 1, 0) AS `January` Can I even do something like this during the SELECT. I tried a BETWEEN and while it did not throw errors it did not give back the expected data...I just got 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DESCRIBE temporary table
I am not finding a quick reference to this, but I wanted to DESCIBE a TEMPORARY TABLE so that I can make sure the index was properly applied. Can this not be done? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TreeView
[snip] how can i create a tree View From a mysql table? [/snip] First you get some leaves. oops, waitnevermind :) I STFW and found http://forums.devarticles.com/mysql-development-50/treeview-of-mysql-tab le-2963.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Im being dumb!
[snip] -Original Message- From: roger.maynard [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2008 7:33 AM To: mysql@lists.mysql.com Subject: Im being dumb! I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this? [/snip] Try this SELECT a.ID, a.Description1, b.Description2, c.Description3, d.Description4 FROM TableA a LEFT OUTER JOIN TableB b ON a.ID = b.ID LEFT OUTER JOIN TableC c ON a.ID = c.ID LEFT OUTER JOIN TableD d ON a.ID = d.ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip]
I had a bit of BFOTO and tried simple inserts.
mysql create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| f |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
[/snip]
The column type needs to be DATETIME.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip] The column type needs to be DATETIME. Thank you for pointing me at TIMESTAMP versus DATETIME. I'll read http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html thoroughly when I can. Can you give a little more detail as to why DATETIME is necessary? [/snip] It was much too quick a reply on my part but it is my understanding that a TIMESTAMP field is updated according to server time and you cannot actually insert a value. I may be wrong as I have never tested this. On the other hand a DATETIME field accepts inserts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] I'm new to mysql. I would like to issue a query from the command line and pass the result to an update done on the command line within the same script. See below. My question is how can I run a select from the command line and pass the values to an update SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] Start here http://dev.mysql.com/doc/refman/5.1/en/declare-local-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] And here http://dev.mysql.com/doc/refman/5.1/en/user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] Hello Jay. Thanks for your reply but where is your solution to my problem. I'm lost here. Help me -- please Thx -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 11:29 AM To: Brown, Charles; mysql@lists.mysql.com Subject: RE: executing query from the command line -- need help [snip] SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] And here http://dev.mysql.com/doc/refman/5.1/en/user-variables.html [/snip] Always reply to all so that this goes back to the list. I found this in my junk folder. When you retrieve the value from the first query assign that value to a variable which can then be used in the second query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sun and mysql
[snip] I am still amazed by the fact that youtube is worth 1.5 billion and MySQL AB barely 1 billion. Did they sell under price? Or does Google just have way to much many to spend/waste? [/snip] Or that Facebook is 'worth' multiple billions when they do not really have a way to make money yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Giant database vs unlimited databases
[snip] The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. [/snip] Not true and it has been proven time and again by the likes of Yahoo and others that size. We routinely use MySQL for large data stores (upwards of half a billion records in a single table) and with proper management we have performance equal to or better than the above mentioned products without the overhead required by either of those. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The value of NULL in Uniqued Columns
This is more of a philosophical issue than anything, but it has jumped up to bite us so I thought I'd make others aware; Since NULL has no value they can be entered multiply times into unique columns. Some will say that NULL is a value and therefore should be unique in this case (only one NULL allowed) and others will say that since NULL has no intrinsic value it can be entered into a unique column as many times as you would like. We have found this behavior in multiple database types (MS-SQL, Oracle) so it is not unique to MySQL, it is just where we noticed it. It is not one of those things that we thought aboutuntil now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY but disregard stop words
[snip] Is there any way to use ORDER BY in such a way as to have it ignore words such as the, a, an, and the like? [/snip] I haven't tested this but you might be able to do it with a little REGEX and a HAVING clause; SELECT REGEX(words) AS undesirable FROM table HAVING stuff undesirable -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query not returning Data
[snip] SELECT * FROM Sight_Hearing_Help WHERE 'type_help' = Eye Exam Glasses AND 'board_action_date' BETWEEN 07-01-2007 AND 12-31-2007 LIMIT 0 , 60; [/snip] Try WHERE type_help LIKE '%Eye Exam Glasses%' and look at your dates in the database themselves even if they are varchars, they are likely formatted -MM-DD. Remove the limit first to make sure you are returning data or make it more like LIMIT 60 first. SELECT * FROM Sight_Hearing_Help WHERE type_help LIKE '%Eye Exam Glasses' AND board_action_date BETWEEN '2007-07-01' AND '2007-12-31' LIMIT 0 , 60; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
funky characters in columns
I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;
mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
|.9040
++
(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.
For troubleshooting purposes, once I had narrowed down the problem
column I did the following
mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
| |
+--+
You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.
update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';
has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.
Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: funky characters in columns
[snip] Try: replace(replace(dealerLong, '\n', ''), '\r', '') [/snip] Didn't work, perhaps because they are hidden. I ended up taking the long road; update table set foo = replace(HEX(foo), '0D', ''); update table set foo = UNHEX(foo); HEX allowed me to see the carriage return (0D) and then use replace syntax to fix. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: csv to mysql
[snip] : i,m looking for a solution for my PDA that Doesn't have a DB Solution installed on it : so im having to write to CSV Files for my Forms , i'm needing a way that when i sink my PDA with my wireless network it Moves the Entire CSV File into a MySQL database :any Suggestions :? [/snip] LOAD DATA INFILE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple questio SQL
[snip] I have a Table and want to know the most visited products. Products - Id - Name - Visited [/snip] SELECT Id, Name, count(Visited) AS Total_Visits FROM product GROUP BY(Id) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: selecting everyting from 2 non-identical tables.
[snip] I have two non-identical tables. They are pretty similar except a few fields. I want to select everything from both for example table1 id name age table2 id name height I want id name height age even if it returns null values. select * from table1, table2 seems to give repeat rows for some reason. [/snip] Use a left outer join, assuming that 'name' is the same in both; SELECT t1.id, t1.name, t1.age, t2.height FROM table1 t1 LEFT OUTER JOIN table2 t2 ON(t1.name = t2.name) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting by a list of possible results in a column....
[snip] I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? [/snip] You can specify ORDER BY foo DESC or ASC and you can do multiple ORDER BY's SELECT * FROM table ORDER BY foo, bar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure not working in legacy ASP
[snip] The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? [/snip] You need a while loop. Does the SP work from the command line properly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [X-POST] Fastest way to dump this huge table
[snip] I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. This is not on my server, and it's in production, so I don't want to risk testing different methods and possibly hanging up their server for a period of time, so I wanted to seek advice here first to find what's the best way to proceed. I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. [/snip] SELECT * INTO OUTFILE /directory/myfile.csv FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: secure port 3306
[snip] I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? [/snip] IPSec tunnel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
[snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet - SOLVED
[snip] [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. [/snip] select store.storeid, store.stname from store where store.storeid not in ( select transaction.storeid from transaction where substring(transaction.created, 1, 10) date_sub(current_date(), interval 1 day) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to tell if something hasn't happened yet
Good day gurus and gurettes! I have a table; | transactionid | int(11) | NO | PRI | | auto_increment | | username | varchar(32) | NO | | || | storeid | varchar(6) | NO | | || | action| int(4) | NO | | || | code | int(2) | NO | | || | ipAddr| varchar(32) | NO | | || | created | datetime| NO | MUL | || | created_by| varchar(32) | NO | | || I used to have a query (I have misplaced it somehow) where I could tell which storied had not logged in (created) today yet. No matter how hard I try I cannot remember the query. What I need is a query that will tell me at any given point during the day which storeid is not online (created). I do have a sister table where all of the storeid's are, so the join happens there. I can test created for IS NULL but it does not limit the query to today. select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' group by store.storeid; How can I limit this to today only without having to hard code a date into the query? TVMIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting a file in MySQL
[snip]
How do i insert a file in a blob field from the command line ?
[/snip]
From http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
mysql UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with INSERT
[snip]
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 's
resources than dual monitor mode to generate the video output. Running
in dual' at line 2
when I try to add:
Note : Single-monitor mode uses more of your computer's resources than
dual monitor mode to generate the video output. Running in dual monitor
mode is recommended because it is more efficient.
My PHP code to add Q A is:
mysql_query(INSERT INTO faqs
(Question, Answer, Category, Date) VALUES('$question', '$answer',
'$category', CURDATE() ) )
or die(mysql_error());
[/snip]
You need to escape your $question and $answer variables, the
apostrophe's contained there are goofing you up.
http://www.php.net/mysqlrealescapestring
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Primary field do I need one
[snip] I'm working through (my first day) PHP MySQL for Dummies and I'm stuck on the primary field for my database. I want to create a racing pigeon results database that has 6 fields only. The results will be imported into the database in bulk from a CSV file Owner - GBring - Ering - Arrivedtime - date - position If I'm not wrong, surely whichever one of the first 3 fields I create as a primary field will stop me entering any races after the first one, because every race thereafter will always for the most part be the same owners and rings. So do I have to have a primary key? [/snip] You do not, but it is bad database design. The simplest (but not necessarily best) thing to do is add an auto-increment column and declare it as your primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: leading the technological bandwagon
[snip] I recently last week Had and experience with an Very small Company, where as they had around 15 Machines all hooked Star topology and a central iis ASP Web server that only showed the date, and a few small utilities when addressed To it, now there entire Operation was based upon Excel, everything not joking I mean everything, was a file share to an Folder (around 90 Folders) in which they had probably 200 + excel sheets in each one u name something u need to do in business I guarantee there was a excel sheet for it, repetitive sheets for Different Operations, all Sheets linked to one another thru references in excel, excel97 Mind u, I was there on a Consulting call, to inform them of a better way to update / Operate there business They ended up saying they liked there Current way better it makes more sence to them and Only wanted me to link 10 more sheets to what they already had and add a few more file shares, of Course I refused this Project and walked out Laughing my ass off in the car [/snip] Seriously? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.24/592 - Release Date: 12/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mode()?
[snip] Is it possible for mysql to return the mathmatical mode of a record set? (the record that occurs most often) [/snip] yes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: finding NULL records
[snip] And the reason for that is because nothing is actually equal to NULL. For a field to be equal to NULL it would actually have to contain NULL, in which case it would not be actually NULL. What? NULL doesn't equal NULL because NULL means unknown. A column can have 2 states: known or unknown (NULL). If the case of a known state, the column has a value, in cause of unknown, it's NULL. You cannot compare to unknown. So this is why the SQL standard says: you either ask for a specific value (eg: myid = 1002) or you ask for a state: myid IS NULL or myid IS NOT NULL (= filled). It has nothing to do with contain NULL or whatever. [/snip] Philosophically this has been the argument concerning NULL for several decades when concerned with data operations. So Martin, you are correct here NULL is unknown and lacks state. Nothing can be equal to (=) something that lacks state, you can only check to see if state does or does not exist. Many more youthful database users do not fully understand NULL and its use. Some actually think (as appeared to be the case with the OP here) that the field contains a value of NULL. As you stated, a value has state and if the field has state it is certainly not NULL. 'IS NULL' and '= NULL' are two very different things. Sure I was being a little cheeky with my response, I should have taken the time to more carefully explain NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: finding NULL records
[snip] I am trying to find records where the value of a filed is NULL. I know that there are records that have null values but the result is always an empty set. eg: select test_id from tests where test_id=NULL always returns an empty set when there are in fact records that have a null value for test_id. Is there some trick to finding null valued records in MySQL? This same sql has always worked on any other dbms I have used. [/snip] Of course this will return an empty set because you have only selected the test_id, try this; SELECT * FROM tests WHERE test_id IS NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: finding NULL records
[snip] Alan Nilsson wrote: On Oct 30, 2006, at 3:27 PM, Jay Blanchard wrote: [snip] I am trying to find records where the value of a filed is NULL. I know that there are records that have null values but the result is always an empty set. eg: select test_id from tests where test_id=NULL always returns an empty set when there are in fact records that have a null value for test_id. Is there some trick to finding null valued records in MySQL? This same sql has always worked on any other dbms I have used. [/snip] Of course this will return an empty set because you have only selected the test_id, try this; SELECT * FROM tests WHERE test_id IS NULL Yes, that works, but I was also trying SELECT * instead of just the key field (just a typo in the example). The problem was in the equal sign versus the 'IS' operator. Any reason why MySQL does not honor field=NULL? Seems kind of odd. Sql standard says you use IS NULL. [/snip] And the reason for that is because nothing is actually equal to NULL. For a field to be equal to NULL it would actually have to contain NULL, in which case it would not be actually NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Doing sum's if certain conditions are true
[snip]
+---+--+---+-+--
+-+
| code | bin | min | ain |
cin | dur |
+---+--+---+-+--
+-+
| NONE | 103939170759 | 485089817 | 3739.1827 |
27797297 | 11681839027 |
Now, what i need todo is exclude certain info from the above NONE entry
if
code2 is equal to something. So for example (in php terminology):
if(code == 'NONE') {
if(code2 == 'DENIED') { continue; }
else {
bin += bytes;
if(bytes min) { min = bytes; }
cin++;
dur += dur;
}
}
after that i could work out the average by dividing bin / cin for what
in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know
mysql
has an if() statement, but I have no idea how to implement it using what
i
want to achieve above.
[/snip]
Basically;
SELECT SUM(IF(`code` = 'NONE', calculation, 0)) AS `ain` FROM `table`
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculation distances
[snip]
we are working on a small project which needs to calculate the closest
distances from an address and/or zipcode entered into a search box. It
will read from a MySQL database of companies, which store their address
and zipcodes.
looking for the best way to approach this. I've seen some zipcode Perl
modules on Cpan, but nothing for helping calculation distances. Can
someone point me in the right direction to accomplish this ... thx's :)
[/snip]
We have started storing latitude and longitude coordinates on our
databases which lends itself to distance calculations. For example, here
is a PHP function which performs the distance calculation
function distance($lat1, $lon1, $lat2, $lon2) {
$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +
cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
return $miles;
}
You may be able to do it entirely in a query by utilizing MySQL's math
functions;
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tables/sum
[snip] Can anyone tell me how to add up values in different tables? For example Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) = n [/snip] SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id = n -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tables/sum
[snip] Can anyone tell me how to add up values in different tables? For example Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) =3D n [/snip] SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id =3D n The above doesn't work I'm afraid. [/snip] Don't be afraid, you must join the tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tables/sum
[snip] [snip] Can anyone tell me how to add up values in different tables? For example Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) =3D n [/snip] SELECT t1.c1 + t2.c2 + t3.c3 FROM table WHERE id =3D n The above doesn't work I'm afraid. [/snip] Don't be afraid, you must join the tables and there must be a matching key in each table. http://www.mysql.com/select -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving Image in Database [again]
[snip] Was wondering if I could get a conversation started on the pros/cons of database storage verse filesystem [/snip] There is additional overhead in saving and retrieving images from a database typically. Most folks benchmark to see which is faster and more efficient on their systems. YMMV. BTW, this is a holy war of immense proportions and has been fought on many a list. If you will search MySQL lists archives, PHP list archives, JAVA archives, etc. you will see many an argument for/against/indifferent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: operation with alias
[snip] i have a question, i want to do something like this select 10 as a, 1 as b, (a+b) as c; im want to get something like this a | b | c - 10 | 1 | 11 how can i do this... i want to do that becouse i get a big value from a sub big subquery, so i don't want to make again the subquery... [/snip] http://www.mysql.com/prepare -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: File (xls, csv, txt) to MySQL
[snip] If I have a file that is in a spreadsheet format, how can I dump that into a MySQL database, using PHP MyAdmin? [/snip] Using a LOAD DATA INFILE query. http://www.google.com/search?hl=enlr=q=csv+to+mysql+phpmyadmin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rand()
[snip] I´ve got a page where a ought to get 20 registers in ramdom order but i want to display it in an alphabetical order. Someone knows if there is a way to get that 20 random registers in alphabetical order? [/snip] SORT BY `registers` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rand()
[snip] Excuse me, but i don´t understand your answer. Could you explain it? [/snip] Add it to the end of your query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dynamic link
[snip] Say I have two regular tables (table1, table2) and what a column (status) in the second table to update when it changes in table1. For example, if I set the status for a user in table1 to 0, the status for all that user's records in table2 dynamically changes to 0. Can this be done? What method is used? [/snip] http://www.mysql.com/trigger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Math problem
[snip] It appears that mysys 4.1 does not know how to multiply a dollar amount to another number. Has anyone else seen this problem? [/snip] Your price column contains a dollar sign, making it a text field that you cannot multiply with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Two approaches, which one is better ?
[snip] I am facing some problems to identify the advantages or disadvantages of the use of INDEXes (a.k.a. VIEWS in other DBMS environments, please correct me if am wrong), this is the scenario: [/snip] index != view AFAIK An index on a column or columns is a method for the database to keep track of and speed access to data that utilizes the index. http://dev.mysql.com/doc/refman/5.0/en/create-index.html A view is a pseudo-table based on a query or set of queries. Consider a query that selects 3 columns from a table that contains 16 columns. You now have a particular 'view' of that tablein the form of a table. http://dev.mysql.com/doc/refman/5.1/en/create-view.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions in AS
[snip]
SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(), 1, 0)) AS ',
char(39),
CURDATE(),
CHAR(39),
' FROM tablename GROUP BY group by psDealer' );
PREPARE stmt FROM @sql;
[/snip]
Very clever Peter! Thanks for your help on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How To Pronounce MySQL
[snip] This may be a really stupid question, but I hate looking stupid if I can avoid it. :-) I have been using Microsoft SQL Server for a while, and I'm now trying to switch all our applications over to use MySQL. Microsoft SQL Server is pronounced Sequel Server. Is MySQL pronounced My Sequel, or is it pronounced My S-Q-L? I mean, generally speaking? [/snip] ** POTENTIAL HOLY WAR ALERT! ** We flip back and forth here, dependent on how fast the conversation is going. Pure database guys want everyone to say, ess que ell, recently though I have even heard a lot of them saying, sequel. YMMV. ** END ALERT! ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How To Pronounce MySQL
[snip] The official way to pronounce MySQL is My Ess Que Ell (not my sequel), but we don't mind if you pronounce it as my sequel or in some other localized way. [/snip] [localized way] The best darned database since the dawn of computing. [/localized way] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
functions in AS
select psDealerID, sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day), 1, 0)) from provision group by psDealerID I love crosstab queries, but one thing really eats at me. I'd like to be able to add significance to the AS with a function. So that the return would look something like; ++++ | psDealerID | 2006-06-07 | 2006-06-06 | ++++ || 0 | 4 | | 301AA | 0 | 0 | | 301AB | 2 | 0 | | 302AA | 0 | 0 | | 303AA | 0 | 1 | We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? I have RTFM and STFW...but to no avail. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions in AS
[snip] We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? How about PREPARE? [/snip] PREPARE treats SELECT statements the same, unless I am missing something. I have done some testing, and have been able to obtain the desired results. Even the simplest example; PREPARE stmt1 FROM 'SELECT curdate() AS ?'; SET @a = curdate(); EXECUTE stmt1 USING @a; Gives a syntax error on the PREPARE statement since AS cannot be a function. Perhaps there is something to a prepare that I should be more aware of? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Passwords in Mysql5.x
[snip] what type of password algorithum does mysql 5.x uses for encrypting passwords? and how does these algorithum keeps the password in secure. [/snip] Here is some helpful info; http://www.mysql.com/search/?q=password+securitycharset= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XML - DB Conversion
[snip] I'm currently looking for a tool that will take XML and produce a database from it. Not really looking for anything in particular at this point, just sort of scoping around. Anyone know of such a tool? [/snip] MySQL kinda' has a built-in tool http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int? [/snip] It does not clearly indicate that the rot fields are stored as INTs, far from it. You cannot 'give' an INT to a DOUBLE as that it is incorrect. Anytime you have strongly typed variables you must use them as they are typed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? [/snip] Do a describe on the table and make sure that they are INTs, then show us the update statement. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. [/snip] Do an update straight to the database and show us that without Java code. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... [/snip] May I suggest echoing your query out so that you can see what it is trying to insert? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Limitations??
[snip] I need to store something like a couple of million rows is a MySql table. Is that ok or do I have to split them up. I intend to index each of the columns that I will need to access so as to speed up access. Insertion will be done only when there is very little or no load on the server and time for this is not really a factor. I also do not have any constraints on disk space. Please let me know if I can just use MySql as it is or if I need to make some changes [/snip] I have databases with half a billion records (several Gb's in size) and have no real problems with performance. I am running dual processor servers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: set DEC as a column name : forbidden
[snip] It seems that the reason is that DEC is a keyword standing for decimal. I do not understand why this cannot be allowed for a column name. [/snip] There are several reserved keywords in MySQL, none of which are recommended for column names. You can try the SQL statement using backticks around the proposed column name to see if that will work, but you may have conflicts later. Of course, you could always call the column by its full name, declination. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is This A Redundant Info Example?
[snip] Why have customer info in both? Delivery and Billing info makes sense, but why the redundant info in both? Anyone got views on this? Do/would you do it differently, and could you tell us why? Cheers. [/snip] It is bad database design IMHO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is This A Redundant Info Example?
[snip] Well, one reason could be, for example, that the address changes over time and they want to know what address the customer was when the order was processed. [/snip] So you would delete an old customer address in favor of a new one? I would rather have an 'active/inactive' column with an accompanying date stamp. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
[snip] I procured myself some zip/lat/long databases from various places. Then I noticed that for the same zip code, I got different values in different databases??!!! [/snip] Latitudes and longitudes are often represented based on their location relative to the equator and the prime meridian; | pos lat | pos lat neg long | pos long | | --- | neg lat | neg lat neg long | pos long | | Sometimes the coordinates are based on positive distances (non-standard lat/long) from the prim meridian and equator, yielding a pai of positive numbers for each coordinate that would not be the same numbers as given by the standard system. Google Maps understands the standard system well, I do not know if it understands the non-standard system -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
[snip] Thanks for the graph. So are you saying that I should use the database that has the negative values, and not the one that uses positive values? I'm in the USA. I don't care about anywhere else (for my location needs that is). [/snip] Yes, that would be using the proper notation for lat and long. To see it in action take a look at http://maps.google.com. Enter an address with a city, state and zip code (such as your own) and then click 'Link to This Page'. Look at the URL and you will see properly notated coordinates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE doesn'r works
[snip] The php and db structure and data are attacheds. Two infos: 1) Im not a expert 2) The code is in the beggining of the development, so are too many uglyness yet =) [/snip] Find a place online to post your code or paste it into the message (only the relevant parts). Usually no one will open an attachment on a mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE doesn'r works
[snip] Find a place online to post your code or paste it into the message (only the relevant parts). Usually no one will open an attachment on a mailing list. [/snip] Post the line of PHP where this query exists. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE doesn't work
[snip] Maybe it a bug of the php's mysql API? [/snip] Nope, I do this all of the time. Head on over to the PHP list and post your code and see what they say. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Working out Square Footage with Feet and Inches
[snip] I have measurements of rooms stored in a table for each house in feet and inches, does anyone know how can I work out the square footage? [/snip] Convert to inches, multiply length * width, divide by 144 (one square foot) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Working out Square Footage with Feet and Inches
[snip] I read this as a SQL syntax question, not a math word problem. As in SELECT ..., (some expression equaling sq ft) AS sqft... [/snip] Cool, then do it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to initiate a prog. via mysql
[snip] I need to start a prog. as soon as a particular field in the mySQL 4.x is changed. ie. when in Flag table if_flag is set to '1' i need to start a script. The script will be in PHP. Pl. tell me how to do so on mySQL 4.x. [/snip] Triggers are not available in 4.x, you will need 5.x -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE doesn't work
[snip] UPDATE command is not working for only the record that I want, but for all in the table. I sees WHERE is not interpretated by Mysql server. At least the result is identic to it. For example, If my php execute UPDATE clientes SET tipo='r', nome_fantasia='cc', estado='24' WHERE id = '5' on the server, I have all records in the table clientes updated for these values. But, if I copy exactily this command from php output and execute in the Mysql server directly, via some mysql client, it updates only record whose id = '5'. PHP Version: 4.4.2, Mysql client API version: 3.23.49. For windows servers or Linux. Some Hint? [/snip] More of a PHP question. Echo the UPDATE statement to the browser so that you can see what it looks like. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Form value editing
[snip] can someone please just send me or post a example of editing feilds of a mysql database within an html form and then updating the values thanks alot here is a small database u can use i can expand on your example as needed [/snip] PHP question; When you click on the form submission button you must issue an update query. Here is a Google link http://www.google.com/search?hl=enq=update+mysql+database+PHP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
[snip] I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? [/snip] You'd have to have CD's for each OS on which you'd like to run. You can test this by putting the Apache executable (or one of the other executables) on a CD and trying to run it. http://www.google.com/search?hl=enq=run+apache+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
[snip] I have no idea if this is possible or not but is there a way to run Apache, PHP, and MySQL from a CD? I'd like it to be possible to run it on Windows, Mac OSX and *nix. If it is possible could someone point me in the right direction? [/snip] Yippee, cross-posting! http://www.google.com/search?hl=enlr=q=run+MySQL+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
[snip] 1. Please always reply to the List. Who runs this list? Could it please be configured to send replies back to the list rather than the individual? It's really annoying to keep ending up with a personal address - it would make things so much easier, and is, to my knowledge, standard practice for mailing lists to have replies automatically go to the list itself. [/snip] This is an un-moderated list and this little battle has been fought. Just hit Reply-all or whatever your e-mail client allows. Many lists (many, many older lists especially) are set up just like this one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
[snip] Yes this battle has been fought before. But this is still a pain in the ass. [/snip] Once you get in the habit your ass will hurt a little less. I know that there are mysql-ites who 'monitor' the list, maybe they can ease your pain. [snip] How many times has someone had their problem solved by someone who accidentally emailed them direct, rather than via the list. So the solution was never seen by anyone else and never made the archives. Hence the same question gets asked again... and again... [/snip] ROFLMMFAO! You mean that people actually STFA? And they don't top post either! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
[snip] However, if the subject has been addressed and the decision made, then there's just no point in this topic. I guess those of us that don't like it, or don't like people inadvertently posting personal replies thanks to that decision, should simply find another list. [/snip] Either that or learn how to move their cursor 1 to the right before they click. Have you STFA for this? Again, Alec points out that there was probably good reason for setting it up like this. Again, I will point out that several are like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
[snip] I accept that this was probably done for a reason, and that other lists work this way (though I've never seen one), so maybe I'd feel better if someone could actually tell me a reason why it is better this way? [/snip] Here is the answer; http://www.unicom.com/pw/reply-to-harmful.html Also read this; http://www.caliburn.nl/topposting.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I get the first and the last day of month
[snip] How can I get the first and the last day of the month? [/snip] http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html last_day() for last day of the month. The first day is always the first. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; [/snip] Use variables; http://dev.mysql.com/doc/refman/5.1/en/set-statement.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. [/snip] More http://dev.mysql.com/doc/refman/5.1/en/example-user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Summing Collums
[snip] i wanna be able to sum a colum in a mysql database and display the sum on the page field name Bondrem so if someone adds to the database the bondremaining colom would add together and show the total on the page record1 - [bondrem]=100 record2 - [bondrem]=450 total bondrem on page would show 550 can anyone help me [/snip] Yes, someone can help you. SELECT SUM(`column`) AS total FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error on T_echo ?? what is this ?
[snip]
i,m getting the following error on my MySQL Code inserting data into a
database, .?
*Parse error*: parse error, unexpected T_ECHO in
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
LINE 30 is echo
Query Finished;
--here is ALL my code --
?
//--php/Mysql Code by Brian E Boothe //
//throw data from form into MySQL database routine //
//initilize Adddatta to mysql database, //
//if($_POST['submit']) //If submit is hit
//{
//then connect as user
//change user and password to your mySQL name and password
mysql_connect(localhost,root,goobers);
//select which database you want to edit
mysql_select_db(test);
//convert all the posts to variables:
$value1 = $_POST['value1'];
$value2 = $_POST['value2'];
$sumfield = $_POST['sumfield'];
//Insert the values into the correct database with the right
fields
//mysql table = news
//table columns = id, title, message, who, date, time
//post variables = $title, $message, '$who, $date, $time
// $result=MYSQL_QUERY(INSERT INTO orders
(id,title,message,who,date,time).
$result=MYSQL_QUERY(INSERT INTO addvalue (`value1`, `value2`,
`sumfeild`).
VALUES ('$value1', '$value2', '$sumfield')
//INSERT INTO `orders` (`OrderNo`, `CompanyName`, `BillingAddress`,
`City`, `StateOrProvince`, `PostalCode`, `PhoneNumber`, `FaxNumber`,
`WebPage`, `ContactFirstName`, `ContactLastName`, `EmailAddress`,
`Notes`, `Customer`, `Startdate`, `Completedate`, `Biddate`,
`Bidamount`, `ProjectInfo`, `ElecProjCost`, `ElecProjBill`, `ElecRem`,
`CtrlProjCost`, `CtrlProjBill`, `CtrlRem`, `OthrProjCost`,
`OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, `BondRem`)
//confirm
echo Query Finished;
?
It is a PHP error, not MySQL. You didn't end your line (;) beginning
with VALUES(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
