PHP data base query

Question

I have business directory site, i want it to be group all post like Craigslist did.

current my data will present on the site like this

<business name1> <phone number> <jan 13 2012>
<business name2> <phone number> <jan 13 2012>
<business name1> <phone number> <jan 12 2012>

I want to layout it like this, so the posts will group by the date published.

<jan 13 2012>
<business name1> <phone number>
<business name2> <phone number>

<jan 12 2012>
<business name1> <phone number>
<business name2> <phone number>

here is my coding

connection 
$query = "SELECT * FROM `myname` ORDER BY `myname`.`id` DESC LIMIT $offset, $limit ";
$result = mysql_query($query);


    while($row = mysql_fetch_row($result))

    {   

?>

<div class="list">
 <div class="ads_name"><a href="http://www.mydomain.com/category/<?=$row[0]?>.html" target="_blank"><?=$row[4]?> </a></div>
    <div class="location"><?= htmlspecialchars($row[5])?></div>
    <div class="phone"><?=formatPhoneNumber($row[3])?></div>
    <div class="date"><?=$row[9]?></div>
</div>

<?php
    }
    mysql_free_result($result);
?>

Add a GROUP BY date clause to your query, and check the value of date as you loop the results. Every time the value changes, break the data into a new section. Without knowing more about your table schema and the desired markup output, we can't give you much of a more precise answer. — DaveRandom
– DaveRandom, Commented Jan 13, 2012 at 17:56

Eugen Rieck · Accepted Answer · 2012-01-13 18:03:12Z

1

First of all you need something like

SELECT * FROM `myname` ORDER BY `myname`.`mydate` DESC, `myname`.`id` DESC LIMIT $offset, $limit

To make sure your rows arrive in the correct order. Next you want to do a classic group-change algorithm:

<?php
...

    $date='__invalid__';
    while($row = mysql_fetch_row($result))

    {   

      if ($row[9]!=$date)
      {
        $date=$row[9];
?>
<div class="date_header"><?=$row[9]?></div>

<?php
      }
?>

<div class="list">
 <div class="ads_name"><a href="http://www.mydomain.com/category/<?=$row[0]?>.html" target="_blank"><?=$row[4]?> </a></div>
    <div class="location"><?= htmlspecialchars($row[5])?></div>
    <div class="phone"><?=formatPhoneNumber($row[3])?></div>
    <div class="date"><?=$row[9]?></div>
</div>

<?php
    }
    mysql_free_result($result);
?>

answered Jan 13, 2012 at 18:03

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

will.i.am Over a year ago

it works great!! but can you explain to me what is 'invalid'; for?? is that same use as blank?? thanks :)

Eugen Rieck Over a year ago

The original value of '__invalid__' is there to make sure, that at the very start of the loop the if ($row[9]!=$date) will allways fire, so that you allways start a page with a date. You could use $date='' or $date=null or whatever, I chose '__invalid__' to make the code readabel.

Collectives™ on Stack Overflow

PHP data base query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related