Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question help! trying to sort data w/ checkboxes

    Hello,

    I first will say that I am pretty new to PHP. I am working on a project for a website that has records that are entered in to a DB the records are then displayed in a page that shows a preview. IE, you see the address, value, # of bedrooms etc.. You can click a link and it will display details..nothing tough there..

    Heres the hard part.. I need to find how to sort these records with checkboxes (next to each DB record ie. 1 main street, 2 main street etc..) when the user clicks a form button.. I've figured out how to put the form button and make a new check box on each line but I cannot figure out a way to sort the data.

    I have tried several ways including making the form go to a 2nd page which would only show the data that was checked but I cannot figure out how to make that work nor can I figure out how to do it with just one page.

    I am really deserpate for an answer because I am really stuck on this and would like to get it done as soon as possible.

    Below is the code for the site, the way I have it now it goes to a 2nd page but that is not required, I just assume that will be easiest.

    Any suggestions on what to do? Thanks in advance!

    Code:
    $stmt = "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE contract_num = '".$contract_num."'";
    
    if ($sortby==1) 
    $stmt .= " ORDER BY address";
    
    elseif ($sortby==2) 
    
    $stmt .= " ORDER BY city";
    
    elseif ($sortby==3) 
    
    $stmt .= " ORDER BY state";
    
    elseif ($sortby==4) 
    
    $stmt .= " ORDER BY county";
    
    elseif ($sortby==5) 
    
    $stmt .= " ORDER BY units";
    
    elseif ($sortby==6) 
    
    $stmt .= " ORDER BY bedrooms";
    
    elseif ($sortby==7) 
    
    $stmt .= " ORDER BY baths";
    
    elseif ($sortby==8) 
    
    $stmt .= " ORDER BY assessed_value";
    
    else
    
    $stmt .= " ORDER BY address";
    
    if (!($dblink = mysql_pconnect($host, $user, $pass))) {
            print("Error connecting to host ");
            print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    }
    
    if (!mysql_select_db($db, $dblink)) {
            print("Error selecting database ");
            print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    }
    
    ?><title>Portfolio Listings</title>
        
    <table width="690" cellspacing="0" cellpadding="3" border="0" style="font-family: sans-serif; font-size: 9pt" align="center">
      <!--DWLayoutTable-->
      <tr> 
        <td width="31" height="21" valign="top"><div align="center"><strong>View</strong></div>
        </td>
        <td width="139" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=1" class="content">Address</a></div>
        </td>
        <td width="111" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=2" class="content">City</a></div>
        </td>
        <td width="28" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=3" class="content">State</a></div>
        </td>
        <td width="100" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=4" class="content">County</a></div>
        </td>
        <td width="35" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=5" class="content">Units</a></div>
        </td>
        <td width="58" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=6" class="content">Bedrooms</a></div>
        </td>
        <td width="32" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=7" class="content">Baths</a></div>
        </td>
      <td width="102" valign="top"><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=8" class="content">Assessed
            Value</a></div>
      </td>
      <td width="102" valign="top"> 
      </form>
      <form name='filter' action='checked.php' method='post'>
        <input type='submit' name='submit' value='Filter' ></td> </tr>
    
    
     
    
     
      <?// execute the statement
    
    if (!$result = mysql_query($stmt, $dblink)) {
            print("There was and error in the query ".mysql_error());
            print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    } else {
    while ($row = mysql_fetch_array($result)) {
    				$row_color = altBgColor();
    			    print ("<tr bgcolor=\"".$row_color."\">\n");
    				print "\n<td align=\"left\" nowrap>
    				<a href=\"show_property.php?id=
    				".$row["ID"]."\">Details</a></td>";
    				print "\n<td align=\"left\">".$row["address"]."</td>";
    				print "\n<td align=\"left\">".$row["city"]."</td>";
    				print "\n<td align=\"left\">".$row["state"]."</td>";
    				print "\n<td align=\"center\">".$row["county"]."</td>";
                    print "\n<td align=\"left\">".$row["units"]."</td>";
                    print "\n<td align=\"left\">".$row["bedrooms"]."</td>";
    				print "\n<td align=\"left\"nowrap>".$row["baths"]."</td>";
    				print "\n<td align=\"left\"nowrap>".$row["assessed_value"]."</td>";
    				print "\n<td align=\"left\"nowrap><input type='checkbox' name='kc' value='ck' /></td>";
    				print "</tr>";
            }
    		}
    ?>
    </table>

    Once again, any help would be great, thanks!

  • #2
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ideas? anyone?

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Ok, lets do one question at a time.
    This is a pretty simple task for you, what I see is a poorly constructed SQL code. No worries, its easily cleaned up. Now, you may be looking at altering your orderby clauses into a groupby clause instead, depeding on your output. But for now we will use an orderby, a more mysql guru can help you on the difference, I unfortunatly cannot.

    Alright, sorting with checkboxes can be a toughie as you don't really have a presidence set for it. So you therefore need to decide the order in which it will be displayed, which I believe is how the difference between the groupby and orderby clauses work.

    First, I'd change what you are querying. I'm understanding you give a smaller section of data for the first display, and a more instensive detailed report for the primary view correct?
    So lets change your query a bit:
    PHP Code:
    $sort_by_assignment = array(
         
    => 'address',
         
    => 'city',
         
    => 'state',
         
    => 'country',
         
    => 'units',
         
    => 'bedrooms',
         
    => 'baths',
         
    => 'assessed_value'
    );
    // Array with keys relational to your checkboxes/links

    $_REQUEST array_merge($_GET$_POST$_COOKIE);

    $condition '1=1';
    $order_by 'ID';
    $direction 'ASC';

    if (isset(
    $_REQUEST['contract_num']))
    {
         
    // This is if they chose an individual record to display more details.  Unsure of your implimentation of how it works.
         
    $condition .= " AND `contract_num` = '" mysql_real_escape_string($_REQUEST['contract_num']) . "'";
    }
    else
    {
         
    // No individual row is selected, let us choose all:
         
    if (!empty($_REQUEST['sortby']))
         {
              
    // Sortby is filled, lets look into it:
              
    if (is_array($_REQUEST['sortby']))
              {
                   foreach (
    $_REQUEST['sortby'] AS $sort_val)
                   {
                        
    $order_by .= ', ' $sort_by_assignment[$sort_val];
                   }
              }
              else
              {
                    
    $sortby mysql_real_escape_string($_REQUEST['sort_by']);
                    
    $order_by .= ', ' $sort_by_assignment[$sortby];
               }
          }
          else
          {
              
    $order_by .= ', address';
           }
    }

    $stmt "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE " $condition " ORDER BY " $order_by " " .  $direction "";

    $query mysql_query($stmt) OR die('Couldn\'t preform query...'); 
    ... is this along the idea that you are looking for? Without knowing what you want to display as details I'm not sure what columns to manipulate. Of course, this is untested, so don't try it without backing up your current files. Let me know if this is the right route you are looking to do...
    Last edited by Fou-Lu; 06-27-2005 at 03:36 PM.

  • #4
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will give that a try..

    to explain your questions: There is a page with records that says i.e.... address 111 main street 2000 rent 2 bedrooms. and you can click the details link which displays the full record (with details) on a new page. So my main issue is sorting the page without details by checkboxes and only showing the records that are checked when the button is clicked.

    thanks for the help.

    If anyone else has input, please feel free to share.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Ok, then the use of the 'filter' form is to generate a list of added/removed information correct? So on my shortaned view, if I check address, city, and cost, it will show all records, but only these three columns, correct? And then there is an added link for details page.
    Or are you referring to bringing up multiple listings for details, perhaps for comparision purposes?

  • #6
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the filter form will do just that: filter. The page before the list has several links, portfolio 1 2 3 etc.. lets say you click portfolio 2. You are brought to a page that contains the code I posted showing ONLY the entries that were entred in to the DB as portfolio 2 (contract_number is the actual name for the record that determines this)

    On this list of portfolio 2 entries there could be 2 or 222. Lets say there are 100 etries for example. I am the user and want to see only entries 1,2, 22, and 55. I need to be able to click on the check box next to these entries and click the form button and only 1,2, 22 and 55 will be left on this list.

    From this list you can compare the most basic things such as price, address etc. and if you click details on any of the entries, you get the full details on that particular property.

    the reason for this is eventually, when sorted, a excel spreadsheet will be generated comparing these properties, so if you have 20 things selected, the excel file will have those 20 entries info in it. If 2 are selected those 2 will be in the excel. I am not going to worry about the excel thing right now, I think I have software that can do the converting etc, but the most important thing right now is sorting.

    hope that helps.

    p.s. I tried the code and not quite sure what went wrong but the table displaying the entries did not appear, only the header file showed up.

    any ideas?

  • #7
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok i've changed the code a bit and almost have it working correctly.


    first of all, here is my new code:
    PHP Code:
    $stmt "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE contract_num = '".$contract_num."'";

    $list '';
    if(!empty(
    $_POST['kc']) && is_array($_POST['kc'])) {
      foreach(
    $_POST['kc'] as $kc) {
        
    $kc = (int)$kc;
        if(
    $kc 0) {
          
    $list .= "$kc";
        }
      }
      
    $stmt .= " AND ID IN ($list)";
    }

    if (
    $sortby==1
    $stmt .= " ORDER BY address";

    elseif (
    $sortby==2

    $stmt .= " ORDER BY city";
    .................... 
    how it works currently is if you only select 1 record and click the button, the other data disappears, however if you select more than 1 record, you get a blank table.

    the code that changes this is:
    PHP Code:
     $list .= "$kc";
        }
      } 
    if I change it to
    PHP Code:
     $list .= "$kc,$kc,$kc,$kc"
    for example, I can show 4 records, if I ake out 2 of those, I can show 2 but the fact is I will be working with an unknown amount that could be 10 or 10,000 and copying and pasting $kc is not the best solution.

    Is there a way I can display all the checked records no matter the number with only one line of code?

    ALSO just for reference: this is what I have for the check boxes
    PHP Code:
    print "\n<td align=\"left\"nowrap><input type='checkbox' name='kc[]' value='{$row['ID']}' /></td>"


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •