[SOLVED] Help with PHP logic

cleibesouza · July 3, 2008

Ok, this is my last resource.

I have some data coming from a view. The view isn't normalized and I can't normalize it. So, I have to deal with what I have.

Currently the view I'm looking at has 3 records

-------------------------------------------------------------------------------------------------------------

fee_pk person_id event_pk session_pk session_name session_date credit_name credit_hours

1 joe1255 23435 64718 session 1 03/19/2008 AAFP Elective 1

2 joe1255 23435 64719 session 2 04/01/2008 AAFP Prescribed 2

3 joe1255 23435 64719 session 2 04/01/2008 AANA 3

As you can see, I have 2 session_pk that are the same (64719).

So, this data should display like this:

Session Name: session 1

Session Date Mar 19, 2008

Credit Name AAFP Elective

Credit Hours 1

-------------------------------------

Session Name: session 2

Session Date April 04, 2008

Credit Name Credit Hours

AAFP Prescribed 2

AANA 3

------------------------------------

Meaning: If the session_pk is the same, don't display on a different row, but under the same session name.

I've tried all I know to solve this and can't get it to work.

My approach was to compare the current session_pk with the next session_pk. If they're the same, don't place them on another row, which works, but because I need until the end of my record set, the data shows up repeated on the different row. Like this:

Session Name: session 1

Session Date Mar 19, 2008

Credit Name AAFP Elective

Credit Hours 1

-------------------------------------

Session Name: session 2

Session Date April 04, 2008

Credit Name Credit Hours

AAFP Prescribed 2

AANA 3

------------------------------------

Session Name: session 3

Session Date April 04, 2008

Credit Name AANA

Credit Hours 3

Any help is greatly appreciated.

themistral · July 3, 2008

Can you post the query that gets the data for you - looks like you might need to use a GROUP BY clause in your sql query.

cleibesouza · July 3, 2008

SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID' ;

I've tried a group by session_pk, but get this error:

Column 'ma_vwPersonAttendance.attendancefee_pk' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16)

grimmier · July 3, 2008

add ORDER BY `session_pk` to your query string.

then use php to iterate through the results.

<?php 


//when displaying your results preform a check
$tmp = 'notset';
do {
     if ($row_Results['session_pk'] != $tmp){
      $tmp = $row_Results['session_pk'];
       echo "<br />"; //this carrage return is to split the results to make everything easier to see.
      
       //   
      //normal output goes here.
      //

      echo "<br />";

  } else {

      //
      //additional results go here
      // 
      echo $row_Results['credit_name']."   ".$row_Results['credit_hours']."\n";

      }

} while ($row_Results = mysql_fetch_assoc($Results));

?>

ok so i am not awake yet. edited some typos and missing semi-colons

cleibesouza · July 3, 2008

ORDER BY MUST be by session_date

grimmier · July 3, 2008

hrm ok then do a double order.

order by `session_date`, `session_pk`

this will first order everything by date and then session pk based upon the date.

The only reason i say to do that is, because just ordering by date, can throw your session_pk out of order if they were entered into the DB out of order.

cleibesouza · July 3, 2008

I'm not too familiar with do while loops.

If I use your example I don't need my for loop right?

This piece of code is all I need?

I'm trying as we 'speak'

grimmier · July 3, 2008

hrm what does your for loop look like?

if i can see that i can just modify what i put up there to your code.

should make life easier.

basically a do while loop is the same as a for loop, the main difference is that a do loop will go through the loop at least once.

then check the while at the end to see if it has to go through it again.

in this case the while is checking to see if there are still more rows of data from your query result.

cleibesouza · July 3, 2008

This is my entire logic. Let me know if it's too confusing:

What I tried to do was to put the session_pk into an array and create another array with the next session_pk and compare both. So I get the results, but because my loop is going through the entire table, I will always get the row that I don't want as another row.

