Topic: MySQL Help please

Hi all! I'm working on a project to make my life easier at my job. I've recently been given the task of managing work that comes in from other offices and after seeing the way this has been done in the past, I've decided to make a database app to store all of this information. I've got my tables set up and forms to input new data and others to view the data, but it's been a long time since I've done any coding and I'm having some trouble getting a couple things figured out.

I've got a few tables:
refmds - list of referring doctors
refsources - list of locations referring doctors work at
insurance - list of insurances (medicare, bcbs, etc...)
hhcmds - list of our doctors
studies - list of studies sent from other offices for our doctors to read

Now, in studies, I have columns that hold the primary key of items in other tables - ordering (stores uid for refmds), reading (stores uid for hhcmds), location (stores uid for refsources) and insurance (stores uid for insurance)

This is all working as expected, but I'd like to be able to retrieve data from other fields of the related tables and show it in my results, for example: instead of showing 2 under "Reading" I'd like to see lname associated with that row in the other table, which in this case would be "Matthews"

mysql_select_db("hhc", $connection);
$SQL = mysql_query("SELECT * FROM studies WHERE lname LIKE '$lname%' AND fname LIKE '$fname%' ORDER BY lname");
echo "<table><tr><th>First Name</th><th>Last Name</th><th>Date of Birth</th>
    <th>Ordering</th><th>Reading</th><th>Date of Service</th><th>Actions</th></tr>\n";
while($row = mysql_fetch_array($SQL)) {
?>
<tr>
	<td><? echo $row['fname'] ?></td>
	<td><? echo $row['lname'] ?></td>
	<td><? echo $row['ptdob'] ?></td>
	<td><? echo $row['ordering'] ?></td>
	<td><? echo $row['reading'] ?></td>
	<td><? echo $row['dos'] ?></td>
	<td>
		<a href="edit.php?uid=<? echo $row['uid'] ?>"><img src="img/edit.png"></a>
		<a href="delete.php?uid=<? echo $row['uid'] ?>"><img src="img/delete.png"></a>
	</td>
</tr>
<?
}
mysql_close($connection);
echo "</table>\n";

I'm not sure what the best way to go about doing this would be, as I will eventually need to reference all of the other tables when it comes time to write the billing script and building the invoices. I've thought about making another loop inside the one that processes the output of the first, but I know there's gotta be a simpler way. I'd like to have as little code as possible doing the work, just to leave as little room for error as I possibly can.

any help you guys could give would be greatly appreciated.

edit: hopefully to make the code fit on the page better


I focus on the pain; The only thing that's real (Trent Reznor)

(Edited by I X I on 07-29-2012 17:17)

You probably need to look into SQL joins - which do just what they say, they 'join' tables together.

so for example

SELECT studies.*, hhcmds.* FROM studies, hhcmd WHERE hhcmd.doc_id = studies.refering_doctor AND  studies.lname LIKE '$lname%' AND studies.fname LIKE '$fname%' ORDER BY studies.lname

so long,

->Tyberius Prime

thanks for the reply, TP. the statement you gave me pulls the right records, but I can't seem to get the data to show up right.

Here's what I have:

$SQL = mysql_query("
			SELECT
				studies.*, hhcmds.* 
			FROM
				studies, hhcmds
			WHERE
				hhcmds.uid = studies.reading
			AND
				studies.lname LIKE '$lname%' AND studies.fname LIKE '$fname%'
			ORDER BY
				studies.lname
		");

		echo "<table><tr><th>First Name</th><th>Last Name</th><th>Date of Birth</th><th>Ordering</th><th>Reading</th><th>Date of Service</th><th>Actions</th></tr>\n";
		while($row = mysql_fetch_array($SQL)) {
	?>
		<tr>
			<td><? echo $row['studies.fname'] ?></td>
			<td><? echo $row['studies.lname'] ?></td>
			<td><? echo $row['ptdob'] ?></td>
			<td><? echo $row['ordering'] ?></td>
			<td><? echo $row['hhcmds.lname'] ?></td>
			<td><? echo $row['dos'] ?></td>
			<td>
				<a href="edit.php?uid=<? echo $row['uid'] ?>"><img src="img/edit.png"></a>
				<a href="delete.php?uid=<? echo $row['uid'] ?>"><img src="img/delete.png"></a>
			</td>
		</tr>
	<?
		}

but my results aren't what I was expecting. I'm not getting any data returned for studies.fname, studies.lname or hhcmds.lname and uid is returning the uid from the hhcmds table, but for the appropriate doctor associated with the number saved in the reading field.

do you suppose I should change the names of the columns in one table or the other? it just doesn't make sense to me that I'd have to use a different name for each table's unique id field.


Do you serve a purpose or purposely serve (Corey Taylor)

well... after a little more searching, I answered my own question. but for the sake of anyone who might be searching and come across this, here's what I came up with.

since I use identical column names in different tables, I had to set up aliases using AS. here's what I came up with

$SQL = mysql_query("
		SELECT
			s.uid AS s_uid,
			s.fname AS s_fname,
			s.lname AS s_lname,
			s.ptdob AS s_ptdob,
			r.lname AS r_lname,
			h.lname AS h_lname,
			s.dos AS s_dos
		FROM
			studies s
			LEFT JOIN refmds r ON r.uid = s.ordering
			LEFT JOIN hhcmds h ON h.uid = s.reading
		WHERE
			s.lname LIKE '$lname%' AND s.fname LIKE '$fname%'  
	");
	echo "<table><tr><th>First Name</th><th>Last Name</th><th>Date of Birth</th><th>Ordering</th><th>Reading</th><th>Date of Service</th><th>Actions</th></tr>\n";
	while($row = mysql_fetch_array($SQL)) {
?>
	<tr>
		<td><? echo $row['s_fname'] ?></td>
		<td><? echo $row['s_lname'] ?></td>
		<td><? echo $row['s_ptdob'] ?></td>
		<td><? echo $row['r_lname'] ?></td>
		<td><? echo $row['h_lname'] ?></td>
		<td><? echo $row['s_dos'] ?></td>
		<td>
			<a href="edit.php?uid=<? echo $row['s_uid'] ?>"><img src="img/edit.png"></a>
			<a href="delete.php?uid=<? echo $row['s_uid'] ?>"><img src="img/delete.png"></a>
		</td>
	</tr>
<?
	}
	mysql_close($connection);
	echo "</table>\n";
?>

Take your time, hurry up; the choice is yours just don't be late (Kurt Cobain)

Seems you're getting along really well

Thanks. those aliases took a minute for me to wrap my head around, but I think I've got it figured out. I took a couple of ancient database programming classes years ago, so I know a little about the concepts - it's just the syntax and best practices that I have to figure out lol


Take your time, hurry up; the choice is yours just don't be late (Kurt Cobain)