Pulling Data from two tables

[ned69]

Hi Guys
I have two tables I'm trying to pull information from. In the end I want to be able to display a username and a school name which are contained in 2 separate tables but linked by the school id number. The problem is that the 'members' table which holds most of the information like the username etc, only contains a school 'id' number while the actual school name is contained in a 'high_schools table' with the actual names. How do I join(or do anything else) that will allow me to display the actual school name and not just the school id number? So that in the end I have the following:
john2005 - Aladin High School
and not just:
john2005 - 57

Below is the code for the two tables and the php script I'm developing to populate the page/report.

Thanks in advance.

MEMBERS TABLE

CREATE TABLE `members` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(15) NOT NULL default '',
  `password` varchar(15) NOT NULL default '',
  `email` varchar(125) NOT NULL default '',
  `first_name` varchar(125) NOT NULL default '',
  `last_name` varchar(75) NOT NULL default '',
  `high_school_attended` int(11) NOT NULL default '0',
  `graduation_year` int(11) NOT NULL default '0',
  `date_of_birth` date NOT NULL default '0000-00-00',
  `comments` text NOT NULL,
  `status` smallint(6) NOT NULL default '1',
  `date_added` date NOT NULL default '0000-00-00',
  `last_modified` date default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3448 ;

HIGH SCHOOLS TABLE
CREATE TABLE `high_schools` (
  `id` int(11) NOT NULL auto_increment,
  `high_school` varchar(125) NOT NULL default '',
  `status` smallint(6) NOT NULL default '1',
  `date_added` date NOT NULL default '0000-00-00',
  `last_modified` date default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1343 ;

<?php

     $dbhost = "localhost";
     $dbuser = "php";
     $dbpwd = "hello";
     $dbname = "jokes";

     $conn = mysql_connect($dbhost, $dbuser, $dbpwd ) or die(mysql_error());
     mysql_select_db($dbname, $conn) or die(mysql_error());
     echo "DB Connected<BR>";
     
     $query = "SELECT * from members";
     $result = mysql_query($query, $conn);
     
     for ($i = 0; $i < mysql_num_rows($result) ; $i++ ){
     
     $username = mysql_result($result, $i, "username");
     $school = mysql_result($result, $i, "high_school_attended");
     
     print "$username - ";
     print "$school<BR>";

     }

?>

[nav33n]

The query would be,

SELECT t1.username, t2.high_school
  FROM members as t1 JOIN high_schools as t2 ON t1.high_school_id = t2.id;

[ned69]

Hey thanks for your help!

Its working but I was wondering why its ordering everything by the High Schools table order i.e. its listing every school in the table alphabetically and then putting a username next to it when it really should be listing a username and then putting the school which that member attended next to their name? I've tried switching the positions of the tables in the query but that doesn't seem to work...any suggestions?

[nav33n]

Are you sure you are printing it in the order you want ? I am not sure why you are getting the output in a weird order. Umm.. Since you are using php, you can echo the output however you want. (I know thats not the exact solution!)

[Taffd]

I think the problem using Nav33n's query is because of the join.
You could try this alternative.

SELECT members.username, high_schools.high_school
FROM members, high_schools
WHERE members.high_school_attended = high_schools.id

I'm assuming (members.high_school_attended) is the school id in the members table.

[ned69]

Thanks for your response. I've tested both queries and they seem to both work.

I was also wondering what to do for my next step. I want to populate an alphabetical listing/menu e.g. A | B | C | D | E | F | G etc. So that if someone clicks on B, all the High Schools under B get listed. Then after that when someone clicks a particular High School, everyone who is in the database under that High School will be listed.

I was thinking of having a Switch statement with cases going from A to Z then under each case have some query that will display that range of letters via the ID number. For example for case A in english it would be Select schools where ID = 1 thru 24 and that would cover all the schools under A. The problem however would arise if I ever had to add extra schools because it would mean having to manually change the Switch statement each time whereas I'm sure there is a query that I can use to check the first letter and have the script generate that on its own. I tried the following which I found online but it MySQL does not seem to like that function. Note this is generic:

SELECT * FROM members
WHERE SUBSTR(schoolname, 1,1)
BETWEEN ‘A’ AND ‘B’;

How can I get this to work?

I think the problem using Nav33n's query is because of the join.
You could try this alternative.

SELECT members.username, high_schools.high_school
FROM members, high_schools
WHERE members.high_school_attended = high_schools.id

I'm assuming (members.high_school_attended) is the school id in the members table.

[Taffd]

Here's how I'd do it.
Let's call your pages 'search' and 'results'.

On your search page list the letters A|B|C|......Z.

Make each letter a link to the results page with a unique id

<a>a href="results.php?id=A">A</a>
<a>a href="results.php?id=B">B</a>

On your search page have your query

<?php
$colname_search = "-1";
if (isset($_GET['id'])) {
  $colname_search = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($xxxxx, $xxxxx);
$query_search = sprintf("SELECT * FROM schools WHERE SUBSTRING(school,1,1) = %s", $colname_search);
$search = mysql_query($query_search, $xxxxx) or die(mysql_error());
$row_search = mysql_fetch_assoc($search);
$totalRows_search = mysql_num_rows($search);
?>

I've tested this query and it works, though you'll have to substitute the correct column names where I've used schools and school in the WHERE statement

Regards
Taffd

[johny_d]

Try this:

<a>a href="results.php?fl=A">A</a>
<a>a href="results.php?fl=B">B</a>

$fl =$_GET['fl']; // fl - First Letter
if (preg_match('#^[a-z]{1}$#i',$fl)) { // check if it is an allowed value - letters from a to z in lower/upper case

$result = mysql_query("SELECT id,high_school FROM high_schools WHERE high_school LIKE '$fl%'";
......
}

For that code to work faster, it's probably a good idea to have an index on the 'high_school' column in your mysql table

[ned69]

Thanks guys....worked like a charm though I was totally lost on how to use Taffd's query. Anyway here is what I ended up with and it works for those who want to use this as a tut:

<?php
     
     $dbhost = "localhost";
     $dbuser = "p";
     $dbpwd = "h";
     $dbname = "j";

     $conn = mysql_connect($dbhost, $dbuser, $dbpwd ) or die(mysql_error());
     mysql_select_db($dbname, $conn) or die(mysql_error());
     echo "DB Connected<BR>";

     $fl =$_GET['fl']; // fl - First Letter
     if (preg_match('#^[a-z]{1}$#i',$fl)) { // check if it is an allowed value - letters from a to   z in lower/upper case

     // the query
     $query = "SELECT id,high_school FROM high_schools WHERE high_school LIKE '$fl%'";
     $result = mysql_query($query, $conn);

     for ($i = 0; $i < mysql_num_rows($result) ; $i++ ){
     
     $school = mysql_result($result, $i, "high_school");     
     
     print "$school<BR>";

     }
     }

?>

Which brings me to my next step on how to list everyone who went to a certain school once that school is selected. E.g. If I click on C and it displays all the schools under C and I want to see everyone who went to Cedar Park High School. I think the simple script would go something like:
SELECT * where high_school = "$school";
but I have no idea how I can embed a query within a query(if thats how its done) i.e. where I have the:
print "$school<BR>";
I suspect I am supposed to put a query there...or is there a better way to do that?