arrays -- how to sort

robkir

Arrays are sure confusing. I've done a bunch of reading, but still don't get it.

Here's the data stored in the table--there are three fields: siteID, gameTime and team:

site time team
1 8:00 am 3-1
2 7:45 am 2-4
3 8:00 am 5-8
4 7:00 am 7-6

I want to create a report that has the following

Site 1 8:00 am 3-1 ||Site 2 7:45am 2-4 || Site 3 8:00 am 5-8 || Site 47:00am 7-6

Because I sorted the records in the the table by gameTime then siteID, when I print the record, I end up with the following:

Site 1 7:00am 7-6 || Site 2 7:45am 2-4 || Site 3 8:00 am 3-1 || Site 48:00 am 5-8

Because it sorts by time first, It puts site 4's information in site 1's column.

Since every row has four columns, and the records contain the siteID, I thought I'd stick everything in an array and sort the array on the siteID, printing them out in groups of four (since there are four sites). But I can't get it to sort right or figure out how to print it out in groups of four. I tried all the obvious sorts (ksort, array_multisort) but nothing jumps out.

Here are snippets of the code:

$sql = 'SELECT * FROM `master_game_sched` '
        . ' WHERE `siteID` = 3 && gameDate = 20110409'
        . ' Order By gameTime, siteID';

I put stuff into an array using this:

$slot[]= $row['field'];
$slot[]=$row['gameTime'];
$slot[]=$row['gameID'];

I'm still shaky on how best to extract the data. I can see its there with print_r. I assume I should use a foreach...?

foreach ($slot as $value) { echo "$value <br/>";}

Direction and explanations--suggestions, etc, are all gratefully accepted.

xxdalexx

When you pull it from the db, $row is already an array, you don't need to transfer the information into another array. You can just do something like so:

$sql = 'SELECT * FROM `master_game_sched` '
        . ' WHERE `siteID` = 3 && gameDate = 20110409'
        . ' Order By gameTime, siteID'; 

$result = mysql_query($sql, $dbcnx);
 //dbcnx is the database connection


while ($row = mysql_fetch_array($result))
{
   echo $row['field'];
   echo $row['gameTime'];
   echo $row['gameID']; 
}

Or whatever you are trying to do with the information. While is a loop it will do for every row in your table.

bradgrafelman

Some notes to clear up any confusion:

The order that the columns appear in your database is irrelevant when retrieving or displaying data.
Don't use 'SELECT ' queries - name the columns (e.g. "SELECT col1, col2, ...") that you need data from (even if that includes every column in the table at that point in time).
Using the above tip, you can SELECT columns in any order.
Use [man]mysql_fetch_assoc/man to retrieve results from the result set into an associative array, where the index will be the name of the column.
Naturally, once you do the above, you can output the data in any order that you like.

xxdalexx

I was throwing a general idea out, and I do agree with what you added on.
Only question I have is why shouldn't you use a SELECT * if you are using every column in the table?

bradgrafelman

xxdalexx wrote:
Only question I have is why shouldn't you use a SELECT * if you are using every column in the table?

There's a couple of reasons, namely: Just because you're using every column now doesn't mean you will in the future if the schema changes.

In other words, it's just more scalable/easier to read if you explicitly name the columns in the SELECT query.

robkir

Thanks for the speedy reply.

I am not sure I clearly stated my problem.

Lets say I had four rows by four columns of information. The column headings are Field 1, Field 2, etc. Each cell presents the Site Number, the Time and the Teams Playing. So column one might say "Site 1, 08:00 am, Team 3-1...etc.

I SELECT the records with ORDER BY using earliest "time" first, then by "site". If all four columns have the same starting time--then all the data will be placed in the correct column. But if one of the games, say the one in column two, starts before the game that should be in column 1 then the sorting doesn't work because the record was sorted by "time".

So...I was thinking the way to tackle the problem was to do things in groups. Since there are always four games in each row, put the records from the table into an array of four games (representing a 'row') and then sort it by "site" Each chunk is printed in a table and we retrieve the next row and repeat.

johanafm

$by_id = array();
# Create an array of rows, using eacb $row[siteId] as key
while ($row = mysql_fetch_assoc($result))
{
	$by_id[$row['siteId']] = $row;
}
# Sort array by keys
ksort($by_id);

# Output 4 elements per row
$item = 0;
foreach ($by_id as $id => $row)
{
	echo $id;
	++$item;

if ( ($item) % 4 == 0)
{
	echo '<br/>';
}
else
{
	 echo ' | ';
}
}

robkir

Hi johanafm

Thanks for the code. I tried to get it run, but had difficulties, probably because I find arrays so confusing. I am in crunch time for the next week trying to get the final details together before this software gets a full on test. I'll revisit this next week (FYI, I worked up another, less elegant solution to get myself through this tournament--but I really need to master this).

Question. If I repost follow up questions here, does phpBuilder notify you, or do you just surf the newbie forum looking for interesting problems to help solve? Or is there a non intrusive way I could follow up with you (okay...so I haven't actually read the instruction manual. There so little time and so much to do.)

Cheers,

robkir

johanafm

I log in here when I feel like it, and nothing would make me come here otherwise. So, a notification would not make me want to go here. Quite the opposite - since then it'd just feel like work. But, even if I wouldn't show up here in time to find your post among the latest ones, the beauty with bigger groups is that other people will.

Whenever you're not sure about something, a visual inspection is often great help. I.e. make use of output functions such as print_r

# Sort array by keys
ksort($by_id);
# use <pre> elements to get sensible formatting of array output in a web browser
echo '<pre>' . print_r($by_id, 1) . '</pre>';

After that you should have a good idea about the array structure, and I believe you'll understand the rest on your own. And if not, just ask.

robkir

I like your philosophy. It reminds me of my own. I appreciate whatever time you are willing to give. PHP Builder has been without a doubt my best teacher.