Recordset to array

Report · Aug 28, 2008

Hi Folks,

I'm trying to turn a recordset into an array so I can filter the results on the page.

This is what I have at present but I only seem to end up with one line in the array when there should be hundreds.

Can Anyone see what I'm doing wrong?

Dave

Report · Aug 28, 2008

mac-in-the-mountains wrote:
> Can Anyone see what I'm doing wrong?

You need to fetch each row.

$coursedates = array();
do {
$coursedates[] = array($row_rs_dates['code'],
$row_rs_dates['start_date'], $row_rs_dates['finish-date']);
} while ($row_rs_dates = mysql_fetch_array($rs_dates));

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 01, 2008

Great thanks David, that works much better. Just one more question.

How do I filter the array to out put only the dates that correspond to certain code.

At the moment I have the attached code:

But that doesn't seem to do much - I get a blank result when I print_r

Report · Sep 01, 2008

mac-in-the-mountains wrote:
> At the moment I have the attached code:
>
> But that doesn't seem to do much - I get a blank result when I print_r
>
> <?php $thiscoursedates=array_filter($coursedates,"SWM01",);?>

The second argument to array_filter should be the name of a callback
function, not the value you want to find.

http://docs.php.net/manual/en/function.array-filter.php

One way to do what you want is this:

$coursedates = array();
do {
if ($row_rs_dates['code'] == 'SWM01') {
$coursedates[] = array($row_rs_dates['code'],
$row_rs_dates['start_date'], $row_rs_dates['finish-date']);
}
} while ($row_rs_dates = mysql_fetch_array($rs_dates));

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 01, 2008

I'm in way over my head here but I'll try and explain what's confusing me.

