如何在下拉列表MySQL PHP中列出数据库的所有表(而不是记录)

Question

我正在创建一个onlineshop，用户将通过输入标题、描述、价格和图像来添加一个新记录，但我也希望将我的所有表名下拉列表，以便用户选择它们作为添加产品的类别。我的数据库的详细信息是: db = onlineshop

connect.php

<?php    
    // Try to connect to MySQL
    $connect = mysql_connect('localhost','root', '') or die('Sorry could not connect to     database');
    // Check connect and return error if failed
 $use_db = mysql_select_db('onlineshop');
$create_db = "CREATE DATABASE onlineshop";
if(!$use_db) {
    echo mysql_error();
    mysql_query($create_db);
    mysql_select_db('onlineshop');
}
$con=mysqli_connect('localhost','root', '');
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create database
$sql="CREATE DATABASE onlineshop";
if (mysqli_query($con,$sql))
  {
  echo "Database my_db created successfully";
  }
else
  {
  echo "Error creating database: " . mysqli_error($con);
  }

//main table
$sql = 'CREATE TABLE mens( '.
       'id INT NOT NULL AUTO_INCREMENT, '.
       'title VARCHAR(20) NOT NULL, '.
       'description  VARCHAR(45) NOT NULL, '.
       'price   FLOAT NOT NULL, '.
       'image varchar(200),'.
       'image_small varchar(200),'.
       'primary key ( id ))';

//copy attributes of the main table
$sql2= 'CREATE TABLE women AS ( SELECT * FROM mens where 1=2)';
$sql3= 'CREATE TABLE kids AS ( SELECT * FROM mens where 1=2)';
$sql4= 'CREATE TABLE infants AS ( SELECT * FROM mens where 1=2)';
$sql5= 'CREATE TABLE baby_books AS ( SELECT * FROM mens where 1=2)';
$sql6= 'CREATE TABLE garden AS ( SELECT * FROM mens where 1=2)';
$sql7= 'CREATE TABLE comics AS ( SELECT * FROM mens where 1=2)';
$sql8= 'CREATE TABLE cooking AS ( SELECT * FROM mens where 1=2)';
$sql9= 'CREATE TABLE desktop AS ( SELECT * FROM mens where 1=2)';
$sql10= 'CREATE TABLE laptop AS ( SELECT * FROM mens where 1=2)';
$sql11= 'CREATE TABLE mobile AS ( SELECT * FROM mens where 1=2)';
$sql12= 'CREATE TABLE misc AS ( SELECT * FROM mens where 1=2)';
$sql13= 'CREATE TABLE moviestv AS ( SELECT * FROM mens where 1=2)';
$sql14= 'CREATE TABLE music AS ( SELECT * FROM mens where 1=2)';
$sql15= 'CREATE TABLE games AS ( SELECT * FROM mens where 1=2)';



$retval = mysql_query( $sql, $connect );
$retval2 = mysql_query($sql2, $connect);
$retval3 = mysql_query($sql3, $connect);
$retval4 = mysql_query($sql4, $connect);
$retval5 = mysql_query($sql5, $connect);
$retval6 = mysql_query($sql6, $connect);
$retval7 = mysql_query($sql7, $connect);
$retval8 = mysql_query($sql8, $connect);
$retval9 = mysql_query($sql9, $connect);
$retval10 = mysql_query($sql10, $connect);
$retval11 = mysql_query($sql11, $connect);
$retval12 = mysql_query($sql12, $connect);
$retval13 = mysql_query($sql13, $connect);
$retval14 = mysql_query($sql14, $connect);
$retval15 = mysql_query($sql15, $connect);
?>

dropdown.php

<?php
$connect = mysql_connect('localhost','root', '') or die('Sorry could not connect to database');

function runSQL($sql)
{
    $mysqlConnection = getConnection();
    $ResultSet = $mysqlConnection->query($sql);

    return $ResultSet;
}

function getTableList()
{
    $sql = "SHOW TABLES";
    $ResultSet = runSQL($sql);

    if(!$ResultSet)
    {
        echo "Table list not found";
    }

    return $ResultSet;
}
?>

索引包含一个表单以及一个我在网上找到的调用dropdown.php的函数。

index.html

   <form action="insert.php" method="post">
<br>
<div><label for="title">Title: </label><input type="text" name="title"/></div>
<div><label for="description">Desc: </label><input type="text" name="description"/></div>
<div><label for="price">Price: </label><input type="text" name="price" /></div>
<input type="submit" name="submit" value="Submit">
</form>



<form id="imageform" method="post" enctype="multipart/form-data" action='ajaximage.php'>
Upload your image <input type="file" name="photoimg" id="photoimg" />
</form>
<div id='preview'>
</div> 


<?php
include_once 'dropdown.php';
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
        <select name="Tables" id="ddTables">
            <?php
                $tableResults = getTableList();
                if($tableResults)
                {
                    if($tableResults->rowCount() > 0)
                    {
                        $tables = $tableResults->fetchAll(PDO::FETCH_NUM);
                        foreach($tables as $table)
                        {
                            $name = $table[0];
                            echo '<option value="'.$name.'">'.$name.'</option>';
                        }
                    }
                }
                else
                {
                    echo '<option value="0">No Data</option>';
                }
            ?>
        </select>
        <input type="submit" id="tableSubmit" value="Submit"/>
    </form>

我得到的只有一个空的下拉列表

我真的是新加入PHP的，所以请告诉我对我的数据库的任何建议，以及如果你建议我包括一个脚本。

我确信问题是index.html中的index.html()函数没有返回任何内容。

可接受任何建议

Answer 1

这是关于如何从PHP.NET中列出表的示例

http://us3.php.net/manual/en/function.mysql-list-tables.php

dropdown.php

    <?php
    $dbname = 'mysql_dbname';

    if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
        echo 'Could not connect to mysql';
        exit;
    }

    $sql = "SHOW TABLES FROM $dbname";
    $result = mysql_query($sql);

    if (!$result) {
        echo "DB Error, could not list tables\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    while ($row = mysql_fetch_row($result)) {


   $tables .='<option value="{$row[0]}">{$row[0]}</option>';

    }

    mysql_free_result($result);
    ?>

在index.html

<?php 

include_once 'dropdown.php'; 

?> 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
<select name="Tables" id="ddTables">
<?php 

echo $tables;

?>
     </select>
     <input type="submit" id="tableSubmit" value="Submit"/>
     </form>

Answer 2

不要尝试运行“显示表”，而是尝试

SELECT *
FROM information_schema.tables
WHERE table_schema = 'onlineshop';

您将希望在WHERE子句中使用更多子句来限制所显示的实际表。

Answer 3

如果您在PHP5.5.0之前，可以使用mysql_list_tables函数(Docs http://us3.php.net/manual/en/function.mysql-list-tables.php)

如果使用PHP5.5.0或更高版本，我不确定mysqli对象是否有类似的方法，但也可以将查询从"SHOW TABLES"修改为"SHOW TABLES FROM $db_name"