The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
New Member - Limiting Rows Shown with Where Query
Discuss Limiting Rows Shown with Where Query in the PHP Development forum on Dev Shed.
Limiting Rows Shown with Where Query PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
December 4th, 2012, 01:30 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
Time spent in forums: 2 h 5 m 38 sec
Reputation Power: 0
|
|
New Member - Limiting Rows Shown with Where Query
Hi, already posted a similar message to this in the PHP forum, and got told that this was more a HTML error.
Had a website built for us earlier this year.
Cutting a long story short, we have news pages with all the news articles down the side - obviously we have lots, so we want to limit them until the beginning of the year, and then for an option "See More" to take us to a different page which then lists every news article on the page after that date (darlingtontowntwinning.co.uk/news_&_events)
I've been told I just need to put a WHERE query statement in somewhere to limit the date value - but i don't know where, or how - and if it is less than $fromDate, then for A single "See More News" link to display and go to APAGE.html (for example)
If someone could point this out to me I'd be greatful.
This is the code we have at the moment:
PHP Code:
<div id="right" class="news">
<h3>Archive</h3>
<? $news=$session->getNews("","","",1);?>
<? while($article=mysql_fetch_array($news)){?>
<?
$date = $article['thedate'];
$year = date('Y', $date);
$month = date('F', $date);
$fromDate = date('>=130690440',$date);
?>
<h4><?=$month." - ".$year;?></h4>
<nav class="small">
<? $innernews=$session->getNews("",$month,$year);?>
<? while($innerarticle=mysql_fetch_array($innernews)){?>
<a href="/news/<?=$innerarticle['ftitle']?>" <? if($title==$innerarticle['ftitle']){?> class="active"<? }?>><?=$innerarticle['title']?></a>
<? }?>
</nav>
<? }?>
</div>
|
December 4th, 2012, 05:48 PM
|
 |
Contributing User
|
|
Join Date: Aug 2011
Location: The Pleiades
|
|
|
Where is the actual query statement which retrieves the data from the database?
It's something along the lines of:
SELECT * FROM table where colname=value LIMIT 0,30
(this retrieves only the first 30 rows which are matched by the query).
Regards,
NM.
|
December 4th, 2012, 06:35 PM
|
 |
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
|
Hi maharrington,
it might be a good idea to read more carefully. When you posted this in the PHP forum, gw1500se told you that this was more of a MySQL question. Nobody said anything about HTML. He also told you that this is the "wrong" code, because it's not where the actual query is.
We need the definition of $session->getNews() and anything that leads to the actual query. Until we have that, there's nothing we could do.
|
December 4th, 2012, 06:36 PM
|
 |
Lost in code
|
|
|
|
|
Out of curiosity, could you link to your thread in the PHP board. I'm surprised someone told you this was an HTML question.
Anyway, the functionality you're going to need to modify is going to be tied to this part:
$session->getNews("","","",1);
That executes code that is somewhere else in your code, so you'll need to dig that up and show it to use. It should be in a block of code labeled "function getNews". With some minor modifications to that it should be possible to do this without too much work.
Edit: In reference to Jacques1's post; this is actually a MySQL question, but don't post this in the MySQL board. The SQL query is burried deep in your PHP code and they won't appreciate having to drag it out. It's also not a very complicated query.
Last edited by E-Oreo : December 4th, 2012 at 06:38 PM.
|
December 4th, 2012, 07:41 PM
|
 |
