CODEWITHSUNDEEP
phpmysqlsqldatabase
Randy Role
Randy Role

Reputation: 23

Selecting 4 columns from 3 Random Data From PHP & MySQL

so I'm using the RAND() LIMIT 3, but I use it in a loop to get 3 random rows.

I need to use each column in a different place in my site. How can I grab the data without running a different query for each column?

The data I need shouldn't be in order, so it'll be for example:

$Title, will be the title of the page.
$price, will be the price of the service/product.
$description, will be the description of the service/product.

Clearly, they're not close one to another in the php file.

Both answers sound logical, but I'm totally new to mysql and I can't make it work.

This is what I have in my php file:

<?php
mysql_connect("localhost", "test", "test") or die (mysql_error ());
mysql_select_db("test") or die(mysql_error());
$strSQL = "SELECT name FROM UserId ORDER BY RAND() LIMIT 3";
$rs = mysql_query($strSQL);
while($row = mysql_fetch_array($rs)) {
  echo $row['name'] . "<br />";
 }
mysql_close();
?>

What it does is return random 'name' column of 3 rows. The table has 'id', 'name' and 'Age'.

Your help is really appreciated!

Upvotes: 2

Views: 281

Answers (2)

ipoga
ipoga

Reputation: 394

If you put the values into variables, such as $title, $price and $description their values will be remembered across the same file, even when using includes.

If you are trying to save the values across different pages, there are different ways of achieving this, although I would probably recommand using $_SESSION to store such information across pages.

If you are doing as first suggested, but without luck, I will need more information to answer your question properly. A small code sample could help.

Edit:

While the answer by @michael-berkowski is fully functional, you don't necessarily have to use $_SESSION to achieve what you want. Since you stated that you're just learning PHP, I've added a different approach. While not as elegant as the other answer, it is faster, and I have edited variables a little (it is a good habit, to use lowercase table names, the same for variables):

<?php
//Insert DB-connection code
$sql = "SELECT `name` FROM `user` ORDER BY RAND() LIMIT 3";
$rs = mysql_query($sql);
//We should check, if there are actually 3 rows, before doing anything else.
if (mysql_num_rows($rs) == 3) {
   $title = mysql_result($rs,0,0);
   $price = mysql_result($rs,1,0);
   $description = mysql_result($rs,2,0);
}
echo("Title: $title <br/>Price: $price <br/>Description: $description");
?>

Good luck learning PHP.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270637

Store them in $_SESSION only if they are not already there, and always access them from $_SESSION.

// On  every page that uses these values:
session_start();
if (empty($_SESSION['rand_rows'])) {

   // DB connection code omitted...

   // This block will execute once per session only...
   // Get 3 random rows
   $strSQL = "SELECT name, id, age FROM UserId ORDER BY RAND() LIMIT 3";
   $rs = mysql_query($strSQL);
   if ($rs) {
     // Fetch all 3 rows and store them in $_SESSION:
     while ($row = mysql_fetch_assoc($rs)) {
       $_SESSION['rand_rows'][] = $row;
     }
   }
}

// Later, get them $_SESSION
// To get the name for example:
foreach ($_SESSION['rand_rows'] as $r) {
  echo $r['name'] . "<br />";
}

// Use the same looping pattern to get the id or age where you need them.

It still isn't clear if you actually need this to persist across page loads. If you only need these rows on one single page and can get 3 different rows on other pages or subsequent page loads, there's no need to store into $_SESSION and instead just store them into an array with:

// Array to hold all results
$results = array();
while ($row = mysql_fetch_assoc($rs)) {
  $results[] = $row;
}

... and use the same foreach pattern to iterate over $results.

Upvotes: 3

Related Questions