Alphabetical MYSQL listing using PHP

[sclarkmc]

I need some help. I have been trying to do this for like 2 weeks and I just cannot come up with a solution. Can anyone offer a suggestion of how I can proceed with this.

My MYSQL table structure is this:

idnum int(10) NOT NULL auto_increment,
day char(3) default NULL,
time varchar(15) default NULL,
section char(2) default NULL,
instructor varchar(30) default '0',
offsite char(1) default 'N',
server int(1) default '0',
courseid varchar(40) default NULL,
station char(3) default NULL,
dayorder tinyint(1) default '0',
semesterid int(10) default NULL,
visible char(1) default 'N',
PRIMARY KEY (idnum,idnum),
UNIQUE KEY idnum (idnum),
KEY idnum_2 (idnum)

I am running the following SQL statement with PHP

Select * from tablename where day=dayname order by courseid;
(there is nothing wrong with the sql it runs fine i just substituted names where the values usually go)

So here's what it is currently doing, it is being output in a table like so this is based on the courseid field:

A B
C D
E F

Which is fine, it outputs the data wonderfully, however I would really like it to appear in this order also based on the courseid field:

A D
B E
C F

I cannot think of way to do this and I've been trying for about 2 weeks.

The problem comes in here. I'm need to use a majority of the fields in the output. It is used to build a table of links to various areas. The server, and courseid make up the link, where a substring of courseid makes up the link text along with the instructor field. The time and offsite fields also determine if additional data shows up next to the link (just an * and the time if it is not a certain time) Anway, I can do all the printing of the different variables, I just need access to all the data of a row

I have thought about using IDNUM, however in this case some of the number are 1 then 200 it just depends on when the class was added. Plus I really don't want this to be handled by the database.

I have probably confused the heck out of some people, however any help you can provide would be most appreciated.

Thanks,
Sean Clark-McCarthy

[mjpelikan]

An ugly way of doing this is to use two queries.
Cycle through them two build your two table columns. Use mysql_num_rows /2 to find the midway point for your second column.

If you wanted to just use one query... you could... hmmm... good luck!

[HalfaBee]

The only way I can see to do this is to read the whole result into an array and the output the data in the order you want it.

The major problem is that you can have any number of results. So you have to know where the halfway point is before you can output the table.

Good luck there are still 9 weeks left in the year :)

HalfaBee

[sclarkmc]

Hey just wanted to let you know that I got it!! here's what I did, this is probably a sloppy way to do this but it works and that is all that matter. Just in case you ever need to use it in the future.

Here got my numbers to start with, total number of rows, then half the number, the ceil funtion corrects for .5 values.

$numrows = mysql_num_rows($result);
$halfrows = $numrows / 2;
$halfrows = ceil($halfrows);


Then this makes a seperate array for each row.

// Populate Rows into Multi Dimensional Array
$c = 0;
while ($getcourseinfo = mysql_fetch_array($result, MYSQL_ASSOC)) {
$c++;
$courseinfo[$c] = $getcourseinfo;
}


Here I start the output by building the table which will be no larger than halfrows. I then assign seperate values for each array element that I'm going to use to make the coding below it easier to write and understand.

$d = 0;
echo "<table border=\"1\" width = \"560\" style=\"border-collapse: collapse\" bordercolor=\"#111111\">\n";
while ($d < $halfrows) {
$d++;
$rowtwo = $d + $halfrows;

//Assign array variables to standard Variables
$class = substr($courseinfo[$d][courseid], 0, 6);
$class2 = substr($courseinfo[$rowtwo][courseid], 0, 6);
$server = $courseinfo[$d][server];
$server2 = $courseinfo[$rowtwo][server];
$courseid = $courseinfo[$d][courseid];
$courseid2 = $courseinfo[$rowtwo][courseid];
$section = $courseinfo[$d][section];
$section2 = $courseinfo[$rowtwo][section];
$instructor = $courseinfo[$d][instructor];
$instructor2 = $courseinfo[$rowtwo][instructor];
$offsite = $courseinfo[$d][offsite];
$offsite2 = $courseinfo[$rowtwo][offsite];
$day = $courseinfo[$d][day];
$day2 = $courseinfo[$rowtwo][day];
$time = $courseinfo[$d][time];
$time2 = $courseinfo[$rowtwo][time];


Then I just output each row, first cell i used the $d index and in the second cell i used the $rwotwo index.

Pretty cool huh??

Thanks for your help.