Mysql & php: temporary/ virtual ids for query results?

lauthiamkok · October 31, 2010, 4:46pm

Hi,

I wonder if it is possible to assign temporary/ virtual IDs for a query result?

For instance, I have this as my query,

SELECT 
			
pg_id AS ID,
pg_url AS URL,
pg_title AS Title,
pg_content_1 AS Content

FROM root_pages

ORDER BY pg_created DESC

output:

ID 	URL 	Title 	Content 
53 	a       A       xxx 
40 	b       B       xxx 
35 	c       C       xxx

you can see the the gap between the IDs - they are very untidy. I wonder if I can make a virtual column or something so that I have the output below,

 ID 	URL 	Title 	Content  Virtual ID
53 	a       A       xxx      3
40 	b       B       xxx      2
35 	c       C       xxx      1

from a query like this below,

SELECT 
    			
    pg_id AS ID,
    pg_url AS URL,
    pg_title AS Title,
    pg_content_1 AS Content
    
    FROM root_pages
    
    ORDER BY virtual_id DESC

is it possible??

thanks!

AnthonySterling · October 31, 2010, 5:02pm

…but what would you do with this ‘virtual id’ ?

If it was merely for display purposes, just increment a counter on each iteration and display this.

lauthiamkok · October 31, 2010, 5:04pm

yes it can with the id alone. but it doesnt output ‘correctly’ when I if I query the odd IDs or even IDs - WHERE MOD(pg_id,2) = 1

AnthonySterling · October 31, 2010, 5:09pm

Maybe this will better explain…


<?php
$records = array(
  array(
    'id'    => 10,
    'title' => 'Foo'
  ),
  array(
    'id'    => 15,
    'title' => 'Bar'
  ),
  array(
    'id'    => 20,
    'title' => 'Ying'
  ),
  array(
    'id'    => 25,
    'title' => 'Yang'
  ),
);

for($index = 0; $index < count($records); $index++){
  $record = $records[$index];
  printf("%d: (%d)%s\
", $index, $record['id'], $record['title']);
}

/*
  0: (10)Foo
  1: (15)Bar
  2: (20)Ying
  3: (25)Yang
*/
?>

Or…


<?php
foreach(new ArrayIterator($records) as $index => $record){
  printf("%d: (%d)%s\
", ++$index, $record['id'], $record['title']);
}

/*
  1: (10)Foo
  2: (15)Bar
  3: (20)Ying
  4: (25)Yang
*/
?>

lauthiamkok · October 31, 2010, 5:12pm

AnthonySterling:

Maybe this will better explain…


<?php
$records = array(
  array(
    'id'    => 10,
    'title' => 'Foo'
  ),
  array(
    'id'    => 15,
    'title' => 'Bar'
  ),
  array(
    'id'    => 20,
    'title' => 'Ying'
  ),
  array(
    'id'    => 25,
    'title' => 'Yang'
  ),
);

for($index = 0; $index < count($records); $index++){
  $record = $records[$index];
  printf("%d: (%d)%s\
", $index, $record['id'], $record['title']);
}

/*
  0: (10)Foo
  1: (15)Bar
  2: (20)Ying
  3: (25)Yang
*/
?>

thanks! it’s a great idea/ solution!

but how can I filter the result further, I want two set of results, even and odd results which can be done in mysql query WHERE MOD(pg_id,2) = 1 and WHERE MOD(pg_id,2) = 0

but how in php alone?

thanks!

AnthonySterling · October 31, 2010, 5:15pm

Are you still wanting all the results, but want to display them in separate areas/blocks?

AnthonySterling · October 31, 2010, 5:22pm


class OddFilterIterator extends FilterIterator{
  public function accept(){
    return 0 === (parent::key() % 2);
  }
}

class EvenFilterIterator extends FilterIterator{
  public function accept(){
    return 0 !== (parent::key() % 2);
  }
}


<?php
foreach(new EvenFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", ++$index, $record['id'], $record['title']);
}

/*
  2: (15)Bar
  4: (25)Yang
*/
?>


<?php
foreach(new OddFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", ++$index, $record['id'], $record['title']);
}

/*
  1: (10)Foo
  3: (20)Ying
*/
?>

Notice the extra iterator around ArrayIterator ?

lauthiamkok · October 31, 2010, 5:23pm

yes all results but displaying even and odd output… not sure if i am explaining it correctly! but this is suppose to be my original full sql query in my code,

SELECT 
			
			pg_id AS ID,
			pg_url AS URL,
			pg_title AS Title,
			pg_content_1 AS Content,
			
			EXTRACT(DAY FROM pg_created) AS Date,
			EXTRACT(MONTH FROM pg_created) AS Month,
			EXTRACT(YEAR FROM pg_created) AS Year,
			COUNT(*) FROM root_pages WHERE `name` &lt;= 'Beta') AS position
			
			FROM root_pages
			
			WHERE MOD(pg_id,2) = 1
			AND root_pages.parent_id = '".$page -&gt; pg_id."'
			AND root_pages.pg_id != '".$page -&gt; pg_id."'
			AND root_pages.pg_cat_id = '2'
			AND root_pages.pg_hide != '1'
			
			ORDER BY pg_created DESC

you see that I want a odd result from this query. then i have another query,

