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?
How to do comparison of adjacent rows in PHP?
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.
Well, I thought of this one...
But I don't think it is that efficient... What do you think?
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);
}
}
?>
Last edited by legend986 on Thu Oct 04, 2007 10:26 pm, edited 1 time in total.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: How to do comparison of adjacent rows in PHP?
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.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?
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...