how to run select query in mysqli

Question

I need to show specific record, record exist in db but it showing nothing. Here my code is.

 $con=mysqli_connect("localhost","root","","test") or die("Connecting to MySQL failed"); 


$name=$_POST['uname'];

$query = "SELECT * FROM officedb WHERE name='.$name.'";
$data=mysqli_query($con,$query);   

while($row=mysqli_fetch_array($data)){
    echo $row['name'];
    echo $row['lname'];
    echo $row['department'];
}

`echo $query = "SELECT * FROM officedb WHERE name='.$name.'";` and than run query in php myadmin — devpro, Feb 26 '16 at 08:05
**user3113899** - use correct way to working with mysql. Your code is vulnerable to SQL injection attacks. Read about prepared [statements](http://php.net/manual/en/mysqli.prepare.php) and use them. — Dmytro, Feb 26 '16 at 08:22

score 3 · Answer 1 · answered Sep 14 '17 at 02:04

The . is the concatenation operator. It is used to put multiple strings or variables together. Inside double quotes, PHP parses variables, so this is not necessary. Additionally, inside either single or double quotes, "." will be treated as a literal period, not the concatenation operator.

But that's not why I'm adding this answer... Putting a post straight into a query is asking for all sorts of bad behavior, which would crash your web application, cause you to lose data, have data compromised, or worse. Research SQL injection and Little Bobby Tables. You're already using MySQLi, which is good, but bring it home...

So, what should be done? Use prepared statements.

$name=$_POST['uname']; /* NEVER TRUST user input. This value 
could be very dangerous!!! */

 $con=mysqli_connect("localhost","root","","test") or die("Connecting to MySQL failed"); 

$stmt = $con->prepare("SELECT * FROM `officedb` WHERE name = ?");
$stmt->bind_param('s', $name);
$stmt->execute();

$data = $stmt->get_result();

while($row=mysqli_fetch_array($data)){
// I assume these came from user input too. Do not trust when printing.
    echo htmlspecialchars($row['name']);
    echo htmlspecialchars($row['lname']);
    echo htmlspecialchars($row['department']);
}

See the bind_param PHP Manual page for more information. Additionally, you should not trust user input when printing back to html. Use htmlspecialchars to help with that (when printing to html, not when adding to DB).

Please consider making this the accepted answer so newcomers do not stumble upon dangerous code.

score -1 · Accepted Answer · answered Feb 26 '16 at 08:06

-1

You are concat the $name variable wrong, just change:

$query = "SELECT * FROM officedb WHERE name='.$name.'";

with this:

$query = "SELECT * FROM officedb WHERE name = '$name'";

Your query looks like:

SELECT * FROM officedb WHERE name = '.test.'

After change as i suggest query should be:

SELECT * FROM officedb WHERE name = 'test'

answered Feb 26 '16 at 08:06

devpro

16,184
3
27
38

You dont even have to do: ` ... name = '$name'";` its enough with `name = " . $test;` – isnisn Feb 26 '16 at 08:20
@isnisn: my friend, name is string you need quotes here :) – devpro Feb 26 '16 at 08:21
@isnisn: if $name is int than no need to use quote, is there any confusion? – devpro Feb 26 '16 at 08:30
1

May depend upon your setup and sql_mode - but i honestly dont need `' '` when doing concats in my PHP 5.4+ with mysql 5.6.24 with PDO :). Sure if i write the query `SELECT ...... WHERE name = 'string''` it's a must. But not if i concat it – isnisn Feb 26 '16 at 09:32
Using this solution in the OP's scenario takes direct form input and throws it into the query string with no checks whatsoever. It's horribly insecure and should not be the accepted answer. – Martin Greenaway Sep 13 '17 at 08:46
I added an answer that I believe explains the question more thoroughly while, importantly, demonstrating code that is more secure. – Justin Apr 12 '18 at 01:06

how to run select query in mysqli

2 Answers2