Link in PHP using SQL generated name

Question

I have written the following code to generate information from an SQL database:

<?php

$search1 = "SELECT Name FROM users";

    if($mysqli->query($search1) == TRUE)
    {
        echo "You have successfully searched the request";
    }


$result = $mysqli->query("SELECT Name FROM users");

echo '<table border=1px>';
echo'<th>Name</th>';
echo $row;

while($row=$result->fetch_array(MYSQLI_ASSOC))
{
   echo'<tr>'; // printing table row

   echo '<td>'.$row['Name'].'</td>';

   echo'</tr>';
}

 echo '</table>';

 ?>

This generates a list of names in the table. There are other columns in the table such as Country, Email, Hobby and Date Signed up. All of which are VARCHAR except the last which is of type DATE. I am trying to figure out code so that when I click on one of the generated names, the rest of the information (Country, Email etc,) is shown.

If you haven't thousands rows in database, you can select all and show 'more info' using JS. — pavel, May 24 '15 at 12:37

score 1 · Accepted Answer · answered May 24 '15 at 12:39

Just doing something like:

 echo '<td><a href=\"userinfo.php?username='.$row['Name'].'\">'.$row['Name'].'</td>';

And then in userinfo.php, read the $_GET['username'] parameter to make a query similar to the one you have above, something like this:

$search1 = "SELECT * FROM users where Name=?";

And then setting the parameter $_GET['username'] to the prepared statement (if you want to avoid MySQL injections).

score 0 · Answer 2 · edited May 23 '17 at 11:51

You can use the following SQL to get only the information that will be used in your listing page, in your case that would be identifier and name columns (you have identifier column, right? if not, check again your database structure - there's something wrong).

SELECT ID, Name FROM `users`

And then you can create extra page in your application, e.g. show.php where you will pass the identifier of each record as $_GET parameter, e.g. show.php?id=5

And there you should create another query:

SELECT * FROM `users` WHERE `ID` = $_GET['id']; /* that's not secure, read below */

Once you have that data, you can list it and you're done.

If you want to create one-page application, you can hide available info with CSS and display it when user clicks on username. Read about jQuery. You can even use AJAX. It's your choice.

If you want to make everything better, you can try use PDO.

Also, be aware of these vulnerabilities:

SQL Injections

Cross-site scripting

I'm looking at doing it as a one-page application using CSS. How would you suggest writing the code for this? — kingduckman, May 24 '15 at 13:13

Link in PHP using SQL generated name

2 Answers2