RE: How to delete records after particular position ?
I believe the syntax is Delete from MyTable Where MyCriteria LIMIT HowMany. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Manisha Sathe [mailto:[EMAIL PROTECTED] Sent: Monday, August 23, 2004 6:08 AM To: [EMAIL PROTECTED] Subject: How to delete records after particular position ? Hi, Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 But i am getting erorr. pls can anybody let me know the exact syntax for this ? regards Manisha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat fields
Good morning to all. How can I combine the elements from many text fields into one text field. I have a table. ID(auto inc) eventID data ID is unique eventID is repeated data represents what happened at this event. I would like to return all of the data for a given event ID, sorted by ID. Thank you. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE Concat fields
Thank you for your response, Egor. My question is whether or not it is possible to concatenate the fields without knowing the event ID. pseudo SQL Select concat(data from all grouped fields) from foo group by (eventID); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concat fields
If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Connections
Good morning to all. I am using the mySQL connector to connect to the mySQL database. My question concerns the session between the DB and the client. How is the session maintained? In other words, how does the DB know that it is communicating with my specific connection? Here is why I ask. In a test under JAVA I create 2 different Connection objects. I verify that they are not the same Java object. I looked at the code for my driver and at it's heart, when I call for a new connection the code looks like it creates a new one and doesn't reuse an existing one. I'm not positive about this yet. If I create these two supposed connections and insert into a table with one, both connections read the same last_insert_ID() value. This tells me that the DB is treating my connections as the same connection. I need to know if the problem is that MySQL is caching and reusing any connection from my client OR if the problem is with my driver. Thank you for insights. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple Connections
So this means that my DB driver is somehow using the same connection when I ask it to create 2 different ones? -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 12:13 PM To: [EMAIL PROTECTED] Subject: Re: Multiple Connections Paul McNeil [EMAIL PROTECTED] wrote: This tells me that the DB is treating my connections as the same connection. I need to know if the problem is that MySQL is caching and reusing any connection from my client OR if the problem is with my driver. For each connection a new thread is created and this thread has every connection-dependent variables set to their default values. I.e. LAST_INSERT_ID() in a new thread will not return you a value from other. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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]
Unique Key question
Good morning. I have a table with field name DATA type tinytext. The table is already populated. I need DATA to be unique. It seems that MySQL doesn't look at the uniqueness of a field in a binary fashion. Example (chosen because I think it is just plain odd) june = Júne If I query as - Select * from myTable where DATA = 'june' - I am returned both. If I query as Select * from myTable where cast(DATA as binary) = 'june' - I am returned only one. How can I set this table so that the unique key is based on the binary value of the field? Do I have to alter the field type or is there a better way? Thank you. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique Key
Thanks to all for helping me sort this out. I am setting the field to tinyblob vs tinytext. Thanks again. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil research Sniffer Anti Spam Sortmonster Research GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very strange database behavior
Good morning to all. I have a very strange problem in my database and am trying to track down whether it is a JAVA problem or a mySQL problem. I have a table that is supposed to accept only unique data, however, at this time, the PK for that information is set to the auto incrementing ID field. The unique field is called 'pat'. Here is a description of the problem. I have a class that posts data to the pat field using java preparedStatements. The statement executes like this // test that the insert is unique SELECT ID from table where pat = 'MyValue'; // If that comes back with an ID, no insert is made // No ID means we insert. if(NoIdReturned){ INSERT INTO table(pat) Values('MyValue'); // I have added a test here to insure that the // data was inserted // I perform the same query as above // This is not in the production code. SELECT ID from table where pat = 'MyValue'; // In tests this comes back with a value every time. // The production code gets the last_insert_id() Select last_insert_id() as myID; // In tests this value and the one above always match. } OK. Here's the oddity, you would think that if I run this class again with the same parameter for MyValue, the flow would be... test for MyValue.. test returns IDExists No insert happens. That's not what is happening. If I run the class... once - Insert happens twice - Insert happens 3 times - ID is found and no insert happens. Oddity #2 - In test 2, the first query returns no ID. After the insert I ask for last insert ID and get 1234. I ask for the ID using the SAME query that returned me NO ID and I get 1233, the ID of the first insert. I have tested this as many ways as I can think. The insert is NOT a delayed insert. I have tried spacing out the tests. I have checked the DB using MySQLCC AND using the query that my test prints to screen and the first insert is always happening. Below are the actual test results from run#2. // Query to see if the data exists. SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; Identity is -1 because no rows exist. AUTO_DOGGY Identity was -1 // Insert the rule Inserting new rule.AUTO_DOGGY // test Testing for AUTO_DOGGY // Same query as above SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; // Below LIID = Last_Insert_ID() // Query ID is what is returned from query LIID = 124566 Query ID = 124565 HELP! God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange database behavior
Thanks for the response. There is currently no primary key on the field so I am relying on the data being there, or not being there, to decide whether it is safe to insert the new data. Through MySQLCC, have_query_cache = YES. Is this the variable I am looking for? What affect would this have? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 11:49 AM To: 'Paul McNeil '; 'MySQL General ' Subject: RE: Very strange database behavior Are you saying that you are not receiving an Unique key violation when you enter in duplicate data or you are relying on a value being present? If you are using the later, do you have query caching turned on? -Original Message- From: Paul McNeil To: MySQL General Sent: 7/7/04 10:43 AM Subject: Very strange database behavior Good morning to all. I have a very strange problem in my database and am trying to track down whether it is a JAVA problem or a mySQL problem. I have a table that is supposed to accept only unique data, however, at this time, the PK for that information is set to the auto incrementing ID field. The unique field is called 'pat'. Here is a description of the problem. I have a class that posts data to the pat field using java preparedStatements. The statement executes like this // test that the insert is unique SELECT ID from table where pat = 'MyValue'; // If that comes back with an ID, no insert is made // No ID means we insert. if(NoIdReturned){ INSERT INTO table(pat) Values('MyValue'); // I have added a test here to insure that the // data was inserted // I perform the same query as above // This is not in the production code. SELECT ID from table where pat = 'MyValue'; // In tests this comes back with a value every time. // The production code gets the last_insert_id() Select last_insert_id() as myID; // In tests this value and the one above always match. } OK. Here's the oddity, you would think that if I run this class again with the same parameter for MyValue, the flow would be... test for MyValue.. test returns IDExists No insert happens. That's not what is happening. If I run the class... once - Insert happens twice - Insert happens 3 times - ID is found and no insert happens. Oddity #2 - In test 2, the first query returns no ID. After the insert I ask for last insert ID and get 1234. I ask for the ID using the SAME query that returned me NO ID and I get 1233, the ID of the first insert. I have tested this as many ways as I can think. The insert is NOT a delayed insert. I have tried spacing out the tests. I have checked the DB using MySQLCC AND using the query that my test prints to screen and the first insert is always happening. Below are the actual test results from run#2. // Query to see if the data exists. SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; Identity is -1 because no rows exist. AUTO_DOGGY Identity was -1 // Insert the rule Inserting new rule.AUTO_DOGGY // test Testing for AUTO_DOGGY // Same query as above SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; // Below LIID = Last_Insert_ID() // Query ID is what is returned from query LIID = 124566 Query ID = 124565 HELP! God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- 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-strange DB behavior
Thanks to all who posted help. I found my error, in my code. Sad sad pumpkin. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Temporary Table, incorrect rows
Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Table update
I added a LIMIT clause to my create table command and now all data is present. Is the 1000 row limit a standard that must always be overridden? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Thanks. That is a much better way to find dupes. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:11 AM To: Paul McNeil; MySQL General Subject: Re: Create Temporary Table, incorrect rows A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Yes I am using mysqlcc. Is that why the 1000 row limit? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:05 AM To: [EMAIL PROTECTED] Subject: Re: Create Temporary Table, incorrect rows On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- 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: connectorJ huge table problem
Dude. I wouldn't post your server ip here. ouch. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Leonardo Francalanci [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 8:55 AM To: 'Mysql ' Subject: R: connectorJ huge table problem Are you using a streaming connection? ehm... how do I know? My conn string is jdbc:mysql://192.168.0.253:3000/LETSPARTY1?autoReconnect=yes where 192.168.0.253 is a machine on my LAN... -- 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 problem
Good morning. Not knowing too much about PHP it looks like you are searching for `name`,`colour`,`gender`,`breed`,`location`,`description` Where there is a whitespace in the name. Could you use, $query_results = sprintf(SELECT * FROM dogslost WHERE `name` LIKE '% %', $crit_results); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, June 21, 2004 5:08 AM To: [EMAIL PROTECTED] Subject: Re: Full text search problem Pieter, I think FTS minimum WORD size is 4 characters - you may to be searching with 3 on 'May May'. Not having ever used FTS; I believe you can adjust it to count 3-character words by changing the configuration, but I'm not sure where - and it would then need re-indexing, if I'm not mistaken. Hope that helps Terry --Original Message- Hi I have a fulltext search on a dbase for lost pets. My problem is the following: I have dog in the database called May May which doesnt show up in the search results. A dog called Doggy Doggy does show up however. I guess the problem is that MySql sees May May as being a date or something and doesnt do a text compare. Here is my query, from php. $query_results = sprintf(SELECT * FROM dogslost WHERE MATCH (`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST ('%s' IN BOOLEAN MODE), $crit_results); any ideas? Regards Pieter -- 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: Group Query
You can use a Left Outer Join. Left Outer Join will include all that matches as well as that which doesn't. The resulting NULL entries for the count will evaluate to 0. SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID GROUP BY(U.User_Location); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 7:54 AM To: [EMAIL PROTECTED] Subject: Group Query Hi, The following table produces a query that shows all bookings that user has made and groups the number of bookings by the users location code. mysql SELECT COUNT(B.Booking_ID), User_Location FROM Bookings B, Users U WHERE U.User_ID = B.User_ID GROUP BY(U.User_Location); At the momnet if no bookings are made by a particular user then their location isnt included in the result. Is it possible to modify this query so that if no bookings are made for a particlar location then the location is still included in the result i.e. +-+---+ | COUNT(B.Booking_ID) | User_Location | +-+---+ | 1 | 01| | 8 | 02 | | 9 | 03 | | 1 | 04 | | 1 | 05 | | 2 | 06 | | 1 | 07 | ... Many thanks for your help _ It's fast, it's easy and it's free. Get MSN Messenger today! 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: MySQL SQL Case Stetement
I have used the CASE statement for ordering many times. It's very useful... SELECT myDATA, CASE WHEN data2 = SomeValue THEN 0 ELSE WHEN data2 = SomeOtherValue THEN 1 ELSE 2 END AS mySort from MyTable Where myConstraints. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Rafi Sheikh [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 12:21 PM To: '[EMAIL PROTECTED]' Subject: MySQL SQL Case Stetement Hi list. Can someone give me a working example of a SQL CASE statement(in SELECT)? I have examples for stored procedures, but none for use within the DML side of SQL. I am trying to in my SELECT (used in PHP) create a variable/col for example: status='high' where value in a other col is withinin certain range. I am sure that I do not have the order of the syntax or complete code. I am using MySQL 4.0.17, and a newbie. Any simple example will help. Please note I am not requesting for use within a procedure or trigger, etc, just a simple DML side SELECT. TIA RS This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- 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: DBI and last_insert_id()
You wrote $pk = $dbh-last_insert_id(); I believe you can get the created ID with the query, Select @@IDENTITY; This returns the last created ID from the connector object. Peace -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBI and last_insert_id()
last_insert_id() should work. You are updating using @bind_values. What is the text of that? Does it indeed create a record? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 15, 2004 12:42 AM To: [EMAIL PROTECTED] Subject: DBI and last_insert_id() Hi all. I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's last_insert_id() function. I have so far: eval { $sth-execute (@bind_values) || die $dbh-errstr; }; # If the above failed, there will be something in the special variable $@ if ($@) { # Dialog explaining error... my $dialog = msgbox( $prospects-get_widget(Prospects), Error updating recordset!, Database Server says:\n . $dbh-errstr, 1 ); $dialog-run; $dialog-destroy; warn Error updating recordset:[EMAIL PROTECTED] . $@ . \n\n; return 0; } $pk = $dbh-last_insert_id(); The statement executes successfully, and the data is inserted. However the above line that fetches the last_insert_id value from MySQL always returns undef. The table has an auto_increment column. What's going on? Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Queries
RE - I have a situation where, within a PHP loop, I am creating over one hundred SQL statement (INSERTS and UPDATES). Would it cut any overhead to send 20 ; seperated queries in one mysql_query() statement, and therefore only execute mysql_query() 5 times for 100 statements, as opposed to sending each statement individually? -- I know that it is possible to send multiple update / insert statements. However, it was my understanding that this is not normal SQL. I am using the Opta driver and it fails when I try to do this. I have successfully done it in MS-SQL. I would test this thoroughly and make sure that it's not going to fail if the DB driver of DB version is swapped out from under the code. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oops
In my last post I noted that I am using Opta driver. Wrong. That was my Ms SQL driver. I am using mysql-connector-java-2.0.14-bin Sorry for confusion. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Last Modified
If your table contains a timestamp field, it will update each time the row is altered. Otherwise I don't think it's possible. You Wrote - Hello I am using PHP, MySQL. How would I query MySQL to display the last time a table was last modified/changed/or updated. For example I have a user that updates a stats page via the web, and I would like it to say (Current up to The last date the stats table was modified). Thanks for your help, I've been looking online as well but thought this may be quicker. Take care. Thanks again. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Last Modified
---I see the type timestamp. Should I leave value, attributes...blank. Do I just query this field and format as need via DATE()? --- Yes. This field is a date actually. It will update each time the record is modified or when a record is created. You can query it as a normal datetime field. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update problem
You have to use UPDATE MyTABLE set MyTABLE.1 = myValue. Otherwise you are saying that the numeric value 1 = some other numeric value. God Bless GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE - Order By Problem
I have never done anything like this but after looking at the spec's I have a possible direction for you In String functions there is LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. I think that if you create a function that uses this to strip the string to the left of the last found space and that returns the string to the right you could call this in your query and use it in the order by statement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order Problem
Andy Eastham put me on the right track for this solution. I just ran the following and it works so please give him the credit. I tested with 'van der Plas' and 'McNeil' SET @tmp = 'van der Plas'; SELECT @tmp, Reverse( SUBSTRING_INDEX(Reverse(@tmp),' ',1) ) as myOrder FROM MyTable; God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. (H) 301-582-3752 (C) 240-291-0767 GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re Select problem
Are you spitting out an output of the query string to verify that the data from the form is making it to the query correctly? GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select double value
Good morning to all. I have a problem with a workaround but I wanted to know if others have run into this. Table DATA Column strength [double] When I select strength from DATA and the result is a non zero amount it returns correctly 3.256498 however if it is a 0 amount I get 0. The problem is that in my java.sql.ResultSet.getDouble(strength) a zero amount throws a number format exception. SO, I placed a conditional SELECT CASE WHEN strength IS NULL OR strength = 0 THEN 0 ELSE strength END This, however seems to truncate the result so that a zero return results in 0 BUT a return of 3.1236564 results in 3. Bummer. Finally I had to restructure my conditional... SELECT CASE WHEN strength IS NOT NULL AND strength != 0 THEN strength ELSE '0' END I feel that this type of data manipulation shouldn't need to be done. Is this a bug or normal for a return type of double? Thanks. Good days to all. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]