...

View Full Version : [solved]Random MySQL Results



zc1
06-04-2007, 01:08 PM
Hi,

I am trying to get a random result from a database table to display random pictures for users if they have more then one picture, at the moment I have it so it pulls the same picture from the database, code below and I called it by putting '.$picsno.'


$picsno = $db->getOne('select picno from ! where userid = ?', array(USER_SNAP_TABLE, $userid) );


I have tried
ORDER BY rand() LIMIT 1 and that didn't work

Can anyone help ?

Regards,
Garry

westmatrix99
06-04-2007, 01:54 PM
Have you tried:

$picsno = "SELECT * FROM picno where userid = $userid ORDER BY rand() Limit 1";
$result = mysql_query($picsno);You need to be able to view the images from the picno table by a specific user?
The limit only limits the data that you are outputting, you could show 1 record from picno per user that matches the query?

Also just out of interest:

$query = "SELECT * FROM picno";
$result = $mysql->SelectLimit($query,1) or die($mysql->ErrorMsg());// you could change this ($query,1) to($query,3) instead

zc1
06-04-2007, 02:06 PM
Hi,

Thanks for your reply

No the result need to be from osdate_usersnaps (if using an array USER_SNAP_TABLE) and the field I need to be pulling data from in picno

I need to pull 1 picno per userid to display the picture, but need it to be random

I tried your first lot of code but it gave me a white page so I tried


$picsno = $db->getOne('select picno from ! where userid = ? ORDER BY rand() LIMIT 1', array(USER_SNAP_TABLE, $userid) );
$result = mysql_query($picsno);

This gave me a different picture, but it still the same all the time

Regards,
Garry

Axis007
06-04-2007, 02:36 PM
I am no good with arrays, maybe you change the array part to also have rand() in it?

Also do you actually have a tablename as "!" and a userid as "?", if so then there is your problem, I think?

zc1
06-04-2007, 02:42 PM
Hi,

I am not much good with arrays either, just the script I am using (osDate) uses them

When using the array ! = USER_SNAP_TABLE (for the array bit)
and ? = $userid (for the array bit)

Regards,
Garry

westmatrix99
06-04-2007, 02:44 PM
$picsno = $db->getOne('select picno from USER_SNAP_TABLE where userid = $userid ORDER BY rand() LIMIT 1';
$result = mysql_query($picsno);

Just out of interest, why the array?

array(USER_SNAP_TABLE, $userid)
it looks like you are doing (LIMIT 1,by array), is that right?
Also what is the number the array is returning?

zc1
06-04-2007, 03:08 PM
Hi,

I was using the array as that is what the script was using I am running this in, I hae now got rid of the array and my code is


$picsno = $db->getOne('select picno from osdate_usersnaps where userid = '.$userid.' ORDER BY rand() LIMIT 1');
$result = mysql_query($picsno);


As for the number it getting:
Without ORDER BY rand() LIMIT 1 it gets: 1
With ORDER BY rand() LIMIT 1 it gets: 2 where the user has more then one image, but it does not give a random picture each time they visit it

Regards,
Garry

westmatrix99
06-04-2007, 03:21 PM
Found this tut, give it a go:
http://www.pixel-designz.net/tutorials/view/11/Advanced_Random_Signature_Generator/

Also there is a random image script here:
http://www.tecnorama.org/document.php?id_doc=57
and here:
http://www.tecnorama.org/document.php?id_doc=58

Hope that helps you?
Cheers

zc1
06-04-2007, 05:34 PM
Hi,

Think I may have got it seems to work ok, can someone confirm


$piccnt = $db->getAll('select picno from ! where userid = ? and ( album_id is null or album_id = ?)',array(USER_SNAP_TABLE, $userid,'0' ) );
$picsno = $piccnt[array_rand($piccnt)]['picno'];

Which I think is the same, but without the array as below


$piccnt = $db->getAll('select picno from osdate_usersnaps where userid = '.$userid.' and ( album_id is null or album_id = '0') ');
$picsno = $piccnt[array_rand($piccnt)]['picno'];

Regards,
Garry



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum