[RESOLVED] fetching next row for a compare

[Solarian_TK]

Hello gurus of the php!

I am trying a little script to update a field in the current row, if the next row same fields do not contain the same sequence.😕

Here is my attempt at pasting the code for you too check and give me some suggestions. This is the portion that is making me crave more coffee.
** = table name

<?php
date_default_timezone_set('AUSTRALIA/BRISBANE');
$lasthour = (time() - 3600);
$lastattack45 = (time() - 2820);
$lasthalfhour = (time() - 1800);
$lastfifteen = (time() - 900);
$lastfhourhalf = (time() - 5400);
$next15 = (time() + 900);
$now = time();

mysql_connect($mysql_host, $mysql_user, $mysql_password) or die('Error connecting to mysql');
mysql_select_db($mysql_database);

$result = mysql_query("SELECT feeder, attacker, defender, attacklandunix, smurfid, smurf, unixtime, record, leader FROM ** WHERE attacklandunix > $now  && smurf <> 'smurfed' ORDER BY attacklandunix DESC, defender DESC")
	or die("SELECT Error: ".mysql_error());

$num = mysql_num_rows($result);

$i=0;
while ($i < $num) {
	$date=mysql_result($result,$i,"unixtime");
	$attacker=mysql_result($result,$i,"attacker");
	$defender=mysql_result($result,$i,"defender");
	$landing=mysql_result($result,$i,"attacklandunix");
	$smurfid=mysql_result($result,$i,"smurfid");
	$smurfed=mysql_result($result,$i,"smurf");
	$leader=mysql_result($result,$i,"leader");
	$feeder=mysql_result($result$i,"feeder");

if ($feeder && $defender != [nextrow] $feeder && $defender) 
		{
		echo "!";
		}
else 
		{
		echo "";
		}
$i++;
} 
?>

[dagon]

currently your taking the stream of data from the db, row by row, usually useful, but you need the whole thing for your compare, so put it all in to an array, then loop through that.

[Solarian_TK]

so start off like this instead?

while($row = mysql_fetch_array($result)){
							$count = 0;
							$feeder = $row['feeder'];
							$date= $row['unixtime'];
							$attacker= $row['attacker'];
							$defender= $row['defender'];
							$landing= $row['attacklandunix'];
							$smurfid= $row['smurfid'];
							$smurfed= $row['smurf'];
							$leader= $row['attack_leader'];

if ($feeder && $defender != [nextrow] $feeder && $defender) 
		{
		echo "!";
		}
else 
		{
		echo "";
		}

} 

[dagon]

while($row = mysql_fetch_array($result)){
                            $a[][feeder] = $row['feeder'];
                            $a[][date]= $row['unixtime'];
                            $a[][attacker]= $row['attacker'];
                            $a[][defender]= $row['defender'];
                            $a[][landing]= $row['attacklandunix'];
                            $a[][smurfid]= $row['smurfid'];
                            $a[][smurfed]= $row['smurf'];
                            $a[][leader]= $row['attack_leader']; 
}

foreach($a as $b){
//
}

[Solarian_TK]

so, if I was just to compare the 'feeder', 'defender', and 'attacklandunix'. I should just have them listed in the while loop?

Then to compare this record to the next record, what would be a good way. The whole table is indexed with an auto-increment INT in col 'record'.

I thank you in advance for your help. This has been one big learning experience for me.

[johanafm]

while () {
...
}

$max = count($a) - 1;
for ($i = 0; $i <  $max; ++$i) {
	if ($a[$i]['somefield'] != $a[$i+1]['somefield'])
}

[Solarian_TK]

So, I started to take both suggestions.
And came up with -

<?php
date_default_timezone_set('AUSTRALIA/BRISBANE');
$lasthour = (time() - 3600);
$lastattack45 = (time() - 2820);
$lasthalfhour = (time() - 1800);
$lastfifteen = (time() - 900);
$lastfhourhalf = (time() - 5400);
$next15 = (time() + 900);
$now = time();
//localhost
$mysql_host = "**";
$mysql_database = "**";
$mysql_user = "**";
$mysql_password = "**";
//=============================
mysql_connect($mysql_host, $mysql_user, $mysql_password) or die('Error connecting to mysql');
mysql_select_db($mysql_database);
$result = mysql_query("SELECT * FROM nation_incoming WHERE attacklandunix > $now  && smurf <> 'smurfed' ORDER BY record") 
		or die("SELECT Error: ".mysql_error());

while($row = mysql_fetch_array($result))
{
	$a[]['feeder'] = $row['feeder'];
       $a[]['attacker']= $row['attacker'];
        $a[]['defender']= $row['defender'];
        $a[]['landing']= $row['attacklandunix'];
        $a[]['leader']= $row['leader']; 
        $a[]['record']= $row['record']; 
}	
$max = count($a) - 1;
echo "$max";
for ($i = 0; $i <  $max; ++$i) {
    if ($a[$i]['record'] != $a[$i+1]['record'])
    echo "record test";
} 
?>

If I left the following like as it was,

$a[][feeder] = $row['feeder'];

I will get the following:
Notice: Use of undefined constant feeder - assumed 'feeder' in /Applications/MAMP/htdocs/nation_test/leader_filter.php on line 23

After the single quotes are added, I get a screen full of:
Notice: Undefined index: record in /Applications/MAMP/htdocs/nation_test/leader_filter.php on line 36

line 36 is:

    if ($a[$i]['record'] != $a[$i+1]['record'])

now I am scratching my head.

[paulnaj]

Try this ...

