[RESOLVED] Getting Complete Query Result into Array

bronzino

I am trying to create a drop-down box that lists a range of years, but excludes years that are in the query result PAfoals. The problem I am having currently is that I am not getting all the values from the query result into an array ($noyears).

The query is here:

$query_PAfoals = sprintf("SELECT HorseYear from Pedigrees WHERE Dam=%s", GetSqlValueString($showmare,"int"));
$PAfoals = mysql_query($query_PAfoals, $CR) or die(mysql_error());
$row_PAfoals = mysql_fetch_assoc($PAfoals);

This query result contains 2005, 2006, and 2007.

The arrays are here:

$years=range('2000', '2008');
$noyears=array();
$noyears[]=$row_PAfoals['HorseYear'];
$openyears = array_diff($years, $noyears);
foreach ($openyears as $yearval)
{
echo $yearval.'<br/>';
}

The output from $openyears is:

2000
2001
2002
2003
2004
2006
2007
2008

As you can see, the only year excluded from the list is the first row in the query result set, 2005. array_diff is not excluding 2006 or 2007 because the array from the query result only contains one element - 2005. When I do this:

print_r($noyears)

I am only getting 2005 as output. I would like to know why I am not getting the entire query result set into the $noyears array. Thank you in advance for any help! 🙂

Weedpacket

$noyears=array();
$noyears[]=$row_PAfoals['HorseYear'];
$openyears = array_diff($years, $noyears);

Because $noyears only contains one element; you'd need to loop over the results to get all of the years.

bronzino

Thanks for your help. I played with this, and I've got two additional questions:

1) Am I using foreach to loop over the results of the query?

2) Where should this new loop go? I tried a foreach loop inside the existing foreach loop, and a foreach loop outside the existing loop, and the results were the same.

I appreciate your assistance.

Weedpacket

1) Am I using foreach to loop over the results of the query?
No, no loop there:

$query_PAfoals = sprintf("SELECT HorseYear from Pedigrees WHERE Dam=&#37;s", GetSqlValueString($showmare,"int"));
$PAfoals = mysql_query($query_PAfoals, $CR) or die(mysql_error());
$row_PAfoals = mysql_fetch_assoc($PAfoals);

As the variable name hints, $row_PAfoals only contains one row.

2) Where should this new loop go?

Just after the query (see the examples on the [man]mysql_query[/man] to see what one might look like). It would surround your [font=monospace]$noyears[]....[/font] line, so that by the time you get to the [font=monospace]$openyears=...[/font] line, [font=monospace]$noyears[/font] will have all of the years in it.

Incidentally, have you had it pointed out to you that the MySQL extension has been deprecated in favour of [man]MySQLi[/man] or [man]PDO[/man]?

bronzino

Weedpacket;11005942 wrote:
Incidentally, have you had it pointed out to you that the MySQL extension has been deprecated in favour of [man]MySQLi[/man] or [man]PDO[/man]?

As I was researching this question on php.net today, I saw that it was deprecated and broke out into a cold sweat. Apparently, I have a lot of work to do.

I looked at the examples and came up with this.

// the query PAfoals is above here
$noyears=array();
while ($row_PAfoals = mysql_fetch_assoc($PAfoals)) {
$noyears[]=$row_PAfoals['HorseYear'];
}
$years=range($startyear, '2012');
$openyears = array_diff($years, $noyears);
foreach ($openyears as $yearval) {
echo $yearval.'<br/>';	 
 }

This is producing something SUPER close to what I want. 🙂 Only problem is that it is skipping (that is, not excluding) the first row in the query result set (2005). It is properly excluding the data from the other two rows in the result set (2006, 2007). Do you think that is because I don't have the loop right, or is it because I need to insert mysql_data_seek somewhere in here to reset a pointer?

Thank you again for your help. I'm excited that this is coming together, and that I am getting a little insight out of it.

bronzino

Found the solution myself I'm happy to say - I did a "do while" loop instead.

End product looks like this:

// PAfoals query above
$noyears=array();
do {
$noyears[]=$row_PAfoals['HorseYear'];
} while ($row_PAfoals = mysql_fetch_assoc($PAfoals));		
$years=range($startyear, '2012');
$openyears = array_diff($years, $noyears);
foreach ($openyears as $yearval)  {
echo $yearval.'<br/>';	 
 }

Thank you for all that you did. Have a great weekend.

Weedpacket

bronzino wrote:
Found the solution myself I'm happy to say - I did a "do while" loop instead.

Actually, it sounds more like you're fetching a row needlessly before you start the loop.

bronzino

If I didn't do the do-while loop, it was consistently leaving out the first row. Thoughts on why that might be?

Weedpacket

It sounds like you're fetching a row needlessly before you start the loop.

bronzino

Weedpacket;11005950 wrote:
It sounds like you're fetching a row needlessly before you start the loop.

I do not understand how that means the loop will omit the first row.

Weedpacket

Say you have seven records: [1, 2, 3, 4, 5, 6, 7].
You fetch one: 1

Now there are six records remaining to fetch: [2, 3, 4, 5, 6, 7].

And those are the ones you loop over: 2, 3, 4, 5, 6, and 7.

Here's another way of looking at it. In your do...while loop, where does [font=monospace]$row_PAfoals['HorseYear'][/font] get its first value from? The mysql_fetch() in the while statement hasn't been reached yet.

bronzino

Yes, I understand now. Thank you for explaining that.

So that leaves several Qs:

1) Is "do while" delivering that otherwise missing row because it doesn't evaluate until the end?

2) Is "do while" just delivering that one otherwise missing row, and no other row?

3) should I revert back to a "while" loop?

Weedpacket

Revert back to a while() loop, yes; and have a look at the code between where you make the query and where you start the loop to find where you're making the extra fetch.

bronzino

Okay here is what I have now:

$query_PAfoals = sprintf("SELECT HorseYear from Pedigrees WHERE Dam=%s", GetSqlValueString($showmare,"int"));
$PAfoals = mysql_query($query_PAfoals, $CR) or die(mysql_error());
//$row_PAfoals = mysql_fetch_assoc($PAfoals);

$noyears=array();
while ($row_PAfoals = mysql_fetch_assoc($PAfoals)){
$noyears[]=$row_PAfoals['HorseYear'];
}	  

$current=date('Y');
$allyears=range($startyear, $current);
$openyears = array_diff($allyears, $noyears);
foreach ($openyears as $openyear)  {
echo '<option value="'.$openyear.'">'.$openyear.'</option>';	 
}

I blew out the fourth line on a hunch that was the extra "fetch" you mentioned. Hopefully that's right - it does seem to be working fine.

Weedpacket

Yep; that was it.

bronzino

Thanks again for your time.