SQLi and retreiving a specific record

Question

looked around, saw a lot of MySQL answers but not MySQLi.. Im attempting to return 1 line of my choosing. at the moment I can return only the first line.

What im trying to get to is, have my main database be linked by ID, when you click the ID, a closer look at the record is on another page..

<?php

$connect = mysqli_connect("localhost", "root", "", "mydb");
$query = "SELECT name, surname FROM info ORDER BY id";
$record = mysqli_query($connect, $query);
@$num_results = mysqli_num_rows($record);

$row = mysqli_fetch_assoc($record);

$fname = $row['name'];
$surname  = $row['surname'];

print $fname;
print $surname;



?>

Answer 1

In order to do what you're asking, first create a list of users:

$connect = mysqli_connect("localhost", "root", "", "mydb");
$query = "SELECT name, surname FROM info ORDER BY id";
$record = mysqli_query($query, $connect);

while($row = mysqli_fetch_assoc($record)){
    $user = $row['name'] . ' ' . $row['surname'];
    echo '<a href="user.php?uid=' . $row['id'] . '">' .$user . '</a></br>';
}

The will create a list of all your users which look like:

<a href="user.php?uid=1">Bart Simpson</a></br>
<a href="user.php?uid=2">Matt Damon</a></br>

And so on.

When you click the user's link in the original page, it should be processed by the code in user.php:

$connect = mysqli_connect("localhost", "root", "", "mydb");
$query = "SELECT name, surname FROM info WHERE id = ?"; // returns one line identified by id - you can use something else if you're guarateed the value is unique in your table
$stmt = mysqli_prepare($connect, $query);
mysqli_stmt_bind_param($stmt, 'i', $_GET['uid']);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name, $surname);
mysqli_stmt_fetch($stmt);

I'll bet you can guess what happens now, can't you? That's right, you can echo out the data for the individual user on this page:

$user = $name . ' ' . $surname;
echo $user;

NOTES:

1. The connection code could be placed in a separate file and included in pages where needed.
2. You could write a function to handle every query you write.
3. In order to prevent the possibility of SQL Injection I have used prepared statements for MySQLi. Even escaping the string is not safe!
4. Generally I would be a lot more consistent with my coding, performing queries the same way each and every time. doing so will reduce troubleshooting time as well as making your code easier for others to read.