[RESOLVED] functions help

jkarr

I have written a function that should (in concept) look at a database and create a drop down list containing every year that is found in a date field. Unfortunatley, not only does it display a blank select input, but it stops parsing the page.

Here is my function:

  function yearSelect($CompanyID){
    include 'db-customer.php';
    $custIDfinance = $CompanyID . "Finance";
    $oldestYearGet = mysql_query("SELECT Date FROM  $custIDfinance desc LIMIT 1") or die(mysql_error());
    $oldestYearGot = mysql_fetch_assoc($oldestYearGet);
    $old = $oldestYearGot['Date'];
    $newestYearGet = mysql_query("SELECT Date From $custIDfinance asc LIMIT 1") or die(mysql_error());
    $newsetYearGet = mysql_fetch_assoc($oldestYearGet);
    $new = $newestYearGet['Date'];
    $old = substr($old, 0, 4);
    $new = substr($new, 0, 4);
    $baseyear = $old;
    while ($baseyear <= $new){
        echo "<option value=$baseyear>$baseyear</option>";
        $baseyear = $baseyear +1;
    } 
  }

The code that I use to call the function:

<tr>
<td><input type="radio" name="Criteria" id="Criteria" value="year">By Year</td>
<td><select name="Date" id="Date"><? yearSelect($CompanyID); ?></select></td>
</tr>

If anyone can see anything wrong, I would greatly appreciate your help.

Thanks,
Johnie Karr

Horizon88

Unfortunatley, not only does it display a blank select input, but it stops parsing the page.

That doesn't sound like what you want at all!

Line 8:

$newsetYearGet = mysql_fetch_assoc($oldestYearGet);

Looks like maybe you copy pasted that from the $oldest code above and didn't change all the variables? Typo on $newest, Get should be Got, following the conventions set above, and $oldestYearGet on that line should probably be $newestYearGet

Weedpacket

Why not select max(date) as max_date, min(date) as min_date and do the thing in one query instead of two?

jkarr

Horizon, Thanks for pointing that out to me. Sometimes I get in to much of a hurry.

Weekpacket, I was not aware of those functions, but now I am, so I will look into it.

Thanks,
Johnie

jkarr

Here is my new code, but it does not change my results.

  function yearSelect($CompanyID){
    include 'db-customer.php';
    $custIDfinance = $CompanyID . "Finance";
    $oldestYearGet = mysql_query("SELECT Date FROM  $custIDfinance desc LIMIT 1") or die(mysql_error());
    $oldestYearGot = mysql_fetch_assoc($oldestYearGet);
    $old = $oldestYearGot['Date'];
    $newestYearGet = mysql_query("SELECT Date From $custIDfinance asc LIMIT 1") or die(mysql_error());
    $newsetYearGot = mysql_fetch_assoc($newestYearGet);
    $new = $newestYearGet['Date'];
    $old = substr($old, 0, 4);
    $new = substr($new, 0, 4);
    $baseyear = $old;
    while ($baseyear <= $new){
        echo "<option value=$baseyear>$baseyear</option>";
        $baseyear = $baseyear +1;
    } 
  }

Thanks,
Johnie

jkarr

Ok. I have change my function to use the MIN() and MAX() functions, and I am getting results, just not what I expceted.

All of my code parses, and everything works just fine, except the only thing that shows up in my drop down is RESO.

Here is my new function:

  function yearSelect($CompanyID){
    include 'db-customer.php';
    $custIDfinance = $CompanyID . "Finance";
    $old = mysql_query("SELECT MIN(Date) FROM  $custIDfinance") or die(mysql_error());
    $new = mysql_query("SELECT MAX(Date) From $custIDfinance") or die(mysql_error());
    $old = substr($old, 0, 4);
    $new = substr($new, 0, 4);
    $baseyear = $old;
    while ($baseyear <= $new){
        echo "<option value=$baseyear>$baseyear</option>";
        $baseyear = $baseyear +1;
    } 
  }

Any help would be appreciated!

