Page 1 of 1
How to do comparison of adjacent rows in PHP?
Posted: Thu Oct 04, 2007 8:35 pm
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?
Posted: Thu Oct 04, 2007 10:04 pm
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.
Posted: Thu Oct 04, 2007 10:12 pm
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...
Posted: Thu Oct 04, 2007 10:22 pm
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?
Posted: Thu Oct 04, 2007 10:25 pm
by superdezign
Why do you run so many queries? Get the data once, sort it out, then do with it what you will.
Posted: Thu Oct 04, 2007 10:35 pm
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...
Re: How to do comparison of adjacent rows in PHP?
Posted: Thu Oct 04, 2007 10:44 pm
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.
Posted: Thu Oct 04, 2007 10:49 pm
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...