[Resolved] [Newbie question] Query pulling info from another table??

[ludachris]

I have tried to search the forums for any info and many websites on a tutorial on this topic but I cant seem to find anything. I am usually the type that will search until I find but I have finally given up.

This is my very first use of PHP and MySQL interaction and I am pretty sure it isnt the BEST way to start 😃 - Below is the code I am working with now. (seems like nothing much, just basic, but it took me quite some time of searching/tutorial viewing)

<?
include("dbinf.inc.php");

mysql_connect($sqladdress,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM los WHERE state='tx'";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"name");
$phone=mysql_result($result,$i,"phone");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"site");
$branch=mysql_result($result,$i,"branch");

echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br>Branch: $branch<br><hr><br>";

$i++;
}

?>

Now heres where it gets interesting and I MIGHT be on the right track. I am making somewhat of a personal website for 300ish people. Based on their branch it will load their contact info (address, city, state, zip etc...) - so I have a seperate table called 'branches'. EXAMPLE: http://homeloancorp.com/los/test.html

Basically - I want the area in the contact information to populate based on their branch.

I am THINKING that I would just do another query that pulls from the 'branch' table 'WHERE branch='$branch' - or something along those lines.

If I am on the right track, what is the proper syntax and 'best practice' for that? If not - ANY help is appreciated 😃

Thanks in advance!
Chris Mase

[paulnaj]

Hi Chris,

Yes, it's another WHERE condition ... you need the state to be a certain value AND you need the branch to be a certain value. You can use AND as well as OR in WHERE clauses. Run through the MySQL Tutorial ... it's especially for beginners.

And I couldn't resist tweaking your code ... I'd give up using mysql_result() altogether ... it looks the easiest to understand, but can require a lot of overhead and make your apps slower. Might as well get used to best practise early, I'd say ...

<?php
include("dbinf.inc.php"); 
mysql_connect($sqladdress, $username, $password); 
@mysql_select_db($database)	or die( "Unable to select database"); 

// Assuming $branch has already been defined (or sent via a form)
$query = "
	SELECT * 
	FROM los 
	WHERE state = 'tx'
	AND branch = '".$branch."'
"; 

$result = mysql_query($query); 

echo '<b><center>Database Output</center></b><br><br>'; 
while($row = mysql_fetch_array($result)){ 
	echo '<b>'.$row['first'].' '.$row['last'].'</b>
	<br>Phone: '.$row['phone'].'
	<br>Mobile: '.$row['mobile'].'
	<br>Fax: '.$row['fax'].'
	<br>E-mail: '.$row['email'].'
	<br>Web: '.$row['web'].'
	<br>Branch: '.$row['branch'].'
	<br><hr><br>'; 
} 
mysql_close(); 
?>

... even if you've got this style from a tutorial or from a downloaded script.

Paul 🙂

[ludachris]

Thank you SO much - I am a best practice fiend (as I said in my original post) so point out ANY flaws - I am well aware of my lack of knowledge in this area 😃

Anyways - I think I used a bad example. The page will be for an individual LO. I need to add the fields address, city, state, zip etc. from the branches table based on what the LO's branch...

<?php 
include("dbinf.inc.php");  

mysql_connect($sqladdress, $username, $password);  

@mysql_select_db($database)    or die( "Unable to select database");  

// Lets say ID is recieved via $_GET (I know the code for this is missing)
$query = " 
    SELECT * 
    FROM los 
    WHERE id = '".$id."'
";  

$result = mysql_query($query);  

echo '<b><center>Database Output</center></b><br><br>';  

while($row = mysql_fetch_array($result)){  

    echo '<b>'.$row['first'].' '.$row['last'].'</b> 
    <br>Phone: '.$row['phone'].' 
    <br>Mobile: '.$row['mobile'].' 
    <br>Fax: '.$row['fax'].' 
    <br>E-mail: '.$row['email'].' 
    <br>Web: '.$row['web'].' 
    <br>Address: '.$row['address'].' // this should somehow pull from the branch table where $row would be the $branch of the LO
    <br>City: '.$row['city'].' // this should somehow pull from the branch table where $row would be the $branch of the LO
    <br>State: '.$row['state'].' // this should somehow pull from the branch table where $row would be the $branch of the LO
    <br>Zip: '.$row['zip'].' // this should somehow pull from the branch table where $row would be the $branch of the LO - would this be considered something like a nested Query? 
    <br><hr><br>';  

}  

