Koozali.org: home of the SME Server

Contribs.org Forums => General Discussion => Topic started by: NickCritten on April 26, 2007, 06:16:46 PM

Title: Mysql help please - Any MySQL gurus?
Post by: NickCritten on April 26, 2007, 06:16:46 PM

Hi All,

Is there any way to parse a MySQL table and incrementally set a record value?
Sorry that's a bit of a crappy explanation - Let me try it this way.

I have a table which has the following fileds

Code: [Select]

myTable:
  id (Primary Key, auto increment)
  Description (VarChar)
  Date (TimeStamp)
  someData (VarChar)
  orderval (smallint, unsigned)

Data is displayed in my PHP Application in the order of orderval e.g.

Code: [Select]

SELECT * FROM myTable ORDER BY `orderval`

The orderval column is required as the data needs to be displayed in a very specific order - not just in date order or sorted by description or whatever.

Usually the values increment in blocks of 10 at a time, i.e. 10, 20, 30, 40 ,50 so that if I need to insert a field between 10 and 20 I can set its orderval to 15 (Does this remind anyone of line-numbering in BASIC?)

It sometimes becomes necessary however for me to "renumber" the order val field.. e.g. the ordervals may go 10,11,12,13,14,15 and I need to insert one between 14 & 15. So I have to renumber the records so that 11 becomes 20, 12 becomes 30, 13 becomes 40 etc. etc.

I was considering writing a bit of PHP to do this, but I was wondering if there is any way of doing this directly under mysql? something like:

Code: [Select]

UPDATE myTable SET `orderval` = lastorderval+10

Any Ideas?

Many Thanks

Title: Re: Mysql help please - Any MySQL gurus?
Post by: CharlieBrady on April 27, 2007, 04:09:36 AM

Quote from: "NickCritten"

Is there any way to parse a MySQL table and incrementally set a record value?

I'm sure you'll have more luck asking elsewhere.

Title: Mysql help please - Any MySQL gurus?
Post by: ajwsurfer on May 17, 2007, 05:32:50 PM

I usualy use something like this:

UPDATE myTable
     SET orderval = orderval + 1
WHERE orderval > 14;

INSERT INTO myTable ....

For the answer to the second part, you will need to come up with a procedure to do this written in PHP or Stored Procedure in MySQL.
where: 11 = 20, 12 = 30, ...


I don't have the exact syntax but using psudo code you want to do something like this:

for ( int i = 11, int j = 20; i <= highestOrderval; i++, j = j + 10)
    UPDATE myTable
          SET orderval = j
     WHERE orderval = i;