SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
-
Sep 21, 2001, 11:36 #1
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Selecting a random id from a database
I am trying to build a script, which selects a random ID from my MySQL database. The ID's are all integers, ranging from 1 to whatever ID is the last is the database.
I have been trying to get a script running that selects a random number out of this heap of integers. This is what I have so far:
PHP Code:<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);
$sql = "SELECT MAX(id) FROM Jokes";
$result = mysql_query($sql);
?>
Thanks in advance.
-
Sep 21, 2001, 12:01 #2
If you just want to get a random record and have MySQL 3.23 or higher you can use ORDER BY rand()
Take a look at this example and freddy's code if you don't have 3.23:
http://sitepointforums.com/showthrea...highlight=RAND
SeanHarry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
Sep 21, 2001, 12:08 #3
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Okay, I have MySQL 3.23.22, which looks sufficient to me
. But I want to select one of the random numbers, and I presume ORDER By RAND() (thanks for introducing me to that function btw
), will generate a random list of all the ID's...
-
Sep 21, 2001, 12:13 #4
No, use it like this to get one random id:
PHP Code:$sql = mysql_query("SELECT ID FROM table ORDER BY rand() LIMIT 1") or die(mysql_error());
Harry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
Sep 21, 2001, 12:18 #5
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks, I had the code set up like that, I only missed the LIMIT 1. I owe you one
!
-
Sep 21, 2001, 12:23 #6
Your welcome
And if you ever see me in a pub
SeanHarry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
Sep 21, 2001, 12:28 #7
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yep, if you help me out one more time, I will certainly do that
.
I now have this code:
PHP Code:<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);
$sql = "SELECT ID FROM Jokes ORDER By RAND(id) LIMIT 1";
$result = mysql_query($sql);
[b]$sql = "SELECT Joke FROM Jokes WHERE ID = '$result'";[/b]
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo ($row["Joke"]);
?>
-
Sep 21, 2001, 12:32 #8
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
$result holds nothing more than a ref to the query result and can't be used in this fashion. Also there is not need to run two queries. Also since you are only getting one record you don't need the while loop around mysql_fetch_array()
PHP Code:<?php
$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);
$sql = "SELECT * FROM Jokes ORDER By RAND() LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo ($row["Joke"]);
?>Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Sep 21, 2001, 12:35 #9
I would change your code to this Mark:
PHP Code:$dbx = mysql_connect() or die("Could not connect to database");
mysql_select_db (Jokes, $dbx);
$sql = mysql_query("SELECT Joke FROM Jokes ORDER BY rand() LIMIT 1") or die(mysql_error());
$result = mysql_fetch_array($sql);
extract($result);
echo $Joke;
Harry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
Sep 21, 2001, 12:40 #10
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I just keep learning new tricks every day. Does extract() "unpack" the result to a string? The rest of the code seems quite familiar, only a few little changes.
-
Sep 21, 2001, 12:43 #11
It saves you having to do this:
PHP Code:$sean = $result["sean"];
Harry Potter
-- You lived inside my world so softly
-- Protected only by the kindness of your nature
-
Sep 21, 2001, 12:44 #12
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
http://www.php.net/extract
It takes an array and creates variables out of each element using the key as the variable name and the value as the value of the element.Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Sep 21, 2001, 12:51 #13
- Join Date
- Jul 2001
- Location
- The Netherlands
- Posts
- 2,617
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Two PHP-Wizards coming to my aid for such a simple script. I feel honored
! Thanks freddy and sean, everything is working just the way I want it right now
.
Bookmarks