Help with some logic in searching through a MYSQL table

[fxr]

I have a database read it for use in php with :

 

$sql = mysql_query("SELECT * FROM $table ORDER BY time ASC") or die(mysql_error());

while($db = mysql_fetch_assoc($sql))

 

i loop through this table until i find a valid condition, when its found, i set a flag and return to the start of the loop, where a different condition is looked for in the proceeding records.

 

how would i return to the place in the loop(table) at the next record following where the orignal first condition was found after that 2nd search has completed?

 

 

basic logic is this:

 

 

while (record)

 

if (1st condition = true)

 

if (2nd condition)

// do stuff

set 1st condition = false

// NEED to find a way to return to the next record after first conditon is found here.

 

if (1st condition)

1st condition = true.

 

next record.

[Ken2k7]

I don't understand what you're trying to do. Seems like a pretty stupid way of doing something. Try calling mysql_fetch_assoc again inside your second if statement. Again, not sure why you would do that since the while loop does that already.

[fenway]

What kind of conditions? Why not include these in the DB query itself?

[fxr]

Both conditions depend on comparisons over a sequence of records.

 

I dont think building it into the SQL query will work (tho i am not 100% sure of that) , i need to know the contents of proceeding records to know if either condition has been met.

 

I am struggling to explain my problem well..  and i am sorry for that.

 

this table contains 100,000 records.

 

i will post a section my inadequate tho working code, i imagine only the most persistent of potential helpers will help me work through this issue.

 

$sql = mysql_query("SELECT * FROM $table ORDER BY time ASC") or die(mysql_error());
while($db = mysql_fetch_assoc($sql)) 

{
if ($db['bid_diff'] < 0 ) { $dbnegdiff = $db['bid_diff'] * -1; }
else { $dbnegdiff = $db['bid_diff'];}
if ($opentargetreached == true)
	{
		$timeframe++;
		if ($timeframe <= $usertimeframetarget)
			{
				if ($userhigherlower == 'higher') //short
					{
						if ($db['ask_high'] > $maxDD) {$maxDD=$db['ask_high']; }
						if (rdecimal($db['ask_low']) <= $target)							
							{
								//$askhigh = rdecimal($maxDD);
								$maxtp = ($target + $usertp ) - rdecimal($db['ask_low']); 
								$maxDD = rdecimal($maxDD) - ($target + $usertp );
								$success++;
								$hour = get_hour($dbfirsttime);
								$successarray[$success] = array (
																 'hour' => $hour,
																 'begintime' => $dbfirsttime,
																 'diff' => $diffstring,
																 'maxDD' => $maxDD,
																 'timeframes' => $timeframe,
																 'maxtp' => $maxtp);
								//undec vars
								$opentargetreached=false;
								$diffstring='';
								$timeframe=0;	
								unset($dbfirsttime);
									unset($dbnegdiff);
								unset($target);
								unset($maxtp);
								unset($hour);
								$yescount=0;
								$maxDD=0;
							}
					}	
				else // long							
					{
						if ($db['bid_low'] < $maxDD) {$maxDDL=$db['bid_low']; }
						if (rdecimal($db['bid_high']) >= $target)

							{
								$bidlow = rdecimal($maxDDL);
								$maxtp = rdecimal($db['bid_high']) - ($target - $usertp ) ; 
								$maxDDL = rdecimal($maxDDL) - ($target - $usertp);					
								$success++;									 
								$hour = get_hour($dbfirsttime);
								$successarray[$success] = array (
																 'hour' => $hour,
																 'begintime' => $dbfirsttime,
																 'diff' => $diffstring,
																 'maxDD' => $maxDDL,
																 'timeframes' => $timeframe,
																 'maxtp' => $maxtp);

								//undec vars
								$opentargetreached=false;
								$diffstring='';
								$timeframe=0;	
								unset($dbfirsttime);
									unset($dbnegdiff);
								unset($target);	
								unset($maxtp);
								unset($hour);
								//unset($bidlow);
								$yescount=0;
								$maxDDL=9999;
							}						
					}
			}
		else //fail in usertimeframe
			{
				$fail++;
				$hour = get_hour($dbfirsttime);
				//undec vars
				$failarray[$fail] = array (
											'hour' => $hour,
											'begintime' => $dbfirsttime,
											'diff' => $diffstring,
											'timeframes' => $timeframe);

				$opentargetreached=false;
				$diffstring='';
				$timeframe=0;	
				unset($dbfirsttime);
					unset($dbnegdiff);
				unset($target);	
				unset($open);
				unset($hour);
				$yescount=0;
				$maxDD=0;
				$maxDDL=0;
				next;
			}
	}		
else 
	{
		$diffstring.=', '.$db['bid_diff'];						
		if($userhigherlower=='higher' && $dbnegdiff >= $userdiff && $db['gap'] !=1 && $db['bid_diff'] > 0 && ($prev_hilo !='lower' || prev_hilo !='flat' ))
			{
				// good higher open 
				$yescount++; 
				$prev_hilo = 'higher';
				if ($yescount==1) {$dbfirsttime =$db['time'];}

			}

		elseif($userhigherlower=='lower' && $dbnegdiff >= $userdiff && $db['gap'] !=1 && $db['bid_diff'] < 0 && ($prev_hilo !='higher' || prev_hilo !='flat'))
			{
				// good lower open
				$yescount++;
				$prev_hilo='lower';
				if ($yescount==1) {$dbfirsttime =$db['time'];}

			}

		elseif($usernoise >= $dbnegdiff && $db['gap'] !=1 && $allowNoise == true )
			{	//  noise 
				$prev_hilo ='noise';
			}

		else  //no good reset vars
			{ 
				$yescount=0;
				unset($dbfirsttime);
				unset($dbnegdiff);
				$prev_hilo = 'flat';
				$diffstring='';

			}

		if ($useropentarget==$yescount)
			{
				$opentargetreached=true;
				if ($userhigherlower == 'higher')
				{ 
					//$open = rdecimal($db['bid_open']);
					$target = rdecimal($db['bid_open']) - $usertp;
					$maxDD =  $db['ask_high'];		
				}
				if ($userhigherlower == 'lower') 
				{
					 $target = rdecimal($db['ask_open']) + $usertp;
					 $maxDDL =  $db['ask_high'];		

				}

				$prev_hilo = 'flat';
			}
	}
}//while

 

 

 

[PFMaBiSmAd]

What kind of conditions?

You did not really answer that question in your last post. I suggest you re-read your first and second post and ask yourself if someone could tell from what you wrote in them what you are doing and what your results should be so that they would be able to tell you an alternate way of doing it. And, no, on one is going to reverse engineer the code to find out what a statement of it is. You have left out the "subject" in your posts. Also, re-read the two replies in between that are asking/telling you that you have not provided understandable or complete information about what you are doing (which is what the rest of my post is going to do as well.)

 

Posting some uncommented code does not answer the question because it does not provide a statement of what you are trying to accomplish (i.e. the most important part of asking someone else to help you with a problem of any kind) and it does not show an example of what the data is and what the expected results should be (which shows the algorithm you are trying to implement.)

 

And no, stating you want to find one condition and go back and find another condition is not a specific statement of an algorithm because you have not stated or shown what the conditions are and what that means relative to example data and results.

 

So, what is the it you are trying to do, what is some example data, and what is the expected results?

[fxr]

OK. i realise i am finding it very hard to explain my issue properly.

 

 

I will try again..

 

i have a table of nearly 100,000 records, i am using a while loop to fill an array $sql with the contents of a record.

 

I check for a condition in this record, based on parameters passed by a user, if the condition isnt met , i go back to the start of the loop and get another record. The condition is checked for again, (this condition requires knowledge of the previous record(s)) , if the condition is still not met, we go back to the start of the loop and another record is read..

 

If eventually the condition is met, a flag is set ($useropentargetreached) and we go back to the start of the loop and grab another record, then another condition from that record is checked for, if its not found then we repeat the loop and get another record, again knowledge of teh previous records is required. I need to keep a count of the number of records between when the 1st condition is met and when the 2nd is found.

 

When that 2nd condition is either found, or not found within x amount of records, the flag ($useropentargetreached) is unset and we go back to teh start of the loop and get a record and start looking for condition 1 again, and so on.

 

 

The data in the records is essentially just.. of datetime, int, int, int, int, int, int.

 

i dont see the point in muddying the water further posting sample data.

 

(The logic i mentioned in post one stands.)

 

 

 

My problem, is trying to build in some kind of 'memory' of where in the table, I was when condition 1 was found , so i can get back there after the search through proceeding records looking for condition 2.

 

 

Its really not that complicated, i am just struggling to explain it right and my lack of skill with both php and mysql patently aint helping.

 

Just hoping someone can nudge me in the right direction, without finding some offence in my posts

[fenway]

I don't think you've helped yourself with that explanation -- start over.

 

"Between records" doesn't mean anything.

 

Give real examples of these condtions.

[fxr]

OK i will try again..

 

 

// pseudocode

 

//user inputted vars

 

$OpenTarget;

$userDiff;

$target;

$targetTime;

 

//other vars

$OpenCount=0;

$toTargetCount=0;

 

read record to $sql with while loop

 

if ($opentargetreached=true)

    //check for condition 2

    if ($toTargetCount =<  $targetTime)  // condition has to be met withing certain amount of records.

          if ($target >= $sql['target'])

              // success

              // do success stuff

              // unset variables set $opentargetreached= false and read a new record and check for condition 1 again

          endif

 

  else // failed to find target within defined amount of records

        // fail

              // do fail stuff

              // unset variables set $opentargetreached= false and read a new record and check for condition 1 again

 

// search for condition 1

if ($userDiff >= $sql['diff'])

      $OpenCount ++;

 

else $OpenCount = 0;

 

if ($OpenTarget == $OpenCount)

    $opentargetreached=true; // sets condition 1 = true.

 

 

clearly, picking up records from the table searching for condition 2 after finding a sequence of records that satisfy condition 1, means i have 'skipped' some records that id rather include in the condition 1 search. i need to work out a way to get back to where i was in the table after a search for condition 2 has completed.

 

hope i am making a little more sense now

 

thanks for any help offered.

[fenway]

Nope, not really... pretend you have no code.

 

What are the real conditions 1 & 2?

 

How do they depend on "multiple" records.

 

Please explain.

[fxr]

condition 1 looks at an int value on the record, if there is a sequence of those higher (or lower) than a certain number e.g 4 records in a row  higher than 10 then its set to true. all of them parameters (higher, 4, 10) are dependent on what the user is asking for.

 

condition 2 looks at a different int value on the record. if that value is found or exceeded in the next record, condition 2 is true, if not we look at the next record, and so on.. for output purposes , i must know how many records there is between where condition 1 and condition 2 are found.

[fenway]

The problem is that databases aren't designed for iterations or loops -- they're based on sets.