for($x = 0; $x < $casceNumRes; $x ++){

$tRow = mssql_fetch_array($transcriptResults);

$sessionDate = explode(' ', $tRow['session_begin_ts']);

$month = $sessionDate[0];

$day = $sessionDate[1];

$year = $sessionDate[2];

$sessionDate = $month . ' ' . $day . ', ' . $year;

$session[] = $tRow['session_pk'];

$creditName[] = $tRow['credit_name'];

$creditHours[] = $tRow['credit_hours'];

for($y = 0; $y < $casceNumRes; $y ++){

$qRow = mssql_fetch_array($qr);

$session2[] = $qRow['session_pk'];

$creditName2[] = $qRow['credit_name'];

$creditHours2[] = $qRow['credit_hours'];

}//end for

echo "<tr><td colspan=\"3\">

<th align=\"left\" width=\"15%\">Session Name:</th>

<td colspan=\"2\">" . $tRow['session_name'] . " - " . $tRow['session_pk'] . "</td>

</tr>

<th align=\"left\">Session Date</th>

<td colspan=\"2\">" . $sessionDate . "</td>

</tr>

<th align=\"left\">Credit Name</th>

<td colspan=\"2\">" . $tRow['credit_name'] . "</td>

</tr>

<th align=\"left\">Credit Hours</th>

<td colspan=\"2\">" . $tRow['credit_hours'];

if($session[$x] == $session2[$x + 1]){

echo "<tr><td> </td>

<td><strong>Credit Name:</strong></td><td> " . $creditName2[$x + 1] . "</td></tr>

<td><strong>Credit Hours:</strong></td><td> " . $creditHours2[$x + 1] . "</td></tr>" ;

}else{ echo "</td></tr>"; }

grimmier · July 3, 2008

here try this.


<?php
	   
$tRow = mssql_fetch_array($transcriptResults); //this should go right after your query is setup.               
$tmp = 'notset';
$x = -1; //set to -1 to start. first pass through will set to 0 and then incriment. 
do {
     if ($tRow['session_pk'] != $tmp){
		$x++;
	   $tmp = $tRow['session_pk'];
	   $sessionDate = explode(' ', $tRow['session_begin_ts']);
	   $month = $sessionDate[0];
	   $day = $sessionDate[1];
	   $year = $sessionDate[2];
	   $sessionDate = $month . ' ' . $day . ', ' . $year;  

		if ($x > 0){ //if this isn;t the first pass through and the session_PK is different that the last one processed we need to close the table before making a newone.
			echo "</table>";
			echo "<br />"; //this carrage return is to split the results to make everything easier to see.
		}
       	
  
  		//Normal Output    
  	echo "<tr><td colspan=\"3\">
                     <table id=\"tb$x\" border=\"0\" width=\"100%\">
                        <tr id=\"test$x\">
                     <td width=\"10\"><input type=\"checkbox\" id=\"check$x\" name=\"" . $tRow['attendancefee_pk'] . "\" value=\"1\" onclick=\"javascript:toggleBG('tb$x')\"></td>
                     <th align=\"left\" width=\"15%\">Session Name:</th>
                     <td colspan=\"2\">" . $tRow['session_name'] . " - " . $tRow['session_pk'] . "</td>
                    </tr>
                    <tr  id=\"test$x$x\">
                       <td> </td>
                       <th align=\"left\">Session Date</th>
                     <td colspan=\"2\">" .  $sessionDate . "</td>
                    </tr>
                    <tr id=\"test$x\">
                       <td> </td>
                       <th align=\"left\">Credit Name</th>
                     <td colspan=\"2\">" . $tRow['credit_name'] .  "</td>
                    </tr>
                    <tr id=\"test$x\">
                       <td> </td>
                       <th align=\"left\">Credit Hours</th>
                     <td colspan=\"2\">" . $tRow['credit_hours']. "</td></tr>";


	  } else {

		  //
		  //additional results go here
		  // 
		  echo      "<tr id=\"test$x\">
						   <td> </td>
						   <th align=\"left\">Credit Name</th>
						 <td colspan=\"2\">" . $tRow['credit_name'] .  "</td>
						</tr>
						<tr id=\"test$x\">
						   <td> </td>
						   <th align=\"left\">Credit Hours</th>
						 <td colspan=\"2\">" . $tRow['credit_hours']. "</td></tr>";
	 }

} while($tRow = mssql_fetch_array($transcriptResults));

?>

i don't have data to play with to test it thoroughly, but it should work.

cleibesouza · July 3, 2008

IT DOES WORK!!!!

I'm trying to display like this only when there's more than one credit under the same session:

Credit Name Credit Hours

test 1 1

test 2 2

grimmier · July 3, 2008

does that mean its working as intended?

cleibesouza · July 3, 2008

I'm having the db admin to add more data to the view so I can fully test it, but so far so good!!

I'll give you an update as soon as I've tested it.

I really appreciate your help on this!!!

grimmier · July 3, 2008

oops one more thing i would add.

after

} while($tRow = mssql_fetch_array($transcriptResults));

