Get dropdown name instead of value when editing user

[Russia]

Hey guys, I am in need of some help, I have a table where it shows the people that have signed into a patient program.

Here is how it looks:



Right now if you look in the Location column, it says 8 instead of the last option in the dropdown being Manalapan.

Here is how the database table looks for offices location:



I would like to grab the office_name data from the database while retrieving the patient information in the table,from the table patients:



It is stored in the database as pat_loc and this one is 8.

In the end, I want it to say the name of the office, not the number it is stored at, first getting it from the patient table then going to the office table for the name.

Is there such a thing that can be done?

If needed, I will show my current code to see where it needs to be added.

[Psycho]

<< Moving to MySQL Forum >>

 

You need to do a JOIN from the patient table to the location table.This is the main purpose of using a "relational" database. Go out and do some research on using JOIN else you are not really using a database effectively.

 

Also, the show and insurance columns have values of 1 and 2 - are those supposed to represent yes and no? If so, you should be using (0) false and (1) true. You can then use those values as logical Boolean values (TRUE/FALSE).

 

Plus, you shuld rename the column pat_location to be location_id to match up with the related field name in the locations table. makes it much easier to associate your foreign keys

 

I don't know what your DB query looks like right now, but this should get you going in the right direction:

SELECT p.pat_id, p.pat_fname, p.pat_lname, o.office_name, p.pat_ins, pat_show
FROM patients AS p
JOIN offices AS o
  ON p.pat_location = o.office_id

EDIT: If you change the show and insurance columns to 0/1 values and if you changes the pat_loc field to be named loc_id, you could simplify the query and you could get descriptive text ('Yes', 'No') returned for the two show and insurance fields.

SELECT p.pat_id, p.pat_fname, p.pat_lname, o.office_name,
       IF(p.pat_ins, 'Yes', 'No'), IF(p.pat_show, 'Yes', 'No')
FROM patients AS p
JOIN offices AS o USING(office_id)

Edited June 28, 2013 by Psycho

[Russia]

Here is what my query looks like
 

<?php
/* 
        VIEW.PHP
        Displays all data from 'players' table
*/

        // get results from database
        $result = mysql_query("SELECT * FROM patients") 
                or die(mysql_error());  
                
        // display data in table
        
        echo "<table width='500px' border='1' cellpadding='10'>";
        echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Location</th> <th>Insurance</th> <th>Show</th> <th>Modify</th></tr>";

        // loop through results of database query, displaying them in the table
$count = mysql_num_rows($result);
//$cursor = mysql_fetch_row($cursor); 
if ($count !== 0) {

        while($row = mysql_fetch_array( $result )) {
                
                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . $row['pat_id'] . '</td>';
                echo '<td>' . $row['pat_fname'] . '</td>';
                echo '<td>' . $row['pat_lname'] . '</td>';
                
                //WHERE I ADDED THE NAME OF THE OFFICE TO REPLACE THE NUMBER VALUE

  $getval = mysql_query("SELECT office_name FROM offices WHERE office_id = " . $row['pat_id'] . "");   
$getval_name = mysql_fetch_row($getval);

                echo '<td>' . $getval_name . '</td>';
                echo '<td>' . $row['pat_ins'] . '</td>';
                echo '<td>' . $row['pat_show'] . '</td>';
                echo '<td><a href="edit.php?pat_id=' . $row['pat_id'] . '">Edit</a>/<a href="delete.php?id=' . $row['pat_id'] . '">Delete</a></td>';
                echo "</tr>"; 
        } 
        echo "</table>";
}


else {
echo "</table><b><center>NO PATIENTS HAVE SIGNED IN YET</center></b>";
}
        
?>

[Psycho]

Did you try the first one I provided?