Passing select value from SQL database with php

Question

I am attempting to use a select form that has fields populated from an SQL database. There is a table of stores in the database and I used this method to get those distinct fields into the select tool.

echo '<select name="StoreName" />'."\n";
while ($row = mysqli_fetch_assoc($distinctResult)){

echo '<option value="'.$row["StoreID"].'">';
echo $row["StoreName"];
echo '</option>'."\n";

};

echo '</select>'."\n";

The correct store names show up on the select tool but when I submit the form data, the value of the form doesn't get passed properly. Here in the initial page:

<?php


require_once 'login.php';

$connection = mysqli_connect(
   $db_hostname, $db_username,
   $db_password, $db_database);
if(mysqli_connect_error()){
die("Database Connection Failed: " .
    mysqli_connect_error() .
    " (" . mysqli_connect_errno() . ")"
    );
};

$query = "SELECT * from PURCHASE";
//echo $query;
$result = mysqli_query($connection,$query);
if(!$result) {
die("Database Query Failed!");
};

$distinct = "SELECT DISTINCT StoreName FROM PURCHASE";


$distinctResult = mysqli_query($connection,$distinct);
if(!$result) {
die("Database Query Failed!");
};

echo '<title>Output 1</title>';
echo '</head>';
echo '<body>';
echo '<h1>Required Output 1</h1>';
echo '<h2>Transaction Search</h2>';
echo '<form action="output1out.php" method="get">';
echo 'Search Store:<br/>';
echo '<br/>';


echo '<select name="StoreName" />'."\n";
while ($row = mysqli_fetch_assoc($distinctResult)){

echo '<option value="'.$row["StoreID"].'">';
echo $row["StoreName"];
echo '</option>'."\n";

};

echo '</select>'."\n";

echo '<input name="Add Merchant" type="submit" value="Search">';
echo '</form>';

mysqli_free_result($result);

mysqli_close($connection);


?>

And this is the output page:

<?php

$transaction = $_REQUEST["StoreName"];

    require_once 'login.php';

    $connection = mysqli_connect(
           $db_hostname, $db_username,
           $db_password, $db_database);



       $sql = "SELECT * FROM PURCHASE WHERE StoreName LIKE '%".$transaction."%'";
        $result = $connection->query($sql);

    ?>

    Purchases Made From <?php echo $transaction ?>
<table border="2" style="width:100%">
    <tr>
                  <th width="15%">Item Name</th>
                  <th width="15%">Item Price</th> 
                  <th width="15%">Purchase Time</th>
                  <th width="15%">Purchase Date</th>
                  <th width="15%">Category</th>
                  <th width="15%">Rating</th>
                </tr>
</table>
     <?php
        if($result->num_rows > 0){
            // output data of each row
            while($rows = $result->fetch_assoc()){ ?>
               <table border="2" style="width:100%">
                <tr>
                    <td width="15%"><?php echo $rows['ItemName']; ?></td>
                    <td width="15%"><?php echo $rows['ItemPrice']; ?></td>
                    <td width="15%"><?php echo $rows['PurchaseTime']; ?></td>
                    <td width="15%"><?php echo $rows['PurchaseDate']; ?></td>
                    <td width="15%"><?php echo $rows['Category']; ?></td>
                    <td width="15%"><?php echo $rows['Rating']; ?></td>
                </tr>
                <?php
            }
        }
        ?>

I am calling the select name StoreName in the output page but it is not passing the value from the drop down. I need to run a query with that term on the second page so I can pull the relevant data for it. If anyone can see what I'm doing wrong, please advise. Thank you.

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 05 '16 at 18:32
far as I can tell, you may be closing your connection too soon with `mysqli_close($connection);` — Funk Forty Niner, May 05 '16 at 18:36
"the value of the form doesn't get passed properly", what value are you getting? — Jose Manuel Abarca Rodríguez, May 05 '16 at 18:37
but then again, you're doing `SELECT DISTINCT StoreName FROM PURCHASE` yet that `$row["StoreID"]` isn't in your select set which is probably the root of the problem here. — Funk Forty Niner, May 05 '16 at 18:38
@JoseManuelAbarcaRodríguez when I call it on the output page, it displays as blank so I guess its passing nil. — punygod, May 05 '16 at 18:39
@Fred-ii- Ah thank you. That was my problem. Sorry still learning. — punygod, May 05 '16 at 18:42
you're welcome and posted it as the solution below ;-) @andrewxt that one had me stumped for a few minutes *lol* — Funk Forty Niner, May 05 '16 at 18:44

score 2 · Accepted Answer · answered May 05 '16 at 18:43

2

Comment to answer:

You're doing SELECT DISTINCT StoreName FROM PURCHASE yet that $row["StoreID"] isn't in your select set.

Add it to your SELECT.

answered May 05 '16 at 18:43

Funk Forty Niner

74,450
15
68
141

Passing select value from SQL database with php

1 Answers1