MYSQL_FETCH_ARRAY without using while

[jshowe]

Hi All,

I am working with MYSQL_FETCH_ARRAY in a situation where a while() is not going to work easily. Does anyone know how to cause the while to advance like in a for loop before completing?

I have a db set up for a calender... their are two columns one for day of the month the other for the description of events for that day. I need to be able to check and see if that day corresponds with the number in the database - if so it prints the description.

I thought I might be able to access the data like a multidimensional array $row[0][0] and just incriment the first [0]... It didn't work correctly though... Any other ways of accomplishing what I am looking to do?

Thanks
Scott

[litebearer]

Are there multiple entries for a given day in the databse?

ie
July 4|Hoe down
Aug 2|Square dance
July 4|Pig roast

Lite...

[jshowe]

Well now that you mention it - that is a very good possibility. That makes things even more complicated.... sheeesh!

[litebearer]

perhaps rethinking the database...

make the dates unique.

make the events field actually contain an array of the events for that date

then your query will only look for a particular date, thereby making only 1 returned row and you can avoid while loop
and the events will be in an array for easy displaying

make any sense?

Lite...

[jshowe]

That could solve part of the problem of having multiple events on the same day. But what about searching in the array that is returned from the db? For instance:

$row=mysql_fetch_array($dbconnect);   

if($day==$row)
{print "$row['0']";
}

Because I am not doing a while($row=mysql_fetch_array()) Only one result is found - it never incriments... Does that make sense?

[litebearer]

if the date/date field is unique then there can only be 1 row returned.

ie

08/01:event_array
08/19:event_array
09/01:event_array

<?PHP

$what_date = "08/01";

$sql = "SELECT event_array FROM tablename WHERE date ='$what_date'";

$result = mysql_query($sql);

$count = mysql_num_rows($result);

// count will either be 0 or 1

if ($count != 1) {
  echo "Nothing found";
} else {
  $all_events =mysql_fetch_object($result);
   $count2 = count($all_events);
   $i = 0;
   for ($i = 0; $i < $count2; $i ++) {
     echo $all_events[$i];
   }
)

I didn't test it and I am by no means proficient at php/mysql, but I think it will work

Lite...

[yelvington]

Please explain why you think there's a problem with using a while() loop to retrieve multiple results from a set.

People do it all the time.

The suggested "fix" of sticking multiple events into an array and shoving that into a single column in a single row is ... well, it's silly.

[litebearer]

Please explain why storing an array in a field is 'silly'?

(BTW calling it stupid is ok - I would take no offense)

Lite...
(^ just a crusty old (59yrs) man trying to learn)

[yelvington]

You'll notice there's no "array" data type in MySQL. You'd have to serialize the data and put it into a string, and the result would be a kludge that not only breaks the principle of storing simple data in simple ways, but also makes it unsearchable. You could not, for example, find all the events that mention 'picnic' in the title.

The much simpler solution is to find out why the original poster finds a while() loop to be difficult. It may simply be a misunderstanding about how to query the database and return the information in an intelligently sorted format.

[wirenot]

Why not use a foreach to cycle through the array, and implode and explode when storing and retrieving it.

I had a similar problem with my website (http://www.newsinmotion.net) , so if you want to PM me, I'll go over the details.

[jshowe]

This is what I am working with.... Thanks for all the suggestions!

<table border = "1" width="500" height="600">
   <tr><th bgcolor = "yellow" colspan = "7"><?php echo (date("F Y",$lastmonth )); ?></th></tr>
   <tr><th>Sun</th><th>Mon</th><th>Tue</th><th>Wed</th><th>Thu</th><th>Fri</th><th>Sat</th></tr>

  <?php
   for ($i = 0; $i <= $t_days; $i++)
   {
     if ($day > 6) {$s++;
       echo "</tr>\n<tr>\n";
       $day = 0; }
     //------------------------------------------------//
     if ($i == date("j")&& $m == 0) {?>
        <td align = "center"><b><?php echo $i; $day++; echo "</b></td>\n"; }
     else if ($i > 0) {?>
        <td align = "center"><?php echo $i; $day++; echo "</td>\n"; }
     else if ($day > 0) {?>
        <tr><td colspan = "<?php echo $day; ?>"><?php echo "</td>\n"; }
     //-----------------------------------------------//
     if ($i == $t_days && $day < 6) {?>
        <td colspan = "<?php echo 7 - $day; ?>"><?php echo "</td>\n</tr>\n"; }
   }
     if ($s==4){print "<tr>\n<td>&nbsp</td></tr>";
     }
	 $previous=$m-1;
	 $next=$m+1;

print "</table></td></tr></table>";

[Sxooter]

join the two tables and pull the rows from the date range you need:

select * from table1 t1
left join table2 t2 on
(t1.id=t2.subid)
where table1.datefield between
2004-06-01 and 2004-07-01
order by datefield, subfield1

Then just process the data in PHP. No need to spend all day seeing if something in t1 has an entry in t2. If it does, then it'll return the corresponding t2 rows, else none. If you need to join on date (not really a normal way of doing things, but workable) then the join on clause would be:

join table2 t2 on
(t1.datefield=t2.datefield)

if you make it a left join then all the rows in the daterange will return, whether there's a match or not. A plain join will only return the rows in t1 that have a match in t2.

[jshowe]

Thanks for the post but I am not trying to combine two tables. I just need to know how to use a while() with the above bit of code.

One idea I had was:
while($row=mySQL_FETCH_ARRAY($dblink)){
$event . $row['$day'] = $row['$event'];
}

Then just do a if($event . $day){ echo $event . $day;}

Basically it would make a variable for every event that would be easily checked for display.... Any thoughts?

[tsinka]

Hi,

hope I didn't misunderstand you, but you can do something like this:

$arrEvents = array();
while ($row = mysql_fetch_array($res)) {
  $arrEvents[$row['day']][] = $row['event'];
}

// now the loop that populates the HTML table
// inside that loop (where $day is the current day
// in the loop)
if (array_key_exists($day,$arrEvents)) {
  // we have one or more events
  echo implode("<br>",$arrEvents[$day]);
}

Hope this points in the right direction.
You might want to use htmlspecialchars if the event names/descriptions contain characters that might break up HTML code.

Thomas

[jshowe]

Thomas,

Thank you!! I am pretty sure that this will work the best for my situation. I will make sure I check for special characters as well. Thanks again. (All resolved)