It looks like you are just recreating the $coursedates array for every course which wold be fine but what I really need to do is filter the $coursedates array for anything that is the same as $row_rs_dates['code'] - (I just used SWM01 as an example.

So when I pop your code it would read like this

$coursedates = array();
do {

if ($row_rs_dates['code'] == $row_rs_dates['code'])
etc etc..........

which wouldn't work too well

But maybe (probably) I'm getting it wrong.

Report · Sep 01, 2008

mac-in-the-mountains wrote:
> I'm in way over my head here but I'll try and explain what's confusing me.
>
> It looks like you are just recreating the $coursedates array for every course
> which wold be fine but what I really need to do is filter the $coursedates
> array for anything that is the same as $row_rs_dates['code'] - (I just used
> SWM01 as an example.

The problem is that you're not explaining what it is that you really
need to achieve. Converting the recordset to an array is basically
unnecessary, but it's something that can be done, so I showed you how to
do it. You can also filter the results, but the question then becomes
"why?" Surely the filtering should be done by the recordset.

The code that I gave you would create an array of all results where the
code is SWM01, but if the recordset contains only SWM01, filtering is
pointless.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 01, 2008

Sorry David,
Here’s an attempt to explain.
I have a page that lists courses. There are several, each one listed according to a course code from a recordset in a repeating region.
The available start date and finish date for each course are stored in a separate table along with the corresponding course code. Each course has a list of start and finish dates. Sometimes 50 or more which are all identified by the course code – for example “SWM01”

Somehow I need to display the concatenated start dates and finish dates for each course within each corresponding course section as they repeat down the page. Preferably as a form list item.

My thinking, was that I needed to take the data out of the dates recordset and put it into a multidimensional array. Then output the dates from that array within the repeated region, filtering the array according to the relevant holiday code.
The alternative seemed only to be creating dozens of recordsets filtering each one according to the coursecode.

Does that make some sense?

Report · Sep 01, 2008

mac-in-the-mountains wrote:
> Somehow I need to display the concatenated start dates and finish dates for
> each course within each corresponding course section as they repeat down the
> page. Preferably as a form list item.

It sounds as though you need to use a GROUP BY clause in your SELECT query:

http://dev.mysql.com/doc/refman/5.0/en/select.html

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 02, 2008

Thanks for the advice David,
I've changed tack and tried to change the recordset to include Group by coursecode but when I test the output it just lists one entry for each code and the others seem to have disappeared.

But even if it listed them all I can't find anything online that tells me how to retrieve only the dates for a specific code.

I've attached the recordset code. Any chance you can let me know why I it's now working and maybe give me a pointer as to how I can retrieve the data I need from the recordset.

Cheers

Dave

Here' sthe recorset code:

$query_rs_dates = "SELECT * FROM dates08 WHERE coursecode LIKE '%SWM%' GROUP BY coursecode ORDER BY start_date ASC ";
$rs_dates = mysql_query($query_rs_dates, $con_mycon) or die(mysql_error());
$row_rs_dates = mysql_fetch_assoc($rs_dates);
$totalRows_rs_dates = mysql_num_rows($rs_dates);

Report · Sep 02, 2008

mac-in-the-mountains wrote:
> I've attached the recordset code. Any chance you can let me know why I it's
> now working and maybe give me a pointer as to how I can retrieve the data I
> need from the recordset.

Sorry, I'm up to my eyeballs in other work at the moment, and working
with GROUP is not one of my strong points. Micha Fesser is the GROUP
king. See if you can get him to respond, maybe by starting a new thread.
Good luck.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Sep 02, 2008

On 02 Sep 2008 in macromedia.dreamweaver.appdev, mac-in-the-mountains
wrote:

> I've changed tack and tried to change the recordset to include Group
> by coursecode but when I test the output it just lists one entry for
> each code and the others seem to have disappeared.
>
> But even if it listed them all I can't find anything online that
> tells me how to retrieve only the dates for a specific code.
>
> I've attached the recordset code. Any chance you can let me know why
> I it's now working and maybe give me a pointer as to how I can
> retrieve the data I need from the recordset.

Could you post the details of the tables involved? In any case,
probably what you want is GROUP_CONCAT():

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

The SQL statement would look something like:

SELECT t1.coursename, GROUP_CONCAT(t2.startdate) AS startDates,
GROUP_CONCAT(t2.enddate) AS endDates
FROM myCourseTable AS t1 JOIN myDatesTable AS t2 ON t1.code = t2.code
GROUP BY t1.code

startDates and endDates will contain a comma separated list of text;
you can split it into an array using the PHP split() function:

$startDateArray = split(',', $row_recordset1['startDate']);

http://www.php.net/split

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Sep 03, 2008

Thanks Joe,

I'll try to explain the whole project better as I am sure the answer doesn't need to be all that complicated.

I have a table called 'courses' which contains 23 different fields - title, description, price, etc, etc. The fields that are relevant here are 'coursecode' which identifies which course we are dealing with and 'dates' which contains the start and finish dates for each course and the corresponding 'coursecode'.

Unfortunately at present 'dates' is a text field which simply contains a list of dates pasted in. I need to change that because although it's easy to display the dates in that format they don't behave like dates so I can't format them and more importantly users can't search or sort them according to date.
Also the dates are exported to me from excel as a csv file and pasting in each list of dates (over 250 different courses) is a 'grimly fiendish' job. Which is how I came about creating the new table 'dates08'

'dates08' is the second table involved. Created by a simple import of the csv file 'dates08' contains three fields
'coursecode', start_date' and finish_date (start_date' and finish_date are both date fields). There are over 1400 rows of date combinations as each course can have many dates.

At present the page uses a recordset 'rs_courses' created purely from the 'courses' table which lists each course and all its details in turn down the page in a repeated region according to their course code. In that repeated region is a list/menu form item which contains the dates (at present from 'courses.dates').
I would like to change the list/menu item to contain the start and finish dates (start_date."-".finish_date) from the table 'dates08' which correspond to the relevant coursecode.

As a back up plan I can create a link that takes the user to another page carrying the course code in the URL to filter a recordset for the correct dates. But I'd rather not make them jump through hoops like that.

Any suggestions how I can use Group by or any other method to sort this problem out?

Thanks for your help.

Dave

Report · Sep 03, 2008

On 03 Sep 2008 in macromedia.dreamweaver.appdev, mac-in-the-mountains
wrote:

> Any suggestions how I can use Group by or any other method to sort
> this problem out?

Did you try what I suggested?

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Sep 03, 2008

Yep,

I tried for all I was worth but it seems my brain isn't big enough yet. I just couldn't get to grips with the 'group by' concept. Maybe I'll just have to take things one step at a time.

Thanks for your help anyway.

Dave

Report · Sep 03, 2008

On 03 Sep 2008 in macromedia.dreamweaver.appdev, mac-in-the-mountains
wrote:

> I tried for all I was worth but it seems my brain isn't big enough
> yet. I just couldn't get to grips with the 'group by' concept. Maybe
> I'll just have to take things one step at a time.

Post:

tablename
fieldname1
fieldname2
...

for any tables and fields you want involved in this query.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Sep 03, 2008

Don't worry Joe,

I think I've come up with a solution.
For anyone that's interested:

I cut the rs_dates recordset code from the top of the page and pasted it inside the rs_courses repeat region. Then I created a variable ($codespecifier) which I set to equal whatever the coursecode in the courses recordset (rs_courses) was.

I altered the the list/menu item code to the attached and although
it felt like a long shot - it seems to work. I just have to work on the formatting of the dates and I'll be home and dry.

Thanks to both of you for your help. It may seem as if I ran around in circles little but it's the little bits of inspiration you provided that helped me understand what was going on. I learned a lot in this post.

Cheers

Dave

Here's the code I ended up with:

do { ?>
<p><span class="body"><a name="<?php echo $row_rs_courses['id']; ?>"></a></span></p>
<?php $codespecifier=$row_rs_courses['coursecode'];

mysql_select_db($database_con_pyb, $con_pyb);
$query_rs_dates = "SELECT * FROM dates08 WHERE coursecode LIKE '%$codespecifier%'";
$rs_dates = mysql_query($query_rs_dates, $con_pyb) or die(mysql_error());
$row_rs_dates = mysql_fetch_assoc($rs_dates);
$totalRows_rs_dates = mysql_num_rows($rs_dates);

*******************

<option value="<?php echo($row_rs_dates['start_date']."-". $row_rs_dates['finish-date']); ?>"><?php echo($row_rs_dates['start_date']."-". $row_rs_dates['finish-date']); ?></option>

Report · Sep 05, 2008

Here you go Joe, but honestly I've sorted it out now so don't go to any trouble:

I have a table called 'courses' which contains 23 different fields - title, description, price, etc, etc. The fields that are relevant here are 'coursecode' which identifies which course we are dealing with and 'dates' which contains the start and finish dates for each course and the corresponding 'coursecode'.

Unfortunately at present 'dates' is a text field which simply contains a list of dates pasted in. I need to change that because although it's easy to display the dates in that format they don't behave like dates so I can't format them and more importantly users can't search or sort them according to date.
Also the dates are exported to me from excel as a csv file and pasting in each list of dates (over 250 different courses) is a 'grimly fiendish' job. Which is how I came about creating the new table 'dates08'

'dates08' is the second table involved. Created by a simple import of the csv file 'dates08' contains three fields
'coursecode', start_date' and finish_date (start_date' and finish_date are both date fields). There are over 1400 rows of date combinations as each course can have many dates.

At present the page uses a recordset 'rs_courses' created purely from the 'courses' table which lists each course and all its details in turn down the page in a repeated region according to their course code. In that repeated region is a list/menu form item which contains the dates (at present from 'courses.dates').
I would like to change the list/menu item to contain the start and finish dates (start_date."-".finish_date) from the table 'dates08' which correspond to the relevant coursecode.