Sorting Listing by Variable

ArchaicMedia · September 4, 2014

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'); ?>

Psycho · September 4, 2014

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.

ArchaicMedia · September 4, 2014

Oh Great! I really appreciate it. I didn't write the code. It was just sent to me asking me to do this.

Psycho · September 4, 2014

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'); ?>

ArchaicMedia · September 4, 2014

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?

CroNiX · September 4, 2014

"errors out"... Can you be more specific? What is the exact error?

ArchaicMedia · September 4, 2014

Nothing shows up. The content area is blank.

CroNiX · September 4, 2014

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.

ArchaicMedia · September 4, 2014

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

CroNiX · September 4, 2014

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.

ArchaicMedia · September 4, 2014

Great. It's all back but, it does not seem to be ordering by either the previous way or by the street address?

CroNiX · September 4, 2014

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.

ArchaicMedia · September 4, 2014

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'); ?>

ArchaicMedia · September 4, 2014

I've tried messing with the ORDER BY property.pr_street ASC to no avail.

CroNiX · September 4, 2014

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.

ArchaicMedia · September 4, 2014

Adding that to the file has it print (I also have PHP errors up.)

Error:

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

ArchaicMedia · September 4, 2014

By "Up" I mean that I have it set to show errors. Currently there are no errors.

ArchaicMedia · September 4, 2014

Awesome! Now it is ordering by the address but it is not ordering the pr_live ones first.

CroNiX · September 4, 2014

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

Psycho · September 4, 2014

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.

ArchaicMedia · September 4, 2014

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.

CroNiX · September 4, 2014

I appreciate you saying that. Most people just take the help and don't post again once they have it working.

Sign In

Sorting Listing by Variable

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

Link to comment

Share on other sites

Join the conversation

Important Information