selecting from multiple tables

[kproc]

Hi

I'm trying to use information in may tables, each table has a common column 'username'. I want to use the data in each table that matches the user name.

the colde below gives this error message
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 ' username = 'proctk', username = 'proctk', username = 'proctk', username = 'proc' at line 1

$username = $_SESSION['username'];

$query= ("SELECT * FROM children, spouse, sibling, aboutMe, users WHERE username = '$username', username = '$username', username = '$username', username = '$username', username = '$username'");

$result = mysql_query($query) or die(mysql_error());

$num = mysql_numrows($result);

mysql_close();


$i=0;

while ($i < $num) { 

[sameer.net.in]

for selecting from tables which match ALL the conditions

$query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");

for checking each table use seperate queries

[aussie girl]

Well firstly it's not a good way to design a database...
Secondly you have 5 tables so you need four joins to connect those tables to start with..

SELECT * FROM children, spouse, sibling, aboutMe, users 
WHERE tablename.username = anothertablename.username
AND tablename.username = anothertablename.username
AND tablename.username = anothertablename.username
AND tablename.username = anothertablename.username
AND tablename.username ='$username';

[kproc]

thanks for the help you just taugh me something new.

[kproc]

I ran into a little snag. It looks like its looping throuhg twice as it doubles each entery.

include 'db.php'; 


$username = $_SESSION['username'];

$query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");

$result=mysql_query($query) or die(mysql_error());

$num=mysql_numrows($result);

mysql_close();


$i=0;

while ($i < $num) {

$fistname = mysql_result($result,$i,"childfirstname");
$lastname = mysql_result($result,$i,"childlastname");
$dob = mysql_result($result,$i,"childdob");
$sex = mysql_result($result,$i,"childsex");

$momFirstName = mysql_result($result,$i,"momFirstName");
$momLastName = mysql_result($result,$i,"momLastName");
$momDob = mysql_result($result,$i,"momDob");
$dadFirstName = mysql_result($result,$i,"dadFirstName");
$dadLastName = mysql_result($result,$i,"dadLastName");
$dadDob = mysql_result($result,$i,"dadDob");
$anniversaryDate = mysql_result($result,$i,"anniversaryDate");
$maddenName = mysql_result($result,$i,"maddenName");

$siblingfistname = mysql_result($result,$i,"siblingfirstname");
$siblinglastname = mysql_result($result,$i,"siblinglastname");
$siblingdob = mysql_result($result,$i,"siblingdob");
$siblingsex = mysql_result($result,$i,"siblingsex");

$spousefistname = mysql_result($result,$i,"spousefirstname");
$spouselastname = mysql_result($result,$i,"spouselastname");
$spousedob = mysql_result($result,$i,"spousedob");
$spousesex = mysql_result($result,$i,"spousesex");


echo "<p class='$sex'><b>First Name:</b> $fistname<br>
<b>Last Name:</b> $lastname<br>
<b>DOB:</b> $dob<br>
<b>Age:</b> $age<br>
<a href='UpdateChildren.php?id=$userid'>Edit</a>
<a href='deleteChild.php?id=$userid'>Delete</a>
</p><br>";

$i++;

} 

[aussie girl]

include 'db.php';


$username = $_SESSION['username'];

$query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");

$result=mysql_query($query) or die(mysql_error()); 

//use the mysql_fetch_array function and test if variables are getting assigned
// keep using the $row variable for any other column info you need printed
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
 echo $row['fistname'];
 echo $row['lastname'];
 echo $row['dob'];
 echo $row['age'];
 
}


mysql_close(); 

[kproc]

I tried the code that you provided but nothing is printed on one screen. I get no error messages, just a blank screen.

In some tables there could be multiple enteries that will be printed as the username could be found more then once in the table.

In my example where I assign everything to variables it works, but creates double enteries for each item that matches the username.


The code that posted above works great when i reference on one table.

[aussie girl]

I tried the code that you provided but nothing is printed on one screen. I get no error messages, just a blank screen.

In some tables there could be multiple enteries that will be printed as the username could be found more then once in the table.

In my example where I assign everything to variables it works, but creates double enteries for each item that matches the username.


The code that posted above works great when i reference on one table.

These should be the name of your columns..change them if you have to

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['childfistname'];
echo $row['childlastname'];
echo $row['childdob'];
echo $row['childage']; 
} 

[kproc]

Thanks for the help,

this displays the information but it repeats it 6 times. there is only one cloumn in table 'spouse' that should be printed.

its doing the same that that was happening when I first started this post

any ideas why

thankyou for your help

<?
include 'db.php'; 

$username = $_SESSION['username'];

$query = ("SELECT * FROM children, spouse, sibling, aboutMe, users WHERE children.username = '$username' AND spouse.username = '$username' AND sibling.username = '$username' AND  aboutMe.username = '$username' AND users.username = '$username'");

$result = mysql_query($query) or die(mysql_error());

$num = mysql_numrows($result);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC) or die(mysql_error())) {
    echo '<p class='.$row['spousesex'].'><b>First Name:</b> '.$row['spousefirstname'].'<br>
    <b>Last Name:</b> '.$row['spouselastname'].'<br>
    <b>DOB:</b> '.$row['spousedob'].'<br>
    </p><br>';
    
    }

?>

[aussie girl]

Without really knowing how your tables are set up and how they relate to each other is hard to say. I can only suggest that it's your database design and having the username in all of the tables is giving duplicate values. Have you tried testing that SQL in MySQL just by itself, does it give you the same results?

[kproc]

Each table has a column called 'username' I did this so that I could link each table as needed. Each table has a various number of columns all set up the same way. There was a comment that my database design was not great.

would there be a web site or tutorial to sho me a better way of doing it.

What I'm trying to do is create a summary page for all the users information from all the tables.

[aussie girl]

Each table has a column called 'username' I did this so that I could link each table as needed. Each table has a various number of columns all set up the same way. There was a comment that my database design was not great.

would there be a web site or tutorial to sho me a better way of doing it.

What I'm trying to do is create a summary page for all the users information from all the tables.

Well if you do a search on entity relationships and normalisation you should get some websites..

But here's a quick tutorial...If we take four(4) of you tables and only put the information about that entity (table) and nothing else

children
child_id(pk)
childfirstname
childlastname
childdob


sibling
sibling_id(pk)
sibfirstname
siblastname
etc
etc

spouse
spouse_id(pk)
spousefirstname
spouselastname
etc
etc

users
user_id(pk)
userfirstname
userlastname
username
etc
etc

These tables should not have any information from any other table in them only information about them...
Then there is the last table..this is the assocate table that links all the other tables using the column _id's

aboutMe
aboutme_id(pk)
child_id(fk)
spouse_id(fk)
sibling_id(fk)
any
other
info

Can you see how there is no dulicated data only the primary key(pk) and foreign key(fk) and that;s how you establish the relationships.. but there are more detailed websites out there I'm sure

[kproc]

Thank you for the help. I'm working at fixing up my table. I hit a snag and not shure how to create the assoiate table. did some google seaching but not getting anything that explains in simple terms.

thank you for the help

[aussie girl]

Thank you for the help. I'm working at fixing up my table. I hit a snag and not shure how to create the assoiate table. did some google seaching but not getting anything that explains in simple terms.

thank you for the help

What kind of snag? An associate table is just a table that has the links to the other tables like the aboutMe table above..There's are also called relationship tables..http://www.umsl.edu/~sauter/analysis/er/er_intro.html
You also need to make sure that the foreign key columns that you set up have to have the same data type and size as the primary key in the original table. Looking at those tables I would get rid of the spouse table that information can be added to the users table since people wouldn't have multiple spouses..

[kproc]

I have a basic understanding of the concept. writeing the code to make sure that the PK is add when data is entered into each table I cannot figure out