Jump to content

Sorting Listing by Variable


ArchaicMedia

Recommended Posts

I got myself wrapped up in something I can't get figured.
 
In the following code, I would like to order the list of properties ("<div class="property">") by the "$pr_street" variable.

 

Any help is appreciated. Thanks,
 

<?php
/////////////// SEO ///////////////
$keywords = "";
$description = "";
/////////////// DB Connection ///////////////////
include('../includes/connect.php');
include('../includes/login.php');


if(isset($_GET['delete'])){
	$del_id = $_GET['delete'];
	$sel_del_imgs_q = "SELECT * FROM pr_imgs WHERE pr_id = '$del_id'";
	$pr_img_del_result = mysqli_query($db, $sel_del_imgs_q);
	while($pr_del_imgs = mysqli_fetch_array($pr_img_del_result)){
		$pr_del_img_id[] = $pr_del_imgs['pr_img_id'];
		$pr_del_img_path_xl[] = $pr_del_imgs['pr_img_path_xl'];
		$pr_del_img_path_l[] = $pr_del_imgs['pr_img_path_l'];
		$pr_del_img_path_m[] = $pr_del_imgs['pr_img_path_m'];
		$pr_del_img_path_s[] = $pr_del_imgs['pr_img_path_s'];
		$pr_del_img_path_xs[] = $pr_del_imgs['pr_img_path_xs'];
	}
	for($i = 0; $i < count($pr_del_img_id);$i++){
		if(!empty($pr_del_img_path_xl[$i])){
			unlink('..'.$pr_del_img_path_xl[$i]);
		}
		if(!empty($pr_del_img_path_l[$i])){
			unlink('..'.$pr_del_img_path_l[$i]);
		}
		if(!empty($pr_del_img_path_m[$i])){
			unlink('..'.$pr_del_img_path_m[$i]);
		}
		if(!empty($pr_del_img_path_s[$i])){
			unlink('..'.$pr_del_img_path_s[$i]);
		}
		if(!empty($pr_del_img_path_xs[$i])){
			unlink('..'.$pr_del_img_path_xs[$i]);
		}
	}
	$dir_to_remove = dirname(getcwd()).'/images/properties/'.$del_id;
	rmdir($dir_to_remove);
	$delete_query = "DELETE property, pr_imgs FROM property LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id WHERE property.pr_id = '$del_id'";
	$deleted = mysqli_query($db, $delete_query);
	if($deleted){
		header('Location:index.php');
		//print $delete_query;	
	}
}

$sel_pr_type = 1; // 1 = For Rent Residential, 2 = For Rent Commercial, 3 = For Sale

$pr_sel_query = "SELECT * FROM property LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0' ORDER BY property.pr_live DESC, property.pr_modified DESC";
$result = mysqli_query($db,$pr_sel_query);
while($pr_row = mysqli_fetch_array($result)){
	$pr_id[] = $pr_row[0];
	$pr_type[] = $pr_row['pr_type'];
	$pr_live[] = $pr_row['pr_live'];
	$pr_title[] = $pr_row['pr_title'];
	$pr_street[] = $pr_row['pr_street'];
	$pr_city[] = $pr_row['pr_city'];
	$pr_state[] = $pr_row['pr_state'];
	$pr_zip[] = $pr_row['pr_zip'];
	$pr_price[] = $pr_row['pr_price'];
	$pr_bedroom[] = $pr_row['pr_bedroom'];
	$pr_bathroom[] = $pr_row['pr_bathroom'];
	$pr_size[] = $pr_row['pr_size'];
	$pr_img_path_s[] = $pr_row['pr_img_path_s'];
}

include('../includes/head.php'); 
include('../includes/header.php'); 

