[SOLVED] PHP doesn't know how to count?

[jesse_james]

I have a repeating region on a page that lists hotels.  It's supposed to list them from cheapest to most expensive.  I have a table set up in MySQL that is simply the numerical price (no dollar signs or anything else - just a number).  But, to my surprise, I see the hotels aren't being ordered right.

 

The $100 hotel shows up first, a $400 hotel next, then a $41 hotel, then a $90 hotel.  OK, it looks like PHP is looking at the first digit of the number and sorting based on that.  So does PHP not know how to count properly?  How can I teach it?

 

Here's the code in the recordset:

 

$query_rsRepeat = "SELECT id, city, placename, category, price FROM travel WHERE category = 'Chad hotels' ORDER BY price ASC";

[natepizzle]

Until you could get a more qualified answer you could try padding the prices with 0's in mysql and strip them with php in the output.

[JasonLewis]

Hm, strange. It's not PHPs fault either, it's MySQLs. What field type is the price field, is it INT or BIGINT?

[SetToLoki]

the problem you are having is not with php its mysql, is the price column set to be an int? is there any spcaes before the inserted prices? have you tried it without ordering using Asc to see if it changes the order? have you tried ordering by a different column just to test your code?

[jesse_james]

It looks like I can change the MySQL "price" field type to some kind of int (smallint, etc), and it will order the hotels by price properly.  BUT, NO DICE.  I don't have price data for all of the hotels, so I want to put "unavailable" in the price field for a few hotels.  Also, I would like to put something like "90-110" in the price field for one.  But if I use the int field type, it will change any wording ("unavailable" in this case) to a 0. 

 

Is there any workaround for this problem?  I would like it to order my numbers properly, but I would also like to be able to put "unavailable" for some of the hotels' price field.

[Alex]

You should have it as an INT. Then when getting it back from the database you can do something like:

 

$row['price'] = (empty($row['price'])) ? 'price unavailable' : $row['price'];

 

If the row is empty, when fetching it it'll set $row['price'] to price unavailable for echoing it out later

[JasonLewis]

You could have a min_price column and a max_price column, with only the min_price being required. Have a few more ifs in your code to add the 90-110, then if any hotels have a price of 0 you can simply display "Unavailable".

And then just order it by the min_price.

[natepizzle]

Create 2 mysql columns, min price and max price, and use php to check for a max price on each row. If it exists then it should display as min - max eg $90 - $100

 

As for the unavailable you could set the min price for those hotels to null and use php to display Unavailable if the min is null

Im not the greatest with mysql but im fairly certain you can use null with integers

[SetToLoki]

It looks like I can change the MySQL "price" field type to some kind of int (smallint, etc), and it will order the hotels by price properly.  BUT, NO DICE.  I don't have price data for all of the hotels, so I want to put "unavailable" in the price field for a few hotels.  Also, I would like to put something like "90-110" in the price field for one.  But if I use the int field type, it will change any wording ("unavailable" in this case) to a 0. 

 

Is there any workaround for this problem?  I would like it to order my numbers properly, but I would also like to be able to put "unavailable" for some of the hotels' price field.

 

could leave the price empty or set it to 0 and when you are looping through your mysql results later add code to check if it is set to 0 or not

 

<php
while($r = mysql_fetch_assoc($results))
{
      if($r['price'] != 0)
      {
             echo $r['name']." - $".$r['price'];
      }
}
?>

[jesse_james]

AlexWD - I tried your code, and......it sort of worked.  When I render the page, every hotel shows up with "price unavailable", rather than just the one hotel that has an empty "price" int field.  I like the idea though.  Any idea what's wrong?

 

My HTML looks like this:

 

Price: $<?php echo $row['price'] = (empty($row['price'])) ? 'price unavailable' : $row['price']; ?>

 

