[SOLVED] Get Previous record

[GrayFox12]

Hey i am trying to get the previous record using a query.

 

At the moment i get the next record by using

 

$query0 = "SELECT * FROM SL_Register WHERE IDNumber > '$IDN'";

 

which works fine, so naturally i put the opposite to get the previous record.

 

$query0 = "SELECT * FROM SL_Register WHERE IDNumber < '$IDN'";

 

But this only returns the first record in the table, whereas i want the record that is 1 below the current record, $IDN.  Please help

 

[Jessica]

WHERE IDNumber = ($ISN-1)

[GrayFox12]

That will not work.  Here is example of an IDNumber. 'E1003'.

[teng84]

what is the data type of your field if that is a char then thats a prob

[Jessica]

Would the one before it be E1002? You could just remove the e, subtract one in php and add it back on.

Otherwise, you'll have to use an actual ID number as in a unique id field in your table.

[GrayFox12]

Yes its varchar.

 

Um how would i go about subtracting a character off?

 

You sure there isnt a function in MSSQL i could use.

[teng84]

I would suggest change the id to int for auto increment

you cannot use order by clause to obtain what you need because it will treat your field as char

not numbers 

 

Yes its varchar.

 

Um how would i go about subtracting a character off?

 

You sure there isnt a function in MSSQL i could use.

this idea will waste your time and slow your code although this will work

[Jessica]

use the string functions in php, such as substr.

The best thing to do would be to use a regular ID, not an alphanumberic one.

[teng84]

you have an option

option 1 : do it this way and do string manipulation and use doubled query and have more or less 20 lines

option 2: change your field to int and auto increment it and query once and have a single line for this stuff

[GrayFox12]

Hang on i just tried this query which worked, but im not sure how get all select all the other columns

 

 

$query0 = "SELECT max(IDNumber)  FROM SL_Register WHERE IDNumber < '$IDN'";

[Jessica]

I'm not sure max will work the way you want on a varchar field, but okay.

After that just add the other fields.

max(IDNumber), column2, column3

[GrayFox12]

Yea it kinda works.  In some cases it skips some records for some reason.  Anyway thankyou for your help

[redarrow]

what about this

 

$query0 = "SELECT * FROM SL_Register WHERE IDNumber < SUBSTRING('$IDN',1)-1";

 

got no idear does this work??????

[GrayFox12]

Wicked thx mate that worked.

 

i used it this way

 

$id = SUBSTR($IDN ,1,4)-1;

 

$query0 = "SELECT * FROM SL_Register WHERE IDNumber LIKE  '%$id%'";