Mysql – How to execute dependent multiple SQL queries from multiple databases via same PHP web page

MySQLPHPphpmyadmin

We can connect a single PHP page with more than one databases. But what about executing multiple dependent queries that belong to different databases?

mysqli_multi_query(connection,query);

does not seems helpful as it asks for a single shot while my second query uses result of the first. I tried using two connection strings and using them with two queries but this does not seems to be helpful. My code uptil now is here:

<div class="image fit">
   <?php
   //Connection with DB
   $user = "root";
   $pass = "";
   $db1 = "raw_images";
   $db2 = "labelled_images";
     $conn1 = mysqli_connect('localhost',$user,$pass,$db1,true);
   $conn2 = mysqli_connect('localhost',$user,$pass,$db2,true);
   if(!$conn1||!$conn2)
   {
   die(mysqli_error());
   }
*/
//query to display image
   $query1 = "SELECT * FROM raw_images.tbl_raw_image WHERE id IN
    (SELECT id FROM (SELECT id FROM raw_images.tbl_raw_image ORDER BY RAND() LIMIT 1) t)";
   $sth = $conn1->query($query1);
   $result=mysqli_fetch_array($sth);
   $id=$result['id'];
   $image=$result['image'];
   $query2="INSERT INTO `labelled_image`.`tbl_labelled_image` (`id`,`image`, `label`, `id_in_raw_image_table`) VALUES (NULL, '$image', '0', '$id')";
   $rs  = mysqli_query($conn2, $query2);
   echo '<img src="data:image/jpeg;base64,'.base64_encode( $image ).'"/>'; ?>
       </div>

When using such approach, first query executes successfully but not the second one. While executing it simply does not inserts values which I retrieved from the first table but only the values which are given directly to the query like label which I pass zero hence it results in the following outcome:

Best Answer

You shouldn't need two connections for this as the databases in your code are on the same MySQL instance and the DB user apparently has grants to use both.

All you have to do is prefix your table with the database name in the SQL statements, as you have already done. You can use the same connection for both. (It doesn't matter which database is specified in the connection parameters, I suspect this parameter can even be empty.)

Related Solutions

Mysql – How to subtract an object from another in PHP/MySQL

$query ="select sum(copies) as totalbooks from booksdetail";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$totalbooks = $row['totalbooks'];  

$query ="select count(id) as issuedbooks from issue";
$result=mysqli_query($con, $query);
while($row=mysqli_fetch_array($result)){
$issuedbooks = $row['issuedbooks'];   

$remainingbooks = $totalbooks - $issuedbooks;

PHP is off-topic for this site though, really.

How to Select Random and Unique Records from MySQL in PHP

Preliminary: I assume this is your table:

CREATE TABLE questions
(
    question_number INTEGER NOT NULL PRIMARY KEY,
    question VARCHAR(255) NOT NULL, 
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    right_answer ENUM ('a', 'b', 'c', 'd') NOT NULL
) ;

You can let MySQL do the hard work for you, and just execute the following SELECT statement, that will sort randomly all your table (i.e.: it is actually shuffling the rows), and just choose the top 5 rows (NOTE: I've used 5 instead of 15 for the sake of simplicity).

The key point: instead of generating a random number from PHP, let the database deal with the random numbers...

SELECT
     question_number, question, answer_a, answer_b, answer_c, answer_d, right_answer
FROM
     questions
ORDER BY
     rand()  
LIMIT
     5 ;

(Converting this to PHP code is quite straightforward)

You will get something like:

question_number | question                                             | answer_a     | answer_b     | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :----------- | :----------- | :----------------- | :---------------------- | :-----------
              2 | Which is the colour of the White HOuse?              | Black        | Green        | Blue               | White                   | d           
              3 | What's the tallest building on Earth as of 2017?     | Empire State | Burj Khalifa | Eiffel Tower       | Tokyo Skytree           | b           
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes          | No           | Depends on gravity | Depends on kind of bird | b           
              6 | Which is the third letter of the alphabet?           | a            | b            | c                  | d                       | c           
              7 | Which is the first vowel?                            | a            | e            | i                  | o                       | a

Repeating the same query will give you a different set of questions (although some might have already be asked):

question_number | question                                             | answer_a | answer_b | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :------- | :------- | :----------------- | :---------------------- | :-----------
              9 | How many primary colours are there (for a human)?    | 3        | 2        | 6                  | 4                       | a           
              5 | How many wheels does a (most common) car have?       | 4        | 6        | 8                  | 10                      | b           
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes      | No       | Depends on gravity | Depends on kind of bird | b           
              2 | Which is the colour of the White HOuse?              | Black    | Green    | Blue               | White                   | d           
              6 | Which is the third letter of the alphabet?           | a        | b        | c                  | d                       | c

When you work with a database, it is normally a good idea to let the database give you the complete answer to your problem, instead of looping in your code.

You can see all the setup and some more executions at dbfiddle here

References:

RAND()
LIMIT

Best Answer

Related Solutions

Mysql – How to subtract an object from another in PHP/MySQL

How to Select Random and Unique Records from MySQL in PHP

Related Question