Question about getting Arrays

[metazai]

Very new to PHP and trying to teach myself, I have a question about mysql_fetch_array.

I'm trying to load data from a table, and I'm looking through perhaps 20 entries, where every time I need to find the one that currently meets some particular criteria and then the only the 7 entries that follow that one in, how would I go about it? I've been able to snag the row that meets the criteria using the following:

PHP Code:

$rs = mysql_query($query,$conn); 
while($row=mysql_fetch_array($rs)){
if ($row[time_start]==$round_time){... 


but can how can mysql_fetch_array be used to find one row and then grab only it and the successive 7 rows?

Any thoughts?

(First Post, sorry if I've done any of this wrong)

Michael

[sde]

hi michael .. we're neighbors =)

from what i'm understanding, i think you should build a new array from the records that meet the 'if' requirments.

for example:

PHP Code:

<?
$rs = mysql_query($query,$conn);
while($row=mysql_fetch_array($rs)){
  if ($row[time_start]==$round_time){
    $new_array[] = $row[time_start];
  }
}
?>

if you are retrieving more than 1 field of each record, then i highly suggest you create a counter just so your rows stay even.

when you use $new_array[] = ... then it is just adding the value to the right o the array. it will be more precise if you use a counter to create the new array. example:

PHP Code:

<?
$counter = 0;

$rs = mysql_query($query,$conn);

while($row=mysql_fetch_array($rs)){
  if ($row[time_start]==$round_time){
    $new_array[$counter]['time_start'] = $row[time_start];
    $new_array[$counter]['other_field'] = $row[other_field];
    $counter++;
  }
}
?>

hope that helps

[metazai]

Actually, it helps a lot, but due to the type of data I'm loading, only ONE of the records will meet the IF requirement. I then want to grab it and the successive 7. (they are time events for a schedule, you see, so I want to show what's happening NOW and the next 7 things that will happen.)

Would a for..next loop, coupled with a my_sql_fetch row help this?

[sde]

PHP Code:

<?
$counter = 0;
// variable to indicate when counting starts
$now_counting = false;

$rs = mysql_query($query,$conn);

while($row=mysql_fetch_array($rs)){

  // start counter when record is found
  if ($row[time_start]==$round_time && $now_counting==false){
    $now_counting = true;
  }

  // build array when counting has started
  if($now_counting == true)
  {
    $new_array[] = $row[time_start];

    // increment counter
    $counter++;
  }

  // leave loop when you've counted 7 records
  if($counter > 7)
    break;
}
?>

more like that?

[metazai]

Thanks for your help . . . this code has helped, and gotten me thinking about other ideas as well.

[sde]

glad i could help =)

[metazai]

This is a modified version of the code above, with

PHP Code:

$current_time 


representing an integer interpretation of the timestamp, and

PHP Code:

start_time 


and

PHP Code:

end_time 


representing integer values in the database. I know that part works, as I've echo-ed it seperately. The problem is that when I echo any of the variables created, they don't serve up an error, they just don't serve up anything. Any ideas what I'm missing here?

PHP Code:

$rs = mysql_query($query,$conn);
while($row=mysql_fetch_array($rs)){
  if ($current_time >= $row[start_time] && $current_time < $row[end_time]) { 
    $now_counting = true; 
  } 
  if($now_counting == true) {
    $time_label[$counter] = $row[time_label];
    $name[$counter] = $row[name];
    $info_link[$counter] = $row[info_link];
    $image[$counter] = $row[image];
    $counter++; 
  } 
  if($counter > 7) 
    break; 
} 


[bdl]

The method you're using seems wasteful in a couple of ways; first off, you're pulling down the entire set of records (since I can't see your SQL I can't confirm this, of course) rather than a specific result set from a properly formed SELECT statement. Secondly, you're using mysql_fetch_array() without specifying the result type, MYSQL_ASSOC (associative array) or MYSQL_NUM (numerically indexed array) so you're getting twice the array you require for your purposes.

I'm curious as to what your SELECT statement looks like; that's alot of code to do something that could potentially be done with a properly formed SQL query. Looking for specific records that match a criteria and then limiting the resultset to 8 records (1+ the following 7) should be simple.

[metazai]

You're right . . . it's VERY wasteful, and I know it. I'm still learning so I draw big and messy, so to speak.

Actually, though, I thought the $counter variable was there to prevent me pulling down the entire set of records, as advised to me in an earlier reply to this post. I'd like to be able to do this more tightly, but more importantly right now, I'd like to get this to WORK.

As I mentioned in an earlier reply, I only need to test ONE of the records to meet the IF requirement, which is related to the server time. I have 24 events to display, and they are in order on the server but they last for different lengths of time, so I need to know not only the start but the end time of each in order to match it to the current server time and to display the next seven events after it, depending on what time someone is looking at my page.

Since I couldn't figure out how to add and subtract with timestamps, I converted them to integers as you can see, and put the start and end times as integers in my mySQL database.

Since you asked, here's more of my code, with protected information covered by All CAPS.

PHP Code:

<?php
$conn = mysql_connect(CONNECTSTRING) or die ("Problem connecting to the database" . mysql_error() ); 
$today = date("l");
$current_minutes = intval(date("i",time())) + (60 * intval(date("H",time()))); 
$counter = 0; 
$now_counting = false; 
$rs = mysql_select_db(RESULTCONNECTSTRING); 
$query = "SELECT start_time,end_time,time_label,name,image,info_link FROM {$today}";
$rs = mysql_query($query,$conn);
while($row=mysql_fetch_array($rs)){
  if ($current_time >= $row[start_time] && $current_time < $row[end_time]) { 
    $now_counting = true; 
  } 
  if($now_counting == true) {
    $time_label[$counter] = $row[time_label];
    $name[$counter] = $row[name];
    $info_link[$counter] = $row[info_link];
    $image[$counter] = $row[image];
    $counter++; 
  } 
  if($counter > 7) 
    break; 
}

[bdl]

You're right . . . it's VERY wasteful, and I know it. I'm still learning so I draw big and messy, so to speak.

Actually, though, I thought the $counter variable was there to prevent me pulling down the entire set of records, as advised to me in an earlier reply to this post. I'd like to be able to do this more tightly, but more importantly right now, I'd like to get this to WORK.

I'm beginning to get the picture here, and I understand that you're just starting out; we all have to start someplace. I really like your comment about 'drawing big and messy' - I've never heard it put quite that way, but that's exactly what it is, isn't it?

Actually, you do pull down most of the records; if you didn't, how would the if() statement know what to compare against? It has to pull down every record in the table in order to compare against the value in $current_time, then when it hits the 'break' statement, kicks out of the loop and stops. But that doesn't necessarily mean you haven't gone through most of the records in the table prior to that point.

At any rate; I'm quite certain we can eliminate a good 75% of the code you have there with a properly formed SELECT; could you possibly provide a portion of your table schema, i.e. the column names and data types? It's really quite simple to work with dates and times within MySQL, rather than having to convert anything, we can do all the comparisons within the SELECT itself.

Just off the top of my head, without seeing more specifics, I'd say a SELECT statement like this would do what you want:

PHP Code:

// table name declaration
$today = date('l');

// SQL, HEREDOC style
$query =<<< END
  SELECT *
  FROM {$today}
  WHERE NOW() BETWEEN start_time AND end_time
  LIMIT 0,7
END; 


Please post back with a few more specifics on the table design and perhaps an actual record from the table.

[metazai]

What's that <<< END on the end of $query all about? And would LIMIT 0,7 grab the 7 records following the one tested with WHERE NOW() BETWEEN start_time AND end_time??

The table is set up with these fields, with a sample entry at the end of each line after the ...

id(TINYINT) set to autoincrease...1
start_time(INT)...600
end_time(INT)...660
time_label(TEXT)...10:00am
name(TEXT)...Event Name
image(TEXT) it's URL information...images/event1.gif
info_link(TEXT) it's also URL information...events/event1.htm

And the results would be used in a fairly straightforward way, embedded in certain cells of an HTML table:

PHP Code:

echo("<td>".$time_label[0]."</td>"); 


[bdl]

What's that <<< END on the end of $query all about? And would LIMIT 0,7 grab the 7 records following the one tested with WHERE NOW() BETWEEN start_time AND end_time??

As mentioned in the comment, the END is simply part of the HEREDOC style; I prefer using HEREDOC when I create an SQL query in my PHP scripts, makes it easier for me to read / manipulate the query instead of having to muck around with quotes and all that.

Yes, the LIMIT clause will return 8 results, starting with the first (counting from zero) in the resultset stipulated in the WHERE clause.

Unfortunately, rather than using timestamps in your table you're converting the values with PHP and inserting those values instead; I see now what you meant in your previous post. You really should consider utilizing timestamp type for these columns. On the subject, I don't quite understand your unique method for timestamp; you take the time in minutes, add that to the product of 60 and the time in hours. So at this moment, it's 11PM, or 23:00 hours, which in your script becomes '1320' (which if you're using this as a timestamp, is more relatable to 1:20PM). Regardless, if you continue to use the same method in your script, should create a unique timestamp that will increment throughout the day.

