RE : MediumBlob / phpMyAdmin / hex dumps Questions
I wonder if you are doing the import with phpMySQL. It does not seem possible to import a large database with phpMySQL (or perhaps any php based utility?) on a local PC running windows without hitting various timeouts. I am dealing with large import by doing them directly using mysql at the command line level -- Jacques Brignon > -Message d'origine- > De : mik...@qualityadvantages.com [mailto:mik...@qualityadvantages.com] > Envoyé : samedi 13 décembre 2008 15:13 > À : mysql@lists.mysql.com > Objet : MediumBlob / phpMyAdmin / hex dumps Questions > > Hello and Greetings mysql list, > > A few weeks ago, I posted a question here about importing a database > to my local XP pro system and getting loads of MySQL server went away > errors during the import. I got suggestions about increasing max > packet size and a few other parameters which I followed but still > could not get a clean import with out the server going away error. > > I have since some more troubleshooting to try to narrow in on the > source of the problem. The table that is getting the error I have > isolated and know that it is a PhotoPlog data table that contains all > the binary images that it is storing. The Table has two fields defined > with mediumblob, one for the picture and the second one for the > thumbnail. > > I have used phpMySQL to export this table and noticed that it is > dumping these fields as HEX and not binary and think that this may be > the source of the problem with importing to another system. > > Anyone have any thoughts on MediumBlog being dumped out as HEX? > > The other troublesome part of this problem is that only some of the > records have problems and no clue what the distinction is between the > ones that work and the ones that fail? > > Any pointer greatly appreciated as always. > > TIA > > > > -- > Best regards, > mikesz mailto:mik...@qualityadvantages.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=jacbrig...@online.fr -- 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 could write this SQL
In case it is acceptable to have a1,b1.qty, b2.qty, b3.qty concatenated in one field GROUP_CONCAT might help http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_ group-concat -- Jacques Brignon > -Message d'origine- > De : Baron Schwartz [mailto:[EMAIL PROTECTED] > Envoyé : vendredi 20 avril 2007 14:52 > À : [EMAIL PROTECTED] > Cc : mysql@lists.mysql.com > Objet : Re: How could write this SQL > > Hi, > > wang shuming wrote: > > Hi, > > How could > > select > >row1 a1,b1.qty, b2.qty, b3.qty > >row2 a2,b1.qty, b2.qty, b3.qty > >row3 a3,b1.qty, b2.qty, b3.qty > > ... > > from a table : > > > > row1 a1,b1,qty > > row2 a1,b2,qty > > row3 a1,b3,qty > > row4 a2,b1,qty > > row5 a2,b2,qty > > row6 a2,b3,qty > > row7 a3,b1,qty > > I think you are describing a cross-tabulation (aka pivot table). You can > do this with > joins, though this particular case looks tricky. There is a good article > on > cross-tabulations here: > > http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html > > Baron > > -- > 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 : RE : SPAM ? SOLVED
Hi, For those who said they were experiencing the same problem than me, I think I found the solution. My mail client is currently configured to leave the messages on the server and it seems that the message causing the problem has something specific preventing my mail server and mail client to flag it as already sent, as a result it keeps being sent again and again to my client. Solution: delete the message on the server. -- Jaqcues Brignon > -Message d'origine- > De : Jacques Brignon [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 25 mars 2007 15:21 > À : 'Stephen Cook' > Cc : 'Jerry Schwartz'; 'MySQL List' > Objet : RE : SPAM ? > > Hi, > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > that link ought to help you > > > Looks a bit extreme, as long as I feel there is some value to be part of > this list :-). > > I suspect all members of the list experience the same problem; I feel it > would be more valuable to solve the problem than to nuke the list. > > It is to be noticed that if you sort your inbox by date, the problem can > remain undetected for a while as long as all the messages have the same > date and time (look for messages from Jerry Schwartz > [EMAIL PROTECTED] dated 22/03/2007 17:53); I received another > 50 of them since my last post. > > As long as Jerry does not seem to receive the messages about the > problem, if he cannot be contacted otherwise is there a way to block his > messages or to unsubscribe this address until the problem is fixed? > > -- > Jacques Brignon > > > > > > -- > 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 : SPAM ?
Hi, > http://lists.mysql.com/[EMAIL PROTECTED] > > that link ought to help you Looks a bit extreme, as long as I feel there is some value to be part of this list :-). I suspect all members of the list experience the same problem; I feel it would be more valuable to solve the problem than to nuke the list. It is to be noticed that if you sort your inbox by date, the problem can remain undetected for a while as long as all the messages have the same date and time (look for messages from Jerry Schwartz [EMAIL PROTECTED] dated 22/03/2007 17:53); I received another 50 of them since my last post. As long as Jerry does not seem to receive the messages about the problem, if he cannot be contacted otherwise is there a way to block his messages or to unsubscribe this address until the problem is fixed? -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : SPAM ?
Hi, > What's kind of the message you guys mentioned? Its always the same message from the same author with the same content, same date same hour, its the one below: > -Message d'origine- > De : Jerry Schwartz [mailto:[EMAIL PROTECTED] > Envoyé : jeudi 22 mars 2007 17:53 > À : [EMAIL PROTECTED]; 'James Tu' > Cc : 'MySQL List' > Objet : RE: Finding a record in a result set > > I don't think that will work. If there are 1,000 records that qualify but > none for Joe, then it will return 1,001. If Joe is in record 1 of the > retrieved record set, and there are 999 other people who match the WHERE > clause, then it will retrieve 1,000. > > Am I missing something? > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > > > -Original Message- > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > > Sent: Thursday, March 22, 2007 12:33 PM > > To: James Tu > > Cc: MySQL List > > Subject: Re: Finding a record in a result set > > > > >I want to do a query of all employees from Maine, ordered > > by hiring date, > > >and figure out where Joe falls in that list. (i.e. which > > record number > > is he?) > > > > If 'Joe' is a unique name LOL... > > > > SELECT 1 + COUNT(*) > > FROM employees > > WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; > > > > PB > > > > James Tu wrote: > > > Is there some quick way to do the following in MySQL? (I > > know I can > > > use PHP to search through the result set, but I wanted to see if > > > there's a quick way using some sort of query) > > > > > > Let's say I know that Joe is from Maine. > > > I want to do a query of all employees from Maine, ordered by hiring > > > date, and figure out where Joe falls in that list. (i.e. > > which record > > > number is he?) > > > > > > -James > > > > > > > > > --MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > --No virus found in this incoming message. > > > Checked by AVG Free Edition. > > > Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: > > > 3/22/2007 7:44 AM > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SPAM ?
Hi, I keep receiving this message by several dozens each day, how can this be stopped? Regards Jacques > -Message d'origine- > De : Jerry Schwartz [mailto:[EMAIL PROTECTED] > Envoyé : jeudi 22 mars 2007 17:53 > À : [EMAIL PROTECTED]; 'James Tu' > Cc : 'MySQL List' > Objet : RE: Finding a record in a result set > > I don't think that will work. If there are 1,000 records that qualify but > none for Joe, then it will return 1,001. If Joe is in record 1 of the > retrieved record set, and there are 999 other people who match the WHERE > clause, then it will retrieve 1,000. > > Am I missing something? > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > > > -Original Message- > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > > Sent: Thursday, March 22, 2007 12:33 PM > > To: James Tu > > Cc: MySQL List > > Subject: Re: Finding a record in a result set > > > > >I want to do a query of all employees from Maine, ordered > > by hiring date, > > >and figure out where Joe falls in that list. (i.e. which > > record number > > is he?) > > > > If 'Joe' is a unique name LOL... > > > > SELECT 1 + COUNT(*) > > FROM employees > > WHERE name <> 'Joe' AND state = 'MA' AND hiredate < ; > > > > PB > > > > James Tu wrote: > > > Is there some quick way to do the following in MySQL? (I > > know I can > > > use PHP to search through the result set, but I wanted to see if > > > there's a quick way using some sort of query) > > > > > > Let's say I know that Joe is from Maine. > > > I want to do a query of all employees from Maine, ordered by hiring > > > date, and figure out where Joe falls in that list. (i.e. > > which record > > > number is he?) > > > > > > -James > > > > > > > > > --MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > --No virus found in this incoming message. > > > Checked by AVG Free Edition. > > > Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: > > > 3/22/2007 7:44 AM > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : error 28 from table handler
Thanks Yes the hoster fixed it by cleaning the temp files Jacques Brignon > -Message d'origine- > De : Sander Smeenk [mailto:[EMAIL PROTECTED] > Envoyé : lundi 3 avril 2006 10:32 > À : mysql@lists.mysql.com > Objet : Re: error 28 from table handler > > Quoting Jacques Brignon ([EMAIL PROTECTED]): > > > "1030 : Got error 28 from table handler" > > % perror 28 > OS error code 28: No space left on device > > > Any idea of what might cause that, and what should be done to correct > > this situation? > > Aparently the disk that stores your MySQL datadirectory is full. > Either you or your hoster should clean it up ;) > > With regards, > Sander. > > -- > | Junk is something you've kept for years and throw away three weeks > | before you need it. > | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D > > -- > 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: error 28 from table handler
Thanks, Table corruption is exactly what I fear as a result of this problem. As long as things semm to work again now after the hister cleaned some temp files, how do I check my db for possible table corruptions? Selon prathima rao <[EMAIL PROTECTED]>: > if u have shut down the system then the table would have been corrupted > - Original Message ----- > From: "Jacques Brignon" <[EMAIL PROTECTED]> > To: > Sent: Monday, April 03, 2006 1:58 AM > Subject: error 28 from table handler > > > > Hi, > > > > I am getting the following error when executing a simple SELECT query > > which used to work: > > > > "1030 : Got error 28 from table handler" > > > > I did not found that in the manual. When googleing it it seems it may be > > related to disk space ... > > > > The server hosting my application is running MySQL 4.0.23. My db uses > > MyISAM tables > > > > Any idea of what might cause that, and what should be done to correct > > this situation? > > > > -- > > Jacques > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > > No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.1.385 / Virus Database: 268.3.4/299 - Release Date: 3/31/2006 > > > > > > -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trans.: Re: error 28 from table handler
Oops forgot to include the list - Message transféré de Jacques Brignon <[EMAIL PROTECTED]> - Date : Mon, 03 Apr 2006 10:07:58 +0200 De : Jacques Brignon <[EMAIL PROTECTED]> Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]> Sujet : Re: error 28 from table handler À : Jos Elkink <[EMAIL PROTECTED]> Well I tried, to the extent I was able to, as long as I do not have full access to the server! I am not running it, I have access to some admin capabilities through DirectAdmin web interface ... which was not running. In the mean time I talked to the server admin and he unocked the problem, a temp was getting full. Not sure which temp file or folder. In order to avoid this to happen agiain, the question I then have is to understand if my application (PHP using MySQL) can be the cause of that? Selon Jos Elkink <[EMAIL PROTECTED]>: > Hi Jacques, > > I presume you already checked whether your harddisk is full ? > > Jos > > On 4/2/06, Jacques Brignon <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I am getting the following error when executing a simple SELECT query > > which used to work: > > > > "1030 : Got error 28 from table handler" > > > > I did not found that in the manual. When googleing it it seems it may be > > related to disk space ... > > > > The server hosting my application is running MySQL 4.0.23. My db uses > > MyISAM tables > > > > Any idea of what might cause that, and what should be done to correct > > this situation? > > > > -- > > Jacques > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > Jos Elkink Postgraduate Student > [EMAIL PROTECTED] Department of Political Science > http://jaeweb.cantr.netTrinity College Dublin > -- Jacques Brignon - Fin du message transféré - -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 28 from table handler
Hi, I am getting the following error when executing a simple SELECT query which used to work: "1030 : Got error 28 from table handler" I did not found that in the manual. When googleing it it seems it may be related to disk space ... The server hosting my application is running MySQL 4.0.23. My db uses MyISAM tables Any idea of what might cause that, and what should be done to correct this situation? -- Jacques -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trans.: Re: Trans.: Re: Finding the row number satisfying a conditon in a result set
- Message transféré de Jacques Brignon <[EMAIL PROTECTED]> - Date : Tue, 31 Jan 2006 13:52:16 +0100 De : Jacques Brignon <[EMAIL PROTECTED]> Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]> Sujet : Re: Trans.: Re: Finding the row number satisfying a conditon in a result set À : [EMAIL PROTECTED] Thanks Shawn for the detailed answer, What I am currently doing is basically what you propose, I do a full query to retreive the row numbers of the subset I want to display and of the "selected" record if any in that subset, then I use another query with LIMIT to get those rows for display. What I am trying to do is to improve the performance by limiting the number of queries and by identifying the most efficient way of finding the row number of the search record. I am currently using brute force by loopiong through the result set until I find the record. The proposal of storing the set in a temp table should improve that, allowing to retrieve the row by a query on that table which we can expect to be faster. So As you correctly describe, what I need is to allow the user to scroll through the set, and as you correctly describe, I am therefore usiong your option 2 doing one query to locate the rows and one with limit to get those to be displayed and of course I am hitting performance issues. I also noticed that all the queries using limit do not run at the same speed, the more you get close to the end of the data set the more it takes time. I uderstand the approach number 3 using a temp table, I am also intersted in your approach number 1 but I am not sure to understand what you mean and how you do that using the PHP MySql function libray. Do you mean passing all the rows of the result at once to the client application and storing them in memory (an array)? If the result set is big, couldn't we hit some limits or experience other performance issues? I see how to get in PHP the values of one row of the result set, how do you get all the rows at once other than looping through the result set and getting one row after the other? -- Jacques Brignon Selon [EMAIL PROTECTED]: > Jacques Brignon <[EMAIL PROTECTED]> wrote on 01/30/2006 10:18:59 AM: > > > Oops! forgoten to include the list in the relply > > > > -- > > Jacques Brignon > > > > - Message transféré de Jacques Brignon <[EMAIL PROTECTED]> - > >Date : Mon, 30 Jan 2006 16:16:53 +0100 > > De : Jacques Brignon <[EMAIL PROTECTED]> > > Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]> > > Sujet : Re: Finding the row number satisfying a conditon in a result > set > > À : Jake Peavy <[EMAIL PROTECTED]> > > > > Selon Jake Peavy <[EMAIL PROTECTED]>: > > > > > On 1/30/06, Jacques Brignon <[EMAIL PROTECTED]> wrote: > > > > > > > > I would like some advice on the various and best ways of finding the > rank > > > > of the > > > > row which satisfies a given condition in a rsult set. > > > > > > > > Let's assume that the result set includes a field containing an > identifier > > > > from > > > > one of the table used in the query and that not two rows have the > same > > > > value > > > > for this identifier but that the result set does not contains all > the > > > > sequential values for this identifier and/or the values are not > sorted in > > > > any > > > > predictable order. > > > > > > > > The brute force method is to loop through all the rows of the result > set, > > > > until > > > > the number is found to get the rank of the row. That does not seem > very > > > > clever > > > > and it can be very time consuming if the set has a lot of rows. > > > > > > > > > > > > use ORDER BY with a LIMIT of 1 > > > > > > your subject line needs work though - a "row number" has no meaning in > a > > > relational database. > > > > > > -jp > > > > > > > Thanks for the tip, I am going to think to it as I do not see right away > how > > this solves the problem. > > > > I agree with your comment, This is precisely because the result row > number is > > not in the database that I need to find it. > > > > The problem I am trying to solve is the following: > > > > A query returns a result set with a number of rows, lets say 15000 as an > > example. > > > > I have an application wich displays those 10 by 10 with arrows > > based navigation > > capabilities (first page, previous page, next page, last page).
Trans.: RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam
I keep forgeting to "reply all". Sorry
- Message transféré de Jacques Brignon <[EMAIL PROTECTED]> -
Date : Tue, 31 Jan 2006 13:33:22 +0100
De : Jacques Brignon <[EMAIL PROTECTED]>
Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]>
Sujet : RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon
in a result set - Bayesian Filter detected spam
À : Gordon Bruce <[EMAIL PROTECTED]>
Thanks. That looks OK from the point of view of generating the row number. But
when this is done if my result set is tens of thousands row long, how do I find
the row number of the record satisfying a conditon (like name = something)
without storing the result of the query in a temp table and query that table to
find the row and its number?
--
Jacques Brignon
Selon Gordon Bruce <[EMAIL PROTECTED]>:
> You can use a user variable [EMAIL PROTECTED] in the sample below} to number
> the rows in
> the result set.
>
>
> mysql> set @row:=0;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10;
> +--+-+
> | @row:[EMAIL PROTECTED] | city_Name |
> +--+-+
> |1 | !fajji !fasan |
> |2 | 'aadeissa |
> |3 | 'abas |
> |4 | 'abas |
> |5 | 'abasabad |
> |6 | 'abd al qader |
> |7 | 'abdullah kalay |
> |8 | 'abdullah kalay |
> |9 | 'abruyeh |
> | 10 | 'adel bagrou|
> +--+-+
> 10 rows in set (0.00 sec)
>
> -Original Message-
> From: Jacques Brignon [mailto:[EMAIL PROTECTED]
> Sent: Monday, January 30, 2006 9:19 AM
> To: mysql@lists.mysql.com
> Subject: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in
> a result set - Bayesian Filter detected spam
>
> Oops! forgoten to include the list in the relply
>
> --
> Jacques Brignon
>
> - Message transféré de Jacques Brignon <[EMAIL PROTECTED]> -
>Date : Mon, 30 Jan 2006 16:16:53 +0100
> De : Jacques Brignon <[EMAIL PROTECTED]>
> Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]>
> Sujet : Re: Finding the row number satisfying a conditon in a result set
> À : Jake Peavy <[EMAIL PROTECTED]>
>
> Selon Jake Peavy <[EMAIL PROTECTED]>:
>
> > On 1/30/06, Jacques Brignon <[EMAIL PROTECTED]> wrote:
> > >
> > > I would like some advice on the various and best ways of finding the rank
> > > of the
> > > row which satisfies a given condition in a rsult set.
> > >
> > > Let's assume that the result set includes a field containing an
> identifier
> > > from
> > > one of the table used in the query and that not two rows have the same
> > > value
> > > for this identifier but that the result set does not contains all the
> > > sequential values for this identifier and/or the values are not sorted in
> > > any
> > > predictable order.
> > >
> > > The brute force method is to loop through all the rows of the result set,
> > > until
> > > the number is found to get the rank of the row. That does not seem very
> > > clever
> > > and it can be very time consuming if the set has a lot of rows.
> >
> >
> >
> > use ORDER BY with a LIMIT of 1
> >
> > your subject line needs work though - a "row number" has no meaning in a
> > relational database.
> >
> > -jp
> >
>
> Thanks for the tip, I am going to think to it as I do not see right away how
> this solves the problem.
>
> I agree with your comment, This is precisely because the result row number is
> not in the database that I need to find it.
>
> The problem I am trying to solve is the following:
>
> A query returns a result set with a number of rows, lets say 15000 as an
> example.
>
> I have an application wich displays those 10 by 10 with arrows based
> navigation
> capabilities (first page, previous page, next page, last page).
>
> I also have a search capability and I need to find in which set of 10 results
> the row I search for will be diplayed in order to show directly the
> appropriate
> page and to know what is the rank of this row in the result set or in the
> page
> to show the searched result row "selected".
>
> As an example the row having a customer id of 125, would have the row # 563
> in
> the result set (not orderd by customer id but by some other
Trans.: Re: Finding the row number satisfying a conditon in a result set
Oops! forgoten to include the list in the relply -- Jacques Brignon - Message transféré de Jacques Brignon <[EMAIL PROTECTED]> - Date : Mon, 30 Jan 2006 16:16:53 +0100 De : Jacques Brignon <[EMAIL PROTECTED]> Adresse de retour :Jacques Brignon <[EMAIL PROTECTED]> Sujet : Re: Finding the row number satisfying a conditon in a result set À : Jake Peavy <[EMAIL PROTECTED]> Selon Jake Peavy <[EMAIL PROTECTED]>: > On 1/30/06, Jacques Brignon <[EMAIL PROTECTED]> wrote: > > > > I would like some advice on the various and best ways of finding the rank > > of the > > row which satisfies a given condition in a rsult set. > > > > Let's assume that the result set includes a field containing an identifier > > from > > one of the table used in the query and that not two rows have the same > > value > > for this identifier but that the result set does not contains all the > > sequential values for this identifier and/or the values are not sorted in > > any > > predictable order. > > > > The brute force method is to loop through all the rows of the result set, > > until > > the number is found to get the rank of the row. That does not seem very > > clever > > and it can be very time consuming if the set has a lot of rows. > > > > use ORDER BY with a LIMIT of 1 > > your subject line needs work though - a "row number" has no meaning in a > relational database. > > -jp > Thanks for the tip, I am going to think to it as I do not see right away how this solves the problem. I agree with your comment, This is precisely because the result row number is not in the database that I need to find it. The problem I am trying to solve is the following: A query returns a result set with a number of rows, lets say 15000 as an example. I have an application wich displays those 10 by 10 with arrows based navigation capabilities (first page, previous page, next page, last page). I also have a search capability and I need to find in which set of 10 results the row I search for will be diplayed in order to show directly the appropriate page and to know what is the rank of this row in the result set or in the page to show the searched result row "selected". As an example the row having a customer id of 125, would have the row # 563 in the result set (not orderd by customer id but by some other criterion like name) and would therefore be displayed in the page showing result rows 561 to 570 When I say row I do not mean a row in any table but a row in the result set produced by the query which can touch several tables. None of the fields of the result set contains the row number, it is just the number of time I have to loop through the result set to get the row in the set which matches my criterion. I hope this makes my question clearer. I am sure this is a pretty common problem, but I have not yet figured out the clever way to tackle it! -- Jacques Brignon - Fin du message transféré - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding the row number satisfying a conditon in a result set
I would like some advice on the various and best ways of finding the rank of the row which satisfies a given condition in a rsult set. Let's assume that the result set includes a field containing an identifier from one of the table used in the query and that not two rows have the same value for this identifier but that the result set does not contains all the sequential values for this identifier and/or the values are not sorted in any predictable order. The brute force method is to loop through all the rows of the result set, until the number is found to get the rank of the row. That does not seem very clever and it can be very time consuming if the set has a lot of rows. -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with back up and restore mysql 5.0
When under 5.0 you need to export your data doing a character set translation to latin1 or iso-8859-1. You also need to adjust the export for compatibilty with older versions (some SQL verbs used in 5.0 are not understood by older versions) Jacques Brignon You can do that either with mysqldump or the lates versions of phpmyadmin Selon Sandeep Raul <[EMAIL PROTECTED]>: > Hi, > > Need your help in restoring mysqldump from version 4.1 to 5.0 > > We wanted to update our mysql server from version 4.1.0 to version 5.0, we > took the mysqldump and upgraded it to version 5.0. But, when we tried to > restored the database dump, it just cannot restore it back. > > We searched on the net and came to know that the problem could be of > character set, something we need to change from latin1 to utf8. We tried all > possible solutions from various links, but still the problem persist. > > It would be really great of you, if you could help us out in this problem. > > Awaiting for the reply. > > > > > Regards, > Sandeep Raul > -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query producing default values
Thanks to all those who provided feed back. As a result I found two ways of solving my problem, one is going along the LEFT JOIN track, and the other one is to run a script before doing the query itself whioch is doable in my environment without modifying the core software and which can then do easily more logic and put the result in a temp table which I then use for the query. After trying the first I decided for the second one which can more easily cope with all the peculiarities found in the data. Jacques Brignon Selon Michael Stassen <[EMAIL PROTECTED]>: > [EMAIL PROTECTED] wrote: > >> Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07 AM: > >> I have a query which scans a subscription database to locate the most > recent > >> expiration date of the subscription to a given periodical or service to > compute > >> the start date of a renewal. > >> > >> It works fine when for a given person such a subscription exists. If none > >> exists, as expected the query produces no results. > >> > >> Any suggestion on how to transform this query to produce a default value > set > >> when no past subscription exists? > >> > >> Here is the query: > >> > >> SELECT > >> id_ltrsn, MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH, CURDATE())) AS > >> subs_start > >> FROM > >> ligne_trans, transaction > >> WHERE > >> transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn = 2 > >> AND ligne_trans.id_cntxt_ltrsn = 1 > >> AND transaction.id_pers_trsn = 278 > >> GROUP BY > >> ligne_trans.id_cntxt_ltrsn > >> > >> When past subscipiton exixts it will produce as an example: > >> > >> id_ltrsn | subs_start > >> - > >> 79 | 2006-11-25 > >> > >> When no past subscription exists I would like to get today's date as a > result > >> instead of nothing, example: > >> > >> id_ltrsn | subs_start > >> - > >> 0| 2005-11-09 > >> > >> > >> Thanks for any help you can provide > >> > >> -- > >> Jacques Brignon > > > > You are asking the database to return with data it does not have. Can you > > not detect the fact that you found no records and use that in your > > application code to supply a default date? That would be much easier to > > implement and maintain than any database-based solution. The SQL can > > become quite convoluted when you start trying to simulate missing values. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > I think this is two separate questions: "How do I get something rather than > nothing when there is no matching row?" and "How do I change the result to > something else if it satisfies certain conditions?" > > I suspect getting mysql to return something rather than nothing may be just a > matter of using a LEFT JOIN. Trouble is, we don't have enough information to > tell. What goes in table transaction? What goes in table ligne_transaction? > Which table contains the columns id_ltrsn, date_fin_ltrsn, and > cd_nature_ltrsn? > If Joe subscribes to "MySQL Today", what does the data look like? On the > other > hand, if Joe doesn't subscribe to "MySQL Today", what does that look like? > > I would expect there's a row for Joe somewhere (a subscriber table), a row > for > "MySQL Today" somewhere (a periodical table), and a row somewhere (a > subscription table linking the first two) that indicates the subscription, if > it > exists. Something like: > > Subscriber >id >name > > Periodical >id >name > > Subscriptions >id >s_id >p_id >date_fin_ltrsn > > With that arrangement, we can easily find when Joe's subscription to "MySQL > Today" ends: > >SELECT MAX(date_fin_ltrsn) >FROM Subscriber s >JOIN Periodical p >LEFT JOIN Subscriptions t ON s.id = t.s_id AND p.id = t.p_id >WHERE s.name = 'Joe' > AND p.name = 'MySQL Today' >GROUP BY s.name, p.name; > > If there are any matching subscriptions, you get the latest date_fin_ltrsn. > If > there are no matching transactions, you get NULL. > > Now, you also wanted to change the output based on two conditions. If > MAX(date_fin_ltrsn) is prior to today, or if there is no subscription, you > want > to get today's date. We can fix the first case with the GREATEST() function, > and the second case with COALESCE(): > >SELECT COALESCE(GREATEST(MAX(date_fin_ltrsn), CURDATE()), CURDATE()) >... > the rest remains the same. > > Can you translate that to your tables? If not, perhaps if you described your > tables, someone could write a query to fit. > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query producing default values
Thanks, that makes a lot of sense. My only problem is that I am using here a standard piece of code on the application side and I would hate to modify it, the thing I have all liberty to change is the query! Reason for trying to ask the query itself to tell me if there is nothing in the DB! -- Jacques Brignon Selon [EMAIL PROTECTED]: > Jacques Brignon <[EMAIL PROTECTED]> wrote on 11/09/2005 09:58:07 AM: > > > I have a query which scans a subscription databse to locte the most > recent > > expiration date of the subscription to a given periodical or serviceto > compute > > the start date of a renewal. > > > > It works fine when for a given person such a subscription exists. If > none > > exists, as expected the query produces no results. > > > > Any suggestion on how to transform this query to produce a default value > set > > when no past subscription exists? > > > > Here is the query: > > > > SELECT > > id_ltrsn, MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH, CURDATE())) > AS > > subs_start > > FROM > > ligne_trans, transaction > > WHERE > > transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn = 2 > > AND ligne_trans.id_cntxt_ltrsn = 1 > > AND transaction.id_pers_trsn = 278 > > GROUP BY > > ligne_trans.id_cntxt_ltrsn > > > > When past subscipiton exixts it will produce as an example: > > > > id_ltrsn | subs_start > > - > > 79 | 2006-11-25 > > > > When no past subscription exists I would like to get today's date as a > result > > instead of nothing, example: > > > > id_ltrsn | subs_start > > - > > 0| 2005-11-09 > > > > > > Thanks for any help you can provide > > > > -- > > Jacques Brignon > > > > You are asking the database to return with data it does not have. Can you > not detect the fact that you found no records and use that in your > application code to supply a default date? That would be much easier to > implement and maintain than any database-based solution. The SQL can > become quite convoluted when you start trying to simulate missing values. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query producing default values
I have a query which scans a subscription databse to locte the most recent expiration date of the subscription to a given periodical or service to compute the start date of a renewal. It works fine when for a given person such a subscription exists. If none exists, as expected the query produces no results. Any suggestion on how to transform this query to produce a default value set when no past subscription exists? Here is the query: SELECT id_ltrsn, MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH, CURDATE())) AS subs_start FROM ligne_trans, transaction WHERE transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn = 2 AND ligne_trans.id_cntxt_ltrsn = 1 AND transaction.id_pers_trsn = 278 GROUP BY ligne_trans.id_cntxt_ltrsn When past subscipiton exixts it will produce as an example: id_ltrsn | subs_start - 79 | 2006-11-25 When no past subscription exists I would like to get today's date as a result instead of nothing, example: id_ltrsn | subs_start - 0| 2005-11-09 Thanks for any help you can provide -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding auto increment value in the dump file
I have not identified which options to use to have the current value of the AUTO_INCREMNT included in the dump file. Is this possible? -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
