How to do comparison of adjacent rows in PHP?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

How to do comparison of adjacent rows in PHP?

Post by legend986 »

If I have some data in the database as:

ID Column A Column B
1...12.............23
2....13............23
3....15............35
4....16............35
5....18............45

Now, I need to extract the rows where the transition occurs. For example, rows with ID 2,3 and 4,5 and put it in another table. What is the best approach to do this?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Into a new table? Through PHP. That shouldn't be hard at all. If they are always going to be in a row, then you could just save the last value and if the current one doesn't equal the last one, then deal with it. If you want to group all similar rows though (adjacent or not), you could save all of the data into an array, sort it, then handle it.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Well, I don't know why but it seems confusing... I will use a mysql_fetch_assoc to fetch each row but then I have just one row in my hand. Even if I save this row, how would I actually construct the loop? Sorry... but I'm a little confused...
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Well, I thought of this one...

Code: Select all

<?php

require "conf_global.php";

$sql = "SELECT * FROM nums_table";
$result = mysql_query($sql);
$tot_rows = mysql_num_rows($result);
for($i=0;$i<$tot_rows;$i++) {
	$sql_prev = "SELECT * FROM nums_a WHERE ID=".$i;
	$result_prev = mysql_query($sql_prev);
	$row_prev = mysql_fetch_assoc($result_prev);
	$sql_cur = "SELECT * FROM nums_a WHERE ID=".($i+1);
	$result_cur = mysql_query($sql_cur);
	$row_cur = mysql_fetch_assoc($result_cur);
	if($row_prev['num'] != $row_cur['num']) {
		$sql = "INSERT INTO nums_b(a,b) VALUES($row_prev[num],$row_cur[num])";
		$result = mysql_query($sql);
	}
}

?>
But I don't think it is that efficient... What do you think?
Last edited by legend986 on Thu Oct 04, 2007 10:26 pm, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Why do you run so many queries? Get the data once, sort it out, then do with it what you will.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Don't hate me but what do you mean "sort it out"? I don't want to be changing the order or something... I want to extract the transition as it is...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to do comparison of adjacent rows in PHP?

Post by califdon »

legend986 wrote:If I have some data in the database as:

ID Column A Column B
1...12.............23
2....13............23
3....15............35
4....16............35
5....18............45

Now, I need to extract the rows where the transition occurs. For example, rows with ID 2,3 and 4,5 and put it in another table. What is the best approach to do this?
Sorry to be so skeptical, but why do you need to do this? Whenever I hear of someone trying to do something like this, I think, He's approaching the problem wrong. If your database schema is designed correctly, there should be no need to create new tables based on the sequence of the data in various rows. That said, maybe you're trying to do something that I just haven't considered.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

Well, its something related to graph traversals. I am going from A to B passing through some fixed points. In this case, the numbers to the right. Some of these numbers belong to a particular 'family'. A family is a set of numbers having similar properties. So, if the number is changing, it means that while traveling, I'm entering a new zone. I am trying to separate the zones. Hope I was able to put it properly...
Post Reply