Selecting a MySQL row using a $_POST variable

Question

I am having some issues figuring out how to print the actual values from a row that contains the desired column value. I have two php files, quantity.php and quantity-inquiry.php, where quantity.php has a post submit form that redirects through quantity-inquiry.php to perform the actual sql.

quantity-inquiry opening php:

//--------------------------------------------------------------------------
// 1) Connect to mysql database
//--------------------------------------------------------------------------
include 'db-connect-99k.php';
$db = new mysqli($host, $user, $pass, $databaseName);

if($db->connect_errno > 0){
  die('Unable to connect to database [' . $db->connect_error . ']');
}

//--------------------------------------------------------------------------
// 2) Query database for data
//--------------------------------------------------------------------------
$searchName = $_POST['name'];
$res = $db->query("SELECT * FROM inventory WHERE name=$searchName");

$searchName is set appropriately from the $_POST (I can echo it out and it prints the correct input from the quantity form field):

<p> Product Name or PPI required (both not required) </p>
<p>--------------------------------------------------</p>
<form action="quantity_inquiry.php" method="post">
    Product Name: <input type="text" name="name"><br />
    PPI: <input type="text" name="ppi"><br />
<input type="submit" name="submit" id="submit">
</form>

In other words if I type "car" in the form field of quantity.php, $name in quantity-inquiry.php will echo out "car" as the $name variable. The problem I am having is that using $name as my query:

$searchName = $_POST['name'];
$sql = "SELECT * FROM inventory WHERE name='".$searchName."'";
$res = $db->query($sql);

I can never seem to get the actual values. I have tried tons of stuff, for example:

while($row = $res->fetch_assoc()){
    $rowName = $row['name'];
    $rowPPI = $row['ppi'];
    $rowQuantity = $row['quantity'];
    $rowPrice = $row['price'];
}

To set variables followed by

<tr>
  <td> $rowName </td>
  <td> $rowPPI </td>
  <td> $rowQuantity </td>
  <td> $rowPrice </td}
</tr>

Doesn't actually select a the field. For example, if I KNOW that I have 'product1' in my inventory table's 'name' column, and it is set as my '$searchName' it doesn't actually select it from the table and print it out as my $rowName, $rowPPI, $rowQuantity, and $rowPrice.

I have tried a number of other things that all end up in some sort of error or incorrect value being printed out. Is my query wrong or am I setting/using my variables incorrectly?

At a minimum, WHERE name=$name should probably be WHERE name="$name" (you need a better quoting solution, but that will get you started). And then you probably also want to get the data out of your result, so something like while($obj = $res->fetch_object()){ echo $obj->name; }. — bishop
– bishop, Commented Jan 17, 2014 at 20:23
@bishop I have edited my code. I have tried it as, for example, $rowName = $row->name... as well, but I always end up getting the error on line 18. Also, I had to concat the variable name onto the end of the string in order to add it to the string like you were saying. Thank you for your input — zgc7009
– zgc7009, Commented Jan 17, 2014 at 21:26

Arian Faurtosh · Accepted Answer · 2014-01-17 21:51:01Z

1

Instead of this

$searchName = $_POST['name'];
$res = $db->query("SELECT * FROM inventory WHERE name=$searchName");

you should do this

$searchName = $_POST['name'];
$query = "SELECT * FROM inventory WHERE name = ?";
if($stmt = $mysqli->prepare($query)){
    $stmt->bind_param('s',$searchName);
    $stmt->execute();
    $result = $stmt->get_result();

    while($row = $result->fetch_assoc()){
        $rowName = $row['name'];
        $rowPPI = $row['ppi'];
        $rowQuantity = $row['quantity'];
        $rowPrice = $row['price'];
        ?>
        <tr>
            <td><?=$rowName?></td>
            <td><?=$rowPPI?></td>
            <td><?=$rowQuantity?></td>
            <td><?=$rowPrice?></td>
        </tr>

        <?php
    }

    $stmt->free_result();
    $stmt->close();
}else die("Failed to prepare!");

Or you can be super fancy and do this:

$query = "SELECT name, pip, quantity, price FROM inventory WHERE name = ?";

if($stmt = $mysqli->prepare($query)){
    $stmt->bind_param('s', $_POST['name']);
    $stmt->execute();
    $stmt->bind_result($rowName, $rowPPI, $rowQuantity, $rowPrice);


    while($stmt->fetch()){
        ?>
        <tr>
            <td><?=$rowName?></td>
            <td><?=$rowPPI?></td>
            <td><?=$rowQuantity?></td>
            <td><?=$rowPrice?></td>
        </tr>
        <?php
    }

    $stmt->free_result();
    $stmt->close();
}else die("Failed to prepare!");

edited Jan 17, 2014 at 21:51

answered Jan 17, 2014 at 21:41

Arian Faurtosh

18.6k23 gold badges81 silver badges118 bronze badges

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

2 Comments

zgc7009 Over a year ago

Better solution than my simple one. Mine works but this is obviously much more in depth.

Arian Faurtosh Over a year ago

@zgc7009 thanks! Just a note: My solution protects your database from SQL Injections... The solution you posted still leaves room for SQL Injection.

zgc7009 · Accepted Answer · 2014-01-17 21:46:56Z

0

So I have just looked at the computer too long. Simple solution to a dumb mistake. I left

$sql = "SELECT * FROM inventory WHERE name='".$searchName."'";

which is right, when I changed the following

$rowName -> $row['name'];

which was wrong. Then when trying to fix it I changed the $sql to

$sql = "SELECT * FROM inventory WHERE name=$searchName";

which was wrong, while changing my variable set to

$rowName = $row['name'];

So long story short if they were set as

$sql = "SELECT * FROM inventory WHERE name='".$searchName."'";
$rowName = $row['name'];

Note - all $row* variables should be set with = and not ->

answered Jan 17, 2014 at 21:46

zgc7009

3,3995 gold badges24 silver badges34 bronze badges

Collectives™ on Stack Overflow

Selecting a MySQL row using a $_POST variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related