Num_rows and rownum

[Philwn]

I have a table and the following code. Im not sure if im using rownum in the correct way as im not getting the result expected.

 

$result = mysql_query("SELECT * FROM totem_ads");
$num_rows = mysql_num_rows($result);
$random_row = rand(1, $num_rows);

$ad_sql = mysql_query("SELECT * FROM totem_ads WHERE rownum='$random_row'");
while($row_advert = mysql_fetch_array($ad_sql))
{
echo "<a href='" . $row_advert['link'] . "'><img src=$img_loc'" . $row_advert['image'] . "' alt='" . $alt_text['image'] . "'></a>";
}

 

THe ad_sql displays as expected SELECT * FROM totem_ads WHERE rownum='1' however the while loop displays nothing which makes me wonder if im using rownum incorrectly. When i search for information on rownum I find all kinds of confusin statements from people posting in forums with problems and not the correct answer im looking for.

[floridaflatlander]

I'm begining PHP too so take this with some salt.

 

Where are you connecting to the database?

 

Do your rows have id's ? Are they auto increments?

 

You "may be able to" use a query something like this

 

$q = "SELECT COUNT (column name ) FROM totem_ads";

$r = mysqli_query ($dbc, $q); // Run the query

$random_row = rand(1, $r);

 

or

 

$q = "SELECT * FROM totem_ads"; (I used the same as you to get a number)

$r = mysqli_query ($dbc, $q); // Run the query

$num = mysqli_num_rows($r); // Count records

$random_row = rand(1, $r);

 

What are you going to do if you delete a row? Do you have a query to run rand() again it hits a deleted row?

 

 

[Philwn]

The database connects before this with an include and works fine as alot of other queries on the page work. My problem was that I want a table which stores several adverts in (image, link, alt_text, ID).

 

If I made it select a random ID number it would work fine untill I deleted a row. I.e if i have 10 rows with Id numbers 1,2,3,4...10 this would work but If i deleted row 5 and the random number was 5 it wouldnt display. Therefore im trying to find a way of selecting a specific row number and found rownum which i thought would do what I wanted.

 

So I count the number of rows which lets say for example is 4 (IDS 2, 5, 7, 9) it would random a number between 1 and 4 and select row number 2 for example which would be ID 5.

 

I hope ive explained my problem clearer.

[zenlord]

Indeed, you're doing it wrong.

 

In a select-statement, after 'WHERE', a column-name has to be entered, followed by a '=' and a condition (constant or variable). I'm guessing 'rownum' is not a valid columnname in your table.

 

If you want to display a random row out of a table, you're better of using the unique index of your table (f.e. 'id'). Use a query to gather all indexes in an array, pick 1 element ($chosen_element) of the array and then query the table with a 'WHERE id = $chosen_element'.

 

Vincent

[Philwn]

rownum is not a column in my table, I was searching for a way of selecting a certain row number from my table and came accross what I thought was a command, I believed ROWNUM was a function like NUMROWS.

 

So How would I random $chosen_element if the ID's were 1 3 5 6. If i used Rand(1, 6) and got 4 then it wouldnt display any image/link????

[floridaflatlander]

Like I said I'm new to this and work within what I've done before,

 

Here's how I think would do it, of course I haven't tested it.

 

// Get a random #

$result = "SELECT * FROM totem_ads";

$result = mysqli_query ($dbc, $result); // Connect to the database connection defined in your include() & run the query

$result = mysql_num_rows($result); // This gets the number of rows

$random_row = rand(1, $result); // If a row is deleted ot missing your rand() will show a null if it lands on it.

 

// Use the random number to get a random row

$randon_row = "SELECT * FROM totem_ads WHERE rownum(column_name) = $random_row"; // is rowmun a column ?

$ad_sql = mysql_query($dbc,$random_row); // Connect again to the database defined in the include() ..

while($row_advert = mysql_fetch_array($ad_sql))

 

----------

Someone with more experience may have more info or be able to trim this up a bit.

--------------------------------------

 

Use an echo statement some where on your page to see if your getting a $random_row value, echo "$random_row";

 

My suggestion is to play with it, I've spent a half a day on stuff like this only to find out it was something very simple.

[zenlord]

rownum is not a column in my table, I was searching for a way of selecting a certain row number from my table and came accross what I thought was a command, I believed ROWNUM was a function like NUMROWS.

I don't use MySQL, so I don't know if it is correct function.

So How would I random $chosen_element if the ID's were 1 3 5 6. If i used Rand(1, 6) and got 4 then it wouldnt display any image/link????

Just insert the ID's in an array and pick one element out of the array, not with rand(), but with array_rand()...

[Philwn]

Sounds like a different way of acieving what im after but the only array I have used is mysql_fetch_array so How would I store the ID numbers in an Array to be able to use rand_array?

 

googling mysql and array just pulls back examples of mysql_fetch_array.

[menator]

Let mysqls' RAND() function do the work. No need to worry if rows have been deleted or not. http://www.mustap.com/databasezone_post_141_mysql-rand-function

[Philwn]

Thanks menator exactly kind of thing I was looking for, alot simpler than previous attempted methods and seems so obvious too.

 

It does say not ideal for large tables but this table will have no more than 10 entries so perfect.

[Pikachu2000]

Why ORDER BY RAND() is bad

[Philwn]

I can understand why its bad but is it so bad if there are only going to be 10 rows in the database?

[Pikachu2000]

If there are only ever going to be 10 rows, it will be fine. If the table grows and performance starts to degrade, that would be the first thing I'd look at though.