Sort by $priority

[lukeurtnowski]

When a query on my page is run, everything is fine and the results are displayed in multiple columns, one of the columns name is priority, whose values are either 1, 2, or 3. What im trying to do is display the results (from the first query) depending on which priority is selected (1, 2, or 3). Since you are experts n ths area, is there aa simple wa to do this? (Like maybe only printing the results of the query whose priority = 1, 2, or 3 )?

[Rawhide]

Normally, you would have the user select a priority, query for only the records that match that priority, and display them.

[lukeurtnowski]

like this, if a priority is selected?

if (isset($priority)) {
$sql1="select *, DATE_FORMAT(a_date, '%m/%d/%Y') as a_date_formatted from company, activity where c_id=a_c_id and medivas ='$poc' and priority ='$priority'";
$query1=mysql_query($sql1) or die(mysql_error());
} 

[Rawhide]

Yes, just like that.

[lukeurtnowski]

K, maybe my logic is messed up, when the page first loads I look to see iif the priority variablle has been set. If it has, I run the same query wit a constraint on the priority variable, is this right, cause nothing is returned once a priority is choosen?

if (!isset($priority)) {
$sql="select *, DATE_FORMAT(a_date, '%m/%d/%Y') as a_date_formatted from company, activity where c_id=a_c_id and medivas ='$poc'  group by medivas";
$query=mysql_query($sql);
$numofrows = mysql_num_rows($query);
} else if (isset($priority)) {
$sql="select *, DATE_FORMAT(a_date, '%m/%d/%Y') as a_date_formatted from company, activity where c_id=a_c_id and medivas ='$poc' and priority ='$priority'";
$query=mysql_query($sql) or die(mysql_error());
$numofrows = mysql_num_rows($query);
} 

Thanks, and if you want to see it in action (if that helps)
http://www.lukesplace.com/Padilla/createreport.php
(the combo box on the far right)

[Rawhide]

The problem is with this line:

<input type="hidden" name="poc" VALUE="
test medivas (1)">

See how the value includes a line break? That's causing your next query to fail. Look at the querystring in the URL after selecting a priority. It now says: "poc=%0D%0A%09test+medivas+%281%29" in stead of "poc=test+medivas+%281%29".

[lukeurtnowski]

Thank you, that worked fine. I notice 1 thing though, I'm running the result though a for and while loop to alternate table rows colors, b ut for some reason it is not working. Do you know why?

for($i = 0; $i < $numofrows; $i++) { 
    if($i % 2) {
        print "<TR bgcolor=\"silver\">\n"; 
    } else { 
        PRINT "<Tr>\n"; 
    } 
        while ($result = mysql_fetch_array($query)) { 
      print "<TD>".$result['c_name']."</TD>";
    print "<TD>".$result['c_city'].", ".$result['c_state']."</TD>";
    print "<TD>".$result['c_desc']."</TD>";
    print "<TD>".$result['rationale']."</TD>";
    print "<td>".$result['contact_name']."</TD>";
    print "<td>".$result['contact_phone']."</td>";
    print "<TD>".$result['a_date_formatted']."</TD>";
    print "<TD>".$result['a_desc']."</TD>";
    print "<td>".$result['a_next']."</TD>";
    print "<td ALIGN=CENTER>".$result['priority']."</TD>";
    print "<td>".$result['medivas']."</td>";
    print "</TR>\n"; 
            } 
        } 

the variable $numofrows returns the number of records found

[Rawhide]

bgcolor is an attribute of the <td> tag, not the <tr> tag.

Do this:

for($i = 0; $i < $numofrows; $i++) { 
    if($i % 2) { 
        $bgcolor = "silver";
    } else { 
        $bgcolor = "white";
    } 
        while ($result = mysql_fetch_array($query)) { 
      print "<tr>"
      print "<TD bgcolor=\"$bgcolor\">".$result['c_name']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['c_city'].", ".$result['c_state']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['c_desc']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['rationale']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['contact_name']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['contact_phone']."</td>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['a_date_formatted']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['a_desc']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['a_next']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\" ALIGN=CENTER>".$result['priority']."</TD>"; 
    print "<TD bgcolor=\"$bgcolor\">".$result['medivas']."</td>"; 
    print "</TR>\n"; 
            } 
        } 

[lukeurtnowski]

I see, you're so smart, thanks

[Rawhide]

LOL Thanks.

[lukeurtnowski]

Greetings rawhide, I got some mysql help from you yesterday and it was extremely useful. Anyway, you made a little scrpt for me so I could alternate row colors, you showed me that <td> and not <tr> has an attribute of bgcolor. Here's the code you gave me...

for($i = 0; $i < $numofrows; $i++) {
    if($i % 2) {
        $bgcolor = "silver";
    } else {
        $bgcolor = "white";
    }
        while ($result = mysql_fetch_array($query)) {
    print "<tr>";
    print "<TD bgcolor=\"$bgcolor\">".$result['c_name']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['c_city'].", ".$result['c_state']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['c_desc']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['rationale']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['contact_name']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['contact_phone']."</td>";
    print "<TD bgcolor=\"$bgcolor\">".$result['a_date_formatted']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['a_desc']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['a_next']."</TD>";
    print "<TD bgcolor=\"$bgcolor\" ALIGN=CENTER>".$result['priority']."</TD>";
    print "<TD bgcolor=\"$bgcolor\">".$result['medivas']."</td>";
    print "</TR>\n";
            }
        } 

The logic seems to be right, however, when I run it and look at the source, all the <td>s' have a bgcolor of white so is there something wrong with the if statement above?
You can see it in action here...(select the MediVas POC at the right)
http://www.lukesplace.com/Padilla/createreport.php

[Rawhide]

Well, $bgcolor = white in every row, so the problem must be either in evaluating the modulo or the determination needs to be moved inside of the while statement.

I'm not a PHP developer, so I can't be sure what the problem is.