PHP Information Retrieval

  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

Hi, I was creating a page that would pull news content from a my sql database. The code connects to the database successfully, however stumbles when the query is being carried out, the first line of it to be precise. Here is the query part

PHP Code: [ Select ]
      <? php
 
   $query='SELECT * FROM news WHERE newsid='.$newsid;
 
   $result=mysql_query($query,$db);
 
   if (mysql_num_rows($result)==0) {
 
  $newstitle='Error';
 
  $newscontent='This news item does not exist.';
 
} else {
 
  $newstitle=mysql_result($result,0,'newstitle');
 
  $newscontent=mysql_result($result,0,'newscontent');
 
}
 
   ?>
  1.       <? php
  2.  
  3.    $query='SELECT * FROM news WHERE newsid='.$newsid;
  4.  
  5.    $result=mysql_query($query,$db);
  6.  
  7.    if (mysql_num_rows($result)==0) {
  8.  
  9.   $newstitle='Error';
  10.  
  11.   $newscontent='This news item does not exist.';
  12.  
  13. } else {
  14.  
  15.   $newstitle=mysql_result($result,0,'newstitle');
  16.  
  17.   $newscontent=mysql_result($result,0,'newscontent');
  18.  
  19. }
  20.  
  21.    ?>


any ideas,

thanks guys,

Steve
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

whats the error message?

This is the line that isn't working??
PHP Code: [ Select ]
    $query='SELECT * FROM news WHERE newsid='.$newsid;

The SQL looks fine lol....

I know it sounds stupid but have you double checked the recordset and field names? and $newsid.
  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

hey, thanks for the reply, i got it sorted, there was a problem with one of the variables in the connect script..doh!

thanks anyway!
  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

on another note, i was wondering if anyone could help me. The query which i created is pretty much the best of my knowledge. What it does is picks up news contents with a specific id (newsid) and displays the contents on a news page (news.php). However I would like to create an index page that might perhaps put all the titles - table field "newstitle" and link them to their respective content page. For example

Gig 1: 22/04/04 would be linked to news.php?id=1
Gig 2: 25/04/04 would be linked to news.php?id=2

If anyone can help me achieve this, i'd be extremely greatful 8)

i realise that what i've just said might not make a bit of sense either lol!
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

PHP Code: [ Select ]
 
<?php
 
$query='SELECT * FROM news';
 
$result=mysql_query($query,$db);
 
 
 
if (mysql_num_rows($result)==0) {
 
  $newstitle='Error';
 
  $newscontent='This news item does not exist.';
 
}
 
else {
 
 
 
  while($obj=mysql_fetch_object($result)) {
 
 
 
    print("Title: ".$obj->title." <a href=\"news.php?id=".$obj->newsid."\">Go</a>");
 
  }
 
}
 
?>
 
 
  1.  
  2. <?php
  3.  
  4. $query='SELECT * FROM news';
  5.  
  6. $result=mysql_query($query,$db);
  7.  
  8.  
  9.  
  10. if (mysql_num_rows($result)==0) {
  11.  
  12.   $newstitle='Error';
  13.  
  14.   $newscontent='This news item does not exist.';
  15.  
  16. }
  17.  
  18. else {
  19.  
  20.  
  21.  
  22.   while($obj=mysql_fetch_object($result)) {
  23.  
  24.  
  25.  
  26.     print("Title: ".$obj->title." <a href=\"news.php?id=".$obj->newsid."\">Go</a>");
  27.  
  28.   }
  29.  
  30. }
  31.  
  32. ?>
  33.  
  34.  

The "while()" loop will extract every row returned in thw query, you can limit the number of rows and sort it if you want to:
Code: [ Select ]
SELECT * FROM news ORDERY BY newsid DESC LIMIT 20
  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

thanks, your a genious!
  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

Just to add to that, thanks Leo, by the way - just what i was looking for -cheers! On the main page (home) i wondered if it might be possible to display some of (not all) the two most RECENT news topics (i.e the ones with the highest id's) with a link to more information. That would for example look like:

news content one. This is the first news content.. [more]

but only for the most recent two (maybe a few lines of content each?)

Thanks a lot in advance,

Steve
  • diverdan
  • Beginner
  • Beginner
  • diverdan
  • Posts: 46
  • Loc: Alabama, USA

Post 3+ Months Ago

change $query to this:
PHP Code: [ Select ]
 
$query='SELECT * FROM news ORDER BY newsid DESC LIMIT 0, 2';
 
 
  1.  
  2. $query='SELECT * FROM news ORDER BY newsid DESC LIMIT 0, 2';
  3.  
  4.  
the ORDER BY newsid DESC will get the news items in order of descending newsid

the LIMIT 0, 2 part tells mysql to return 2 items starting at element 0.
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Code: [ Select ]
SELECT newsid as newsid, title as title, SUBSTRING(content, 1, 50) as summary FROM news ORDERY BY newsid DESC LIMIT 2


The substring() MySQL function can save you some work in selecting the first 20 characters from content. Of course, it will cut the string in the 20th characters so it can look ugly sometimes.
For a better solution, you will have to work the substring in PHP making a function able to cut the text just between words.
  • Scorpius
  • Proficient
  • Proficient
  • User avatar
  • Posts: 401
  • Loc: Scorpion Hole

Post 3+ Months Ago

Or something else you could do if you would like to get even more advanced is have the query be different depending on variables, like number per page, which page, or show all. There are many more things you can do along with those.
  • steve101
  • Beginner
  • Beginner
  • steve101
  • Posts: 35

Post 3+ Months Ago

thanks guys, i appreciate the help you've given me on this and i hope it wasnt an inconvenience - it got my problems/ideas sorted!

Thanks again,

Steve

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 60 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.