PHP MySQL Rand Array

[thunderousity]

I've currently got a mysql RAND() function to select random rows of data. (Not the most efficient method but it works) My current code returns 1 record but I want to return 3 results so I've changed the LIMIT from 1 to 3 as below. $query_rs = "SELECT * FROM tblexample ORDER BY RAND() LIMIT 3";$rs = mysql_query($query_rs, $database) or die(mysql_error());$row_rs= mysql_fetch_assoc($rs);$totalRows_rs = mysql_num_rows($rs); I need to include the individual rows in various parts of my web page. e.g. Result 1 at the top of the page, Result 2 and 3 elsewhere.How can I alter the code so that I can reference and include the 3 results individually. I assume I can put these in some sort of array.

[niche]

php while loop.

while ($row_rs= mysql_fetch_array($rs)) {  //do your magic for each row here}

This will loop through each row for you. EDIT: mysql_fetch_array() turns each row selected into an array

Edited June 24, 2012 by niche

[thunderousity]

You'll have to excuse my weak php knowledge. My understanding is that the while loop is ok for a list of rows for example. Ford MustangChevy CorvettePontiac Firebird However, I need to echo one of the 3 rows separately and uniquely in various parts of the page. Usually I echo a single result like so with the RAND LIMIT set to 1; Row 1 = Ford Mustang

<h1>More <?php echo $row_rs['results'];?>Ford Mustang</h1>

However, now I have 3 in the RAND LIMIT I have 3 rows. e.g. Row1 = Ford MustangRow2 = Chevy CorvetteRow3 = Pontiac Firebird Now I need to put a result for whichever row I decide into differing parts of the page. (The <?php echo $row_rs['Row n'];?> part is for example purposes only.)

<h1>Car: <?php echo $row_rs['Row1'];?>Ford Mustang</h1>

html...

<p>Car: <?php echo $row_rs['Row3'];?>Pontiac Firebird</p>

more html...

<h3>Car: <?php echo $row_rs['Row2'];?>Chevy Corvette</h3>

So somehow I think I need to put row 1, 2 and 3 into an array so that I can call them separately and uniquely. I hope that makes sense.

[niche]

Then put an if statement in your while loop with a counter

$counter = 0;while ($row_rs= mysql_fetch_array($rs)) {  if ($counter == 0) {	 //fords	 $counter++;  } elseif  ($counter == 1) {	 //chevy	 $counter++;  } elseif  ($counter == 2) {	 //chrysler	 $counter++;  }}

. EDIT: Obviously, this code depends on consistent query results. Instead of a counter, you can simply test for make in the if. if ($row_rs['make'] == "chevy") { //do chevy magic here}

Edited June 25, 2012 by niche

[justsomeguy]

You can use a while loop to store the results into an array, and then access each array item individually where you want it to go. You can also use mysql_result to get a value from a certain row in the result set without needing to loop through and build an array first. http://www.php.net/manual/en/function.mysql-result.php

[niche]

Interesting jsg. Are you saying treat the mysql resource as a multidimensional array?

Edited June 25, 2012 by niche

[justsomeguy]

Either loop through the result to build an array of the rows, or randomly access the records directly with mysql_result.

[niche]

The manual said mysql_result() is slower. In your opinion, would it be noticeably slower?

[justsomeguy]

Not for a result with 3 records.

[niche]

Frankly, I'm always impressed how fast everything works almost without regard to what I'm writing. Edit: Displaying a page in 5 seconds or less is my goal.

Edited June 25, 2012 by niche

[Guest So Called]

I'm seeing my pages served in about 80-300 milliseconds including 12-20 MySQL accesses, on a shared hosting LAMP server. I'm curious where you would see pages served that would challenge 5 seconds.

[justsomeguy]

Heavy database work is usually the reason for long request times. Take this query for example:

TRUNCATE TABLE content_session_archive;TRUNCATE TABLE content_session_history_archive;INSERT INTO content_session_archive SELECT * FROM content_session;INSERT INTO content_session_history_archive SELECT * FROM content_session_history;

Not very complex, it empties 2 tables and copies 2 other tables. I've seen that take 6 minutes to finish when the tables get big enough. I've got a script running on one server right now that started about 14 hours ago. That's a script that runs overnight and does all of the tasks that take too long to run on demand during the day. Right now it's rebuilding content assignments for 34,284 users, which it started only about 20 minutes ago. Previously it rebuilt content for 34,056 users and that started at 3:22 AM and finished at 12:21 PM, about 20 minutes ago. Obviously that's a target for optimization, but there are just some things that take a while to finish. The process to rebuild content takes a while because of the complexity of the system where content gets assigned to users. In a more practical example, it's common to see a report take several minutes to generate and output as CSV or PDF format if it contains a lot of data.

[Guest So Called]

I was thinking in terms of the amount of time a visitor would have to wait for a website page to be served. I think most site visitors would probably give up before 14 hours. Are we discussing site visits or database updates? If the latter then I apologize for misunderstanding the topic.

[justsomeguy]

I guess it depends on the definition of "site". If you're talking about a purely informational site then it shouldn't take long at all, but a full-blown web application (e.g. Facebook) will typically hit the database a lot harder.

[niche]

I think we're discussing both. There's no question the faster you can display a page the better. Likewise, housekeeping scripts can run for hours possibly days. As soon as I discovered php, I started moving our file maintenance and analysis from an old text editing program that we used for over thirty years to php. Just like jsg, we have scripts that run a very long time. Though when it comes to highly valued custom user displays, 5 secs is not too long IMO. I agree that simple price-availability displays should display as fast as possible. EDIT: I remember In Star Trek - The Next Generation Movie, Data told Picard the compuer needed a few minutes to make all the calculations. They haven't made a computer as fast as the Enterprise's, yet. lol.

Edited June 25, 2012 by niche

[Guest So Called]

Well it takes me a couple minutes to update my IP to country database. I download it and unzip it, and then it takes a couple minutes to read the CSV flat text file and insert it into a table. I have a script that reads about 50,000 log records (in MySQL) and analyzes them for human visits vs. search engine indexing visits, takes about 10 seconds, but that's admin only. I'm sure we agree that maintenance tasks can take quite a lot more time than ordinary site visits from the public.