Please login or register.

Login with username, password and session length
Advanced search  

News:

Latest sNews - sNews 1.7 - with its own forums - for discussion and user mods.

Author Topic: [SOLVED] Random article error  (Read 3060 times)

gybrus

  • Newbie
  • *
  • Karma: 0
  • Posts: 7
[SOLVED] Random article error
« on: April 05, 2008, 08:31:24 pm »

Hi,
I'm trying to make a code that will give me one random chosen article from the category with id 1 or so.

I already got it working to give me 1 random article from all the articles.
The only problem is that it displays articles from all category's and not only those from the category with id 1.

At this point i got the following code:
Code: [Select]
<?php

//User settings
$db['dbhost'] = "censored"; //MySQL Host
$db['dbname'] = "censored"; //Database Name
$db['dbuser'] = "censored"; //Database Username
$db['dbpass'] = "censored"; //Database password

//Connect to database
$con mysql_connect ($db['dbhost'], $db['dbuser'], $db['dbpass']);
if (!$con)
  {
  die('Could not connect: ' mysql_error());
  }
mysql_select_db ($db['dbname'], $con);

//Choose random article and output display code.
function RArticle() {
  $sql mysql_query ("SELECT `title`, `text` FROM `articles` T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM `articles`) AS x ON T.ID >= x.ID LIMIT 1")
or die (mysql_error ());
  $res mysql_fetch_assoc ($sql);
  $randomtitle $res['title'];
  $randomarticle $res['text'];

  echo "<h2>"$randomtitle ."</h2>"$randomarticle;
}
?>


I think it will work when I add something like "WHERE category = '1' " but when I place it after "FROM `articles`" it will produce the following error
Quote
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM `articles`) AS x ON T.ID >= x.ID' at line 1

Thanks in advance,
K
« Last Edit: April 06, 2008, 01:37:16 am by Keyrocks »
Logged
No clouds today.. :)
My online portfolio

Joost

  • Guest
Re: Random article error
« Reply #1 on: April 05, 2008, 09:35:41 pm »

Try:

Code: [Select]
<?php

  
$sql mysql_query ("SELECT `title`, `text` FROM `articles` WHERE category = 1 ORDER BY RAND() LIMIT 1")

?>

On a huge database, this one might be faster:

Code: [Select]
<?php

SELECT 
`title`, `textFROM `articlesT JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM `articles`) AS x ON T.ID >= x.ID AND category 1 LIMIT 1
?>
« Last Edit: April 05, 2008, 10:12:55 pm by Joost »
Logged

gybrus

  • Newbie
  • *
  • Karma: 0
  • Posts: 7
Re: Random article error
« Reply #2 on: April 05, 2008, 10:19:20 pm »

Thank you very much.
I'll use the second one, although my database isn't that big.

The second one works by the way :)
,K

Problem solved
Logged
No clouds today.. :)
My online portfolio