ob_end_flush(); // ob_start() in /includes/login.php
?>
<div class="content">
  <div class="hldr">
  <?php if($loggedin){ ?>
    <div class="left">
    <h2><a href="/admin/">Admin</a></h2>
        <?php for($i = 0; $i < count($pr_id); $i++){ 
			if($pr_live[$i] == 1){
				$islive[$i] = ' • <span class="live">Live</span>';
			}
		?>
		<div class="property">
        	<div class="thumb"><a href="/property_details.php?property=<?php print $pr_id[$i]; ?>"><img src="<?php print $pr_img_path_s[$i]; ?>" width="100" height="100" /></a></div>
            <div class="details">
           	  <h4><a href="/property_details.php?property=<?php print $pr_id[$i]; ?>"><?php print $pr_title[$i]; ?></a></h4>
                <ul>
                	<li>Bedroom: <?php print $pr_bedroom[$i]; ?> • Bathroom: <?php print $pr_bathroom[$i]; ?></li>
                    <li class="adrs"><?php print $pr_street[$i].', '.$pr_city[$i].', '.$pr_state[$i].' '.$pr_zip[$i]; ?></li>
                    <li>Price: $<?php print $pr_price[$i]; ?> 4 • Size: <?php print $pr_size[$i]; ?> sq ft</li>
                    <li><a href="/admin/add-property.php?edit=<?php print $pr_id[$i]; ?>">EDIT</a> • <a href="/admin/index.php?delete=<?php print $pr_id[$i]; ?>" class="red" onclick="return confirm('Are you sure you want to deletet this Property?');">DELETE</a><?php print $islive[$i]; ?></li>
                </ul>
            </div>
		</div>
        <?php } ?>
    </div>
    <div class="right">
      <div class="sidebarHome">
	   <div class="sidebarHome">
    	  <p><strong><a href="/admin/">Admin Home</a></strong></p>
    	  <p><strong><a href="add-property.php">Add Property</a></strong></p>
    	  <p><strong><a href="for-rent-res.php">Edit Properties for Rent - Residential</a></strong></p>
    	  <p><strong><a href="for-rent-com.php">Edit Properties for Rent - Commercial</a></strong></p>
    	  <p><strong><a href="for-sale.php">Edit Properties for Sale </a></strong></p>
    	  <p><strong><a href="index.php?logout=1">Log Out </a></strong></p>
	    </div>
    </div>
    <div class="clear"> </div>
  </div>
<?php }else { ?>
	<div class="fullpage">
    	<div class="login">
        <?php print $error_msg; ?>
    	<form method="post" action="index.php">
        	<label>Username:</label>
        	<input type="text" name="user" class="txt" />
            <label>Password:</label>
            <input type="password" name="pass" class="txt" /><br />
            <input type="submit" name="login" value="Login" class="submit" />
            </form>
        </form>
        </div>
    </div>
<?php } ?>
</div>
<?php include('../includes/footer.php'); ?>
Link to comment
Share on other sites

That code is hard to follow and it seems you are doing some unnecessary things. For example this

 

while($pr_row = mysqli_fetch_array($result)){
$pr_id[] = $pr_row[0];
$pr_type[] = $pr_row['pr_type'];
$pr_live[] = $pr_row['pr_live'];
$pr_title[] = $pr_row['pr_title'];
$pr_street[] = $pr_row['pr_street'];
$pr_city[] = $pr_row['pr_city'];
$pr_state[] = $pr_row['pr_state'];
$pr_zip[] = $pr_row['pr_zip'];
$pr_price[] = $pr_row['pr_price'];
$pr_bedroom[] = $pr_row['pr_bedroom'];
$pr_bathroom[] = $pr_row['pr_bathroom'];
$pr_size[] = $pr_row['pr_size'];
$pr_img_path_s[] = $pr_row['pr_img_path_s'];
}

 

Why dump all the values from the query into separate arrays and why is the first value set using the numeric index from the results instead of the named index? That value HAS to have a field name - use that. And, you can just output the results when you read the records from the result set from the query. Let me try and provide a solution in a few minutes.

Link to comment
Share on other sites

OK, I read through your code and you are already ordering the query that is used to get those results.

 

SELECT *
FROM property
LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0'
ORDER BY property.pr_live DESC, property.pr_modified DESC

You should order the results how you want them displayed.

 

So, are you wanting them to ALL be sorted on 'pr_street' or are you wanting them to be sorted as they are now and then sorted by 'pr_street' where 'pr_live' and 'pr_modified' are the same?

 

There's a lot more I would change, but give this a try (might be some minor typos)

 

