Problems with displaying mysql results

[akueffler]

I’m a newbie that needs help please! :-\

This is the situation. I work at a non profit museum that has Yoga Classes. Currently the yoga instructor handles all here current and previous class signups on paper, which becomes a pain in the butt when we have to lookup something. So I suggested setting everything up with php/MYSQL.

I currently have a page that she can view all of her students, and a page where she can add students and/or transactions. The problem I am having is where it displays all transactions for an individual student. I can get it to display all transactions for all students, or all students.

Can someone help me, or at lease point me in the right direction, on how to be able to select a name and have all transactions for that name displayed. I know it can be done, but I just can’t seem to figure it out.

Here is the tables I am using:

Yoga_patron table[code]
`p_id` int(8) NOT NULL auto_increment COMMENT 'Patron ID',
  `member` char(3) NOT NULL default '' COMMENT 'Member of PGSMOA',
  `f_name` varchar(100) NOT NULL default '' COMMENT 'First Name',
  `l_name` varchar(100) NOT NULL default '' COMMENT 'Last Name',
  `full_name` varchar(200) NOT NULL default '' COMMENT 'Full Name',
  `address` varchar(150) NOT NULL default '' COMMENT 'Address',
  `city` varchar(50) NOT NULL default '' COMMENT 'City',
  `state` varchar(50) NOT NULL default '' COMMENT 'State',
  `zip` int(10) NOT NULL default '0' COMMENT 'Zip Code',
  `phone` int(11) NOT NULL default '0' COMMENT 'Phone Number',
  PRIMARY KEY  (`p_id`)
[/code]
[code]
Yoga_transaction table
`t_id` int(8) NOT NULL auto_increment COMMENT 'Transaction ID',
  `trans_date` date NOT NULL default '0000-00-00' COMMENT 'Date of Transaction',
  `exp_date` date NOT NULL default '0000-00-00' COMMENT 'Experation Date',
  `p_id` int(8) NOT NULL default '0' COMMENT 'Patron ID',
  `receipt` int(8) NOT NULL default '0' COMMENT 'Receipt Number',
  `cost` varchar(8) NOT NULL default '' COMMENT 'Cost of Transaction',
  `paidby` varchar(10) NOT NULL default '' COMMENT 'Paid with',
  PRIMARY KEY  (`t_id`)
[/code]
I am using:
Server version: 4.1.21
PHP Version: 5.1.6

The part of code that I think the problem is occurring is this I can’t seem to figure out how to get it to take the p_id from patron table and lookup/display all transaction that have the same p_id from the transaction table:
[hr]
[code]<?php
$conn = mysql_connect("localhost", "user", "password")
    or die($msg_no_connect);
mysql_select_db("pgsmoa")
    or die(mysql_error());
$res = mysql_query("SELECT f_name, l_name, address, city FROM yoga_patron;")       
    or die(mysql_error());
if (mysql_num_rows($res) > 0) {
    echo '<table border="1">';
    echo '<th>First Name</th><th>Last Name</th><th>Address</th><th>City</th>';
while ($row = mysql_fetch_assoc($res)) {
      echo "<tr>
            <td>{$row['f_name']}</td>
            <td>{$row['l_name']}</td>
            <td>{$row['address']}</td>             
            <td>{$row['city']}</td>
            </tr>"; 
}
  echo '</table>';
} else
  echo 'No rows in selected table'; ?>[/code]
[hr]

Any help would be greatly appreciated. Thank you in advance for your time and information.

Aaron

[artacus]

[code]
SELECT *
FROM yoga_patron AS p
JOIN yoga_transaction AS t ON p.p_id = t.p_id
WHERE p.last_name LIKE 'Gulliver%'[/code]