adding a condition to display a list of songs added to the database since xyz

Hi there

I know nothing about php apart from how to spell it, am not simply asking a general question but rather looking for someone to do the work for me. So I wonder if anyone would be interested in earning a little beer money and look at a php page for me, I won’t post any details here but basically I have a page setup, in fact it’s a duplicate of a page I already have on my website so it’s all good and correct, except I need to add a condition so it only displays stuff based on that condition, the bulk of it, I have a MySQL database holding my web radio playlist, I have duplicated this php and renamed it and I need that this page only displays those songs added within the last 30 days. I’ve even got the condition string as I took it from another page using the same condition but uses it for a slightly different purpose but I don’t know where to put it or what part of the string I need to use so it does what I want.

Anyway if anyone is interested to at least looking at this, i would first send you the link to the resulting (duplicate) page (before the change I want) and explain what I am trying to do then I can send you the php page, because from this post I don’t think I have made sense to anyone, but with viewing my stuff I think it will be clear. And if you think you can do it without having to dig in deep and don’t need to look at all my config files, and spend hours, I will be glad to pay for the work. I would think it’s a real easy thing to do and I think a php developer would probably laugh at how easy it is.

Thanks

Nathalie

The above post is mine and I have registered now so I can be emailed. And thanks for moving it to the right place hadn’t looked , it was late :-X

I will take a look at it.

Hi there

Thanks for your reply - I’ve got someone looking at it right now, it seems it was a bit of cow of a change to implement ;D but if my guy has no success (trying one last thing) I will gladly contact you. The difficulty lies in not knowing the radio software php system and how the pages are generated, code/class files etc. so it’s possible even a great php developer may find it a bit of a challenge :o - I always want complicated things- Will get back to you eitherway within 2 days

cheers

So you’ve got a page that display a list of songs, and you want to limit the result based on dates?

I guess you have something like this (or similar) to display the songs on the page you have atm
SELECT * FROM songs LIMIT 0, 100

You can just add the clause in the query
SELECT * FROM songs WHERE added BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW() LIMIT 0, 100

Thanks - my class file is not using such sql statements, but I finally worked out where I have to put the condition, it’s a class file which is then referenced in a code file (I duplicated everything to have a separate playlist page (playlist.new, code + class files and named everything with .new and referenced everything correctly with the “new” name )

// Return tracks in the queuelist table
$select = $db->select()
->from(array(‘s’ => ‘songlist’),
array(’*’))
->join(array(‘q’ => ‘queuelist’),
‘q.songID = s.ID’,
array(‘requestID’))
->where(‘s.songtype IN (?)’, array(‘S’, ‘C’)) //Only return song of type S
->order(‘q.sortID ASC’)
->limit($count);

Now I just have to find the person who knows where in this bit it can say “where 30 days or less added”,

All I know is that the php condition is this

<?php if (strtotime($recentSong->date_added) > (time() - 2592000)): ?>

as this is used on another page to achieve something different ( in relation to 30 days too=, but it’s how to write this correctly in the code above in the class file like -->where etc… This query does not come from a class file but from a display.playlist.php page (that’s the page being generated and going on the website so am sure I can’t just take this one to one)

I’m guessing this is built on top of some kind of framework / orm? You should figure out which one you’re running as it will be much easier consulting with the documentation than us guessing.

If I were to guess however, I would say you’re using Zend (it surely isn’t doctrine or active record)
http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html
http://framework.zend.com/manual/1.12/en/zend.db.select.html

as we can see in the manual it’s just to add another where, like this

[php] // Return tracks in the queuelist table
$select = $db->select()
->from(array(‘s’ => ‘songlist’),
array(’*’))
->join(array(‘q’ => ‘queuelist’),
‘q.songID = s.ID’,
array(‘requestID’))
->where(‘s.songtype IN (?)’, array(‘S’, ‘C’)) //Only return song of type S
->where(‘s.added BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW()’)
->order(‘q.sortID ASC’)
->limit($count);[/php]

Note that I am far from confident this will work, I’m not sure it will work like this in Zend, and I’m not sure you’re using Zend at all

another go could be to actually send in dates

[php] // Return tracks in the queuelist table
$select = $db->select()
->from(array(‘s’ => ‘songlist’),
array(’*’))
->join(array(‘q’ => ‘queuelist’),
‘q.songID = s.ID’,
array(‘requestID’))
->where(‘s.songtype IN (?)’, array(‘S’, ‘C’)) //Only return song of type S
->where(‘s.added > ?)’, $30DaysFromNow)
->order(‘q.sortID ASC’)
->limit($count);[/php]

well thanks for the efforts I will try in a bit. About the framework model stuff all I can say :o :o :o no idea, but even not knowing much, I don’t think such a framework is in place, the “word” never cropped up anywhere ever, this is a php system which comes out of the box and I just change the html part to “decorate” the pages (pretty colours etc…). I don’t touch the php stuff, I just get developers to do it… but I will sure try but I think am on the right track anyway. If this command doesn’t work, it will be something similar… cheers :smiley:

well getting somewhere it doesn’t like s.added

Oops! Something went wrong…
Mysqli prepare error: Unknown column ‘s.added’ in ‘where clause’

You have to switch “added” with the name of the timestamp/date/datetime column from the songlist table

well getting closer but the $30days format bit doesn’t seem to work (blank page) so am trying using the format which I know works on another page

am trying to translate your code (‘s.added > ?)’, $30DaysFromNow)
to something like

->where(‘s.date_added > (time() - 2592000’))

Because the condition which works (for another purpose on another page) is

<?php if (strtotime($recentSong->date_added) > (time() - 2592000)): ?>

(recentSong is not relevant, it serves another purpose)

I have tried several variants

but the problem is the syntax each time

Oops! Something went wrong…

Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) - 2592000)) ORDER BY q.sortID ASC LIMIT 100’ at line 2

It seems to complain about " ->order(‘q.sortID ASC’)" I don’t know why it complains about the number of entries of 100, it’s supposed to create as many pages as needed and you can scroll with “next”. In the code file there is this $limit = Def(‘limit’, 100); // How many items will be displayed

I don’t see the relevance… anyway… :-X

If you or someone wants to take this offline and do it as a freelance job and try various variants for me to try that would be great say upto to one hour, anything beyond would just mean it needs more than just this change and I’ll have to leave it. I mean the original entire playlist page (without the date_added condition) works perfectly it displays all my songs, so why should it not be as easy as adding the ->where dooda with date_added :-[. I think this princess can’t be rescued from the tower lol

!!! STOP PRESS !!!

I think I’ve done it, I don’t know how, I am almost sure this is a dream and I will wake up and this never happened :o :o :o :o :o :o, it was one line of code and @JimL thanks because it was your bit of help above with the combination of understanding I was doing everything in the wrong place (it does help to read the function titles :’(). But as I said this is probably a dream and it won’t work when I wake up…

I feel like jumping up and reach the ceiling…

Sponsor our Newsletter | Privacy Policy | Terms of Service