<?php
/////////////// SEO ///////////////
$keywords = "";
$description = "";
/////////////// DB Connection ///////////////////
include('../includes/connect.php');
include('../includes/login.php');
 
 
if(isset($_GET['delete']))
{
$del_id = $_GET['delete'];
$query = "SELECT pr_img_id, pr_img_path_xl, pr_img_path_l, pr_img_path_m, pr_img_path_s, pr_img_path_xs
              FROM pr_imgs WHERE pr_id = '$del_id'";
$result = mysqli_query($db, $query);
 
    $deleteIDs = array();
while($row = mysqli_fetch_array($result))
    {
if(!empty($row['pr_img_path_xl'])){
unlink('..'.$row['pr_img_path_xl']);
}
        if(!empty($row['pr_img_path_l'])){
unlink('..'.$row['pr_img_path_l']);
}
if(!empty($row['pr_img_path_m'])){
unlink('..'.$row['pr_img_path_m']);
}
if(!empty($row['pr_img_path_s'])){
unlink('..'.$row['pr_img_path_s']);
}
if(!empty($row['pr_img_path_xs'])){
unlink('..'.$row['pr_img_path_xs']);
}
 
        $dir_to_remove = dirname(getcwd()).'/images/properties/'.$row['pr_img_id'];
        rmdir($dir_to_remove);
}
 
    //Remove records for deleted id
    $deletedIDList = implode();
    $query = "DELETE property, pr_imgs
              FROM property
              LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id
              WHERE property.pr_id = '$del_id'";
    $deleted = mysqli_query($db, $query);
    if($deleted){
        header('Location:index.php');
        //print $delete_query; 
    }
}
 
$sel_pr_type = 1; // 1 = For Rent Residential, 2 = For Rent Commercial, 3 = For Sale
 
$query = "SELECT *
          FROM property
          LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0'
          ORDER BY pr_street ASC";
$result = mysqli_query($db,$pr_sel_query);
 
$propertyList = '';
while($row = mysqli_fetch_assoc($result))
{
    $liveText = ($row['pr_live']) ? ' • <span class="live">Live</span>' : '';
 
    $propertyList .= "<div class='property'>\n";
    $propertyList .= "  <div class='thumb'>\n";
    $propertyList .= "      <a href='/property_details.php?property={$row['pr_id']}'><img src='{$row['pr_img_path_s']}' width='100' height='100' /></a>\n";
    $propertyList .= "  </div>\n";
    $propertyList .= "  <div class='details'>\n";
    $propertyList .= "      <h4><a href='/property_details.php?property={$row['pr_id']}'>{$row['pr_title']}</a></h4>\n";
    $propertyList .= "      <ul>\n";
    $propertyList .= "          <li>Bedroom: {$row['pr_bedroom']} • Bathroom: {$row['pr_bathroom']}</li>\n";
    $propertyList .= "          <li class='adrs'>{$row['pr_street']}, {$row['pr_city']}, {$row['pr_state']} {$row['pr_zip']}</li>\n";
    $propertyList .= "          <li>Price: ${$row['pr_price']} 4 • Size: {$row['pr_size']} sq ft</li>\n";
    $propertyList .= "          <li>\n";
    $propertyList .= "              <a href='/admin/add-property.php?edit={$row['pr_id']}'>EDIT</a> •\n";
    $propertyList .= "              <a href='/admin/index.php?delete={$row['pr_id']}' class='red' onclick='return confirm('Are you sure you want to deletet this Property?');'>DELETE</a>{$liveText}\n";
    $propertyList .= "          </li>\n";
    $propertyList .= "      </ul>\n";
    $propertyList .= "  </div>\n";
    $propertyList .= "</div>\n";
}
 
include('../includes/head.php'); 
include('../includes/header.php'); 
 
ob_end_flush(); // ob_start() in /includes/login.php
 
?>
<div class="content">
  <div class="hldr">
  <?php if($loggedin){ ?>
    <div class="left">
    <h2><a href="/admin/">Admin</a></h2>
    <?php echo $propertyList; ?>
    </div>
    <div class="right">
      <div class="sidebarHome">
  <div class="sidebarHome">
       <p><strong><a href="/admin/">Admin Home</a></strong></p>
       <p><strong><a href="add-property.php">Add Property</a></strong></p>
       <p><strong><a href="for-rent-res.php">Edit Properties for Rent - Residential</a></strong></p>
       <p><strong><a href="for-rent-com.php">Edit Properties for Rent - Commercial</a></strong></p>
       <p><strong><a href="for-sale.php">Edit Properties for Sale </a></strong></p>
       <p><strong><a href="index.php?logout=1">Log Out </a></strong></p>
   </div>
    </div>
    <div class="clear"> </div>
  </div>
<?php } else { ?>
<div class="fullpage">
     <div class="login">
        <?php print $error_msg; ?>
     <form method="post" action="index.php">
         <label>Username:</label>
         <input type="text" name="user" class="txt" />
            <label>Password:</label>
            <input type="password" name="pass" class="txt" /><br />
            <input type="submit" name="login" value="Login" class="submit" />
            </form>
        </form>
        </div>
    </div>
<?php } ?>
</div>
<?php include('../includes/footer.php'); ?>
Link to comment
Share on other sites

