querying a mysql DB and displaying what is returned

Question

Apologies and excuse me if my language is not correct, and if this is a novice question.

This is my php code that create a basic dropdown(with the names in the table Sandy,Tom,Tina) which is fed from a php query.

<?php
$servername = "localhost";
$username = "username";
$password = "12345";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "select * from  potluck";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    echo "<select name='list2' id='list2'>";
    while($row = $result->fetch_assoc()) {
        echo "<option>".$row["name"]."</option>";
    }
    echo "</select>";
} else {
    echo "0 results";
}
$conn->close();
?>

How do I echo to the screen just below the drop down what item was selected?
Or more specifically.
How do I post back to the php db this select * from potluck where name="Sandy"; and then display just below the dropdown what was returned(just a basic string will suffice at this stage)? I am not sure of the ordering of this part and would appreciate any help.

mysql> select * from potluck;
+----+-------+----------------+-----------+-------------+
| id | name  | food           | confirmed | signup_date |
+----+-------+----------------+-----------+-------------+
|  1 | Sandy | Key Lime Tarts | N         | 2012-04-14  |
|  2 | Tom   | BBQ            | Y         | 2012-04-18  |
|  3 | Tina  | Salad          | Y         | 2012-04-10  |
+----+-------+----------------+-----------+-------------+
3 rows in set (0.00 sec)



mysql> select * from potluck where name="Sandy";
+----+-------+----------------+-----------+-------------+
| id | name  | food           | confirmed | signup_date |
+----+-------+----------------+-----------+-------------+
|  1 | Sandy | Key Lime Tarts | N         | 2012-04-14  |
+----+-------+----------------+-----------+-------------+
1 row in set (0.00 sec)

Ref here - might help with my understanding

A simple example of Get working:

<form id="s" method="get">
<select name="size">
    <option value="small">Small</option>
    <option value="medium">Medium</option>
    <option value="large">Large</option>
</select>
<input type="submit" name="Submit" value="Submit">
</form>

<?php
if( $_GET["size"])
{
echo "Welcome: ". $_GET['size']. "<br />";
}
?>

flint · Answer 1 · 2018-10-30T21:11:20.080

-1

You need a form, a submit button and some PHP code to handle the form submission.

<?php
$servername = "localhost";
$username = "username";
$password = "12345";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "select * from  potluck";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    echo '<form action="http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'].'" method="POST">';
    echo "<select name='list2' id='list2'>";
    while($row = $result->fetch_assoc()) {
        echo '<option value="'.$row['name'].'">'.$row["name"]."</option>";
    }
    echo "</select>";
    echo '<input type="submit" value="Submit"/></form>';
} else {
    echo "0 results";
}

if (isset($_POST['name'])) {
   $result = $conn->query("SELECT * FROM potluck WHERE name='".$_POST['name']."' LIMIT 1");
   var_dump($result->fetch_assoc());
}
$conn->close();
?>

edited Oct 30 '18 at 21:11

answered Oct 30 '18 at 04:27

flint

345
5
15

See about sql injection and the importance of properly prepared and bound queries – Strawberry Oct 30 '18 at 06:18
Yeah, sql injection is a problem here. I think this guy is trying to learn some basics. I'm sure that the first time you figured out how to submit a form you weren't worried about sql injection, I sure wasn't. – flint Oct 30 '18 at 18:32
tks, when I press submit does not work - reloads a page that cannot be found. – HattrickNZ Oct 30 '18 at 20:40
will look this up `sql injection and the importance of properly prepared and bound queries` but if you have a good ref of this in laymans terms it would be appreciated. – HattrickNZ Oct 30 '18 at 20:41
You'll want to check the output of $_SERVER['HTTP_HOST'] and be sure that it is the url where your code resides. See https://stackoverflow.com/questions/6768793/get-the-full-url-in-php. I've updated my answer with a better way to get that url. – flint Oct 30 '18 at 21:10

querying a mysql DB and displaying what is returned

1 Answers1