mysql_close();  

?> 

Let me know if I am explaining this wrong or not making sense. Sorry for posting a bad example in the first place :queasy:

[paulnaj]

Ok, so you should have some field that is common to both tables 'los' and 'branch' which I'm going to suppose is 'id'.

The query should then look something like ...

<?php 
$query = "
	SELECT * 
	FROM los 
	LEFT JOIN branch USING (id)
	WHERE los.state = 'tx'
"; 
?>

I say 'something like' because I'm guessing your table structure. If this doesn't work, you might want to post up your CREATE statements verbatim.

P.

[ludachris]

Well there are about 5-30 loan officers in each branch.

I think the only thing they have in common would be that. The LO would have branch specified (for instance) as 'Nashville' and the 'branchid' of one of the branches in the branch table would be 'Nashville'.

Example...
John Doe, Jane Dean, and Jim Smith are all in the 'Nashville' branch. Basically rather than inputting the same address for all loan officers at this branch, I want them to get their address from the branches table.

http://www.homeloancorp.com/los/test.html

There is an example of what will be the output. Everything there will pull from the LO table EXCEPT the address data In the contact info section.

I will post the CREATE statements when I get in the office tomorrow.

Thank you so much for helping me!!

[paulnaj]

Thank you so much for helping me!!

Hey, no problem ... loads of people in this forum are at it all the time.

Anyway, yes ... it's time to see your CREATE statements!

Paul.

[ludachris]

Ok I went ahead and started over from scratch... http://www.homeloancorp.com/los/test.html - Here is an example of what I will be outputting (eventually) - By an LO specifying they are in the Corporate branch I would like the address fields to pull from the Corporate row in the branches table. Below is all of the information/code.

Let me know if you need ANYTHING else. Again, thank you SO MUCH for helping me.

Here are the CREATE's....

//Table with the loan officers
CREATE TABLE `los` (
`id` SMALLINT( 4 ) NOT NULL ,
`fname` VARCHAR( 20 ) NOT NULL ,
`lname` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 40 ) NOT NULL ,
`webid` VARCHAR( 25 ) NOT NULL ,
`cel` VARCHAR( 15 ) NOT NULL ,
`branch` VARCHAR( 30 ) NOT NULL ,
`bio` BLOB NOT NULL ,
PRIMARY KEY ( `id` ) 
)

//Table with all of the branches
CREATE TABLE `branches` (
`id` SMALLINT( 4 ) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 40 ) NOT NULL ,
`address` VARCHAR( 40 ) NOT NULL ,
`city` VARCHAR( 25 ) NOT NULL ,
`state` VARCHAR( 2 ) NOT NULL ,
`zip` SMALLINT( 5 ) NOT NULL ,
`fon` VARCHAR( 12 ) NOT NULL ,
`fax` VARCHAR( 12 ) NOT NULL ,
`free` VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
);

I put 2 records in each table. Here is the code for http://ludachris.org/losoutput.php

<?
include("dbinf.inc.php");