SetToLoki, I tried your code, and the result blew my mind.  Really weird - it somehow broke my repeating region; instead of all of the items in the region repeating, now I just get one hotel listing, but with multiple prices (now the price is repeating, but the entire div doesn't repeat).

 

ProjectFear, your advice sounds GREAT.  It's just too bad that I don't know enough to use it.  I work in Dreamweaver, and although I have a couple years experience of web design, I don't enough enough to take your advice and make a solution out of it.  Thanks for the post though.

[SetToLoki]

AlexWD - I tried your code, and......it sort of worked.  When I render the page, every hotel shows up with "price unavailable", rather than just the one hotel that has an empty "price" int field.  I like the idea though.  Any idea what's wrong?

 

My HTML looks like this:

 

Price: $<?php echo $row['price'] = (empty($row['price'])) ? 'price unavailable' : $row['price']; ?>

 

SetToLoki, I tried your code, and the result blew my mind.  Really weird - it somehow broke my repeating region; instead of all of the items in the region repeating, now I just get one hotel listing, but with multiple prices (now the price is repeating, but the entire div doesn't repeat).

 

ProjectFear, your advice sounds GREAT.  It's just too bad that I don't know enough to use it.  I work in Dreamweaver, and although I have a couple years experience of web design, I don't enough enough to take your advice and make a solution out of it.  Thanks for the post though.

 

could you post your code, sounds like you are using dreamweavers ability to mess php up we all start somewhere but I wouldn't ever reccomend the dribble that dreamwevwer coughs up. 

 

if I am still awake when you do I will rewrite the page for you

 

[jesse_james]

Yeah, Dreamweaver has been aggravating me more and more lately.  I'm working on CS4 right now, and I swear I find a new bug every day.

 

SetToLoki - I'm not sure if you meant I should post all of my code.  I'm not sure you want me to post my entire php file  .  Anyways, I've posted a hefty portion of the recordset header code below, in addition to code for the entire repeating region.

 

First, the header code:

$maxRows_rsRepeat = 12;
$pageNum_rsRepeat = 0;
if (isset($_GET['pageNum_rsRepeat'])) {
  $pageNum_rsRepeat = $_GET['pageNum_rsRepeat'];
}
$startRow_rsRepeat = $pageNum_rsRepeat * $maxRows_rsRepeat;

mysql_select_db($database_dsLink, $dsLink);
$query_rsRepeat = "SELECT id, city, placename, category, price FROM travel WHERE category = 'Chad hotels' ORDER BY price ASC";
$query_limit_rsRepeat = sprintf("%s LIMIT %d, %d", $query_rsRepeat, $startRow_rsRepeat, $maxRows_rsRepeat);
$rsRepeat = mysql_query($query_limit_rsRepeat, $dsLink) or die(mysql_error());
$row_rsRepeat = mysql_fetch_assoc($rsRepeat);

if (isset($_GET['totalRows_rsRepeat'])) {
  $totalRows_rsRepeat = $_GET['totalRows_rsRepeat'];
} else {
  $all_rsRepeat = mysql_query($query_rsRepeat);
  $totalRows_rsRepeat = mysql_num_rows($all_rsRepeat);
}
$totalPages_rsRepeat = ceil($totalRows_rsRepeat/$maxRows_rsRepeat)-1;

 

Now here's the repeating region in the body:

<?php do { ?>
  <div class="repspc">
                                                                                  <dt><a href="chad_hotel.php?articleID=<?php echo $row_rsRepeat['id']; ?>"><?php echo $row_rsRepeat['placename']; ?></a></dt>
                                                                                  <dd><strong><?php echo $row_rsRepeat['city']; ?>, Chad</strong><br />
                                                                                  Price: $<?php echo $row_rsRepeat['price']; ?> per night</dd>
                                                                                </div>
                                                                                  <?php } while ($row_rsRepeat = mysql_fetch_assoc($rsRepeat)); ?>

 

And of course, the only part we're concerned with is the "price"

 

[SetToLoki]

not tested it but something like

 

<?php

mysql_select_db($database_dsLink, $dsLink);
$sql = "SELECT (id, city, placename, category, price) FROM travel WHERE category = 'Chad hotels' ORDER BY price ASC";
$result = mysql_query($sql) or die(mysql_error());

echo "<div class='repspc'>";
while($r = mysql_fetch_assoc($result))
{

$s  = "<dt><a href='chad_hotel.php?articleID=".$r['id'].">".$r['placename']."</a></dt>";
$s .= "<dd><strong>".$r['city']." Chad</strong><br />";
$s .= "Price: ".$r['price']." per night</dd>";

echo $s."<br /><br />";
}
echo "</div>";
?>

 

should do it

 

also you had some sort of paging thing going on at the top that I have deleted in this version wasn't relevant to the code you was struggling with

 

$maxRows_rsRepeat = 12;
$pageNum_rsRepeat = 0;
if (isset($_GET['pageNum_rsRepeat'])) {
  $pageNum_rsRepeat = $_GET['pageNum_rsRepeat'];
}
$startRow_rsRepeat = $pageNum_rsRepeat * $maxRows_rsRepeat;

[jesse_james]

I managed to get the code from AlexWD to work.  Thanks to AlexWD for that contribution. 

 

SetToLoki - your solution looks interesting.  Does it have anything in it that will render an "unavailable" readout?

 

Thanks to everyone for posting.  AlexWD was right on target.

 

[SetToLoki]

I managed to get the code from AlexWD to work.  Thanks to AlexWD for that contribution. 

 

SetToLoki - your solution looks interesting.  Does it have anything in it that will render an "unavailable" readout?

 

Thanks to everyone for posting.  AlexWD was right on target.

 

 

lol oops no is 3am tired but would be easy to add

 

<?php

mysql_select_db($database_dsLink, $dsLink);
$sql = "SELECT (id, city, placename, category, price) FROM travel WHERE category = 'Chad hotels' ORDER BY price ASC";
$result = mysql_query($sql) or die(mysql_error());

echo "<div class='repspc'>";
while($r = mysql_fetch_assoc($result))
{
if ($r[price] != 0)
        {
   $s  = "<dt><a href='chad_hotel.php?articleID=".$r['id'].">".$r['placename']."</a></dt>";
   $s .= "<dd><strong>".$r['city']." Chad</strong><br />";
   $s .= "Price: ".$r['price']." per night</dd>";
}
        else
        {
   $s  = "<dt><a href='chad_hotel.php?articleID=".$r['id'].">".$r['placename']."</a></dt>";
   $s .= "<dd><strong>".$r['city']." Chad</strong><br />";
   $s .= "Price is currently unavailable at this time</dd>";
        }
         
echo $s."<br /><br />";
}
echo "</div>";
?>

 

if the price was 0 in database it would print all the data but where price usually is would say Price is currently unavailable at this time