CODEWITHSUNDEEP
phphtmlmysqlpdo
Dhinesh Kumar
Dhinesh Kumar

Reputation: 25

How to display different mysql table using dropdown list in php

I have different tables like table 1 to 5 in mysql database. I want a code for displaying each table when i select it from drop down table list. when i try to execute the below i got no response and no data loaded form the sql. 

<div> 
<form action="table1.php" method="GET">
<input list="name" name="name">
<datalist id="name">
    <option value="table 1">
    <option value="table 2">
    <option value="table 3">
    <option value="table 4">
    <option value="table 5">
</datalist>
<input type="submit" name="search" value="search">
</form>
</div>

<div>
<table width="600" border="0" cellpadding="1" cellspacing="1">
    <tr>
        <th>Column 1</th>
        <th>Column 2</th>
        <th>Column 3</th>
        <th>Column 4</th>
    </tr>
</table>

in the php code i assign a variable for the selection value and given in the program. but i got a error that the variable is undefined or not be used in the $conn statement. 

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sheet";
$tbname= $_GET['name'];

if (isset($_GET['search'])) {
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare("SELECT * FROM '$tbname'");
        $stmt->execute();

        $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

        echo "<tr>";
        echo "<th>".$result['Column 1']."</th>";
        echo "<th>".$result['Column 2']."</th>";
        echo "<th>".$result['Column 3']."</th>";
        echo "<th>".$result['Column 4']."</th>";
        echo "</tr>";
    }
    catch(PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    $conn = null;
    echo "</table>";
}

Upvotes: 2

Views: 1837

Answers (3)

Vara Prasad
Vara Prasad

Reputation: 497

Hope this code will helps you,

I have slightly changed your code structure and added fetchAll() method to retrieve the rows from db, you can use fetch() method also to fetch row by row And finally I'm printing those rows at the end.

<?php 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sheet";
$tbname= $_GET['name'];
$results = array();

if (isset($_GET['search'])) {
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare("SELECT * FROM `$tbname`");
        $stmt->execute();

        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $results = $stmt->fetchAll();//Add this line to get db results

    } catch(PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    $conn = null;
}
?>

<div> 
    <form action="table1.php" method="GET">
        <input list="name" name="name">
        <datalist id="name">
            <option value="table 1">
            <option value="table 2">
            <option value="table 3">
        </datalist>
        <input type="submit" name="search" value="search">
    </form>
</div>

<div>
    <table width="600" border="0" cellpadding="1" cellspacing="1">
        <tr>
            <th>Column 1</th>
            <th>Column 2</th>
            <th>Column 3</th>
            <th>Column 4</th>
        </tr>
        <?php
        if (is_array($results) && count($results)>0) {
            foreach ($results as $result) {
                echo "<tr>";
                echo "<td>".$result['Column 1']."</td>";
                echo "<td>".$result['Column 2']."</td>";
                echo "<td>".$result['Column 3']."</td>";
                echo "<td>".$result['Column 4']."</td>";
                echo "</tr>";
            }
        }
        ?>
    </table>
</div>

Upvotes: 0

Jonathan
Jonathan

Reputation: 2877

All you've done here is set the fetch mode, you didn't actually fetch anything.

change:

$stmt->setFetchMode(PDO::FETCH_ASSOC);

echo "<tr>";
echo "<th>".$result['Column 1']."</th>";
echo "<th>".$result['Column 2']."</th>";
echo "<th>".$result['Column 3']."</th>";
echo "<th>".$result['Column 4']."</th>";
echo "</tr>";

to:

while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
     echo "<tr>";
     echo "<th>".$result['Column 1']."</th>";
     echo "<th>".$result['Column 2']."</th>";
     echo "<th>".$result['Column 3']."</th>";
     echo "<th>".$result['Column 4']."</th>";
     echo "</tr>";
}

Upvotes: 1

Swyam Joshi
Swyam Joshi

Reputation: 31

You have placed table name in single quotation which is incorrect, you need to use backticks instead.

The following line in your PHP code is wrong

$stmt = $conn->prepare("SELECT * FROM '$tbname'");

It should be changed to this

$stmt = $conn->prepare("SELECT * FROM `$tbname`");

Upvotes: 3

Related Questions