How to fetch data in PHP with MySQLi?

Question

I tried several times but cannot succeed in getting the right syntax—according to PHP 5.5.12 —to fetch single or multiple rows from my database.

session_start();
$con=mysqli_connect("localhost","root","","doortolearn");
if (!$con) {
    echo "Could not connect to DBMS";       
}
    $query="select * from teacher where tremail='$_POST[email]' and trpasssword='$_POST[password]'";
    $result=mysqli_query($con,$query);
    $flag=FALSE;
    while ($row=mysqli_fetch_array($result,MYSQLI_BOTH)) {
        $_SESSION['email']=$row['email'];
        $flag=TRUE;
    }

Always print your query,then you'll know what's was the prblm — ɹɐqʞɐ zoɹǝɟ, Jun 04 '14 at 03:54
guys, welcome to hackers heaven!!! **NEVER** use posted data directly to run query. — bansi, Jun 04 '14 at 03:56
`'$_POST[email]'` should be `'{$_POST['email']}'` you missed braces. but i don't recommend using it in your query — bansi, Jun 04 '14 at 03:58
When you finish the site, let me know, my password is going to be `' OR 1` — bansi, Jun 04 '14 at 04:07
See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php (which would also "fix" at least one of the issues) — user2864740, Jun 04 '14 at 04:08
i m a begginer till now... Please help me out, rather than increasing my probs — Rahul Goel, Jun 04 '14 at 04:09
check these links also http://www.unixwiz.net/techtips/sql-injection.html and http://en.wikipedia.org/wiki/SQL_injection or search google for `sql injection` you will get tons of resources at any level — bansi, Jun 04 '14 at 04:10
How do you get down from an elephant? You don't - you get down from a duck! How do you you do this with mysqli? You don't, you do it with PDO. Especially if you are just learning, learn with PDO. Also, always use parameter binding for input from the user (which includes $_GET), and use `filter_input(INPUT_GET, )`, don't access $_GET directly. And don't store the password in the database, store its `sha1()` hash. — Mawg says reinstate Monica, Jun 04 '14 at 04:19
@Mawg what does filter_input to do with thus question? What's wrong with mysqli? — Your Common Sense, Jun 04 '14 at 04:26

score 1 · Accepted Answer · edited Dec 03 '17 at 13:43

First, you have no single quotes ' around $_POST[password]:

$query = "SELECT * FROM teacher WHERE tremail='". $_POST['email'] ."' and trpasssword='" . $_POST['password'] . "'";
$result = mysqli_query($con, $query) or die(mysqli_error($con));
$flag = FALSE;
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    $_SESSION['email'] = $row['email'];
    $flag = TRUE;
}

But past that, do you even have a MySQL database connection set here? I see $con but is that really working?

Also, check if there are errors by adding or die(mysql_error($con)) to your mysqli_query($con, $query) line.

Also, you have a $_SESSION value, but do you even set session_start at the beginning of your script?

But I also recommend you use mysqli_stmt_bind_param for your values to at least escape them if you are not going to do basic validation:

$query = "SELECT * FROM teacher WHERE tremail=? and trpasssword=?";
mysqli_stmt_bind_param($query, 'ss', $_POST['email'], $_POST['password']);
$result = mysqli_query($con, $query) or die(mysqli_error($con));
$flag = FALSE;
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    $_SESSION['email'] = $row['email'];
    $flag = TRUE;
}

score 0 · Answer 2 · answered Dec 09 '20 at 22:26

To successfully fetch data from MySQL using mysqli extension in PHP you need to perform more or less three actions: connect, execute prepared statement, fetch data.

Connection:

The connection is really simple. There should always be only 3 lines of code for opening a connection. You enable error reporting, create new instance of mysqli, and set the correct charset.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'db_name');
$mysqli->set_charset('utf8mb4');

Prepared statement

This is the tricky part. You need to prepare SQL statement to be executed. Careful, never concatenate PHP variables into SQL directly. Bind the variables using placeholders. Once the statement is ready you can execute it on the server.

$stmt = $mysqli->prepare('SELECT * FROM teacher WHERE tremail=?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();

Fetch the data

If your prepared statement should return some results, you need to fetch them and do something with the records. To fetch the result use get_result(). This will give you an object that you can iterate on to fetch each row one by one.

$result = $stmt->get_result();
foreach ($result as $row) {
    echo $row['user_id'];
}

If you are only starting learning PHP, please consider learning PDO instead. It is easier to use and offers more functionality. Use mysqli only for legacy projects.

score -1 · Answer 3 · answered Feb 20 '19 at 07:33

-1

can You try this code

<?php $query=mysqli_query($connection, "SELECT * FROM user");
while($rows=mysqli_fetch_array($query)){ ?>
<tr>
<td><?php echo $rows['name']; ?></td>
<td><?php echo $rows['age']; ?></td>
<td><?php echo $rows['mobile']; ?></td>
<td><?php echo $rows['email']; ?></td>
</tr>
<?php } ?>

answered Feb 20 '19 at 07:33

VIVZON

1
1
4

please explain in detail your code if it is possible. – dpapadopoulos Feb 20 '19 at 08:13

score -2 · Answer 4 · answered Mar 01 '17 at 18:00

-2

$r =$mysqli->query("select * from users");

while ( $row =  $r->fetch_assoc() )
{
?>
  <tr>
  <td><?php echo $i++; ?></td>
  <td><?php echo $row['name']; ?></td>
  <td><?php echo $row['pwd']; ?></td>
  </tr>
  <?php
  }
  ?>

answered Mar 01 '17 at 18:00

Dewesh Sinha

1
1

How to fetch data in PHP with MySQLi?

4 Answers4

Linked

Related