Page 1 of 1

Unknown table 'students' in field list error...?

Posted: Fri Dec 17, 2004 8:26 am
by sn202
Using the following code to show transactions taken place by a student along with the balance of their account. But i'm getting a "Unknown table 'students' in field list" error.??

Code: Select all

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

Posted: Fri Dec 17, 2004 9:21 am
by kettle_drum
And you have a table in the database called students? Spelt in the same way?

Posted: Fri Dec 17, 2004 10:05 am
by Weirdan
you're trying to use students table without mentioning it in FROM clause in the second query:

Code: Select all

$sql="SELECT transaction.*, students.studentid FROM transaction
JOIN users
ON transaction.studentid=students.studentid
WHERE students.studentid="$username"";
perhaps it should be:

Code: Select all

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