[RESOLVED] array_push() and looping ...

interface

[FONT=Verdana]To the willing members @ phpbuilder.com,

I'm trying to get a filtered list of all the genres in a certain table. Since a genre occurs several times in the table, I only want every genre once.

E.g. instead of Humour, Humour, Humour, Painfull, Painfull, 3D Animation
I want: Humour, Painfull, 3D Animation

The following script works (kinda), it gives me the list I want...

--------------------------------------
$db = new COM("ADODB.Connection");
$db->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\some_database.mdb");
$rs = $db->execute("SELECT genre FROM some_table ORDER BY genre ASC");

$genre = $rs->Fields('genre');

while (!$rs->EOF) {
$myArray = array/COLOR;

if (!in_array($genre, $myArray)) {
array_push($myArray, $genre);
}
$rs->movenext;

foreach($myArray as $value) {
print "$value, ";
}

}

... but unfortunately it also throws me an error:

Fatal error: Uncaught exception 'com_exception' with message 'Source: ADODB.Field.
Description: Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.' in :\the_above_php_script.php:52

So if anybody knows what I'm doing wrong or what I could do better, feel free to point me in the right direction ...

Sincerly,

interface[/FONT]

trexx

there is a much easier way. SQL does all that for you if you use the distinct command.

select distinct genre FROM some_table ORDER BY genre ASC;

that's it... you'll get a result set with each genre in it only one time

and i've found that concerning your error:

This is a recordset error. It means that you have tried to read into a variable or display in a web page a record from the recordset that has either been deleted or does not exist.

The most common occurance of this error is if you have run a database query that has not returned any records and you have then tried to read in a record from the recordset that contains no data.

You need to first check that there is a record in the recordset before reading it in (eg. 'If NOT rsRecordSet.EOF Then').

which line in your code snippet is line 52? =)

interface

Line 52 of my code is "?>", lol 🙂

Anyways,
"SELECT distinct genre FROM some_table SORT BY genre ASC"
does not work, I get the following error:

'Item cannot be found in the collection corresponding to the requested name or ordinal'

So I think ACCESS thinks "distinct genre" is a field.... bugger 🙂
Other clues, if any?

Sincerly,

interface

[edit]For the record, I'm using PHP 5 with MS Access[/edit]

paulnaj

You could try ...

SELECT genre, COUNT(*) as count
FROM some_table
GROUP BY genre
ORDER BY genre ASC;

... which should work in Access.

Paul 🙂

interface

Access doesn't support multi-line queries 🙁
At least not that I know off...

Now I have the following query:

$rs = $db->execute(SELECT genre, COUNT(*) as 'count' FROM some_table GROUP BY genre ORDER BY genre ASC");

Still, I get the same error as my previous post:

Fatal error: Uncaught 'com_exception' with message 'Source: ADODB Recordset
Item cannot be found in the collection corresponding to the requested name or ordinal'

Access still thinks "genre, COUNT(*) as 'count'" is a fieldname...

interface

trexx wrote:
there is a much easier way. SQL does all that for you if you use the distinct command.
select distinct genre FROM some_table ORDER BY genre ASC;
that's it... you'll get a result set with each genre in it only one time

I said it didn't work in Access, but it does, I just had to make distinct -> DISTINCT.
pffff that's lame (for me it is, I feel like an idiot!) (:

So thanks Trexx!

Sincerely,

interface