Array (multidimensional) help - using array like SQL tables to get round lack of Union in MySQL3.

[Dave Smithz]

Hi there.

Because of the lack of a Union query in MySQL 3 I have decided to take the
approach where I populate two arrays with values from similar tables in DB.
In this case they are `courses` and `lessons`

Lets say Courses has fields
CourseID(PK), Date, Name, RunBy, Status

The lessons table is related to the Courses table in that a Course can have
a number of lessons and a lesson is always part of a course.
Lessons has fields
LessonID(PK), CourseID (FK), Date, Name, Status

and therefore I do two SQL calls which fills two arrays in PHP.
So array $arrayCourses might contain :
[0] = Array("CourseID " => "12", "Date" => "12/12/03","Name" =>"Physics",
"Status" => "Active")
[1] = Array("CourseID " => "14", "Date" => "14/12/03","Name" =>"English",
"Status" => "Active")
[2] = Array("CourseID " => "15", "Date" => "14/12/02","Name" =>"Latin",
"Status" => "Closed")
....etc.

So array $arrayLessons might contain :
[0] = Array("CourseID " => "12", "Date" => "18/12/03","Name" =>"Physics -
Triangles", "Status" => "Active")
[1] = Array("CourseID " => "12", "Date" => "19/12/03","Name" =>"Physics -
Circles", "Status" => "Active")
[2] = Array("CourseID " => "14", "Date" => "15/12/03","Name" =>"English -
Literature", "Status" => "Active")
[3] = Array("CourseID " => "15", "Date" => "15/12/02","Name" =>"Latin -
nouns", "Status" => "Closed")
....etc.

I now want to combine (add one to the bottom of the other) these two arrays
(As the fields are the similar) and know I can do it (I believe) by just
adding the arrays together.
So
$CombinedArray = $arrayCourse + $arrayLessons;
would give me an array containing 7 elements in the above example (although
each element is another array).

However, I want the newly formed $CombinedArray to be so that the main
elements are ordered by the courseID. So rather then the elements of
$arrayLessons being after those of $arrayCourse, I want the elements to
appear in an order defined by the value held in courseID.

How can I achieve this? It is very complicated. With a Union query in SQL it
would be easier to do this I think but I am do not want to have to learn the
lack of Union work around.

Further to this. Lets say one of the arrays like $arrayLessons had an extra
element (e.g. number attending) that was not in $arrayCourses what would
happen to that element?

Thanks in advance for any help.

Kind regards

Dave

[Frank Jones]

Re: Array (multidimension al) help - using array like SQL tables toget round lack of Union in MySQL3.

Dave Smithz wrote:[color=blue]
> Hi there.
> <SNIP>[/color]

A better alternative might be to just have a nested SQL query... eh.


$listing = array();
$sql = 'SELECT * FROM COURSES';

// insert the mysql_query stuff here. left out for this post

while($row = mysql_fetch_ass oc($result))
{
$listing[] = $row;
// or maybe echo '<h1>'.$row['name'].'</h1>' etc.. here instead?


// NOTE THE '_' prepended to the variable names here - don't //
forget :)
$_sql = 'SELECT * FROM LESSIONS WHERE COURSE_ID = '.
$row['course_id'] .' ORDER BY name DESC';

// NOTE THE '_' prepended to the variable names here - don't //
forget :)
// insert the mysql_query stuff here. left out for this post
while($_row = mysql_fetch_ass oc($_result))
{
$listing[] = $_row;
// or maybe echo '<b>'.$row['name'].'</b>' etc.. ?

}
}


Should give you what you want... Although if you do this, you can
probably just print it out directly without having to use an array at all.

[Ewoud Dronkert]

Re: Array (multidimension al) help - using array like SQL tables to get round lack of Union in MySQL3.

"Dave Smithz" <SPAM FREE WORLD> wrote:[color=blue]
> I now want to combine (add one to the bottom of the other) these two
> arrays (As the fields are the similar) and know I can do it (I believe) by
> just adding the arrays together.
> So $CombinedArray = $arrayCourse + $arrayLessons;[/color]

No, see http://www.php.net/manual/en/languag...tors.array.php
Only elements from the 2nd array with keys that do not exist in the 1st
array are appended. Use array_merge() in stead. See

http://php.net/arraymerge

[color=blue]
> However, I want the newly formed $CombinedArray to be so that the main
> elements are ordered by the courseID.[/color]

http://php.net/usort , see example 2.

--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/