Changing database with PHP/MySQL based on drop-down selection

I currently have several databases with the same tables (people, engagements, groups, grievances, etc.). I have an application that allows you to view pie charts and maps of the data as well as editing values in each table. The data used by the application is available in every database, but, each database also has fields that are unique to itself and can not be found in the other databases.

Since the applications are exactly the same, I would like to combine them into a single application and allow users (surveyors) to pick the database they connect to and see the data for that database across all the pages in the application.

Is this possible? Can I have a drop-down list in index.php that updates the database connection in db.php so every page that requires db.php shows the data for the database selected in the index.php page?

db.php

<?php
header('Content-type: text/html; charset=UTF-8');
// host name, username, password, database
$con = mysqli_connect("localhost","root","","golddb");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
  }

  // Change character set to utf8
  if (!mysqli_set_charset($con, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($con));
    exit();
} else {

}
?>

index.php

<?php
//include auth.php file on all secure pages
include("auth.php");
require("db.php");
?>
...
<!-- Page header with links to Dashboard & Map -->
    <header id="page-content-header">
        <div class="row">
            <!-- Use element to open sidenav -->
            <div class="col-xs-10">
                <span style="font-size:30px;cursor:pointer" onclick="openNav()">&#9776; open</span>
            </div>

        </div>
        <div class="row" id="main1">
            <div style="float: left;">
            <h3><strong>Mining:</strong> Gold Mine</h3>
            </div>
            <form action="#" method="post">
                <!-- Select project Bar -->
                <div style="float: right; width: 20%;">
                    <p>
                        Project
                        <select name="db_connection">
                            <option value="golddb">gold</option>
                            <option value="tutorial">tutorial</option>
                        </select>
                        <input type="submit" name="submit" value="Get Selected Values" />
                    </p>
                </div>
            </form>
            <?php
                if(isset($_POST['submit'])){
                $selected_val = $_POST['db_connection'];  // Storing Selected Value In Variable
                // Change database to the selected value (selected_val)
                mysqli_select_db($con,$selected_val);
                }
            ?>
        </div>
    </header>

Thanks for any help

1 answer

  • answered 2018-10-09 16:36 Nomis

    I'm pretty sure your code work just fine, but since you don't try to make any requests after the mysqli_select_db($con,$selected_val); there's no difference.

    But maybe what you were after was to change it from the start, in this case in db.php you could try :

    // host name, username, password, database
    if(isset($_POST['db_connection']))$selected_val = $_POST['db_connection'];
    else $selected_val='golddb';
    $con = mysqli_connect("localhost","root","",$selected_val);
    

    A more secure way to go would be to use a switch case instead of an user-sent data:

    if(isset($_POST['db_connection'])){
      switch ($_POST['db_connection']) {
        case 'tutorial':
            $selected_val='tutorial';
            break;
        default:
            $selected_val='golddb';
            break;
      }
    }
    else $selected_val='golddb';
    $con = mysqli_connect("localhost","root","",$selected_val);
    

    You can also rename $con as $con_selected if you wish to use both the old connexion (maybe for your general DB, with authentification's data, one your users shouldn't be able to see) and the new.