Jump to content


Photo

myql php get table values


  • Please log in to reply
13 replies to this topic

#1 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 29 July 2006 - 08:49 PM

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
<?
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++;

}

?>



#2 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 29 July 2006 - 09:03 PM

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

<?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>";
}
?>

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

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#3 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 29 July 2006 - 09:14 PM

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

#4 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 29 July 2006 - 09:23 PM

Try this...(make sure that the column names correspond to the ones you have)
<?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>';
}
?>

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#5 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 29 July 2006 - 09:38 PM

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

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



#6 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 29 July 2006 - 09:45 PM

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();

#7 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 29 July 2006 - 09:49 PM

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


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



#8 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 29 July 2006 - 11:11 PM

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

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

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#9 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 29 July 2006 - 11:22 PM

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

while ($row = mysql_fetch_array($result, MYSQL_ASSOC) or die(mysql_error() {

you need an additional closing bracket after the mysql_error()

eg

while ($row = mysql_fetch_array($result, MYSQL_ASSOC) or die(mysql_error()) {


If you're not part of the solution, you're part of the precipitate

#10 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 29 July 2006 - 11:37 PM

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

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#11 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 30 July 2006 - 12:54 AM

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

#12 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 30 July 2006 - 01:34 AM

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

#13 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 30 July 2006 - 03:53 AM

You could add LIMIT 1 to the end.

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#14 proctk

proctk
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts

Posted 30 July 2006 - 04:18 AM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users