Thanks again. 

 

You are correct I would like them "sorted as they are now and then sorted by 'pr_street'"

 

I have tried your code and it errors out. I tried taking a look in win-merge so I could see the differences but, the differences are many and confusing : ).

 

Can you give me another push in the right direction?

Link to comment
Share on other sites

That generally indicates a fatal error, but you don't have display_errors enabled. Enable it at the top of your script or check the php error log for the reason assuming you have error logging enabled.  This should be done for any script you are developing, but have them disabled on the production site.

Link to comment
Share on other sites

Ok. This is the error I am getting

 

Warning: mysqli_query() [function.mysqli-query]: Empty query in /mnt/stor1-wc1-dfw1/641050/642424/www.sunworldgroup.com/web/content/admin/index.php on line 59

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /mnt/stor1-wc1-dfw1/641050/642424/www.sunworldgroup.com/web/content/admin/index.php on line 62

Link to comment
Share on other sites

It's probably these lines:

$query = "SELECT *
          FROM property
          LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0'
          ORDER BY pr_street ASC";
$result = mysqli_query($db,$pr_sel_query);

$pr_sel_query should probably just be $query.

Link to comment
Share on other sites

I don't see why it wouldn't, but since you are joining on another table it's generally a good idea to add the table name to selects, where's, order by's etc., just like you are in the actual join

ORDER BY property.pr_street ASC

If both tables had a field named pr_street, there would be errors.

 

You've also probably altered your code quite a bit, so it would be helpful to see the current code.

Link to comment
Share on other sites

Currently the code is as follow:

<?php
/////////////// SEO ///////////////
$keywords = "";
$description = "";
/////////////// DB Connection ///////////////////
include('../includes/connect.php');
include('../includes/login.php');
 
 
if(isset($_GET['delete']))
{
$del_id = $_GET['delete'];
$query = "SELECT pr_img_id, pr_img_path_xl, pr_img_path_l, pr_img_path_m, pr_img_path_s, pr_img_path_xs
              FROM pr_imgs WHERE pr_id = '$del_id'";
$result = mysqli_query($db, $query);
 
    $deleteIDs = array();
while($row = mysqli_fetch_array($result))
    {
if(!empty($row['pr_img_path_xl'])){
unlink('..'.$row['pr_img_path_xl']);
}
        if(!empty($row['pr_img_path_l'])){
unlink('..'.$row['pr_img_path_l']);
}
if(!empty($row['pr_img_path_m'])){
unlink('..'.$row['pr_img_path_m']);
}
if(!empty($row['pr_img_path_s'])){
unlink('..'.$row['pr_img_path_s']);
}
if(!empty($row['pr_img_path_xs'])){
unlink('..'.$row['pr_img_path_xs']);
}
 
        $dir_to_remove = dirname(getcwd()).'/images/properties/'.$row['pr_img_id'];
        rmdir($dir_to_remove);
}
 
    //Remove records for deleted id
    $deletedIDList = implode();
    $query = "DELETE property, pr_imgs
              FROM property
              LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id
              WHERE property.pr_id = '$del_id'";
    $deleted = mysqli_query($db, $query);
    if($deleted){
        header('Location:index.php');
        //print $delete_query; 
    }
}
 
$sel_pr_type = 1; // 1 = For Rent Residential, 2 = For Rent Commercial, 3 = For Sale
 
$query = "SELECT *
          FROM property
          LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0'
          ORDER BY pr_street ASC";
$result = mysqli_query($db,$query);
 
$propertyList = '';
while($row = mysqli_fetch_assoc($result))
{
    $liveText = ($row['pr_live']) ? ' • <span class="live">Live</span>' : '';
 
    $propertyList .= "<div class='property'>\n";
    $propertyList .= "  <div class='thumb'>\n";
    $propertyList .= "      <a href='/property_details.php?property={$row['pr_id']}'><img src='{$row['pr_img_path_s']}' width='100' height='100' /></a>\n";
    $propertyList .= "  </div>\n";
    $propertyList .= "  <div class='details'>\n";
    $propertyList .= "      <h4><a href='/property_details.php?property={$row['pr_id']}'>{$row['pr_title']}</a></h4>\n";
    $propertyList .= "      <ul>\n";
    $propertyList .= "          <li>Bedroom: {$row['pr_bedroom']} • Bathroom: {$row['pr_bathroom']}</li>\n";
    $propertyList .= "          <li class='adrs'>{$row['pr_street']}, {$row['pr_city']}, {$row['pr_state']} {$row['pr_zip']}</li>\n";
    $propertyList .= "          <li>Price: ${$row['pr_price']} 4 • Size: {$row['pr_size']} sq ft</li>\n";
    $propertyList .= "          <li>\n";
    $propertyList .= "              <a href='/admin/add-property.php?edit={$row['pr_id']}'>EDIT</a> •\n";
    $propertyList .= "              <a href='/admin/index.php?delete={$row['pr_id']}' class='red' onclick='return confirm('Are you sure you want to deletet this Property?');'>DELETE</a>{$liveText}\n";
    $propertyList .= "          </li>\n";
    $propertyList .= "      </ul>\n";
    $propertyList .= "  </div>\n";
    $propertyList .= "</div>\n";
}
 
