Page 1 of 1

Help with separating query results

Posted: Thu Oct 20, 2005 3:08 pm
by chazlink
Hello all,

I hope everyone is doing well today. I have a question that may or may not be fairly easy to answer. I am building a search for a site that uses php/mysql to search a database of students and return the results.

The results are organized by academic quarter and then by last name within the quarter.

Here is the code:

Code: Select all

$result = mysql_query( "SELECT * FROM mentee WHERE published = 'yes' ORDER BY quarter DESC, trim(lastName)" )
					or die("SELECT Error: ".mysql_error());
My question is this:

All of the results are showing up fine, however, there is no real way to differentiate when the first quarter ends, and the second quarter begins (besides when the Last name letters start over at 'A' again).

Is there a way to insert say, a line break, in between the first, second, third, and so on quarters?

Any help would be greatly appreciated. Thank you all so much for your time and assistance, I know it is valuable.

-chazlink

Posted: Thu Oct 20, 2005 3:24 pm
by TeckniX
It seems that you're ordering stuff by quarters, so you are storing quarter values in your DB - Simply add a manual break when going through the return data when the quater number change.

Posted: Thu Oct 20, 2005 3:34 pm
by chazlink
Hello TeckniX


Thanks for the reply. Unfortunately, I am a bit of a total n00b when it comes to the syntax, and I am generally new at php also. Could you possibly give me a bit more detail?

I can show you more code if you would need it...

Thank you so much for your time.

-chazlink

Posted: Thu Oct 27, 2005 12:20 pm
by TeckniX
ok in your code

Code: Select all

$result = mysql_query( "SELECT * FROM mentee WHERE published = 'yes' ORDER BY quarter DESC, trim(lastName)" )
               or die("SELECT Error: ".mysql_error());
you are ordering the result by the DB value ' quarter' - So when you fetch the information if the quarter value changes, then you can simply draw a line and make it seem like you've done 4 different query

So your code would be something like the above then :

Code: Select all

$tempQrt=$mysql_result($result, , quarter);
    
    while ($row = mysql_fetch_array ($result)) {
       
       //Then simply add an if
       if($tempQrt=!$row["quarter"] )  //so if I just went into a new quarter then add line break
       echo "------------------ break of quarter -------------------";

       echo "1st Column name goes here ".$row[0]."<br>\n";

       echo "2nd Column name goes here ".$row[1]."<br>\n";
             .   
             .
             .
         echo "Quarter ".$row["quarter"]."<br>\n";
         $tempQrt = $row["quarter"];   //set the $tempQrt to the last seen quarter
}
If i'm wrong someone correct me, but without testing this code i think it should work with some minor tweaks here and there.
Enjoy!

Posted: Thu Oct 27, 2005 12:39 pm
by shiznatix
it might be easier if you split up your sql table with different rows

year - the year of the quarters
quarter1 - the first quarter
quarter2 - the second quarter
quarter3 - the third quarter

then do

Code: Select all

$sql = '
  SELECT
    quarter1, quarter2, quarter3
  FROM
    table_name
  ORDER BY
    year
  DESC
';

$query = mysql_query($sql) or die(mysql_error());

while ($info = mysql_fetch_assoc($query))
{
    $quarter1[] = $info['quarter1'][$info['year']];
    $quarter2[] = $info['quarter2'][$info['year']];
    $quarter3[] = $info['quarter3'][$info['year']];
}

echo '<pre>';
print_r($quarter1);
echo '</pre>';

echo '<pre>';
print_r($quarter2);
echo '</pre>';

echo '<pre>';
print_r($quarter3);
echo '</pre>';
you can mess around with the info in every which way when doing it that way. thats at least how i would do it