Can I randomly select a row from one table and use a static identifier to select the corresponding data in another table?

Question

The following script was originally designed to randomly select a row, which could do well, however I cannot figure out how to change it to select data from a second table using a column identifier called "nid" ", which is the same in both tables :

$conn = mysqli_connect($host,$username,$password, $database) or die(mysql_error ()); $total_rows = 5; $selected_row = mt_rand(0, $total_rows); $query="SELECT * FROM `node` LIMIT $selected_row, 1;"; $result=$conn->query($query); while($row = $result->fetch_assoc()) { $node=$row["nid"]; echo $row["title"]; $query2="SELECT * FROM `field_data_body` LIMIT $node, 1;"; $result2=$conn->query($query2); while($row = $result->fetch_assoc()) { echo $row["body_value"]; } 

Admittedly, the above does not work very closely, but it was hard for me to find an example of what I was after.

These tables are in the database for the Drupal site, so the header and body_value fields are in two different tables; ultimately, I would like to repeat the result corresponding to the set of title and body_value for randomly selecting a node.

Speaking of this script in particular, I want to use nid to find the corresponding row for the second table.

Is it possible?

The bit that works, the selection of the data that I want to get from one table, has the following format:

  $total_rows = 5; $selected_row = mt_rand(0, $total_rows); $query="SELECT * FROM `field_data_body` LIMIT $selected_row, 1;"; $result=$conn->query($query); while($row = $result->fetch_assoc()) { echo $row["body_value"]; } ?> 

UPDATE:

At the suggestion of the commentator, I used the connection and as a result:

 $conn = mysqli_connect($host,$username,$password, $database) or die(mysql_error ()); $total_rows = 5; $selected_row = mt_rand(0, $total_rows); //Use the result in your limit. $query="SELECT a.nid, a.title, b.entity_id, b.body_value FROM node a, field_data_body b WHERE a.nid = b.entity_id LIMIT $selected_row, 1;"; $result=$conn->query($query); while($row = $result->fetch_assoc()) { echo $row["title"]; echo " | "; echo $row["body_value"]; } 

which worked perfectly.

Answer 1

for future reference, since you solved your problem, you need to close the previous connection before starting a new one or get the results for free

 $conn = mysqli_connect($host,$username,$password, $database) or die(mysql_error ()); $total_rows = 5; $selected_row = mt_rand(0, $total_rows); $query="SELECT * FROM `node` LIMIT $selected_row, 1;"; $result=$conn->query($query); while($row = $result->fetch_assoc()) { $node=$row["nid"]; echo $row["title"]; $result->close(); // close $result $query2="SELECT * FROM `field_data_body` LIMIT $node, 1;"; $result2=$conn->query($query2); while($row = $result->fetch_assoc()) { echo $row["body_value"]; } $result2->close(); // close $result2 

I also noticed that you did not close the connection on UPDATE that you posted either, which will cause this problem to restart later.

Answer 2

If you are going to select a single record, you do not need to define a LIMIT, instead you can use a randomly generated value to select a row using it in WHERE!

Answer 3

You can simply join the two tables. Also, your code has a potential problem. It relies on the existence of at least 5 records. You can avoid this by putting randomization in the query. Then the code will be

 $conn = mysqli_connect($host,$username,$password, $database) or die(mysql_error()); $query = "SELECT n.*, b.* FROM `node` AS n LEFT JOIN `field_data_body` AS b ON n.nid=b.nid ORDER BY REVERSE(RAND()) LIMIT 0, 1;"; $result = $conn->query($query); $row = $result->fetch_assoc(); $result->close(); echo $row["title"]; echo $row["body_value"]; 

The REVERSE() function makes the result from RAND() more random. You can omit it if you wish.