homepage Welcome to WebmasterWorld Guest from 54.166.14.218
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Get single record from MySQL db
newbie question but can't find answer
pulszar

5+ Year Member



 
Msg#: 3140240 posted 9:20 pm on Oct 30, 2006 (gmt 0)

I have a database that holds article information (i.e. title/headline, date, category, link, full story, etc) and I'm having a hard time finding out how to get one record/row at a time for a page displaying recent articles (ilounge is a good example of what I'm trying to do).

Anyway, if each record has an ID thats a primary key, how do I get one record/article?

I know it's something like:

SELECT * FROM articles

but do I put the record id after (WHERE record_id = 1)?

Everywhere I searched just had all kinds of complex ways to display lots of table data in various ways but I just want one row at a time. Such a noob question but thank you in advance to anyone that can answer.

 

bcolflesh

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3140240 posted 9:56 pm on Oct 30, 2006 (gmt 0)

You answered your own question - you can specify a unique field (record_id in your case) in your WHERE clause.

If this is to show the last five results for a news page or something similiar, you can also LIMIT the query, ex:

SELECT * FROM articles LIMIT 5

That would return the last 5 records ascending by default.

pulszar

5+ Year Member



 
Msg#: 3140240 posted 7:10 am on Oct 31, 2006 (gmt 0)

D'oh!

Thanks man, I thought that method was more of a quick fix than an official method. Since I'm all new to databases, I'm not up to speed on proper coding yet.

Incidentally, I have one more question - suppose the following crude example:

<?php
$query = 'SELECT * FROM articles WHERE 1';
$result = mysql_query($query);
$row = mysql_fetch_array($result);

$title= $row[1];
$date= $row[2];
$link = $row[3];
$category= $row[4];
$categoryLink= $row[5];
$story = $row[6];
?>

<div class="example1">
<?php
echo $title;
echo $date;
echo $link;
echo $category;
echo $categoryLink;
echo $story;
?>
</div>

If I have, say, 10 records full of articles and related info, and I want to manually pick which to display - how would I reuse the above info to show the data? Like if I wanted articles 1, 3, 5, and 9?

(Thanks in advance)

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3140240 posted 4:05 pm on Nov 7, 2006 (gmt 0)

You could use the IN predicate:
$query = 'SELECT * FROM articles WHERE articleID IN(1,3,5,9)';

leadegroot

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3140240 posted 4:51 am on Nov 8, 2006 (gmt 0)

It sounds like you want to display multiple records from your dataset?
You have to loop through the mysql_fetch_array command, eg:
$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result)) {
printf("ID: %s Name: %s", $row[0], $row[1]);
}
(example stolen from php.net example in manual)

pulszar

5+ Year Member



 
Msg#: 3140240 posted 5:11 pm on Nov 9, 2006 (gmt 0)

Thanks for the replies everyone, I appreciate the advice. I think I might have found an alternate way to do what I was thinking of. In my example, I have 3 tables in the database. We'll call them "articles", "reviews", and "news". I am storing all the content in the tables (obviously), but to call the specific articles (or news/reviews) I want for the home page, I am using a variable in the SELECT statement on an include page.

It looks something like:

$query = 'SELECT * FROM articles WHERE article_id = $a_id'

<code to display xhtml and query result>

the above code goes in a file called showarticle.php (the same will apply to news and reviews respectively). Then on the index page where I want to display certain articles/reviews/news, I would use the following code:

<?php
$a_id = 4;
include('showarticle.php');
?>

..and just change the a_id value for the article I want to display. The only problem with doing this is that I will need to know the ID number for every article I insert that I want on the homepage and I will have to manually enter the above code for each display:

<?php
$a_id = 4;
include('showarticle.php');
?>

<?php
$a_id = 2;
include('shownews.php');
?>

<?php
$a_id = 12;
include('showreview.php');
?>

<?php
$a_id = 20;
include('showarticle.php');
?>

I'm sure there is a way I could incorporate a check box on the form I built to enter the content that gets inserted into the database where if the check box is checked, the php code is automatcially inserted on the home page. I just have no idea how to do that yet.

pulszar

5+ Year Member



 
Msg#: 3140240 posted 3:44 pm on Nov 14, 2006 (gmt 0)

Update:

For the record, I realize that the above method is rather tedious and that there is always a better way. I changed the database to consolidate all articles/news/reviews/etc and will have them sorted on the site using a category table instead. As for the select items I want displayed on the home page, I just added a story_index_page field with a single CHAR that has a default value of N. When I want a particular story displayed on the home page, I set it to Y. The query statement on the main page looks for all records with that Y and sorts them by date. So much easier.

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3140240 posted 10:33 pm on Nov 20, 2006 (gmt 0)

Sounds like a much better layout. I have a similar status column but it allows for setup first (Pending), then the home page (Active), Inactive, and Archived. Archived allows the article to remain actively linked, but not on the home page or main news display page. That's the one thing that is nice about database work, you can grow it as necessary when you start it out with the right structure.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved