Inspiring

Question

Recordset to array

Forum|Forum|17 years ago
August 28, 2008
10 replies
1026 views

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

Server side applications

This topic has been closed for replies.

D

davecheet

Inspiring

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.

M

mac-in-the-mountainsAuthor

Inspiring

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>

N

Newsgroup_User

Inspiring

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

M

mac-in-the-mountainsAuthor

Inspiring

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

N

Newsgroup_User

Inspiring

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/

M

mac-in-the-mountainsAuthor

Inspiring

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);

N

Newsgroup_User

Inspiring

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/

M

mac-in-the-mountainsAuthor

Inspiring

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?

N

Newsgroup_User

Inspiring

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/

M

mac-in-the-mountainsAuthor

Inspiring

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.

N

Newsgroup_User

Inspiring

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/

M

mac-in-the-mountainsAuthor

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded