Relational database

@DasherAug 09.2011 — #I am not the best at MySQL but something like this;

SELECT memberinfo.meminfo FROM memberinfo WHERE membertable.memID = memberinfo.memID AND membertable.name = 'John Doe'

Assuming the common relationship between the two tables is memID.

@eval_BadCode_Aug 10.2011 — #You will need a PRIMARY and FOREIGN key to relate the tables.

You can create compound primary keys, but lets get the basics first. Each record/row/tuple in a table should have a PRIMARY key which is unique from all other records in that table, it is used for identifying the record. Sometimes your table doesn't have a [U]natrual[/U] primary key, so you must introduce a primary key. When I introduce a primary key I use the following (just as a place to start- which can be altered later if needed):

[CODE]
 
 CREATE SCHEMA Members DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 USE Members;
 
 CREATE  TABLE Members.member (
 memID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 name VARCHAR(100) NOT NULL ,
 userName VARCHAR(100) NOT NULL ,
 dateJoined TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
 PRIMARY KEY (memID) ,
 INDEX userName USING BTREE (userName ASC) )
 ENGINE = InnoDB;
 
 CREATE TABLE memberinfo (
 memberinfo_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
 memID int(10) unsigned NOT NULL,
 meminfo varchar(500) DEFAULT 'Not Available',
 PRIMARY KEY (memberinfo_ID),
 KEY FK_memberinfo2member (memID),
 CONSTRAINT FK_memberinfo2member FOREIGN KEY (memID) REFERENCES member (memID) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 [/CODE]

So what we've done here is created two basic tables [B]which are relational[/B]. They are related using memID, which is a primary and foreign key we introduced to the schema (database).

Now, to join our tables- or rather... in the case of your issue- to traverse these tables we can use something simple like this:

[code=php]
 
 <?php
 
 $con = mysql_connect('foo','bar','baz');
 if($con) mysql_select_db('Members');
 if(mysql_errno()) die (mysql_errno() . ': ' . mysql_error());
 
 if(isset($_GET['memID'])) {
 $sqlResource = mysql_query(sprintf("
 SELECT member.name, memberinfo.info
 FROM memberinfo JOIN member USING(memID) 
 WHERE memberinfo.memID = &#37;d", $_GET['memID']
 ));
 if($sqlResource && !mysql_errno() && mysql_num_rows($sqlResource)) {
 list($name,$info) = mysql_fetch_row($sqlResource);
 printf("<table><tr><th>..::Member Info::..</br>%s</th></tr><tr><td>%s</td></tr></table>", htmlentities($name),htmlentities($info));
 }
 } else {
 $sqlResource = msql_query("SELECT member.name,member.memID FROM member");
 if($sqlResource && !mysql_errno() && mysql_num_rows($sqlResource)) {
 echo '<table><tr><th colspan="2">Members<br /><br /></th></tr>';
 while($r = mysql_fetch_assoc($sqlResource)) {
 printf('<tr><td>%s</td><td><a href="%s">Click for more info</a></td></tr>', htmlentities($r['name']), $_SERVER['PHP_SELF'] . '?memID=' . $r['memID']);
 }
 }
 }
 
 ?>
 [/code]

Didn't test it, but its mostly there. Also, I should point out that this looks like a 1:1 relationship- you may consider just making a single table and bringing "memberinfo" into "member". Unless you are wanting to store more than 1 piece of "info" about any given member, there is no need for it.

Cheers

@UmuhireauthorAug 10.2011 — #Thanx guys, work like sharp!!!

Webdeveloper.com rux

Relational database

3 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Relational database

3 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

3 Comments(s) _↴

Also in #PHP _↴