mysql_connect($sqladdress,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="
	SELECT *
	FROM los
";

$result=mysql_query($query);
echo "<b><center>Current LO's</center></b><br><br>";

while ($row = mysql_fetch_array($result)){
    echo '<b>'.$row['fname'].' '.$row['lname'].'</b>
    <br>E-mail: '.$row['email'].' 
    <br>WebID: '.$row['webid'].' 
    <br>CEL: '.$row['cel'].' 
    <br>Branch: '.$row['branch'].' 
    <br>Bio: '.$row['bio'].'
    <br><hr><br>';
}
mysql_close();
?>

Here is the code for http://ludachris.org/branchesoutput.php

<?
include("dbinf.inc.php");

mysql_connect($sqladdress,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="
	SELECT *
	FROM branches
";

$result=mysql_query($query);
echo "<b><center>Current Branches</center></b><br><br>";

while ($row = mysql_fetch_array($result)){
    echo '<b>'.$row['name'].'</b>
    <br>Address: '.$row['address'].' 
    <br>City: '.$row['city'].' 
    <br>ST: '.$row['state'].' 
    <br>Zip: '.$row['zip'].' 
    <br>FON: '.$row['fon'].'
    <br>FAX: '.$row['fax'].'
    <br>FREE: '.$row['free'].'
    <br><hr><br>';
}
mysql_close();
?>

[paulnaj]

Hi Chris,

Having looked at your table structure, can I suggest a few small changes ...

CREATE TABLE `los` ( 
`los_id` SMALLINT( 4 ) NOT NULL , 
`fname` VARCHAR( 20 ) NOT NULL , 
`lname` VARCHAR( 25 ) NOT NULL , 
`email` VARCHAR( 40 ) NOT NULL , 
`webid` VARCHAR( 25 ) NOT NULL , 
`cel` VARCHAR( 15 ) NOT NULL , 
`branch_id` SMALLINT( 4 ) NOT NULL , 
`bio` BLOB NOT NULL , 
PRIMARY KEY ( `id` )  

)

CREATE TABLE `branches` ( 
`branch_id` SMALLINT( 4 ) NOT NULL AUTO_INCREMENT , `name` VARCHAR( 40 ) NOT NULL , 
`address` VARCHAR( 40 ) NOT NULL , 
`city` VARCHAR( 25 ) NOT NULL , 
`state` VARCHAR( 2 ) NOT NULL , 
`zip` SMALLINT( 5 ) NOT NULL , 
`fon` VARCHAR( 12 ) NOT NULL , 
`fax` VARCHAR( 12 ) NOT NULL , 
`free` VARCHAR( 15 ) NOT NULL , 
PRIMARY KEY ( `id` )  

);

So,

Name changes ...

los.id => los.los_id
branches.id => branches.branch_id

... are cosmetic, but make life simpler later.

And a field change. Replace the field...

los.branch

... with ...

branch_id SMALLINT( 4 ) NOT NULL

... so that instead of storing the NAME of a branch, you store it's unique identifier (primary key). This is the standard way to relate two tables. Your way is OK and might work, but this is best practise and will always work.

Let me know once you've done that (or aren't able for some reason) and we'll carry on after.

Paul 🙂

[ludachris]

Ahhh! I see where you are going... I went ahead and changed the branches.branches_id to match the los.branches_id field because I dont want to confuse myself with trying to find out "ok branch #32, which one is that... AHH Ohio"

So, here's what I have now... (also changed the id field names as you suggested)

CREATE TABLE `branches` (
`branches_id` varchar( 30 ) NOT NULL default '',
`name` varchar( 40 ) NOT NULL default '',
`address` varchar( 40 ) NOT NULL default '',
`city` varchar( 25 ) NOT NULL default '',
`state` char( 2 ) NOT NULL default '',
`zip` smallint( 5 ) NOT NULL default '0',
`fon` varchar( 12 ) NOT NULL default '',
`fax` varchar( 12 ) NOT NULL default '',
`free` varchar( 15 ) NOT NULL default '',
PRIMARY KEY ( `branches_id` ) 
) 

CREATE TABLE `los` (
`los_id` smallint( 4 ) NOT NULL default '0',
`fname` varchar( 20 ) NOT NULL default '',
`lname` varchar( 25 ) NOT NULL default '',
`email` varchar( 40 ) NOT NULL default '',
`webid` varchar( 25 ) NOT NULL default '',
`cel` varchar( 15 ) NOT NULL default '',
`branches_id` varchar( 30 ) NOT NULL default '',
`bio` blob NOT NULL ,
PRIMARY KEY ( `los_id` ) 
)

Thanks again!!! (getting anxious to see final step!! 😃 )

[paulnaj]

Yes, that's fine, as long as you can be sure that branches_id will be always be unique.

(Also, forgot to mention to update the primary key defn. ... well spotted).

Now, you can use the branches_id field to JOIN these two tables together ...

<?php // los_branch.php
include("dbinf.inc.php"); 

mysql_connect($sqladdress,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 


// However you get this value ...
$branches_id = 'Ohio';

$query = " 
	SELECT * 
	FROM los 
	JOIN branches USING (branches_id)
	WHERE branches_id = '".$branches_id."'
	ORDER BY los.lname ASC, los.fname ASC
"; 

$result = mysql_query($query); 
$num_rows = mysql_num_rows($result);

echo "<center><b>".$branches_id." Current LO's</b> (".$num_rows.")</center><br><br>"; 
while ($row = mysql_fetch_array($result)){ 
	// Easy way to see what's going on ...
	echo '<hr><pre>';	print_r($row);	echo '</pre>';
} 
mysql_close(); 
?> 

Paul.

[ludachris]

I think I might be doing something wrong. http://ludachris.org/testing123.php

That is the output I get with 'Corporate' specified as the branches_id.

What would be the proper syntax/best practice to pull the info piece by piece ( like here http://homeloancorp.com/los/test.html ). Would it be a basic '.$row['city'].' for the city (from branches row) and the same '.$row['fname'].' for name (from los row) .

I tried doing some different outputs and it's like its not grabbing a single record. Any idea on what I might have screwed up here?

Again, thank you for your help and pardon my stupidity 😃

[paulnaj]

Hmmm,

Not sure what's wrong there.

Try this ...

<?php // los_branch.php 
include("dbinf.inc.php");  

mysql_connect($sqladdress,$username,$password);  

@mysql_select_db($database) or die( "Unable to select database");  

$query = "SELECT * FROM los JOIN branches USING (branches_id)";  

$result = mysql_query($query);  

while ($row = mysql_fetch_array($result)){  

    echo '<hr><pre>';    print_r($row);    echo '</pre>'; 
}  

mysql_close();  

?>

... just to see that the JOIN is working.

Paul:

[ludachris]

Ok i tried that and it brought a blank page... I went ahead and tried this...

 <?php // los_branch.php 
include("dbinf.inc.php");  

mysql_connect($sqladdress,$username,$password);  

@mysql_select_db($database) or die( "Unable to select database");  

$query = " 
    SELECT * 
    FROM los 
    JOIN branches USING (branches_id)
";  

$result = mysql_query($query);  

$num_rows = mysql_num_rows($result); 
echo "<center><b>Current LO's</b> (".$num_rows.")</center><br><br>";  

while ($row = mysql_fetch_array($result)){  

    echo '<hr><pre>';    print_r($row);    echo '</pre>'; 
}  

mysql_close();  

?> 

And now $num_rows is 0. I dont know if thats progress or any help but I kept www.ludachris.org/testing123.php up so you can see.

[ludachris]

Ok i just noticed something in phpMyAdmin...

The additional Features for working with linked Tables have been
deactivated. To find out why click here.

I am ONLY using ONE database so i specified it as the pmadb and it didnt work. Looking through the errors it looks like I am not even close to being able to do JOINs. sigh I could scream right now. Lots of references to a relation table, an info table a coords table and a column info table.

Must ALL of this be configured for what I am trying to do to work?

If so I am so deeply sorry I wasted your time. I will try and figure all of this stuff out and set it up first - from here I might be able to get this figured out as well.

Thank you for all of your help Paul. I am so sorry I wasted your time 🙁 .

ps. - reply to this if my assumptions are correct and I will delete this post. And a possible finger towards where to start learning this (best practice as well, tutorials that you find out later tought you the wrong way suck!) 😃

[paulnaj]

Hey Chris,

No worries ... but I don't really undertand how phpmyadmin could affect your ability to join tables (I don't use it much).

Since you're using it though ...

Select your database, click on the SQL tab.

Cut'n'paste ...

SELECT *
FROM los
JOIN branches USING (branches_id)

... directly into the input box, and click GO to see what happens.

P.

[ludachris]

Hmmm - when I did that it says

Your SQL-query has been executed successfully (Query took 0.0237 sec)

😕 😕 😕
Any thoughts?

[paulnaj]

Hmmph,

So no errors as such, but no records either.

OK, if you want, would you export your tables using phpmyadmin(structure and data) to a text file (.txt would be best) and post it up.

Just would like to see it in full, make sure there's nothing I've missed.

P.

[ludachris]

Here is the .CSV...

"1";"Corporate";"3520 Northbelt E. #200";"Houston";"TX";"32767";"281-892-0000";"281-892-0001";"1-800-892-3000"
"2";"Spring";"7886 Louetta Rd. #850";"Spring";"TX";"32767";"281-655-9967";"281-655-6767";"1-800-892-4000"
"101";"Chris";"Mase";"[e-mail]";"cmase";"713-252-0000";"Corporate";"Welcome to my website!

Whether you're buying, selling, refinancing, or building your dream home, you have a lot riding on your decision. Market conditions and mortgage programs change frequently, and I welcome the opportunity to serve your needs with quick and accurate real estate financing advice. I have the expertise and knowledge to help you determine the absolute best loan program to meet your objectives.

Ensuring that you make the right choice for you and your family is my ultimate goal. And I am committed to providing my customers with mortgage services that exceed their expectations. I hope you'll browse my website, check out the different loan programs I have available, use my decision-making tools and calculators, and apply for a loan using the online application.

After you've applied, I'll call you to discuss the details of your loan. As always, you may contact me anytime by phone, fax or email for personalized service and expert advice.

I look forward to working with you! "
"102";"Chris";"Alvers";"[e-mail]";"calvers";"713-252-0001";"Corporate";"Welcome to my website!

Whether you're buying, selling, refinancing, or building your dream home, you have a lot riding on your decision. Market conditions and mortgage programs change frequently, and I welcome the opportunity to serve your needs with quick and accurate real estate financing advice. I have the expertise and knowledge to help you determine the absolute best loan program to meet your objectives.

Ensuring that you make the right choice for you and your family is my ultimate goal. And I am committed to providing my customers with mortgage services that exceed their expectations. I hope you'll browse my website, check out the different loan programs I have available, use my decision-making tools and calculators, and apply for a loan using the online application.

After you've applied, I'll call you to discuss the details of your loan. As always, you may contact me anytime by phone, fax or email for personalized service and expert advice.

I look forward to working with you! "

[paulnaj]

Hi cHris,

OK that's the data ... can you do the structure as well. Just doulbe-checking I haven't missed something.

P.

[ludachris]

Here we go!

-- Server version: 4.0.24

-- PHP Version: 4.3.10

-- Database: db98339507

---

--

-- Table structure for table branches

CREATE TABLE branches (
branches_id varchar(30) NOT NULL default '',
name varchar(40) NOT NULL default '',
address varchar(40) NOT NULL default '',
city varchar(25) NOT NULL default '',
state char(2) NOT NULL default '',
zip smallint(5) NOT NULL default '0',
fon varchar(12) NOT NULL default '',
fax varchar(12) NOT NULL default '',
free varchar(15) NOT NULL default '',
PRIMARY KEY (branches_id),
UNIQUE KEY branches_id (branches_id)
) TYPE=MyISAM;

---

--

-- Table structure for table los

CREATE TABLE los (
los_id smallint(4) NOT NULL default '0',
fname varchar(20) NOT NULL default '',
lname varchar(25) NOT NULL default '',
email varchar(40) NOT NULL default '',
webid varchar(25) NOT NULL default '',
cel varchar(15) NOT NULL default '',
branches_id varchar(30) NOT NULL default '',
bio blob NOT NULL,
PRIMARY KEY (los_id)
) TYPE=MyISAM;

Let me know if you need anything else THANKS SO MUCH!!