MySQL link to return results

Question

I have an image that is displayed from a table and I want the user to be able to click the image which directs them to a page that returns the rest of the data for that row. Do I need a php loop for this? I can't quite figure it out. This returns the Last Name, First Name, and an Image:

    <?php
    if (isset($_GET['LastName'])) {
    $ln = $_GET['LastName'];
    }
        include 'connection.php';
        $query = "SELECT * FROM residents WHERE LastName like '$ln%' ";
        $result = mysql_query($query);


    while($person = mysql_fetch_array($result)) { ?>
    <div class="media col-sm-4">
         <a class="pull-left" href="redirectionpage.php?<?php echo $person['ID'];?>.php">
    <img class="media-object" src="upload/<?php echo $person['Picture'];?>"   width="100" height="100"/>
    </a>
    <div class="media-body">
     <h4 class="media-heading"><?php echo $person['LastName'] . ", " . $person['FirstName']; ?></h4>

   </div>
    </div>
    <?php }>?

Is the best way to accomplish this by redirecting the user to a new page and using a mysql statement to display the new data?

This is the code for the other page:

    <?php
    //Gets data from the database
        include ('header.php');
        include ('footer.php');
        include ('connection.php');



        $query = "SELECT * FROM residents WHERE ID = LastName LIMIT 1";
        $result = mysql_query($query);


       while($row = mysql_fetch_array($result)){
       $outputpicture.='<div><p><img src="upload/' . $row['Picture'].'" width="450" height="550"/></p></div>';
       $outputname.= $row['LastName'] . ", " . $row['FirstName']. '<br />';
       $outputspouse.= $row['Spouse']. '<br />';
       $outputrelatives.= $row['Relatives']. '<br />';
       $outputaddress.= $row['Address']. '<br />';
       $outputbirthday.= $row['Birthday']. '<br />';
       $outputbegan.= $row['BeganResidence']. '<br />';
       $outputended.= $row['EndedResidence']. '<br />';
       $outputformer.= $row['FormerResidence']. '<br />';
       $outputcareer.= $row['Career']. '<br />';
       $outputeducation.= $row['Education']. '<br />';
       $outputmaritalstatus.= $row['MaritalStatus']. '<br />';
       $outputsiblings.= $row['Siblings']. '<br />';
       $outputspecialinterests.= $row['SpecialInterests'].'<br />';
       }


      ?>

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). You do not need a loop, you just need to query and return the data. — Jay Blanchard, Apr 29 '15 at 15:34
I second that, preparing statements are the way to go. At very least check out `mysqli` instead :-) — Kevin Nagurski, Apr 29 '15 at 15:39
@KevinNagurski `mysqli` ;-) *bit of a typo* - *"At very least check out `mysli` instead"* — Funk Forty Niner, Apr 29 '15 at 15:40
This `WHERE ID = LastName` may throw an error when doing `$result = mysql_query($query) or die(mysql_error());` *won't it Sam?* - @JayBlanchard - probably why it's not working. Unless that ID column is supposed to match the LastName column. So, I could be wrong here. Unlike a fine cup of Espresso or cappuccino, I'm not "perfect". ;-) — Funk Forty Niner, Apr 29 '15 at 15:44
Thanks! I'm definitely new to this so any suggestions certainly help. As for the query statement, I'm having trouble figuring out exactly what I need to return the data. Each image is identified by their ID. I'm having trouble making a statement that will return data for each ID. Would any of you be able to steer me in the right direction? — IGrowBeards, Apr 29 '15 at 15:48
*Yes Ralph* that could be one problem because that would make the query invalid @Fred-ii- — Jay Blanchard, Apr 29 '15 at 15:52
It's hard to say what you want to do in your query `WHERE ID = LastName LIMIT 1";` can you elaborate on that bit of code? — Funk Forty Niner, Apr 29 '15 at 15:54
At the moment when I click an image the user is taken to the redirectionpage.php?ID so the URL reads as redirectionpage.php?1 or redirectionpage.php?2 depending on the ID. I'm trying to come up with a statement so that when a certain ID appears the page will return the corresponding data. Does that make it more clear? — IGrowBeards, Apr 29 '15 at 16:09
I can return values for MIN or MAX ID. Even ASC or DESC. But not the specific value for the ID. — IGrowBeards, Apr 29 '15 at 16:13

Don't Panic · Accepted Answer · 2015-04-29T19:46:03.200

0

The link needs to be changed to

<a class="pull-left" href="redirectionpage.php?id=<?php echo $person['ID'];?>">

Adding this id= will allow you to access the id parameter in redirectionpage.php via the $_GET superglobal.

$id = $_GET['id'];

You can use the $id variable to select the specified row from your database. You will see a lot of examples that do it like this:

$query = "SELECT * FROM residents WHERE ID = $id";

This works but you should not do it, because it creates an SQL injection vulnerability. Read about prepared statements and create your SQL so that you can pass the ID as a paramater.

$stmt = $pdo->prepare("SELECT * FROM residents WHERE ID = :id");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();

This should also be applied to the code you use to select the list of images.

Assuming ID is the key for your residents table, this should select only one record, so you will not need the while loop. You can just use one fetch.

edited Apr 29 '15 at 19:46

answered Apr 29 '15 at 16:38

Don't Panic

41,125
10
61
80

I made the changes to the link and to the queries on the redirectionpage.php. Here is what I have so far: – IGrowBeards Apr 29 '15 at 18:29
sorry about that...I made changes to the link as suggested and changed the query to $query = SELECT * FROM residents WHERE ID = $id; just to see if it would function. I'm still getting no results displayed. – IGrowBeards Apr 29 '15 at 19:00
I didn't notice this at first, but I don't see anything in redirectionpage.php that would actually print anything. It looks like you're just assigning all the `$row` values to variables. You need to `echo` them instead. – Don't Panic Apr 29 '15 at 19:04
I have $row echoed out below my php code: Name: Spouse(s): That's just a brief portion of it. It's too long for me to paste in as a comment. – IGrowBeards Apr 29 '15 at 19:10
Oh dear. I didn't notice the second .php either. – Don't Panic Apr 29 '15 at 19:45

MySQL link to return results

1 Answers1