linking a record across 2 tables to edit

[jeff5656]

A user clicks this link:

(query from table "icu"....)
<a href="editdos.php?action=edit&id=<?phpecho $row['id_incr']; ?>

 

here is the action:

 

editdos.php:

$consultsq1 = "SELECT * FROM icu
					WHERE id_incr = '" . $_GET['id'] . "'";
$result = mysql_query ($consultsq1) or die ("Invalid query: " . mysql_error ());
$row = mysql_fetch_array ($result);

$id_incr = $row['id_incr'];

 

 

I want to populate a form with record from the link AND bring in data from another table called dos.  The table "dos" has a field "pt_id" that equals the primary key id_incr from the ICU table.  How do I modify this code to briong in data from both fields for this particular record?

[gevans]

can you list the fields in each table that you need? and which tables they belong to!

[revraz]

Just add the field name to your Select statement, and JOIN the other table using the IDs.

[jeff5656]

can you list the fields in each table that you need? and which tables they belong to!

ICU table:

 

CREATE TABLE `icu` (
  `id_incr` int(254) NOT NULL auto_increment,
  `rm_loc` varchar(20) NOT NULL,
  `patient` varchar(20) NOT NULL,
  `mrn` varchar(20) NOT NULL,
  `age` varchar(20) NOT NULL,
  `race` varchar(20) NOT NULL,
  `gender` enum('m','f') NOT NULL,
id incr is the primary key

 

The dos table is:

CREATE TABLE `dos` (
  `pt_id` int(20) NOT NULL,
  `billing_date` date NOT NULL,
  `billing_id` int(20) NOT NULL,
  `dx` int(20) NOT NULL,
  `staff` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

The pt_id is the foreign key for ICU (id_incr).

 

As you can see other tables need to be linked (billing_id), but I need to learn how to do this with 2 tables first.  So I want to populate a form from a record in dos that matches the id in ICU.  I want to bring in fields from both tables into the form.  The form would then update the DOS table. 

[gevans]

SELECT 1.id_incr, 2.pt_id, 1.rm_loc FROM icu 1, dos 2 WHERE 1.id_incr=2.pt_id

[jeff5656]

SELECT 1.id_incr, 2.pt_id, 1.rm_loc FROM icu 1, dos 2 WHERE 1.id_incr=2.pt_id

 

Don't I need the get['id']?  Otherwise how will I know which record the user has selected from the previous link?

This is my current code before your suggestion:

$consultsq1 = "SELECT * FROM icu
                  WHERE id_incr = '" . $_GET['id'] . "'";
   $result = mysql_query ($consultsq1) or die ("Invalid query: " . mysql_error ());
   $row = mysql_fetch_array ($result);
   
   $id_incr = $row['id_incr'];

 

[revraz]

His example is showing you how to do the query.  Substitute your variable after the where clause just like you did before.

[jeff5656]

like this?

 

$consultsq1 = "SELECT 1.id_incr, 2.pt_id, 1.rm_loc FROM icu 1, dos 2 
WHERE 1.id_incr=2.pt_id and 1.id_incr = '" . $_GET['id'] . "'";

[gevans]

try it and find out

[jeff5656]

I made a separate post I probably should have just added to this thread sorry

 

Now I get this error:

 

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, dos 2 WHERE 1.id_incr = 2.pt_id and id_incr = '8'' at line 1

 

Code: [select]

$consultsq1 = "SELECT 1.id_incr, 2.pt_id, 1.rm_loc FROM icu 1, dos 2

                  WHERE 1.id_incr = 2.pt_id and 1.id_incr = '" . $_GET['id'] . "'";

[gevans]

$consultsq1 = "SELECT icu.id_incr, dos.pt_id, icu.rm_loc FROM icu, dos

                  WHERE icu.id_incr = '" . $_GET['id'] . "'";

[DarkWater]

I was under the impression that you could not have numbers as aliases.  If my memory is correct, this should work:

 

SELECT icu.id_incr, 2.pt_id, 1.rm_loc FROM icu INNER JOIN dos ON icu.id_incr = dos.pt_id AND icu.id_incr = 4;

 

Substitute 4 for your GET param.  Also, sanitize your GET input!  Make sure it's actually a number!