Welcome to WebmasterWorld Guest from 54.198.114.138

Forum Moderators: open

Message Too Old, No Replies

Get single record from MySQL db

newbie question but can't find answer

     
9:20 pm on Oct 30, 2006 (gmt 0)

New User

5+ Year Member

joined:Sept 26, 2006
posts:26
votes: 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.

9:56 pm on Oct 30, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 21, 2003
posts: 2355
votes: 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.

7:10 am on Oct 31, 2006 (gmt 0)

New User

5+ Year Member

joined:Sept 26, 2006
posts:26
votes: 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)

4:05 pm on Nov 7, 2006 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12541
votes: 1


You could use the IN predicate:
$query = 'SELECT * FROM articles WHERE articleID IN(1,3,5,9)';
4:51 am on Nov 8, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 27, 2003
posts: 1642
votes: 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)

5:11 pm on Nov 9, 2006 (gmt 0)

New User

5+ Year Member

joined:Sept 26, 2006
posts:26
votes: 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.

3:44 pm on Nov 14, 2006 (gmt 0)

New User

5+ Year Member

joined:Sept 26, 2006
posts:26
votes: 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.

10:33 pm on Nov 20, 2006 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12541
votes: 1


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.