CSS & JS/DOM Adept
|
|
Join Date: Jul 2004
Location: USA
|
|
|
December 4th, 2012, 08:22 PM
|
|
Lost in code
|
|
|
|
|
Maybe move this to the PHP board and I'll close that other thread. Anyway, the next step in fixing the problem is the one that Jacques1's and I mentioned.
|
December 5th, 2012, 06:36 AM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
Time spent in forums: 2 h 5 m 38 sec
Reputation Power: 0
|
|
Sorry guys (my bad!)!
The function is (I believe):
PHP Code:
function getNews($title,$month,$year,$group){
if($title){
$q=$this->query("SELECT * FROM ".TBL_NEWS." WHERE ftitle = '$title'" );
return mysql_fetch_array($q);
}else if($year && $month){
$q=mysql_query("SELECT * FROM ".TBL_NEWS." WHERE (FROM_UNIXTIME(thedate, '%Y') = '$year') AND (FROM_UNIXTIME(thedate, '%M') = '$month') ORDER BY thedate DESC");
return $q;
}else if($group){
$q=$this->query("SELECT * FROM ".TBL_NEWS." GROUP BY (FROM_UNIXTIME(thedate, '%Y')),(FROM_UNIXTIME(thedate, '%M')) ORDER BY thedate DESC" );
return $q;
}else{
$q=$this->query("SELECT * FROM ".TBL_NEWS." ORDER BY thedate DESC" );
return $q;
}
}
I'll move the post to the PHP forum as suggested.
It's not letting me move the thread.
|
December 5th, 2012, 07:06 AM
|
|
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
What a mess. 
Whoever programmed this has obviously no idea about how to store and process dates in SQL, doesn't know the purpose of "GROUP BY" and couldn't decide which database library to use.
Anyway, you'll have to add a query that will select the news up to a certain year:
Code:
SELECT
*
FROM
[your table]
WHERE
EXTRACT(YEAR FROM FROM_UNIXTIME(thedate)) < [your year]
You'll also have to change the method parameters. You can either add yet another parameter (not very pretty) or use an associative array to specify the search parameters or split the method into specialized methods like getNewsWithTitle(), getNewsUntilYear().
Last edited by Jacques1 : December 5th, 2012 at 07:17 AM.
|
December 7th, 2012, 06:13 AM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
Time spent in forums: 2 h 5 m 38 sec
Reputation Power: 0
|
|
Thanks for your help Jacques1 - however, I'm new to this and haven't really got a clue what I'm doing.
I know this is the code that i need to insert (i believe):
Code:
SELECT * FROM ".TBL_NEWS." WHERE EXTRACT(YEAR FROM FROM_UNIXTIME(thedate, '%Y'))>=130690440
But i don't get where, or how this will allow me to insert a link underneath saying "See More"?
Last edited by maharrington : December 7th, 2012 at 06:13 AM.
Reason: Removed un-needed ) from the code.
|
December 7th, 2012, 01:22 PM
|
|
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
Quote: | Originally Posted by maharrington however, I'm new to this and haven't really got a clue what I'm doing. |
I'm sorry to say, but then somebody else should do the job.
When you don't know PHP and MySQL at all and cannot even apply a simple query template, how is this supposed to work? I mean, even if somebody wrote all the code for you, how would you know it doesn't crash the website under certain conditions or create security holes?
If you don't know anybody with web programming experience, your organisation should grab a few dollars (um, pounds) and hire a programmer.
|
December 7th, 2012, 02:03 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
Time spent in forums: 2 h 5 m 38 sec
Reputation Power: 0
|
|
Excuse Me!
I believe that if you had read my original post you would have seen that the website was developed by somebody else - therefore, assuming that I had no knowledge of websites, how would I know what I'm doing.
In addition to this, if you had read the first message on the other category you would actually notice that I said I was willing to learn and that I'm new to this....but to learn I need more direction than what you have provided me.
I am an individual, working on a voluntary basis for a charitable organisation...they have no more money to spend on programmers to do what could be done by myself IF i was given a reasonable direction on what to do.
It is responses like this that give forums a bad name for themselves....I've come on here for assistance, which, you have somewhat provided me with (for which I'm grateful), but when I ask for further assistance you throw this back in my face!
If you would like to help out, then please do so...but DO NOT speak to me as if I'm supposed to know everything there is to know about programming (I'll quite happily go elsewhere....where in the past I've had pleasant experiences and people have actually been willing to help out a little more!)
And yes....it's pounds!
|
December 7th, 2012, 11:24 PM
|
|
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
What I find quite annoying is that you (like many people) don't seem to view programming as actual work. You have no idea what to do and now expect others to do your job -- for free, of course.
I mean, when your car breaks, you'll hardly turn to some car forum to explain you exactly how to repair the engine. You pay a mechanic to do it. But when it comes to programming, suddenly everybody thinks he could do anything with a little free help from the Internet.
There's a difference between helping someone and doing his job. We can help you with the query or with concrete questions. But I don't think anybody here is willing to walk you through the whole process of creating that link when your part is limited to copying and pasting his code. That's the point where we ask people to hire a programmer.
But if you know some other forum where the users gratefully write the code for free, great, let them do it.
All I can do is explain the query again, because you haven't fully understood it:
The genius who designed the database has stored the dates as Unix microseconds (either because he tried to outsmart MySQL or didn't know the date datatypes). So unless you want to fumble with microseconds, you'll have to use FROM_UNIXTIME() to convert the microseconds into actual dates. That's what
Code:
FROM_UNIXTIME(thedate)
does. You now have an actual date like '3/11/2012'.
To extract the year from the date, you do not use this '%Y' stuff. That's for outputting pretty date strings. The correct way is
Code:
EXTRACT(YEAR FROM FROM_UNIXTIME(thedate))
Now you have an actual year like 2012 or 1998, that microseconds stuff is gone. So to get the articles starting with 2012, you'd say
Code:
EXTRACT(YEAR FROM FROM_UNIXTIME(thedate)) >= 2012
It might be a good idea to actually try this out on your local computer before putting it online.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