SELECT 
			
			pg_id AS ID,
			pg_url AS URL,
			pg_title AS Title,
			pg_content_1 AS Content,
			
			EXTRACT(DAY FROM pg_created) AS Date,
			EXTRACT(MONTH FROM pg_created) AS Month,
			EXTRACT(YEAR FROM pg_created) AS Year,
			COUNT(*) FROM root_pages WHERE `name` &lt;= 'Beta') AS position
			
			FROM root_pages
			
			WHERE MOD(pg_id,2) = 0
			AND root_pages.parent_id = '".$page -&gt; pg_id."'
			AND root_pages.pg_id != '".$page -&gt; pg_id."'
			AND root_pages.pg_cat_id = '2'
			AND root_pages.pg_hide != '1'
			
			ORDER BY pg_created DESC

which is for an even result.

it has the problem I stated above…

lauthiamkok · October 31, 2010, 5:31pm

AnthonySterling:


class OddFilterIterator extends FilterIterator{
  public function accept(){
    return 0 === (parent::key() % 2);
  }
}

class EvenFilterIterator extends FilterIterator{
  public function accept(){
    return 0 !== (parent::key() % 2);
  }
}


<?php
foreach(new EvenFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", ++$index, $record['id'], $record['title']);
}

/*
  2: (15)Bar
  4: (25)Yang
*/
?>


<?php
foreach(new OddFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", ++$index, $record['id'], $record['title']);
}

/*
  1: (10)Foo
  3: (20)Ying
*/
?>

Notice the extra iterator around ArrayIterator ?

thank you! it’s an arrogant and beautiful codes!

can I ask - FilterIterator, ArrayIterator, etc are these default classes/ library from PHP 6??

thanks!

AnthonySterling · October 31, 2010, 5:35pm

Arrogant?

Nope, they’re from the [B]S[/B]tandard [B]P[/B]HP [B]L[/B]ibrary, or SPL. It comes as [URL=“http://www.php.net/manual/en/spl.installation.php”]default in PHP 5.0.0 onwards.

lauthiamkok · October 31, 2010, 5:40pm

sorry I meant to write - ELEGANT!

aw! that’s what I thought! never tried to explore it before!

lauthiamkok · October 31, 2010, 7:42pm

sorry, i have to come back to this as I found another solution by using mysql only - I nearly got I wanted with this link,
http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/

so I have managed to enumerate each row,

SELECT 
u.pg_id AS ID, 
u.pg_url AS URL,
u.pg_title AS Title,
u.pg_content_1 AS Content,
@rownum:=@rownum+1 AS rownum

FROM (
	SELECT pg_id, pg_url,pg_title,pg_content_1
	FROM root_pages
	
	WHERE root_pages.parent_id = '7'
	AND root_pages.pg_id != '7'
	AND root_pages.pg_cat_id = '2'
	AND root_pages.pg_hide != '1'
	
	ORDER BY pg_created DESC
) u,

(SELECT @rownum:=0) r

result,

    ID 	URL 	Title 	Content 	rownum
    53 	a 	x 	x 	        1
    52 	b 	x 	x 	        2
    43 	c 	x 	x        	3
    41 	d 	x 	x       	4

but how can I work on it a bit further - I want to display the odd or even records only like the ones below - is it possible?

odd records,

    ID 	URL 	Title 	Content 	rownum
    53 	a 	x 	x 	        1
    43 	c 	x 	x        	3

even records,

    ID 	URL 	Title 	Content 	rownum
    52 	b 	x 	x 	        2
    41 	d 	x 	x       	4

thank you.

p.s. I don’t quite understand the sql query actually even though I almost got the result, for instance, what do the ‘u’ and ‘t’ mean?

AnthonySterling · October 31, 2010, 7:53pm


<?php
$records = array(
  array(
    'id'      => 10,
    'title'   => 'Foo',
    'rownum'  => 1
  ),
  array(
    'id'      => 15,
    'title'   => 'Bar',
    'rownum'  => 2
  ),
  array(
    'id'      => 20,
    'title'   => 'Ying',
    'rownum'  => 3
  ),
  array(
    'id'      => 25,
    'title'   => 'Yang',
    'rownum'  => 4
  ),
);

class EvenFilterIterator extends FilterIterator{
  public function accept(){
    $current = parent::current();
    return 0 === ($current['rownum'] % 2);
  }
}

foreach(new EvenFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", $record['rownum'], $record['id'], $record['title']);
}


/*
  2: (15)Bar
  4: (25)Yang
*/
?>

lauthiamkok · October 31, 2010, 7:58pm

AnthonySterling:


<?php
$records = array(
  array(
    'id'      => 10,
    'title'   => 'Foo',
    'rownum'  => 1
  ),
  array(
    'id'      => 15,
    'title'   => 'Bar',
    'rownum'  => 2
  ),
  array(
    'id'      => 20,
    'title'   => 'Ying',
    'rownum'  => 3
  ),
  array(
    'id'      => 25,
    'title'   => 'Yang',
    'rownum'  => 4
  ),
);

class EvenFilterIterator extends FilterIterator{
  public function accept(){
    $current = parent::current();
    return 0 === ($current['rownum'] % 2);
  }
}

foreach(new EvenFilterIterator(new ArrayIterator($records)) as $index => $record){
  printf("%d: (%d)%s\
", $record['rownum'], $record['id'], $record['title']);
}


/*
  2: (15)Bar
  4: (25)Yang
*/
?>

aw! got it! it’s so elegant with Standard PHP Library, love it! thanks

AnthonySterling · October 31, 2010, 8:02pm

You’re most welcome.