wierd issue when looking selecting results using '>'

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

wierd issue when looking selecting results using '>'

Post by primate »

I have a database of pages that I need to re-order so that they are listed incrementally from 1 to the last page if a page is deleted.

The database is MSSQL.

I thought the code below would this however I have an odd problem.

The code works as far as "SELECT PageID, Position FROM Navigation WHERE ( Position > '$position' )" line whereby it all goes a bit odd.

If $position is an odd number less than 10, no results will be returned where Position is greater than 10.

If $position is a number greater than 10, eg. 13, results are returned where position is 1 through to 10 then greater than 13, even though they should only be returned where Position is greater than 13.

So essentially the code works unless there are 10 or more pages then it all goes horribly wrong.

$position is generated correctly, I've checked this, its just seems to be something in the way SQL is handling the query, although I am sure its me doing something wrong but I can't figure out what....

Code: Select all

<?php


foreach ($_SESSION as $page) /*need to find the location of the array element in $_SESSION that will hold the position of the page in the navigation bar - this searches $_SESSION for $_POST['page']*/
	{ 
    	 foreach($page as $key=> $pageinfo) 
     	{	 
        	 if ($pageinfo['PageID'] == $_POST['page']) {
            	   $position = $pageinfo['Position'];
				  
				    } 
     	} 
	} 
	
	print $position; //this is correct
	
	
$query = "SELECT PageID, Position FROM Navigation WHERE ( Position > '$position' )";

$results= mssql_query($query);
while ($query_result=mssql_fetch_array($results)){

	if (isset($existing_positions)){
		
			$existing_positions[$n]['PageID'] = $query_result['PageID'];
			$existing_positions[$n]['Position'] = $query_result['Position'];
			$n++;
			
			} else {
				
				$n=0;
				$existing_positions = array ( $n=> array( 'PageID' => $query_result['PageID'], 'Position' => $query_result['Position']));
				$n++;
				
				}
		}
		
	print '<pre>';
	print_r ($existing_positions);  //these are wrong depending onthe value of $position
	print '</pre>';
	
	$n=count($existing_positions);				
	for ($x=0; $x<$n; $x++){   //decrease the positions by one
		
			$existing_positions[$x]['Position']--;
			$query= "UPDATE Navigation
						SET Position = '{$existing_positions[$x]['Position']}'
						WHERE (PageID = '{$existing_positions[$x]['PageID']}')";
			mssql_query($query);
			
			}



?>
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Anyone? Surely I haven't stumped you guys too? :?
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

it might be the single quotes around $position in the query. it makes it think its doing greater than on a string. which would mess it up in the matter you describe. Also, what type of field is Position?
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Dumb mistake time, I had the wrong data type selected for the column :oops:
Post Reply