PHP question

[Ciqala]

i'm using the php function rand() to determine a random record to display by doing the following (pseudo-code)

$max = select max(id_field) from table
$id = rand(1,$max)
$result = select * from table where id_field = $id

this works great except i have removed some rows from the table so there are gaps in the id_field so when it hits an $id that no longer exists mysql_result($result,0,'column_name') falls over with cannot jump to row 0 in table (because $result is empty)

any ideas on a way around this.

Ciq

 

[Ciqala]

oops forgot to mention id_field is set to auto_increment too.

 

[Bob]

This is more of a mySQL question.

What I would do is this (pseudo):

1) Select the max(id_field)
2) Pick your random number
3) check to see if that record number exists (i.e. Do a query and see if it returns any information).

4) if info exists ---> do your processing
if info does not exist -----> redo the random number pick


Another way (but more CPU intensive) would be to query the database for all records, and then store all of the id_field numbers in an array. You can then determine the length of the array and pick a random number between 0 and the array length-1. You can then pick out that array element, which contains the id_field, and you can select that DB info again. Example:

Database has records with id_fields of: 1, 2, 3, 5, 7, 9, 10

Query DB and store the id_field in an array, so now we have:

array = [1, 2, 3, 5, 7, 9, 10]

length of array = 7

pick a random number between 0-6.. lets say 4.

array element number 4 is 7 (remember array elements start at 0).

query the database again for all of the information with the id_field of "4".

Good luck.

-Bob

 

[Customb]

Hello,

The most common way to do this is to select one row randomly.
This can be done with the following query:

SELECT * FROM <table> ORDER BY RAND() LIMIT 1

Hope this helps...
Best Regards.

 

[Bob]

Originally posted by Customb
SELECT * FROM <table> ORDER BY RAND() LIMIT 1

Awesome! You learn something every day

Files that away for future use



-Bob

 

[Ciqala]

does anyone know if the table rowid is kept sequential i.e.

if i had 5 rows of data in my table

rowid - field_id
1----------1
2----------2
3----------3
4----------4
5----------5

and say i deleted row 3 would the table then be in this state?

rowid - id_field
1---------1
2---------2
3---------4
4---------5

if so i imagine i could do...
$id = max(rowid)
$result = select * from table where rowid = $id

 

[Customb]

To the best of my knowledge, MySQL has no support for rowid.

In fact, there is a variable named _rowid, but this variable represents an primary and unique field that is of int() type.

So, there is no way that one can select the n'th "phisical row" from a MySQL table.

Hope this helps also.

Best Regards.

 

[Customb]

If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in Version 3.23.11).

 

[Ciqala]

mysql_connect($db['serv'],$db['user'],$db['pass'],$db['name']);
mysql_select_db($db['name']) or die("Unable to select database ".$db['name']);
$query="select * from hhd_quotes order by rand() limit 1";
$result=mysql_query($query);
$num=mysql_num_rows($result);
$i = 0;
$quote_id=mysql_result($result,$i,"id");
$quote_quote=mysql_result($result,$i,"quote");
$quote_by=mysql_result($result,$i,"by");
$quote_movie=mysql_result($result,$i,"movie");
$quote_name=mysql_result($result,$i,"name");
$quote_email=mysql_result($result,$i,"email");
$quote_website=mysql_result($result,$i,"website");

Tried this but it only returns the first row. any ideas?

 

[Customb]

Can you tell me the structure of the table?

 

[Ciqala]

its a bit of a big file but heres the structure
(only have mspaint on this laptop)

http://www.hiphopdvds.com/images/table_structure.bmp

I think its table type MyISAM as well

 

[Customb]

The way I presented early seems to be working only for the latests releases of MySQL (I do not know for sure the release).

If it is not the case, I do not know what the problem is. I use this query a lot and it works every time.

Regards.

 

[Ciqala]

thanks anyways customb it definately sounds like that is the most efficient way to do it.

i'll see if i can pick at it a bit more and fix it.

once finished this will be my first official php script and is a random movie quote generator that can be included in any php page and formatted as desired and i am quite excited...

this is a bit of a steep learning curve i'm on though i'm a powerbuilder programmer by profession which is similiar to VB in terms of syntax so php is worlds apart from it. i'm getting there slowly though.

besides this part of dnforum needs a bit of a jumpstart it could be a good resource if we got some decent topics going.