add in an echo for </table>

this will close the last table of results, since it will not be going through he loop anymore once out of data.

cleibesouza · July 3, 2008

It looks like it didn't work. I had more data added to the view with the same session_pk and other added with different session_pk. Apparently it's grouping the events with the same session_pk in pairs. What should happen is that all events with the same session_pk should show together, under the same session name.

Look at the screenshot:

Thanks.

grimmier · July 3, 2008

did you try adding the 2nd order by criteria?

that should fix that.

cleibesouza · July 3, 2008

That's how the query is right now:

SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID' order by session_begin_ts, session_pk

but still doesn't work as intended.

grimmier · July 3, 2008

sorry about that i keep forgetting its a MS SQL database and not MySQL

You can not perform an ORDER BY on a text, ntext, or image field (those fields are actually pointers) in a MS SQL database.

if either of those fields are text fields i would say change them to varchar(sizelimit) type fields. this will allows the order by to work.

cleibesouza · July 3, 2008

I still don't think it's a problem with the Order by, because it should put everything with the same session_pk under the same session_name. Right now it does in pairs and not for the existing 4 all together. If the order by was a problem, it wouldn't put them in pairs.

Make sense?

grimmier · July 3, 2008

it would if they were entered into the DB in that order though.

if ordering can't sort by the 2nd field. you will only get output sorted by the first field, and then everything else is in order they were input.

so say you have a db

Fields = f_name, l_name, date_joined , comment.

with data something like

john, doe, 01/10/2008, none

adam, sandler, 11/23/2007, something

jane, smith, 01/10/2008, none

adam, doe, 01/10/2008, another doe.

brian, adams, 02/13/2008, none

say you sorted your query like "SELECT * FROM DB ORDER BY date_joined ASC"

you would get output as one would expect.

adam, sandler, 11/23/2007, something

john, doe, 01/10/2008, none

jane, smith, 01/10/2008, none

adam, doe, 01/10/2008, another doe.

brian, adams, 02/13/2008, none

But if you were to sort it like "SELECT * FROM DB ORDER BY date_joined, l_name ASC"

your output would be

adam, sandler, 11/23/2007, something

john, doe, 01/10/2008, none

adam, doe, 01/10/2008, another doe.

jane, smith, 01/10/2008, none

brian, adams, 02/13/2008, none

Notice that the doe's are still in the order that they were entered into the DB.

if the field for l_name was a text field and not a varchar field. your sort for the 2nd one would come out the same as the first one. since after sorting by date it couldn't do anything to the text field and thus won't sort further.

check against an unsorted dump of the same query and most likely that is what was happening.

you could even go as far as running 3 seperate queries to compair the results.

say

query1 = SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID'

query2 = SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID' order by session_begin_ts

query3 = SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID' order by session_begin_ts, session_pk

if i am correct then you should have the same dump output between query2 and 3.

a way to test this would be a query 4

query4 = SELECT * FROM ma_vwPersonAttendance WHERE person_id = '$casceID' order by session_pk

this one is trying to only sort by session_pk. if those are still out of order then its the wrong field type.

cleibesouza · July 7, 2008

Because of the break I didn't have time to thank you for helping me with this issue.

Thank you very much!!!

Sign In

[SOLVED] Help with PHP logic

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information