Setting Variable to array within mysql_query() array

[PianoGuy]

I found your site while attempting to solve the problem of utilizing JOIN statements in a PhP mysql_query. Your timely and well-structured assistance to LemonFizz was very helpful, and enabled me to develop the query below.

I have now queried multiple tables to produce an associative array of all of a particular student's data after selecting the student's unique ID from a drop-down. This required the use of JOIN statements in my query. The query section of the file is as follows:

if(isset($_POST["submit"])) {

$SelectID = $_POST["SelectID"];

$StudQry = mysql_query(
"select
STUDENT_CONTACT.STUD_ID as 'STUD_ID',
STUDENT_CONTACT.FNAME as 'FNAME',
STUDENT_CONTACT.MI as 'MI',
STUDENT_CONTACT.LNAME as 'LNAME',
STUDENT_CONTACT.LAST_FIRST_MI as 'LastFirstMI', STUDENT_CONTACT.ADDR1 as 'ADDR1',
STUDENT_CONTACT.ADDR2 as 'ADDR2',
STUDENT_CONTACT.CITY as 'CITY',
STUDENT_CONTACT.STATE as 'STATE',
STUDENT_CONTACT.ZIP as 'ZIP',
STUDENT_CONTACT.HPHONE as 'HPHONE',
STUDENT_CONTACT.WPHONE as 'WPHONE',
STUDENT_CONTACT.EMAIL as 'EMAIL',
STUDENT_CONTACT.PHOTO as 'PHOTO',

STUD_PROGRAM.ENTRYDATE as 'EntryDate',
STUD_PROGRAM.COMPDATE as 'CompDate',

PROGRAMS.PROG_NAME as 'ProgName',

STUDENT_NOTES.STUDNOTE_DATE as 'StudNoteDate',
STUDENT_NOTES.STUDNOTE as 'StudNote',

ATTENDANCE.* as 'Attend2[]',

STUDENT_PERF.* as 'StudPerf2[]',

STUDENT_SCHOOL.CLASS as 'SchoolClass',
STUDENT_SCHOOL.MAJOR as 'Major',

SCHOOLS.SCHOOLNAME as 'School'

from STUDENT_CONTACT

inner join STUD_PROGRAM on STUDENT_CONTACT.STUD_ID=STUD_PROGRAM.STUD_ID
inner join PROGRAMS on PROGRAMS.PROG_ID=STUD_PROGRAM.PROG_ID

left join STUDENT_PERF on STUDENT_CONTACT.STUD_ID=STUDENT_PERF.STUD_ID

left join STUDENT_NOTES on STUDENT_CONTACT.STUD_ID=STUDENT_NOTES.STUD_ID

left join ATTENDANCE on STUDENT_CONTACT.STUD_ID=ATTENDANCE.STUD_ID

left join STUDENT_SCHOOL on STUDENT_CONTACT.STUD_ID=STUDENT_SCHOOL.STUD_ID

left join SCHOOLS on STUDENT_SCHOOL.SCHOOL=SCHOOLS.SCHOOL_ID

where STUDENT_CONTACT.STUD_ID = '$SelectID'");

$row = mysql_fetch_array($StudQry, MYSQL_ASSOC);

I am able to set variables to and display all of the information, except the two arrays. I believe that I just don't know the proper syntax to apply a mysql_fetch_array() to those arrays and set the result to a variable.

So, I have three questions:
1) Am I thinking correctly?
2) If so, what is the proper syntax?

Grateful for any help,
PianoGuy

[coopster]

Welcome to WebmasterWorld, PianoGuy.

I believe you are trying to apply a PHP/HTML array-building trick within a MySQL query statement ... that won't work. PHP will build arrays for you automagically when you use the bracketed syntax coming from an HTML form value [php.net], but that is specific to PHP and has nothing at all to do with MySQL.

It seems as though you are trying to return every column of those two particular tables as an array. Well, that's a lot of work. You would have to concatenate every one of the columns together and return them as one value, then in PHP you would have to break them apart again anyway as PHP will not recognize it as an array, merely as a string value.

You should either name each column specifically as you have done with the rest of your tables or simply leave it as is without any ALIAS name.

... 
STUDENT_NOTES.STUDNOTE as 'StudNote', 
ATTENDANCE.*, 
STUDENT_PERF.*, 
STUDENT_SCHOOL.CLASS as 'SchoolClass', 
...

The only danger is that one of the column names in either of those two tables has the same name as another column in your query as there are quite a few columns being returned.

Make sense?