Array help... 2

[Tasuki]

I'm having a tough time trying to find a solution to this, hopefully someone can shed some light.

I'm trying to grab the earliest and latest date in the database. So this is my code:

$dateresult = mysql_query("SELECT date from tblClass
			GROUP BY date");
$datearray = mysql_fetch_array($dateresult);

$e_date = array_shift($datearray);
$l_date = array_pop($datearray);

This gives me:

$e_date = 1999-11-30
$l_date = 1999-11-30

Checking the query in MySQL, I get:

+------------+
| date       |
+------------+
| 1999-11-30 |
| 2004-02-03 |
| 2005-03-02 |
+------------+
3 rows in set (0.00 sec)

Printing the array out in PHP I get (printed before POP and SHIFT):

Array ( [0] => 1999-11-30 [date] => 1999-11-30 )

I don't understand why the SQL works in MySQL but does not return the correct results in PHP. If anyone can find what I'm doing wrong, please let me know.

Thanks for looking.

-T

 

[kenrbnsn]

Did you try:

$dateresult = mysql_query("SELECT date from tblClass
            order BY date");
$datearray = mysql_fetch_array($dateresult);

$e_date = $dataarray[0];
$l_date = $datearray[count($dataarray)-1];

Ken

 

[Tasuki]

Thanks Ken, just tried:

$dateresult = mysql_query("SELECT date from tblNetClass
			GROUP BY date");
$datearray = mysql_fetch_array($dateresult);
//print_r($datearray);
//$e_date = array_shift($datearray);
//$l_date = array_pop($datearray);

$e_date = $datearray[0];
$l_date = $datearray[count($datearray)-1];

print_r($datearray);
echo "<br>E: $e_date <br>L:$l_date";

And received this in PHP:

Array ( [0] => 1999-11-30 [date] => 1999-11-30 )
E: 1999-11-30
L:

I don't think that works since the printed array only returns those dates. There should be all of these dates in the array:

mysql> select date from tblnetclass group by date;
+------------+
| date       |
+------------+
| 1999-11-30 |
| 2004-02-03 |
| 2005-03-02 |
+------------+
3 rows in set (0.00 sec)

Thanks for looking.

-T

 

[Tasuki]

Sorry, I didn't notice you changed to ORDER BY... still yields same results. In MySQL I get:

+------------+
| date       |
+------------+
| 1999-11-30 |
| 1999-11-30 |
| 1999-11-30 |
| 2004-02-03 |
| 2005-03-02 |
+------------+
5 rows in set (0.00 sec)

 

[kenrbnsn]

Try this:

$datearray = array();
$dateresult = mysql_query("SELECT date from tblNetClass
            GROUP BY date order by date");
while ($rs = mysql_fetch_assoc($dateresult))
     $datearray[] = $rs['date'];
$e_date = $datearray[0];
$l_date = $datearray[count($datearray)-1];

print_r($datearray);
echo "<br>E: $e_date <br>L:$l_date";

With your original code, you were only retieving the first record. You have to retieve all of the records before you can get the earliest and latest.

Ken

 

[DRJ478]

MySQL queries return a resource handle, not a full set of the matched records.
All you need is a loop structure that repeats the mysql_fetch_assoc() while records are there:

$result = mysql_query($result) OR die(mysql_error());

# iterate the result set
while ($row = mysql_fetch_assoc($result)){
   # process individual records here
   etc.

Ok?

 

[Tasuki]

Thanks Ken, that works great.

Thanks DRJ478, I thought it returned all the results in an array in the variable.

 

[ronnyjljr]

Oddly,

The array that is being returned has indices [0] and [date], where [date] should be [1].

Have you tried just...

$e_date = $datearray[0];
$l_date = $datearray["date"];
//or
$l_date = $datearray[1];
// Instead of this? $l_date = $datearray[count($datearray)-1];

Give that a try...
~Ron


cout << "If you don't know where you want to go, we'll make sure you get taken";

 

[DRJ478]

ronnyjljr

That is not odd at all. When mysql_fetch_array() is called without any arguments as to how it will return numerical index and column name index. All values appear twice for that reason.
mysql_fetch_assoc() is just as associative array
mysql_fetch_row() is numerical only