Dropdown With Mysql Tables' Names Of A Database As Options And Php-Mysql Errors/suggestions

[maicol07]

Hi,

I'm trying to build a dropdown in a form with, as options, the names of the tables of one database. How can I do?

This is my script, it doesn't work: there isn't the dropdown...

<form method="get">
<?php
// database connection
include('connect-db.php');
$result = mysql_query("SHOW TABLES")
or die(mysql_error());
?>
<select name="select1">
<?php
while ($line = mysql_fetch_array($result)) {
?>
// The database I choose is "members", as can you see in the code below
<option value="<?php echo $line['Tables_in_members'];?>"> <?php echo $line['Tables_in_members'];?> </option>
<?php
}
?>
</select>
<input type="submit" class="btn waves-effect waves-light" value="SEND">
</form>

connect-db.php

<?php
/*
CONNECT-DB.PHP
Allow PHP to connect to the database
*/

// Database variables (I'm trying on XAMPP)
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'members';
 
// Database connection
$connection = mysql_connect($server, $user, $pass)
or die ("Impossible connecting to the server ... \n" . mysql_error ());
mysql_select_db($db)
or die ("Impossible connecting to the database ... \n" . mysql_error ());
 
?>

Thanks

Edited July 21, 2017 by maicol07

[Krydos]

This works for me:

<?php

// load database values
require 'config.php';

// connect to database
$con = new mysqli($db_host, $db_user, $db_pass, $db_data);
if ($conn->connect_error) die("Connection failed: ".$conn->connect_error);

// perform query
$sql = "show tables";
$result = $con->query($sql);

// start dropdown select
echo "<select>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row['Tables_in_username_db'];
  echo "<option value='$table'>$table</option>";
}
echo "</select>";

Edited July 14, 2017 by Krydos
added line numbers

[maicol07]

Ok thanks, I'll try. But... the config.php file? I have to use the connect-db.php file or not?

 

Also I need a sql query (for an another component) to select all the data in the database from all the tables that have the prefix $username (it's a php variable that contains the username of the user, so a user can have some tables with his prefix and I want to select all the data from all of his tables). Thanks again

Edited July 13, 2017 by maicol07

[Krydos]

Ok thanks, I'll try. But... the config.php file?

The config.php is a common way to separate the $db_host, $db_user, $db_pass, and $db_data variables from the code. If you have 15 different php files they can all include config.php to load those database variables, and if you change the password for example you only have to change it in one place. It's good programming practice to do stuff like this. In this case the config.php would look like:

 

<?php

$db_host = "localhost";
$db_user = "maicol07_user";
$db_pass = "bestpaswordever";
$db_data = "maicol07_db";

Also I need a sql query (for an another component) to select all the data in the database from all the tables that have the prefix $username (it's a php variable that contains the username of the user, so a user can have some tables with his prefix and I want to select all the data from all of his tables). Thanks again

Change line 11 to

$sql = "show tables like '$username_%";

[maicol07]

Ok thanks, but with the SELECT and not with the SHOW statement...

[Krydos]

Well, the show command only loads the tables that start with the username, and then the select tags display the names of the tables which start with the username_ bit. Maybe I'm not understanding what you're trying to do?

[maicol07]

With the SQL SELECT statement, I can select all the data (or a column) of a table. Example: SELECT * FROM table1

If I have table1, table2, table3, ... how can I SELECT all the data of all the tables?

Thanks

[Krydos]

You could do queries in a while loop.

show tables
while (another table) {
  select * from table
}

[maicol07]

Sorry but I can't understand well the condition in the while and in the line below the table...

[Krydos]

It's just psuedocode.

 

<?php

// however you get your username variable
$username = "maicol07";

// load database values
require 'config.php';

// connect to database
$con = new mysqli($db_host, $db_user, $db_pass, $db_data);
if ($conn->connect_error) die("Connection failed: ".$conn->connect_error);

// perform query
$sql = "show tables like $username_";
$result_tables = $con->query($sql);

// start dropdown select
echo "<select>";

// for each table in database
while ($row_tables = $result_tables->fetch_assoc()) {

  // store the name of the table we're working with
  $table = $row_tables["Tables_in_$db_data"];

  // perform query
  $sql = "select data from $table";
  $result_data = $con->query($sql);

  // for each value in the column data
  while ($row_data = $result_data->fetch_assoc()) {

    // store the value of this row of data
    $data = $row_data['data'];

    // create a dropdown entry
    echo "<option value='$data'>$data</option>";
  }
}
echo "</select>";

This code is untested.

[maicol07]

No, I mean this code:

show tables
while (another table) {
select * from table
}

Anyway, the code you write doesn't work on XAMPP... it gives me this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0' at line 1

Edited July 14, 2017 by maicol07

[Krydos]

The php I posted above was the expansion of the psuedocode that you didn't understand

 

What is your sql query?

[maicol07]

I used the first code you gave me (second post) and it gives me that error.

 

So, the error is related to that code, that has the Sql query SHOW TABLES.

 

The other question is: the script I wrote in my last post can already be used or I need to edit something?

Thanks

[Krydos]

The code here https://www.helionet.org/index/topic/28805-dropdown-with-mysql-tables-names-of-a-database-as-options/?p=131382 is tested and works fine on Tommy.

[maicol07]

Ok thanks, it is only because on XAMPP it gives me an error, but if it works on Tommy ok!