my rand() query does not work..

[wudz]

Hi,
I wish to select three random auctions for display.
I have previously aquired the total live auctions and rand() them as below, which is inserted in the loop..

$randitem = rand(1,$auctionnum);

The result I get is the three auctions, but they are not randamised the same three are always displayed incremental and ascending...Could someone explain what I have goy wrong..

$result = mysql_query ( "SELECT * FROM AUCTION_auctions
WHERE closed='0'
AND suspended = '0'
ORDER BY '$randitem' LIMIT 3" );

Cheers in anticipation.

John.....AGAIN..hi

 

[BabyJeffy]

Give this a try...

"SELECT *, RAND($randitem) as randy
FROM AUCTION_auctions
WHERE (closed='0') AND (suspended = '0')
ORDER BY randy LIMIT 3"

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[Itshim]

BabyJeffy's suggestion will work, but for future reference you may want to take a look at this comment on rand(), especially if your using rand() in a loop or on Windows. This happened to me before and I was able to fix it using the suggestion.

 

[wudz]

Hi BabyJeffy 'n' Itshim,

First may I say thank you for your swift response.

I replaced the lines and got an error:-

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.........

This is the line:-

while ($row=mysql_fetch_array($result))

Any thoughts lads, I appreciate that you have only a snippit of the total script, if you wish or it may make it easier I will post the script if needed.
I am correct in taking out my rand() and changing the variable $randitem to $auctionnum which holds the active auctions int. to randimise..

Before you answer I will double check that $auctionnum does hold the int. required...pretty sure I checked earlier, but at my age you never know....Hi

Thanks again lads..

John

 

[wudz]

Hi,
Just checked that it held the correct number, it has 30 which is correct for all the test auctions I am running of which 5 should be live...hope this helps.

Cheers
John

 

[Itshim]

This error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.........

means that MySQL returned an error instead a a result (resource), if you post your SQL statement we could probably figure out what went wrong. You could also use mysql_errno(), and mysql_error(); to find what MySQL error you are getting.

 

[wudz]

Hi Itshim
Sorry the above error was caused by me uploading my cobbled about backup file by mistake.. now all cleaned up no error.

I now get a random display ie auction 30,26 and 27, but they are always the same even when reloaded..

This is the statement of which $auctionnum holds 30 of which the last 5 are active as selected by closed and suspended..
Hope this helps..


$result = "SELECT *, RAND($auctionnum) as randy
FROM AUCTION_auctions
WHERE (closed='0') AND (suspended = '0')
ORDER BY randy LIMIT 3";





if ($result)

{

$tplv = "";

$bgColor = "#EBEBEB";

while ($row=mysql_fetch_array($result))

{


/* prepare some data */

Hope all makes some sense,,,

Cheers
John

 

[jpadie]

I've never tried this before (health warning!) but i think you will always get the same three as all you are doing is providing a seed value to the randomising function in mysql.

to obtain a "random" integer between i and j mysql recommend you using this function

FLOOR(i + RAND() * (j – i + 1))

you would integrate this in your code as

$i = 1;
$j = ? ; //put max value here
$result = "
           SELECT 
                *, 
                FLOOR($i + RAND() * ($j – $i + 1)) as randy
           FROM 
                AUCTION_auctions
           WHERE 
               (closed='0') 
               AND 
               (suspended = '0')
           ORDER BY 
               randy 
           LIMIT 
               3";

 

[wudz]

WOW! Thanks jpadie,I will give it a go. I thought I had got it right with my original script....how wrong I was.

This PHP has got quite a varied language...bring back BASIC....Hi

Let you know how it goes..

Cheers to all for their inputs.

John

 

[jpadie]

sorry John - i don't think i'm right here. i had misunderstood what the code was trying to achieve.

the problem is still that you are adding a seed to rand().

this should do it (or i have misunderstood again)

$i = 1;
$j = ? ; //put max value here
$result = "
SELECT
*
FROM
AUCTION_auctions
WHERE
(closed='0')
AND
(suspended = '0')
ORDER BY
rand()
LIMIT
3";

have a look at

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

for more info

 

[wudz]

Hi jpadie,
Well I uploaded and unfortunalty got no display of any items, I have 43 test auctions which I placed directly to $j just to be certain.
Have I got the below correct?
let you have a think as it is well over my head now..hi


$i = 1;
$j = 43; //put max value here
$result = mysql_query("
SELECT
*,
FLOOR($i + RAND() * ($j – $i + 1)) as randy
FROM
AUCTION_auctions
WHERE
(closed='0')
AND
(suspended = '0')
ORDER BY
randy
LIMIT
3");

cheers in anticipation

John

 

[wudz]

Hi jpadie,
I think we cross post's. I have uploaded your second script and did not get any displayed auction and then noticed that $i and $j are not in the statement, should they be inserted in the rand...rand($i,$j) or am I wrong.

Just to confirm my requirement, I wish to extract 3 live auctions at random for display. My test data at present holds 43 auction of which 5 are live more added as they time drop out.

Hope yhis makes sense

John

 

[jpadie]

no - no value for $i and $j are needed. i had thought you were trying to randomise amongst an known list of auction numbers and thus needed some random integers. my fault for not reading the post properly.

I get pseudo random results from the query

Select * from $table order by rand() limit 3

(btw this is a fine method for a small recordset (<1000 records) but there is a real performance penalty in doing this for the entire set. let me know if you are planning on having large recordsets and i will post a more optimised query)

which leads me to believe that something else is afoot with your code. could you post your table structure and some sample data?

meanwhile try this code. you need to fill in your db parameters. it then CREATES a test table and fills it with 100 rows of data (it will only do this once, however many times the script is run) and then outputs three times a random selection of three rows.

<?
$hostname = "";
$username = "";
$password = "";
$database = "";

@mysql_connect($hostname,$username,$password) or die("unable to connect to database server");
@mysql_select_db($database) or die ("unable to select database");

$table = "adie_random_table";

$cr_query = 
"CREATE TABLE  $table  (
  id varchar(255) NOT NULL,
  vl varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;";

//create test table
if (mysql_query($cr_query) === false ):
	echo "table already created";
else:
//use this for creating some table values
	for ($i=0; $i<100;$i++):
	mysql_query("insert into $table set id='$i', vl='value_$i'");	
	endfor;
endif;

$query = "Select * from $table order by rand() limit 3";

for ($i=0; $i < 3; $i++):
	$resultset = mysql_query($query);
	if (!$resultset) {die (mysql_error());}
	echo "<table>";
	while ($results=mysql_fetch_assoc($resultset)):
	?><tr><td><?=$results['id']?></td><td><?=$results['vl']?></td></tr> <?
	endwhile;
	echo "</table> <br/> <br/>";
endfor;

?>

 

[wudz]

Hi jpadie,
yep I have an id for every auction seq. updated as the auctions are submitted, the random display is only required on the index page. So I thought a new call would be made every time the index page was exited and entered..But thinking about it this surley would not occur if it changed for every user logging in as the db would become server hunger.
Would an approach say reload (what? or which?) every say 5 mins the view_feature.php where the script is held and is called by the index page(home)...

I hope this makes sense, seems far more complexed than I first envisged...

Cheers
John

 

[jpadie]

nothing complex here - really.

i'm not certain that I understand your english though (no disrespect intended). the other confusion is that if my guess as to your meaning is correct, it appears non-sequitur to my previous post.

questions:

1. did the sample code that i provided work for you?
2. can you post your table structure (i.e. a create statement from mysql)?
3. can you post, say, 10 sample rows as a mysql export (i.e. an insert statement). if you can't i can create them if you give me 2.
4. is it likely that you will have more than 1000 auction items at any one time?

there is no reason why a simple randomising script or query will not work. once we establish the answers to 1-4 we can write a query that will not be server hungry or resource intensive (if i have understood your post correctly).

 

[wudz]

Hi jpadie,
Very sorry for my command of the terms, I sort of understand what is going on and the logic from my old BASIC days, pre functions, but only been using PHP for a couple of months...so still quite thick...hi

All that said GREAT all working as I wanted...why!. I was using $SESSIONS to retrieve the total auctions on the db, thought that would be OK.
Now I have used fetch as below and placed the rand inside the statement, before it was nestled in the loop...NOW WORKS GREAT USING THE SELECT function provided by your good selves.....THANK YOU...

$COUNTERSTOSHOW = @mysql_fetch_array (@mysql_query("SELECT *
FROM PHPAUCTION_counterstoshow"));
$query = "select * from AUCTION_counters";
$result_counters = mysql_query($query);
if($result_counters) {
if($COUNTERSTOSHOW['auctions'] == 'y')
$auctionnum = mysql_result ($result_counters,0,"auctions");
$auctionit = rand(1,$auctionnum);
}



//-- Set offset and limit for pagination

$limit = 20;
if(!$offset) $offset = 0;

//--- retreive three random live auctions

$result = mysql_query
("SELECT *,
RAND($auctionit) as randy
FROM AUCTION_auctions
WHERE (closed='0') AND (suspended = '0')
ORDER BY randy LIMIT 3");




if ($result)

{

$tplv = "";

$bgColor = "#EBEBEB";

while ($row=mysql_fetch_array($result))


The problem was obviously caused by my sort comings with PHP, but we got there in the end through this GREAT forum.

Just why did it not work using $SESSIONS or was it because I had RAND incorrectly placed? I presume the latter!

Cheers all involved.

John

 

[jpadie]

$SESSIONS does not exist as a superglobal variable. the problem might have been that it should be $_SESSION (note the underscore).

without your table structure it's impossible to tell what you're doing with the sql calls but ... just in case ... never retrieve the number of rows that exist by using select * coupled with num_rows. you should always use select count(*) from table

lastly you haven't answered the question on recordset size. if you have a large recordset i'd recommend going back to a mysql randomising solution and using the following syntax:

$table = "";
$query = "
SELECT *
  FROM $table  AS set1 JOIN 
       (SELECT ROUND(RAND() * 
                     (SELECT MAX(ID) 
                        FROM $table)) AS ID)
        AS set2
 WHERE set2.ID >= set2.ID 
 ORDER BY set1.ID ASC 
 LIMIT 3;";

//adapted from post on jan.kneschke.de

 

[wudz]

Hi jpadie,
Here is the auctioned items db structure, being relistic a 1000 auctions would be great....Hi

#
# Table structure for table `PHPAUCTION_auctions`
#

CREATE TABLE `AUCTION_auctions` (
`id` int(32) NOT NULL auto_increment,
`user` int(32) default NULL,
`title` tinytext,
`starts` varchar(14) default NULL,
`description` text,
`pict_url` tinytext,
`category` int(11) default NULL,
`minimum_bid` double(16,4) default NULL,
`reserve_price` double(16,4) default NULL,
`buy_now` double(16,4) default NULL,
`auction_type` char(1) default NULL,
`duration` varchar(7) default NULL,
`increment` double(8,4) NOT NULL default '0.0000',
`location` varchar(30) default NULL,
`location_zip` varchar(10) default NULL,
`shipping` char(1) default NULL,
`postage` double(3,2) default NULL,
`payment` tinytext,
`international` char(1) default NULL,
`ends` varchar(14) default NULL,
`current_bid` double(16,4) default NULL,
`closed` char(2) default NULL,
`photo_uploaded` char(1) default NULL,
`quantity` int(11) default NULL,
`suspended` int(1) default '0',
`private` enum('y','n') NOT NULL default 'n',
`num_bids` int(11) NOT NULL default '0',
`sold` enum('y','n','s') NOT NULL default 'n',
`shipping_terms` tinytext NOT NULL,
`relist` enum('0','1') NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=35 ;

I hope this what you want to see. OK about $SESSIONS error and using the SELECT count(*), I will try your script tonight when I get back and post tomorrow..

Thank's jpadie your are one patience bloke!

Cheers
John