So taking this into consideration, you should be able to use the same combination of values in the query, e.g.

PHP Code:

// table declaration
$today = date("l");
// unique 'timestamp'
$timestamp = intval(date("i")) + (60 * intval(date("H")));
// SQL
$query = <<< END
  SELECT *
  FROM {$today}
  WHERE {$timestamp} BETWEEN start_time AND end_time
  LIMIT 0,7
END;
// run the query, return the resultset
$result = mysql_query($query) OR die(mysql_error());
// run a while loop over the resultset
while( $row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    // display the results for each record (simple formatting)
    echo $row['id'] ."\t";
    // etc
    echo $row['start_time'] ."\t";
    // etc
    echo $row['time_label'] ."\t";
    // etc
    echo '<br />';
} 


Unless I'm mistaken, that should work for you...

If you'd like, I'd be happy to advise further or provide some changes to optimize your table layout.

[metazai]

This does not quite work, although it is cleaner. It's only grabbing one record. How does LIMIT work, exactly? My understanding was that it would grab only 7 items that met the criteria, which is why I was confused. Only ONE item is going to meet the criteria at any given time of the day, I just want that item and whatever the next 7 items are, although I know that the next items will NOT meet the timestamp criteria.

Or am I understanding this wrong?

[sde]

Limit will retrieve the first <number specified> rows of the query.

for example, if my query normally would return: apple, orange, pear, banana

then if i limit 2, it will return: apple, orange

i'm just explaining limit, i haven't read all this code exchange between you and bdl yet

[metazai]

This is exactly the problem. The query would normally return only one entry, so it seems to me that LIMIT is meaningless here, but I'm still trying to understand so many things . . . at any rate, bdl, this is why I was downloading so much earlier. I need the regular query result WHICH SHOULD ONLY EVER BE ONE THING AT A TIME, then the next 7 things bu ID number, regardless of matching the query or not.