Jump to content

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


Recommended Posts

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 by maicol07
Link to comment
Share on other sites

  • Replies 34
  • Created
  • Last Reply

Top Posters In This Topic

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 by Krydos
added line numbers
Link to comment
Share on other sites

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 by maicol07
Link to comment
Share on other sites

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_%";
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 by maicol07
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share


×
×
  • Create New...