SELECT Statement in PHP returns different results than when run manually

[clodhoppers18]

I am writing a short script to grab the oldest pulled number for a lottery ball. The numbers available for each ball are 0-9. I run the code below for the first 2 balls, and it works just fine, however, when it runs for the ball3 column, it returns the first record, not the 10th. Please assist me in troubleshooting this.

$query3 = mysql_query("SELECT DISTINCT(`ball3`) FROM `cash3` ORDER BY `date` DESC");
$i = 0;
while ($i < 9) {
    $row3 = mysql_fetch_assoc($query3);
    $ball3 = $row3['ball3'];
    echo "<br>" . $ball3; //added for debuging
    $i++;
}

 

When running the select statement in PHPMyAdmin, I get this result in this order:

      4

      3

      8

      9

      0

      2

      1

      7

      6

      5

 

When I run the code above, I get this:

7

8

2

5

0

6

3

1

4

 

There is nothing that is the same, despite the exact same query being run. I have added the display of the entire page's code below. Please assist me in figuring this out

 

Thank you in advance.

 

 

$query1 = mysql_query("SELECT DISTINCT(`ball1`) FROM `cash3` ORDER BY `date` DESC");
$i = 0;
while ($i < 9) {
    $row1 = mysql_fetch_assoc($query1);
    $ball1 = $row1['ball1'];
    $i++;
}

$query2 = mysql_query("SELECT DISTINCT(`ball2`) FROM `cash3` ORDER BY `date` DESC");
$i = 0;
while ($i < 9) {
    $row2 = mysql_fetch_assoc($query2);
    $ball2 = $row2['ball2'];
    $i++;
}

$query3 = mysql_query("SELECT DISTINCT(`ball3`) FROM `cash3` ORDER BY `date` DESC");
$i = 0;
while ($i < 9) {
    $row3 = mysql_fetch_assoc($query3);
    $ball3 = $row3['ball3'];
    echo "<br>" . $ball3; //added for debugging
    $i++;
}

echo $ball1;
echo $ball2;
echo $ball3;

[patrickmvi]

If it's not too big, can you provide the contents of the cash3 table?

[clodhoppers18]

The file is uploaded here:

 

http://www.mediafire.com/?sharekey=6499f5b5e8dec3ea7f7ec40ada4772a6e04e75f6e8ebb871

 

I have tried "SELECT `ball1` FROM `cash3` GROUP BY `ball1` WHERE max(`date`);"

 

And it seems that it is not working either. What can I do?

[Philip]

Are you sure you're running the right query?

 

This is what I am pulling from phpMyAdmin:

SQL query: SELECT DISTINCT(`ball3`) FROM `cash3` ORDER BY `date` DESC LIMIT 0, 30 ; 
Rows: 10

ball3
7
8
2
5
0
6
3
1
4
9
----
SQL query: SELECT DISTINCT(`ball2`) FROM `cash3` ORDER BY `date` DESC LIMIT 0, 30 ; 
Rows: 10

ball2
7
1
3
6
0
9
5
4
8
2
---
SQL query: SELECT DISTINCT(`ball1`) FROM `cash3` ORDER BY `date` DESC LIMIT 0, 30 ; 
Rows: 10

ball1
0
8
3
7
4
1
5
6
2
9

[clodhoppers18]

I can't seem to get this to work at all.

 

I have tried variations of Group By, Order By, and Distinct, and cannot seem to get it to work.

 

All I am trying to get is a list of each ball, ordered by the the order of appearance from the latest date.

 

So if you have the following:

Date Ball1

2009-06-01  3

2009-06-01  4

2009-05-31  3

2009-05-31  2

2009-05-30  1

2009-05-30  0

2009-05-29  1

2009-05-29  2

2009-05-28  8

2009-05-28  7

 

Would return this result:

3

4

2

1

0

8

7

 

I think it is definitely something with my SQL, so I hope this helps.

 

[TeNDoLLA]

Have you tried ORDER BY date GROUP BY ball?

[TeNDoLLA]

Ooops.. i am not sure if I misunderstood this..

Have you tried just this "SELECT DISTINCT ball FROM ballzor ORDER BY date DESC" or maybe SELECT DISTINCT ball FROM ballzor would do alone? Atleast I get the result you said above.

[clodhoppers18]

I have tried a lot of different SQL statements, and none of them seem to be working. I have tried a combination of the following (maybe I haven't tried the right one's together, but i know I've tried each of these in some way or another:

 

SELECT

 

-DISTINCT (ball1)

-ball1

 

-FROM cash3

 

-GROUP BY

--date

--max(date)

--min(date)

--ball1

 

-ORDER BY

--date

--max(date)

--min(date)

--ball1

 

-DESC

-ASC

 

I still need some assistance.