Topic awaiting preservation: PHP/MySQL - getting info from multiple tables (Page 1 of 1)

I'm trying, but it's not working...

$Data = mysql_db_query("ixifx_net","SELECT * FROM board WHERE topic='$page'");

	while ($AA = mysql_fetch_array($Data)) {

		$pageID = $AA["pageID"];

		$postdate = $AA["postdate"];

		$userID = $AA["userID"];

		$message = $AA["message"];

		$userData = mysql_db_query("ixifx_net","SELECT * FROM members WHERE userID='$userID'");

		$BB = mysql_fetch_array($userData, MYSQL_ASSOC);

		$postcout = $BB["postcount"];

		echo "			<hr width=\"100%\">\n";

		echo "			<h2>\n";

		echo "				$userID\n";

		echo "			</h2>\n";

		echo "			<h3>\n";

		echo "				$postdate\n";

		echo "			</h3>\n";

		echo "			$message\n";

		echo "			<h3>\n";

		echo "				$postcount posts\n";

		echo "			</h3>\n";

	}

the code in action

everything looks right when I look at it, so I guess what I need as a fresh set of trained eyes to look at it.

any help is, as always, greatly appreciated



I focus on the pain; The only thing that's real (Trent Reznor)

I guess I should have titled this one "Asylum color scheme helps to find errors"

quote:

---

I X I said:

$postcout = $BB["postcount"];

---

should have been $postcount =

I just didn't see it until I posted it here...



You could stand me up at the gates of hell, but I won't back down (Tom Petty)

you probably want to be using php->mysql_query instead of php->mysql_db_query. The former is quite a bit faster.

Also, read up on mysql->Join. Even a very simple join would get you what you want in one query.

so long,

->Tyberius Prime

thanks for the info TP

I gotta say, after doing the simple things I have going on with my page, it makes me really appreciate the amazing work you and the others do/have done in getting this place up and keeping it running.



I know the pieces fit, 'cause I watched them fall away (Maynard J. Keenan)

$Data = mysql_db_query("ixifx_net","SELECT * FROM board, members WHERE topic='$page' ORDER BY postID ASC");

while ($AA = mysql_fetch_array($Data)) {

	$pageID = $AA["pageID"];

	$postdate = $AA["postdate"];

	$userID = $AA["userID"];

	$message = $AA["message"];

	$postcount = $AA["postcount"];

	$sig = $AA["sig"];

	echo "			<hr width=\"100%\">\n";

	echo "			<h2>\n";

	echo "				$userID\n";

	echo "				&nbsp;&nbsp;$postcount posts\n";

	echo "			</h2>\n";

	echo "			<h3>\n";

	echo "				$postdate\n";

	echo "			</h3>\n";

	echo "			$message\n";

	echo "			<br>\n";

	echo "			$sig\n";

}

this code is giving me some pretty strange output, which can be seen here or at any of the other pages which can be reached via the "back" link at the bottom of the page.

the problem is that it's repeating every post by each other member... I had trouble following the stuff at mysql.com so I just jumped in and gave it a whirl. it is close in that I have one query that does indeed get the info from both tables. I gathered that there are inner, left and right join but I have no clue exactly which one I need to use. if somebody could explain to me which one and why it'd be a great deal help.



I know the pieces fit, 'cause I watched them fall away (Maynard J. Keenan)

instead of starting a new topic, I'll just ask in here...

http://www.ixifx.net/members.php?page=edit&userID=guest

the password for the guest account is "password" w/o quotes, if you're interested in testing it

case "edit":

	$SQL = mysql_connect("server", "user", "pass");

	mysql_select_db("ixifx_net") or die("Unable to select database");

	$Data = mysql_db_query("ixifx_net","SELECT * FROM members WHERE userID='$userID'");

	$AA = mysql_fetch_array($Data, MYSQL_ASSOC);

	$password = $AA["password"];

	$joined = $AA["joined"];

	$from = $AA["from"];

	$about = $AA["about"];

	$sig = $AA["sig"];

	if($REQUEST_METHOD == "POST"){

		$valid = true;

		if(!trim($pass)){

			$errString .= '<font color="#FF0000"> You must enter a Password.</font><br>';

			$valid = false;

		}

		if($valid == false){

			echo "<h2> $errString </h2>\n";

		}else{

			if ($pass == $password) {

				$ins = "UPDATE members SET from='$newfrom', about='$newabout', sig='$newsig' WHERE userID='$userID '";

				if (mysql_db_query("ixifx_net",$ins)){

					$return = 'Edit Complete';

					$errString = 'Thank you for posting';

				}else{

					$return = 'Edit Failed';

					$errString .= mysql_error();

				}

			}else{

				$errString .= 'Could not log in';

			}

			echo $return;

			echo "<br>";

			echo $errString;

			echo "<br>";

			echo "<a href=\"members.php?page=view&userID=$userID\">Back</a>";

		}

	}else{

		?>

		<form action="members.php?page=edit&userID=<? echo $userID ?>" method="post">

		<table width="100%">

			<tr>

				<td width="30%">User ID:</td>

				<td align="right">

					<? echo $userID ?>

				</td>

			</tr>

			<tr>

				<td>Joined:</td>

				<td align="right">

					<? echo $joined ?>

				</td>

			</tr>

			<tr>

				<td>From:</td>

				<td align="right">

					<input type="text" name="newfrom" value="<? echo $from ?>" size="36" />

				</td>

			</tr>

			<tr>

				<td valign="top">About:</td>

				<td align="right">

					<textarea name="newabout" cols="32" rows="4"><? echo $about ?></textarea>

				</td>

			</tr>

			<tr>

				<td valign="top">Sig:</td>

				<td align="right">

					<textarea name="newsig" cols="32" rows="3"><? echo $sig ?></textarea>

				</td>

			</tr>

			<tr>

				<td>Password:</td>

				<td align="right">

					<input type="password" name="pass" value="" size="16" />

				</td>

			</tr>

			<tr>

				<td align="right" colspan="2">

					<input type="submit" value="Submit" />

				</td>

			</tr>

		</table>

		</form>

		<?

	}

break;

the error I'm getting looks like this:

quote:

---

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 'from='', about='123', sig='I don\'t belong here' WHERE userID='

---

and it happens when I try to update the user profile. the query is on line 21 of the code posted above

I've tried changing variable names for fear I was using a reserved variable, I've tried toying with the spacing and quotes, I've looked for examples of working code, but all I kept getting was the syntax copied verbatim from www.mysql.com (which I've said before is overly complicated for a country bumpkin like myself). I'm not sure what in the world I'm doing wrong, and I've asked several people for help via IM and nobody could seem to find my error.

so, please, what am I doing wrong?



Life is too short, so love the one you got (Bradley Nowell)

you'll need to print the full query here before I can tell you anything about it.
just print($query); before you mysql_query(), then show it here.
Life's too short to read all your code ;-)

oh, that's nowhere near all my code, TP just one page out of dozens I've been working on since I started php

zavaboy took some time out this afternoon to help me with it all one-on-one, for anybody interested it was

$ins = "UPDATE members SET from='$newfrom', about='$newabout', sig='$newsig' WHERE userID='$userID '";

should have been

$ins = "UPDATE members SET `from`='$newfrom', about='$newabout', sig='$newsig' WHERE userID='$userID'";

two tiny errors caused me about 6 hours of grief, and what's worse is that I tried 'from' but didn't think to use ` instead of '

anyway, thanks for thinking about helping.
maybe next time ,



Life is too short, so love the one you got (Bradley Nowell)