Welcome to WebmasterWorld Guest from 107.22.87.205

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)

5+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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)

5+ Year Member



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)

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



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)

WebmasterWorld Senior Member 10+ Year Member



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)

5+ Year Member



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)

5+ Year Member



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)

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



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month