myql php get table values

[proctk]

Below is code that I'm trying to use get data from tables in my database and assign each column to a variable.  The row to get the information from is determined by the username. I used the same code to get information from individual tables and it works fine but for some reason its echoing double enteries for each row.


The biggest change is the query selecting the tables

any help is great
[code=php:0]
<?
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);

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");
$userid = mysql_result($result,$i,"userid");
$age = round(dateDiff("/", date("m/d/Y", time()), $dob)/365, 0);


$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='$spousesex'><b>First Name:</b> $spousefistname<br>
<b>Last Name:</b> $souselastname<br>
<b>DOB:</b> $sousedob<br>
<b>Age:</b> $spouseage<br>
<a href='Updatespouse.php?id=$userid'>Edit</a>
<a href='deleteSpouse.php?id=$userid'>Delete</a>
</p><br>";

$i++;

}

?>

[/code]

[pixy]

Dude, you did it fan more difficult than it needs to be. You can just do something like...

[code]
<?php
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    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>
    <b>Age:</b> $row['spouseage']<br>
    <a href='Updatespouse.php?id=$row['userid']'>Edit</a>
    <a href='deleteSpouse.php?id=$row['userid']'>Delete</a>
    </p><br>";
}
?>[/code]

Whatever is inside the brackets [] is the name of the column you're drawing information from, while $result is your mysql_query() variable.

[proctk]

I get this eror message

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\MyServer\xampp\htdocs\NewStuff\familypages\myFamily.php on line 52

[pixy]

Try this...(make sure that the column names correspond to the ones you have)
[code]
<?php
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    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>
    <b>Age:</b> '.$row['spouseage'].'<br>
    <a href="Updatespouse.php?id='.$row['userid'].'">Edit</a>
    <a href="deleteSpouse.php?id='.$row['userid'].'">Delete</a>
    </p><br>';
}
?>
[/code]

[proctk]

and I drag this on,


error
Parse error: parse error, unexpected '{' in C:\MyServer\xampp\htdocs\NewStuff\familypages\myFamily.php on line 51.

the first line of code is line 51

updated code
[code=php:0]

$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>';
mysql_close();
?>

[/code]

[gizmola]

Yes of course. The error is telling you the problem.  You start a while loop with a '{' and there's no '}' to end the loop.  Should be right before your mysql_close();

[proctk]

I made the changes and I get the  same error message.  I place a } just before mysql_close ().  I double checked the column names, all looks good.

updated code


[code=php:0]
<?
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>';

}
mysql_close();
?>

[/code]

[pixy]

[code]
<?
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>';

}
mysql_close();
?>[/code]

Try that. There was a missing ' mark and some ))) at the end.

[sanfly]

The "Unexpected { " error is because of this line here

[code]while ($row = mysql_fetch_array($result, MYSQL_ASSOC) or die(mysql_error() {[/code]

you need an additional closing bracket after the mysql_error()

eg

[quote]while ($row = mysql_fetch_array($result, MYSQL_ASSOC) or die(mysql_error()[color=red])[/color] {[/quote]

[pixy]

^ You need three at the end: ))).

[proctk]

Hi

It dislays the information but repeats it 6 times when there is only one entery in the database. I have no idea why this is happening, its doing the samething it did when I assigned the columns to variables

[proctk]

I think that I have an idea what is wrong,

It's looking for the username in all tables and when it finds a match it repeats the print the data each time

I  only want each match to print once

[pixy]

You could add LIMIT 1 to the end.

[proctk]

I have sent all ay working on this issue. It turns out my database table design is not great. 

I'm trying to figureout how to create master table to hold myall  my tables primary keys in

assocate table - don't have a clue how to set this up


mytables
about me
aboutme_id(pk)
child_id(fk)
spouse_id(fk)
sibling_id(fk)

any pointers where I can find information on this