PHP function getListingListLinks()

[renfley]

Hey guys ive got another fun one here.

 

I want to pull title, price, insertdate from database and display each listing like so

 

March 9:

 

123 bigwalkwayapartment - 900$

1455 little house must se!!!

 

March 8:

 

123 bigwalkwayapartment - 900$

1455 little house must se!!!

 

And so forth so basicly post the link by date.

 

function getListingListLinks(){//for the links
$ocdb=phpMyDB::GetInstance();
        $query="SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts";
        $result=$ocdb->getRows($query);
Something??

         For Each Date 
             echo date;
            echo "<a href="#">title and price</a><br>;

}

 

I know im still far from completing my function but i def need the help!

 

Thanks

[Kvashir]

Ok i don't know the class phpMyDB, anyway, you've now to fetch the query and print every information stored in the db

 

In a simple sql query this will be done in this way:

 

<?php

//Fetching:

$query="SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts";

while($result=mysql_fetch_assoc($query))

{

        //Printing results:

      echo $result['title']." ".$result['insertDate']." ".$resul['price'];

}

?>

 

Sorry for my bad english Hope it helps!

[Psycho]

I think what you are trying to say is that you want the date to display one time and then all the entries for that date, then the next dat and those entries.

 

All you need to do is first order the results by date then create a "flag" variable to detect when the date changes to determine when to display the next date. You are apparently using a class for your query, so I'm not sure of the proper way to loop through the records. So, I just used a normal while() loop with mysql_fetch_assoc() for illustrative purposes. You will need to modify for your purposes.

function getListingListLinks(){//for the links
    $ocdb = phpMyDB::GetInstance();
    $query = "SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts ORDER BY isertDate";
    $result = $ocdb->getRows($query);

    $output = '';
    $currentDate = false;
    while($row = mysql_fetch_assoc($result))
    {
        if($currentDate != $row['insertDate'])
        {
            $output .= "<br><b>{$row['insertDate']}</b><br><br>\n";
            $currentDate = $row['insertDate']
        }
        $output .= "<a href=''>{$row['title']}</a><br>\n";
    }
    return $output;
}

 

 

Also, you shouldn't echo within functions. Create the output and return the output to where the function is called. E.g.

echo getListingListLinks();

[renfley]

Phyco i think that would work i will give it a try

[trq]

Im not sure that would work since i want to display all links from a certain date

 

So change your query to produce the results you want.

[renfley]

These is something not working with phycos code, im not a pro but should the while be close? with endwhile;

[renfley]

So ive cleaned the code a bit but im not getting Any Results being printed!! In the index ive properly echo the Function but to no avail Any input?

 

function getListingListLinks(){//for the links
    $ocdb = phpMyDB::GetInstance();
    $query = "SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts ORDER BY insertDate";
    $result = $ocdb->getRows($query);

    $output = '';
    $currentDate = false;
    while($row = mysql_fetch_assoc($result))
    {
        if($currentDate != $row['insertDate'])
        {
            $output .= "<br><b>{$row['insertDate']}</b><br><br>\n";
            $currentDate = $row['insertDate'];
        }
        $output .= "<a href=''>{$row['title']}</a><br>\n";
    }

    return $output;
}

 

[trq]

Your using some class (phpMyDB), but then trying to retrieve a result set using mysql_fetch_assoc. I suggest you take a look at the documentation for this phpMyDb class and find it's equivalent.

[trq]

ps; Do you have error reporting enabled like you should while developing?

[renfley]

yup error is up and running  here i modified my code a little which works but doest give me the desired result!

 

function getListingListLinks(){//for the links
    $ocdb = phpMyDB::GetInstance();
    $query = "SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts ORDER BY insertDate";


$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
    echo $row['insertDate'];
echo $row['title'];
    echo $row['price'];
echo "<br>";
}   
   
}

 

Now this looks like the following.

 

2012-02-27 14:25:20Georgious 2 Bedroom Apartment Located in downtown Ottawa1250

2012-02-27 14:26:39Wow, Very clean 3Bdrm Apt. 750

2012-02-27 14:27:34Simple Loft lacated minutes from downtown.0

2012-02-27 14:28:29Spacious 3 Bdrm Ap located in orleans0

2012-02-27 14:30:08Huge 2Bdrm Apartment Available ASAP850

 

What i would like to achieve is this

 

March 12, 2012

Georgious 2 Bedroom Apartment Located in downtown Ottawa 1250$

Wow, Very clean 3Bdrm Apt. 750$

 

March 11, 2012

Spacious 3 Bdrm Ap located in orleans 0$

Huge 2Bdrm Apartment Available ASAP 850$

 

I am getting there but i just dont have the knowledge to get it to work the way i want. Now i did get part of my code from php.net and i am still looking but havnt found my answer as of yet!

 

[renfley]

If someone is looking at this ive change the query from

 

$query = "SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts Order BY insertDate";

 

$query = "SELECT title, insertDate, price FROM ".TABLE_PREFIX."posts GROUP BY insertDate";

 

REason for this is i want to display each title based on day so example today we have two ads i wanna show 2 ads if yesterday had on it would display the date and the single ad.

 

Can anyone confirm that group by would be better.

[trq]

That is not at all what GROUP BY does. Using a GROUP BY will return 1 record for each insertDate.

[Psycho]

I'm at a loss. I gave you the "logic" that would provide the solution you were after and specifically stated

You are apparently using a class for your query, so I'm not sure of the proper way to loop through the records. So, I just used a normal while() loop with mysql_fetch_assoc() for illustrative purposes. You will need to modify for your purposes.

 

You then respond that it doesn't work without making any of the changes necessary to work with the class you were using. Then, later, you change to use the built-in MySQL function and don't use the code I provided which would have worked. Not worth my time. Sorry.