I commented out the "substr" statements to see what RESO was supposed to be:

That changed my output to:

<option value=Resource id #6>Resource id #6</option>
<option value=7>7</option>

The data I have under the Date column in this table is:

Thanks,
Johnie Karr

Weedpacket

jkarr wrote:
Ok. I have change my function to use the MIN() and MAX() functions,

But still using two queries instead of one.

You've also removed the code that actually fetches the data out of the result set.

jkarr

weedpacket,

I guess, I'm just not sure how to use the MIN/MAX functions properly. I have not found any good examples with google. Do you know of any?

Thanks,
Johnie Karr

Weedpacket

Have you tried MySQL's manual? But what's the difficulty? You'd use them the same way you'd use any other such function.

jkarr

Before you even mentioned about fetching the data I tried this:

function yearSelect($CompanyID){ 
    include 'db-customer.php'; 
    $custIDfinance = $CompanyID . "Finance"; 
    $oldget = mysql_query("SELECT MIN(Date) FROM  $custIDfinance") or die(mysql_error()); 
    $old = mysql_fetch_assoc($oldget);
    $newget = mysql_query("SELECT MAX(Date) From $custIDfinance") or die(mysql_error()); 
    $new = mysql_fetch_assoc($newget);
    $old = substr($old, 0, 4); 
    $new = substr($new, 0, 4); 
    $baseyear = $old; 
    while ($baseyear <= $new){ 
        echo "<option value=$baseyear>$baseyear</option>"; 
        $baseyear = $baseyear +1; 
    } 
  }

This didn't get me anything either so I tried:

function yearSelect($CompanyID){ 
    include 'db-customer.php'; 
    $custIDfinance = $CompanyID . "Finance"; 
    $oldget = mysql_query("SELECT MIN(Date) FROM  $custIDfinance") or die(mysql_error()); 
    $oldgot = mysql_fetch_assoc($oldget);
    $old = $oldgot['Date'];
    $newget = mysql_query("SELECT MAX(Date) From $custIDfinance") or die(mysql_error()); 
    $newgot = mysql_fetch_assoc($newget);
    $new = $newgot['Date'];
    $old = substr($old, 0, 4); 
    $new = substr($new, 0, 4); 
    $baseyear = $old; 
    while ($baseyear <= $new){ 
        echo "<option value=$baseyear>$baseyear</option>"; 
        $baseyear = $baseyear +1; 
    } 
  }

And I still didn't get any results. I will follow up with mysql manual this evening.

Thanks,
Johnie

jkarr

Ok. Using the second piece of code, I thought I wasn't getting anything, but I am. I looked at the HTML of the output and here is what I'm getting:

<option value=></option>

So, now I need to echo my new and old variables to see what I am getting.

jkarr

Ok:

function yearSelect($CompanyID){ 
    include 'db-customer.php'; 
    $custIDfinance = $CompanyID . "Finance"; 
    $oldget = mysql_query("SELECT MIN(Date) FROM  $custIDfinance") or die(mysql_error()); 
    $oldgot = mysql_fetch_assoc($oldget); 
    $old = $oldgot['Date'];
    $newget = mysql_query("SELECT MAX(Date) From $custIDfinance") or die(mysql_error()); 
    $newgot = mysql_fetch_assoc($newget); 
    $new = $newgot['Date'];
    $old = substr($old, 0, 4); 
    $new = substr($new, 0, 4); 
    $baseyear = $old; 
    echo "<option>$old</option><option>$new</option>";
    while ($baseyear <= $new){ 
        echo "<option value=$baseyear>$baseyear</option>"; 
        $baseyear = $baseyear +1; 
    } 
  }

gives me:

<option></option><option></option><option value=></option>

so, either I'm not doing something correctly with my query. While I look in the manual, does anybody see anything wrong?

Thanks,

jkarr

Ok. The problem was the way I was defining variables.

//I had this:
$old = $oldgot['Date'];

//It should be this:
$old = $oldgot['MIN(Date)'];

Thanks weed packet for pointing me in the right direction.