PHP/MySQL: Get name for specific ID

Question

I am a beginner to MySQL and Im trying to retrieve a name for a specific ID.

Here is how my database looks like:

I have a table of contacts which contains ID, first and last name.

I want to retrieve the first and last name for a specific ID in the same form.

So I want to end up with a form of options and the option value will be the first name for each ID.

For example:

<form>
<select name="users">
<option value="">Select a person:</option>
<option value="5"> <?php echo "$first $last" ?> </option>
<option value="10"> <?php echo "$first $last" ?> </option>
<option value="15"> <?php echo "$first $last" ?> </option>
</select>
</form>

The values are the ID's.

and my PHP:

<?php   include("connection.php");

$query="SELECT * FROM contacts";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");

$i++;
}

?>

in my actual example I get the first and last name of the last's person in the database only.

So how can I retrieve the first and last name for specific ID's of which I assign in my form (?)

adidasadida · Accepted Answer · 2012-03-03 01:23:57Z

3

This is because your while loop is overwriting $first and $last every single time. Try

$users = array();

while ($row = mysql_fetch_assoc($result)) { 
  $users[] = $row;
}

And in HTML (EDITED):

<form>
  <select name="users">
    <option value="">Select a person:</option>
    <?php foreach($users as $row): ?>
      <option value="<?php echo $row['id']; ?>"> <?php echo $row['first'] . " " .  $row['last']; ?> </option>
    <?php endforeach; ?>
  </select>
</form>

edited Mar 3, 2012 at 1:23

answered Mar 3, 2012 at 1:03

adidasadida

1202 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Johny Bryne Over a year ago

This doesn't compile: <?php echo "$row['first'] $row['last']" ?>. I tried this: echo $row['first']; echo $row['last'] and it works but there is not space between first name and last name

adidasadida Over a year ago

Sorry I didn't check syntax. I edited the HTML portion so that it adds a space between first and last name.

Johny Bryne Over a year ago

Thank you. This works as printing all the ID's. What If I want to print first and last name for ID 13 and 15 only? That was my question.

adidasadida Over a year ago

If you know which ID you want at the time of querying, then you can change your sql query to "SELECT * FROM contacts where id in (13, 15)" If not, then you can add a if statement in the HTML portion. <?php if($row['id'] == 13 || $row['id'] == 15): ?>/* ... */<?php endif; ?> Sorry for the code in the comments.

Marc B · Accepted Answer · 2012-03-03 01:00:23Z

1

Assuming your contacts table has a primary key field, you'd use THAT field to put into your <option> tags as the value. Then you've got a 1:1 correspondence between a particular <option> and a record in the database. If you go by name alone, you'll end up (say) 5 "John Smith" entries and it would be impossible to tell which one you want.

So, make your query be:

SELECT id, firstname, lastname
FROM ...

and then:

    while ($row = mysql_fetch_assoc($result)) {
       echo <<<EOL
<option value="$row[id]">$row[firstname] $row[lastname]</option>

EOL;
    }

answered Mar 3, 2012 at 1:00

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Johny Bryne Over a year ago

According to your paragraph, you are right and that is my question. I want to have my primary key of which is the ID, in the option's value and then print the name for that specific ID. But your answer doesn't seem to compile in PHP. Note that I am a beginner.

user684934 · Accepted Answer · 2012-03-03 01:00:52Z

0

$users = $_POST['users'];
$query = 
"SELECT 
    ID, FIRSTNAME, LASTNAME 
FROM 
    CONTACTS 
WHERE 
    ID = '$users'";

Of course, while this works, it is a classic example of a SQL injection vulnerability. You'll want to either sanitize the input or use a prepared statement and binding variables.

answered Mar 3, 2012 at 1:00

user684934

Comments

theking963 · Accepted Answer · 2012-03-03 01:02:46Z

0

You can do

$query="SELECT * FROM contacts WHERE id = '$id'";

A little googling will go a long way. Read this and this.

answered Mar 3, 2012 at 1:02

theking963

2,2267 gold badges29 silver badges42 bronze badges

1 Comment

Johny Bryne Over a year ago

I am aware of that but this gives you only the first and last name for 1 id.

Collectives™ on Stack Overflow

PHP/MySQL: Get name for specific ID

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related