How to isolate a single result from a table in a database?

Question

I am trying to fetch data from one database table to another. I have been able to this with SQL's JOIN feature but I cannot figure out how to isolate a single result from the database and display it to the corresponding user's profile page. As of now, all the entries stored in the database are shown and not the corresponding one. How would I go about solving this?

Every single possible combination of inner and outer, left and right joins with both databases to try and only display the one entry for the user.

<?
$result=mysql_query("SELECT nea_profile.fullname, nea_profile.dob,nea_profile.bio 
                    FROM nea_profile 
                        JOIN nea_users ON nea_profile.userid = nea_users.userid 
                    WHERE nea_users.fullname = nea_profile.fullname");
?>

<div class="column">
<h2>About</h2>
    <div align="center">
        <table border="8">
             <?
                while ($row=mysql_fetch_array($result)) { 
                    echo "<tr><td><b>Full Name</b> - $row[fullname] 
                    echo "<td><b>Born</b> - $row[dob]</td></tr>";
                    echo "<tr><td>$row[bio]</td><td></td></tr>";
                }
            ?>
        </table>
    </div>

As previously mentioned, every entry is displayed and not the one that corresponds to the user.

Use a WHERE clause on the query like `WHERE WHERE nea_users.fullname = 'Ricardo Castillo'` — RiggsFolly, Apr 05 '19 at 15:29
Table definition and content samples would make this more easily answerable. (See also [mcve].) — mario, Apr 05 '19 at 15:29
In regard to @RiggsFolly, That does work but seeing as there are multiple entries in the table, it would have to change to every user on the site so it cannot be hard coded. — Ricardo Castillo, Apr 05 '19 at 15:36
If your question refers more on how to use variables safely within your query string from PHP. Using the deprecated PHP Mysql driver (as you are doing) check out this: https://www.php.net/manual/en/function.mysql-escape-string.php Otherwise, I recommend looking for a tutorial that makes use of a generic database driver (PDO, https://www.php.net/manual/en/class.pdo.php) or something specific to mysql (https://www.php.net/manual/en/class.mysqli.php) and then looking into bindings — Matt, Apr 05 '19 at 15:39
THe HARD CODED bit was only to demonstrate the idea, it was **NOT** intended to be a complete solution. You would use a variable in there. _There are 1000's of example on SO and Millions on the internet._ ___Or, if all else fails, you could always look in the PHP Manual there are examples in there___ — RiggsFolly, Apr 05 '19 at 17:05
Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7.0+. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Apr 05 '19 at 17:07

Matt · Answer 1 · 2019-04-05T15:43:30.773

Your where clause performs the same function as a join and is redundant (unless nea.users and nea.profile contain the same IDs with different "fullname" values). Currently you are asking your database to give you ALL results where nea_users table column user_id is has a matching user_id column value in the nea_profile table and then where the fullname of the entry in nea_users is equalled to the fullname of nea_profile. No other clause is being specified here.

Your where clause needs to specify the exact value of what the user data you need, be it by full name or by ID. If by full name then you'd look at changing your query to appear like so:

SELECT nea_profile.fullname, nea_profile.dob,nea_profile.bio 
                FROM nea_profile 
                    JOIN nea_users ON nea_profile.userid = nea_users.userid 
                WHERE nea_users.fullname = 'John Doe'

Moreover, I'd recommend taking a look at making use of "INNER JOIN" as this will only return results if it is able to find a result where the JOIN relationships are successful.

How to isolate a single result from a table in a database?

1 Answers1