BasiM Posted October 31, 2013 Share Posted October 31, 2013 Hi Hope someone can help me. I have two tables from the database. 1 table - patient and the 2nd table - medical aidI want to extract data from both table. The query runs but it duplicates the data in the table. I am not sure what I have done wrong. Here is the code <?php echo "<table border='1' align='center' cellspacing='0' width='650px' cellspacing='0'> <tr><th>Surname</th> <th>Name</td> <th>ID Number</th> <th>Contact Numbet</th><th >Medical Aid Scheme</th><th>Medical Aid Number</th></th></tr>"; //connect to database $connection = mysql_connect('localhost','root','') or die ("Couldn't connect to server."); $db = mysql_select_db('medionline', $connection) or die ("Couldn't select database."); $query = 'SELECT patient.name,patient.surname,patient.id_number,medical_aid.medical_aid_scheme,medical_aid.medical_aid_numberFROM patient,medical_aidWHERE patient.id_number = medical_aid.id_numberORDER BY patient_id';$result = mysql_query($query) or die("Couldn't execute query. ". mysql_error());while($row = mysql_fetch_array($result)){$id = $row['id_number'];echo "<tr ><td>";echo $row['surname'];echo "</td><td>";echo $row['name'];echo "</td><td>";echo $row['id_number'];echo "</td><td>";echo $row['medical_aid_scheme'];echo "</td><td>";echo $row['medical_aid_number'];echo "</td><td>";echo "</tr>";}echo"</table>";mysql_close();?> Quote Link to comment Share on other sites More sharing options...
AdRock Posted October 31, 2013 Share Posted October 31, 2013 Why don't you join the 2 tables using a JOIN $query = 'SELECT patient.name,patient.surname,patient.id_number,medical_aid.medical_aid_scheme,medical_aid.medical_aid_number patient.name,patient.surname,patient.id_number,medical_aid.medical_aid_scheme,medical_aid.medical_aid_number FROM patient INNER JOIN medical_aid ON patient.id_number = medical_aid.id_number ORDER BY patient_id'; Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 31, 2013 Share Posted October 31, 2013 (edited) The query is returning all records where the patient's id_number matches the id_number in the medical_aid table. So if a patient has two or more records in the medical_aid table you'll see their name, surname and id printed two or more times in the table, but the medical id and medical scheme will be different. If you only want to see the patients name, surname and id printed once but have their medical aids listed then you'll need to add more logic to your while loop when displaying the patients records Edited October 31, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.