running 2 SQL queries error!... help needed!

[sn202]

Hi, basically i'm trying to run this script which looks at a mysql database, looking up student records to display the student's balance and transaction history, (script below) problem is i'm getting a parse error when trying to run the second SQL query, which I'm guessing is the problem (running two querys). Any ideas, help will be much appriciated as I don't have long to get this all done (5 hours ish) and my system seems to be falling into an abyss of chaos!

<?php $username = $_POST['username'];
$self =$_SERVER['PHP_SELF'];
$refer =$_SERVER['HTTP_REFERER'];

#connect to MYSQL
$conn = @mysql_connect( "linuxproj", "userid", "password" )
or die( "could not connect" );
#select the specified database
$rs = @mysql_select_db ( "db_sn202", $conn )
or die( "could not select database" );
#create the sql query
$sql="select accountbal from students where username=\"$username\"";
#exercute the query
$rs = mysql_query( $sql, $conn )
or die( "could not exercute query" );

while( $row = mysql_fetch_array( $rs ) )
{
echo( "Balance: " . $row["balance"] ) ;
}
#create the sql query
$sql="SELECT transactions.*, students.studentno FROM transactions
JOIN users
ON transactions.studentno=students.studentno
WHERE students.studentno=\"$username\""
#exercute the query
$rs = mysql_query( $sql, $conn )
or die( "could not exercute query" );

while( $row = mysql_fetch_array( $rs ) )
{
echo( "Balance: " . $row["balance"] ) ."<br>");
}

{ $msq = "Welcome $username to your account summary"; }
?>

[edited by: coopster at 1:06 pm (utc) on Dec. 17, 2004]
[edit reason] generalized connection info [/edit]

[Salsa]

It looks like there's a parse error at the end of your second $sql--missing ;

Also, when doing a nested query like this, I think you'll want to use a second set of variable names, like $rs2, $row2... or you'll be overwriting values that the outside loop still depends on.

When you want to make a script die on a failed mysql function call, take advantage of mysql_error(), like this:

...or die("could not exercute query: ".mysql_error());

...that will tell you why if failed

I hope this helps.

[sn202]

I'm getting a Parse error: parse error in /home/sn202/public_html/student.php on line 32. error. This coincides with the first line of my echo for my second sql query...? any advice?

code:

<?php $username = $_POST['username'];
$self =$_SERVER['PHP_SELF'];
$refer =$_SERVER['HTTP_REFERER'];

#connect to MYSQL
$conn = @mysql_connect( "linuxproj", "sn202", "e1420518" )
or die( "could not connect" );
#select the specified database
$rs = @mysql_select_db ( "db_sn202", $conn )
or die( "could not select database" );
#create the sql query
$sql="select balance from students where username=\"$username\"";
#exercute the query
$rs = mysql_query( $sql, $conn )
or die( "could not exercute query" );

while( $row = mysql_fetch_array( $rs ) )
{
echo( "Balance: " . $row["balance"] ) ;
}
#create the sql query
$sql="SELECT transaction.*, students.studentno FROM transaction
JOIN users
ON transaction.studentno=students.studentno
WHERE students.studentno=\"$username\"";
#exercute the query
$rs2 = mysql_query( $sql, $conn )
or die( mysql_error() );

while( $row2 = mysql_fetch_array( $rs ) )
{
echo( "Transaction ID: " . $row2["transactionid"];
echo( "studentid: " . $row2["studentid"];
echo( "Product id: " . $row2["productid"];
echo( "type of transaction " . $row2["type"];
echo( "value " . $row2["value"]. "<br>");
}

{ $msq = "Welcome $username to your account summary"; }
?>

[sn202]

Now i'm getting a "Unknown table 'students' in field list" error...?

[Salsa]

while( $row2 = mysql_fetch_array( $rs2 ) )

...I guess you already noticed that you weren't closing the parentheses on your echos...

[sn202]

Yeah already spotted that one, its not that...

[Salsa]

You spotted what? The $rs2, too?

[sn202]

yeah, here's my updated code:
<?php $username = $_POST['username'];
$self =$_SERVER['PHP_SELF'];
$refer =$_SERVER['HTTP_REFERER'];
error_reporting(E_ALL);
#connect to MYSQL
$conn = @mysql_connect( "linuxproj", "sn202", "e1420518" )
or die( "could not connect" );
#select the specified database
$rs = @mysql_select_db ( "db_sn202", $conn )
or die( "could not select database" );
#create the sql query
$sql="select balance from students where name=\"$username\"";
#exercute the query
$rs = mysql_query( $sql, $conn )
or die( mysql_error() );

while( $row = mysql_fetch_array( $rs ) )
{
echo( "Balance: " . $row["balance"] ) ;
}
#create the sql query
$sql="select transaction.*, students.studentid FROM transaction
JOIN users
ON transaction.studentid=students.studentid
WHERE students.studentid=\"$username\"";
#exercute the query
$rs2 = mysql_query( $sql, $conn )
or die( mysql_error() );

while( $row2 = mysql_fetch_array( $rs2 ) )

{
echo( "Transaction ID: " . $row2["transactionid"] );
echo( "studentid: " . $row2["studentid"] );
echo( "Product id: " . $row2["productid"] );
echo( "type of transaction " . $row2["type"] );
echo( "value " . $row2["value"]. "<br>");
}

{ $msq = "Welcome $username to your account summary"; }
?>

Can u see wots wrong with it? i'm getting error: "Unknown table 'students' in field list"

[coopster]

You haven't specified the students table in your query, but yet you are referring to columns from that table in your second query...

$sql="select transaction.*, students.studentid FROM transaction 
JOIN users 
ON transaction.studentid=students.studentid 
WHERE students.studentid=\"$username\"";