<?php
date_default_timezone_set('AUSTRALIA/BRISBANE');
$lasthour = (time() - 3600);
$lastattack45 = (time() - 2820);
$lasthalfhour = (time() - 1800);
$lastfifteen = (time() - 900);
$lastfhourhalf = (time() - 5400);
$next15 = (time() + 900);
$now = time();
//localhost
$mysql_host = "**";
$mysql_database = "**";
$mysql_user = "**";
$mysql_password = "**";
//=============================
mysql_connect($mysql_host, $mysql_user, $mysql_password) or die('Error connecting to mysql');
mysql_select_db($mysql_database);
$result = mysql_query("SELECT * FROM nation_incoming WHERE attacklandunix > $now  && smurf <> 'smurfed' ORDER BY record")
        or die("SELECT Error: ".mysql_error());

// Declare the array before using it
$a = array();
// use mysql_fetch_assoc() rather than // use mysql_fetch_array()
while($row = mysql_fetch_assoc($result)){
	$a[] = $row; // Just append the whole row
}

$max = count($a) - 1;
echo $max;
for($i = 0; $i < $max; $i++){ // You were incrementing *before* ... ++$i
	if($a[$i]['record'] != $a[$i+1]['record']){
	    echo 'record test ['.$i.']';
	}
}
?> 

[Solarian_TK]

hmm...
so during the IF statement
should this work? or am I just going totally out in left field?

  mysql_query("UPDATE nation_incoming SET leader = '1' WHERE record = '$a[$i]['record']' && defenderid != '$a[$i+1]['defenderid']'"); 

[paulnaj]

You need to be careful when embedding variables in strings. I never do it - too hard to debug. Easier to cut in and out of strings - in which case, always use single-quotes (save double-quotes for HTML) ...

<?php
mysql_query('
UPDATE `nation_incoming` 
SET `leader` = "1" 
WHERE `record` = "'.$a[$i]['record'].'" 
AND `defenderid` != "'.$a[$i+1]['defenderid'].'"
');
?>

... and why not lay it out so it's readable 😉

[Solarian_TK]

I managed to got it to work last night with the following:

$a = array();
while($row = mysql_fetch_assoc($result)){
//echo "checking for leaders <p>";
	$a[] = $row; 
}

$max = count($a) -1;
for($i = 0; $i < $max; $i++){

$feeder = $a[$i]['feeder'];
$nextfeeder = $a[$i+1]['feeder'];

$killer = $a[$i]['attacker'];
$killernext = $a[$i+1]['attacker'];

$defender = $a[$i]['defender'];
$nextdefender = $a[$i+1]['defender'];

$current = $a[$i]['record'];
$nextrow = $a[$i+1]['record'];

$landing = $a[$i]['attacklandunix'];
$nextlanding = $a[$i+1]['attacklandunix'];



if($current != $nextrow)
	{
        //echo "checking for leaders <p>";
	   if($feeder != $nextfeeder && $defender != $nextdefender && $attacker != $nextattacker && $landing != $nextlanding)
	  	{
  		//echo "leader by all criteria <p>";
  		mysql_query("UPDATE nation_incoming SET leader = '1' WHERE record = '$current'"); 
  		}
	elseif($feeder != $nextfeeder)
	  	{
  		//echo "leader by change of feeder <p>";
  		mysql_query("UPDATE nation_incoming SET leader = '1' WHERE record = '$current'"); 
  		}
  	elseif($defender != $nextdefender)
	  	{
  		//echo "leader by mismatch defender <p>";
  		mysql_query("UPDATE nation_incoming SET leader = '1' WHERE record = '$current'"); 
  		}
  	elseif($killer != $killernext && $landing != $nextlanding)
	  	{
  		//echo "leader via attack and landing <p>";
  		mysql_query("UPDATE nation_incoming SET leader = '1' WHERE record = '$current'"); 
  		}
	else
   		{
   		//echo "no leaders <p>";
   		mysql_query("UPDATE nation_incoming SET leader = '0' WHERE record = '$current'"); 
		}
	}
}
echo "<p>checking for leaders COMPLETE<p>";

[bradgrafelman]

Solarian_TK wrote:

I managed to got it to work last night with the following

...by creating a bunch of superfluous variables? :p Note that your original problem was that you can't reference multidimensional array items inside a double quoted string without using the "complex" syntax with {} braces, e.g.:

echo "This is an array item: {$array[$i]['key']}!";

Furthermore, array items with string keys can't be referenced inside a string without using a) the complex syntax above, or b) omitting the string delimiters, e.g.:

$array['key'] = "Hello world!";

echo "Message is: $array[key]";

By the way, it looks like you're looping through one MySQL result set just to execute other queries in a loop. To me, this raises huge red flags that your SQL code could be highly unoptimized/inefficient.

Either way, don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.

[paulnaj]

Hi,

Hmm ... surprised it worked. Both $attacker and $nextattacker weren't declared/assigned a value.

Sort of 'proves' Brad's point about creating superfluous variables when they already exist. Can be buggy.

I also notice that the logic in the if conditions can be reduced to one 'if' condition!!

<?php
$a = array();
while($row = mysql_fetch_assoc($result)){
//echo "checking for leaders <p>";
    $a[] = $row;
}

$max = count($a) - 1;
for($i = 0; $i < $max; $i++){
	$current = $a[$i]; //  For readability
	$next = $a[$i+1]; //  For readability
	if($current['record'] != $next['record']){
		$leader = (
			$current['feeder'] != $next['feeder']
			||	$current['defender'] != $next['defender']
			|| ($current['attacker'] != $next['attacker'] && $current['attacklandunix'] != $next['attacklandunix'])
		)? '1': '0';
		mysql_query('UPDATE `nation_incoming` SET `leader` = "'.$leader.'" WHERE record = "'.$current['record'].'"');
	}
}
echo "<p>checking for leaders COMPLETE<p>";
?>