Can't output relevant mysql information to clicked link using SELECT *FROM table WHERE variable LIKE '$variable'

Question

I have a mysql database named "drinks", in that database I have one table named "persons" and in "persons" I have two people, Bryan(fname) Fajardo(lname) 21(age) and Andross H Age:20.

In my index.php I have links set up from all of the people in table persons. I am trying to get my links to work so that when I click on either name, the information relevant from that person is outputted into my other page (where the link goes to) which is: insert.php.

I have been trying for hours to run some test by clicking on the Bryan link and outputting only his last name etc. etc. My objective: is to be able to link the people from "persons" table and then upon click go to insert.php and output that person's information there.

Here is my current code from Index.php.

<html>
<head>



<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>

<?php
//Connect to the database
    $con = mysqli_connect("localhost","username","password","drinks");
    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }else{
        echo '<span style = "background: red ;">MSQL Connected.</span>' ;
    }

    $result = mysqli_query($con,"SELECT * FROM persons");

    while($row = mysqli_fetch_array($result)) {
    Print '<dd><a href=insert.php?
            fname="'.$row['fname'].'">'.$row['fname'].'</a></dd>';  
    }
    mysql_close();
    ?>


</body>
    </html>

and here is my Insert .php where I want the relevant information to be printed.

<html>
<head>



<link rel="stylesheet" type="text/css" href="style.css">

</head>
<body>
<div class = "full-header">
    <div class = "mid-header span12 center">

    </div>
</div>
<div class = "main-content-container full_w">
    <div class = "span12 main-content center">
        <?php
        $cont = mysqli_connect("localhost","username","password","drinks");
        // Check connection
        if (mysqli_connect_errno()) {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }else{
            echo '<span style = "background: red ;">yay</span>' ;
        }
        $fname = $_GET['fname'];

        $sel = ($cont,"SELECT * FROM persons WHERE fname ='%$fname%'");
        while($rower = mysqli_fetch_array($sel)) {
            Print $rower['lname'];
                            //why is this not printing Bryan's last name?
        }
        ?>
    </div>
    </div>


</body>
</html>

Thank you in advance, I appreciate the help, I have just recently gotten into php and database building/summoning.

EDIT: I also have been reading that this is becoming deprecated and PDO is going to be used now, if you have a solution that involves PDO, I would appreciate that as well, but I am very new to PDO. EDIT 2: Changed "table" to "persons" in insert.php query.Still did not fix.

First write in question, if and what error-code MySQL generates. — SteAp, Jun 15 '14 at 01:58
Consider using the primary key to reference a person when you navigate between different people, it is more accurate. I believe your issue is due to using the equal operator instead of the LIKE operator. See: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html — Robert, Jun 15 '14 at 02:00
I didn't mention it but I had it on "LIKE" at first, I changed it because it didn't work. I just tried it again and It still didnt work. :/ SteAp, I'm not getting any errors, my page is just blank. — Bryan Fajardo, Jun 15 '14 at 02:03
hmmm `$sel = mysqli_query($cont,"SELECT...");` where is the function call? Showing the contents of `$sel` would help lead to this solution: `var_dump($sel)` - this should be a mysqli_result object resource. see the docs: http://www.php.net/manual/en/mysqli.query.php, also obligatory sql injection link: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — zamnuts, Jun 15 '14 at 02:04
Did you change 'table' to 'persons' as well (in your query)? That would do it :) — Robert, Jun 15 '14 at 02:10
I was just following the same code I had done on my previous index.php page, so you're telling me to change `$sel = ($cont, "SELECT...` to `$sel = mysqli_query($cont,"SELECT"..?` — Bryan Fajardo, Jun 15 '14 at 02:11
@Robert I HAD NOT done that, but I **just** did it and it still did not fix it... damn... — Bryan Fajardo, Jun 15 '14 at 02:13
Well regardless, you should use something like PDO. This is a good tutorial to get you started: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html — Robert, Jun 15 '14 at 02:19

score 2 · Answer 1 · answered Jun 15 '14 at 03:27

