How to display different mysql table using dropdown list in php

Question

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>";
}

Try just "SELECT * FROM $tbname" you don't quote table names. — miken32
– miken32, Commented Apr 4, 2017 at 5:30
Also you better check your input matches a known table name before you try sending it to your database. — miken32
– miken32, Commented Apr 4, 2017 at 5:32
why are you using get and not post if it is going from a local html file to a local php file? — Gert
– Gert, Commented Apr 4, 2017 at 5:38
I have checked the inputs. there is no response in the page. not even showing any errors. — Dhinesh Kumar
– Dhinesh Kumar, Commented Apr 4, 2017 at 5:38
hi Gert. I have tried post method and also get method. no responses in both . — Dhinesh Kumar
– Dhinesh Kumar, Commented Apr 4, 2017 at 5:40

Jonathan · Accepted Answer · 2017-04-04 05:47:59Z

3

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`");

edited Apr 4, 2017 at 5:47

Jonathan

2,87515 silver badges23 bronze badges

answered Apr 4, 2017 at 5:35

Swyam Joshi

312 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dhinesh Kumar Over a year ago

Thank you Joshi. But still i get no response or no data loaded from database.

Swyam Joshi Over a year ago

Do you get any sql error when you run sql query ? please check it and also make sure database connection settings proper and those table has data in them

Jonathan · Accepted Answer · 2017-04-04 05:51:39Z

1

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>";
}

answered Apr 4, 2017 at 5:51

Jonathan

2,87515 silver badges23 bronze badges

4 Comments

Dhinesh Kumar Over a year ago

Thank you Augwa, I got the following error Parse error: syntax error, unexpected 'catch' (T_CATCH) in C:\wamp64\www\sample\table1.php on line 74

Jonathan Over a year ago

you left out the trailing } brace that was already there. after the closing brace of while () { ... } you need to have another one.

Jonathan Over a year ago

Proper formatting of your code will help prevent problems like that. I edited your question to properly format your php code.

Dhinesh Kumar Over a year ago

Thank you Augwa, now i get the data . thank you for the help.

Vara Prasad · Accepted Answer · 2017-04-04 06:38:09Z

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>

Collectives™ on Stack Overflow

How to display different mysql table using dropdown list in php

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related