Database and ID's

Anon

Hello, can anybody help?
I have a database of publications, and would like them to display appropriately:

To submit a publication into the database, people are using a special form, which has 3 options: as_news, as_articlelink and as_article. All that information is stored in the database in the column called "style".

What I want to do is the following:
To display all the publications from the database with the style = "as_articlelink".
SQL: select * from publications where style = "as_articlelink";

Problems:
1. For some reason I get displayed only 1 publication rather than all.
2. Since it's "as_articlelink" how can I make Title appear as link, and once its clicked people will see the actual article? (not just the title)
Something like this? <a href="index.php?$id">$title</a> ?

Please help!

Anon

Your SQL Statment seems fine.
Let me see your while loop. (or all of your code where you are displaying the data)

Derek

Anon

Derek C., problem 2 is my concern right now... any ideas?

Anon

Probem 2:

Yes close to what you have:

<a href="index.php?id=$id">$title</a>

Now on the next page (index.php)
you will do your query like this:

SELECT * FROM publications WHERE id='$id';

Then you can print the information from just that row.

Derek C.

Anon

Thanks Derek, question based on your answer:
If I want to display 5 (new) articles on my index.php page, what should I do to have index.php display:

links.php:
<a href="index.php?id=1">$title</a>
<a href="index.php?id=2">$title</a>
<a href="index.php?id=3">$title</a>
<a href="index.php?id=4">$title</a>
<a href="index.php?id=5">$title</a>

index.php:
SELECT * FROM publications WHERE id='1,2,3,4,5';

that means that I'll have to edit links.php by replacing ?id=number all the time I want to publish a new article?

Anon

For example if you just want to display the last 5 articles your SQL query would be like this:

SELECT id, title FROM publications WHERE style='as_articlelink' ORDER BY id DESC LIMIT 0, 5;

Now if you have a date column then replace it instead of using ORDER BY id, you would put ORDER BY date or whatever your date column is.

Now you would just be selecting 5 rows, start with the newest.

Now in your while loop all you would have is:

<a href="index.php3?id=$id">$title</a>

Before i say anything else, are you creating a loop to print this link for each one?

Derek

Anon

Thanks Derek, yes I'll need to print this link for each one. So once someone clicks on it the whole article will show up?

Because:
as_articlelink --
title (as link)

as_article --
title
author
date
content

So is someone clicks on "as_articlelink", "as_article" should show up...

Anon

Paul, if you aren't giving each link a unique, arbitrary id # (most likely using an AUTO_INCREMENT int field) you are in for a very difficult time making this all work. Because such an ID is guaranteed to be unique, it is by far the best sort of field to select on when you want to display an individual article. It also allows your user to make corrections or updates (what if they mistype the url the first time around) without affecting any relationships that row is already in.

Anon

I'm using ID as an integer (int) it's the primary key and auto_increment

Anon

Then you're home free, and you certainly don't have to edit anything by hand when you add new links (lots of the error checking omitted):

links.php:

<?php
$conn = mysql_connect(...);
mysql_select_db("my_db");

if ($limit != '')
   $limit = "limit $limit,10";
else
   $limit = "limit 10";

$result = mysql_query("select title, id from publications order by id $limit");
while ( list($title, $id) = mysql_result($result))
   {
   echo "<a href="show_title.php?id=$id">$title</a>
   }
...

show_title.php:

if (ereg('^{[0-9][0-9]+$',} $id ) // foil hackers
   {
   $result = mysql_query(
   "select * from publications where id = $id");
   ...

Anon

Kirk Parker wrote:

$conn = mysql_connect(...);
mysql_select_db("my_db");

I always wanted to ask someone if there is any benefit to the approach you give above, or:

$conn = mysql_connect(...);
$sql = "select * from [DATABASE].[TABLE]";
$result = mysql_query($sql);

Is it a matter of taste? Is one or the other way quicker?