I can understand how it is when first starting out. Once you wrap your mind around the basic parts of it the rest will flow.

Since you asked for a better way I am going to suggest a class I personally use in all my projects.

https://github.com/joshcam/PHP-MySQLi-Database-Class

Of course don't forget to download the simple MYSQLI class from the link above and include it just like I do below in your project. Otherwise none of this will work.

Here us the first page which contains the table with all the users from your persons Db table. We list them in a table with a simple edit/view button.

PAGE 1

 <?php 
        require_once('Mysqlidb.php');

        //After that, create a new instance of the class.

    $db = new Mysqlidb('host', 'username', 'password', 'databaseName');

    //a simple select statement to get all users in the DB table persons
    $users = $db->get('persons'); //contains an Array of all users 


    ?>
    <html>
    <head>



    <link rel="stylesheet" type="text/css" href="style.css">
    </head>
    <body>

<table>

    <th>
        First Name
    </th>
    <th>
        Last Name
    </th>
    <th>&nbsp;</th>

<?php 

//loops through each user in the persons DB table
//the id in the third <td> assumes you use id as the primary field of this DB table persons
foreach ($users as $user){ ?>
    <tr>
        <td>
            <?php echo $user['fname'];?>
        </td>
        <td>
            <?php echo $user['lname'];?>
        </td>
        <td>
        <a href="insert.php?id=<?php echo $user['id']; ?>"/>Edit/View</a>   
        </td>
    </tr>

<?php } ?>

</table>
</body>
    </html>

So that ends your first page. Now you need to include this code on your second page which we are assuming is called insert.php.

PAGE 2

<!--add this to your insert page-->

 <?php 
        require_once('Mysqlidb.php');

        //After that, create a new instance of the class.

    $db = new Mysqlidb('host', 'username', 'password', 'databaseName');

    //a simple select statement to get all the user where the GET 
    //variable equals their ID in the persons table
    //(the GET is the ?id=xxxx in the url link clicked)

    $db->where ("id", $_GET['id']);
    $user = $db->getOne('persons'); //contains an Array of the user

    ?>

<html>
<head>



<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
    <table>

<th>
    First Name
</th>
<th>
    Last Name
</th>
<th>user ID</th>


<tr>
    <td>
        <?php echo $user['fname'];?>
    </td>
    <td>
        <?php echo $user['lname'];?>
    </td>
    <td>
    <?php echo $user['id']; ?>  
    </td>
</tr>

</body>
</html>

Thank you for taking the time to write all that, I will look into that class and using it further down, since it is probably a better approach. — Bryan Fajardo, Jun 16 '14 at 02:21

score 1 · Accepted Answer · edited May 23 '17 at 11:56

You have two main errors. On index.php you are wrapping your query string values in quotes

Print '<dd><a href=insert.php?
        fname="'.$row['fname'].'">'.$row['fname'].'</a></dd>';

This should really be

Print '<dd><a href="insert.php?
        fname='.$row['fname'].'">'.$row['fname'].'</a></dd>';

Next, on your second page, you need to use LIKE on your query.

$sel = ($cont,"SELECT * FROM persons WHERE fname LIKE '%$fname%'");

That said, you really should use parameters because the current method is going to open your script up to SQL Injection, and you should consider using a primary key in your querystring instead of passing the person's name.

Print '<dd><a href="insert.php?
   id='.$row['id'].'">'.$row['fname'].'</a></dd>';

And your query

$id = intval($_GET['id']);
$sel = ($cont,"SELECT * FROM persons WHERE id = $id");

One final note, on your index page, you are using mysql_close instead of mysqli_close to close your database connection.

Wow, thank you so much Robbert, the quotes were actually my problem. And thanks, someone had previously mentioned to change the '=' to LIKE instead. This fixes my current issues. I really appreciate your help in answering my question. — Bryan Fajardo, Jun 16 '14 at 02:20

Can't output relevant mysql information to clicked link using SELECT *FROM table WHERE variable LIKE '$variable'

2 Answers2

Linked