include('../includes/head.php'); 
include('../includes/header.php'); 
 
ob_end_flush(); // ob_start() in /includes/login.php
 
?>
<div class="content">
  <div class="hldr">
  <?php if($loggedin){ ?>
    <div class="left">
    <h2><a href="/admin/">Admin</a></h2>
    <?php echo $propertyList; ?>
    </div>
    <div class="right">
      <div class="sidebarHome">
  <div class="sidebarHome">
       <p><strong><a href="/admin/">Admin Home</a></strong></p>
       <p><strong><a href="add-property.php">Add Property</a></strong></p>
       <p><strong><a href="for-rent-res.php">Edit Properties for Rent - Residential</a></strong></p>
       <p><strong><a href="for-rent-com.php">Edit Properties for Rent - Commercial</a></strong></p>
       <p><strong><a href="for-sale.php">Edit Properties for Sale </a></strong></p>
       <p><strong><a href="index.php?logout=1">Log Out </a></strong></p>
   </div>
    </div>
    <div class="clear"> </div>
  </div>
<?php } else { ?>
<div class="fullpage">
     <div class="login">
        <?php print $error_msg; ?>
     <form method="post" action="index.php">
         <label>Username:</label>
         <input type="text" name="user" class="txt" />
            <label>Password:</label>
            <input type="password" name="pass" class="txt" /><br />
            <input type="submit" name="login" value="Login" class="submit" />
            </form>
        </form>
        </div>
    </div>
<?php } ?>
</div>
<?php include('../includes/footer.php'); ?>
Link to comment
Share on other sites

I'm sorry, I still don't see why it wouldn't.

 

I'm assuming your query is running or you wouldn't get any results, but you should still check for an error.

$result = mysqli_query($db,$query);
if ( ! $result)
{
  printf("Error: %s\n", mysqli_error($db));
}

Obviously you wouldn't just want to dump the error to the screen on a live site as that tells the average user nothing, but can be quite helpful to malicious users by exposing db info.

Link to comment
Share on other sites

What changed that it started working?

 

As far as order by pr_live first, you need to tell it to.

ORDER BY pr_live ASC, pr_street ASC

So that will first order by pr_live, and then any pr_live columns that have the same value it will order it by pr_street.

 

Something like:

pr_live = 1, street = 123 some street

pr_live = 1, street = 234 some other street

pr_live = 2, street = 123 some street

pr_live = 2, street = 456 another street

Link to comment
Share on other sites

It is a live site but, it's in the admin section where you must be logged in.

 

You're editing files ON A LIVE SITE?! That's suicide. Take a backup of the code and the database and make changes in a development environment!

 

Anyway, your original "request" was vague. But, now you've confirmed that you really DON'T want to sort by street. What you really want is to sort by pr_live, then by pr_modified THEN by street. Therefore the query should probably look like this:

 

SELECT *
FROM property
LEFT OUTER JOIN pr_imgs ON property.pr_id = pr_imgs.pr_id AND pr_imgs.pr_img_count = '0'
ORDER BY property.pr_live DESC, property.pr_modified DESC, property.pr_street ASC

 

Also, you should not use '*' in the SELECT and should instead list out the actual fields you will use. And, that was production code? There's a lot of inefficient code in there. I hope you looked at the code I provided to see the types of changes that can be made. I assume the same types of problems exist elsewhere.

Link to comment
Share on other sites

Oh Dear God it Works! Thank you so much!

 

@Psycho - I didn't write the code but I will definitely work on what you are saying. Also I will head your warning of the testing server in the future.

 

@CroNix - Thanks for all your help I couldn't have done it without you (At least not in any sort of reasonable time span : ))

 

Thanks again. I had originally posted this on Stack Overflow but they laughed me out of the board. I can't thank you enough.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.