Problems displaying user specific data from a MySQL table

[WillUK]

Hi

 

I hope someone can help....although nobody else has been able to offer a solution that has worked, so I'm not holding my breath.

 

I am very new to PHP, yet have been put in a position where I have to develop a web application, which uses an email and password login process.

 

I need to build a script for a particular page that will allow the logged in user to view pieces of data, specific to them, from a MySQL table.

 

Obviously, displaying all of the data in the table will not be appropriate. I need to just have the query run to pull a specific piece of data for display for the user.

 

I have been over and over this script, and the other scripts within the application directory to see where I might be going wrong, but I just cannot find it. And it is getting to the stage, where I don't believe that I can figure it out!! Honestly - it is getting to the 'throw the damn machine through the window' point!

 

Basically, my main areas of concern are that:

1/ I am misunderstanding the use of a session, to access and retrieve user data in a table.

2/ I am misunderstanding the scope of variables, and therefore calling on password/email verification methods within MySQL queries, that will not work, because I have not set up the code or set up the variables properly (or placed them in the right place).

 

The aim of the script that I am trying to build is to retrieve a user 'balance' which is of floating point type, from the SQL table in order for it to be displayed on the user's 'profile page'.

 

I have decided to post 2 scripts: login.php and account_balance.php, so that hopefully you can analyse and let me know what I am doing wrong and where my code needs correcting. The MySQL table structure is as follows:

 

table name: at_client_profile

column(attribute) order: client_id, password, trading_name, ind_type, balance, email

 

I have been working on this script for a week or so, and have used numerous different approaches in my attempt to solve this.

 

Currently I am receiving the following error message when I run the account_balance.php script:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Atkinsons\account_balance.php on line 17, which refers to the 'while($row = mysql_fetch_array($balance))'

 

Here are the scripts:

client_login.php , which I have included because it sets the variables that I am attempting to use in the SQL SELECT query, in order to retrieve specific user data.

 

account_balance.php , which is where the SQL query is located...

 

Start of Login script.

// CHECK  CLIENT LOGIN FORM
if (isset($_POST['submit'])) { //handle the form.

require_once ('inc/mysql_connect.php'); //Connect to the db.

// Create a function for escaping the data.
function escape_data ($data) {
global $dbc; //Need the connection.
if (ini_get('magic_quotes_gpc')) {
	$data = stripslashes($data);
	}
	return mysql_real_escape_string($data);
	} //End of function.

	$message = NULL; //Create an empty new variable.

	//Check for an email.
	if (empty($_POST['email'])) {
	$e = FALSE;
	$message .= '<p> You forgot to enter your email!</p>';
	} else {
		$e = escape_data($_POST['email']);
	}	

	//Check for a password.
	if (empty($_POST['password'])) {
	$p = FALSE;
	$message .= '<p>You forgot to enter your password!</p>';
	} else {
		$p = escape_data($_POST['password']);
	}

	if ($e && $p) { // If email and password have been verified....

	//Retrieve the client_id and trading_name for that username/password combination.
	$query = "SELECT * FROM at_client_profile WHERE email = ('$e') AND password = ('$p')";
	$result = @mysql_query ($query); //Run the query.
	$row = mysql_fetch_array ($result, MYSQL_NUM); //Return a record, if applicable.

	if ($row) {//A record was pulled from the database.

	//Set the cookies/sessions & redirect.
	session_name ('clientlive'); //To set session name.
	session_start();
	$_SESSION['client_id'] = $row[0];
	$_SESSION['password'] = $p;
	$_SESSION['trading_name'] = $row[2];
	$_SESSION['ind_type'] = $row[3];
	$_SESSION['balance'] = $row[4];
	$_SESSION['email'] = $e;

	header ("location: http://" . $_SERVER['HTTP_HOST'] .          dirname($_SERVER['PHP_SELF']) ."./client_profile.php");
	exit(); //Quit the script.

	} else { // No record matched the query.
	$message ='<p> The email and password entered do not match those on file.</p>';
	}

	//mysql_close(); //Close the daabase connection.

	} else {
		$message .='<p> Please try again.</p>';
	}

End of Login Script.

 

 Start of account balance script.

session_name ('clientlive');
session_start();
include ('inc/header.php');
include ('inc/left.php');
include ('inc/footer.php'); //Include the HTML footer.

$query = "SELECT balance FROM at_client_profile WHERE email = ('$e') AND password = ('$p')";
//$query = "SELECT balance FROM at_client_profile WHERE client_id= ".$_SESSION['client_id'].";'"

$result = @mysql_query($query); // You need to have this line to get the data from the db
$balance = mysql_num_rows ($result); //Assigns balance from SQL query to $balance variable.

// if ($balance > 0) {  //If it ran OK, display the records.

while($row = mysql_fetch_array($balance))
{
echo "The balance of your account is".$row['balance'];

echo "<br />";
}

//echo "<p><big> <b> The outstanding balance on your account is $balance </b></big></p>";
//mysql_free_result ($result); //Free up the resources.


//} else {
//	echo '<p> You do not have a balance outstanding. If you wish to enquire further please call a member of staff.</p>'; //If it did not run ok.

//}
mysql_close();

End of account balance script.

 

Other points: The account_balance.php script is linked via another script, account_profile.php

 

I know that this is a tall order, but as I can't figure it out, and I'm pretty new to PHP, I thought the best thing to do was look for some help... ???

 

Thanks

 

 

 

 

 

 

 

[GingerRobot]

First up, i'm just going to direct you to this page of a tutorial i wrote. It'll show you how to make sure those queries are being executed and how to debug them. I suggest you start by applying that.

 

Edit: Second, i also note that, unless defined in those included files, the variables used in this query:

 

$query = "SELECT balance FROM at_client_profile WHERE email = ('$e') AND password = ('$p')";

 

Are undefined. Did you mean to extract them from the $_SESSION array?

[Mad Mick]

With respect to:  while($row = mysql_fetch_array($balance))

 

$balance is the number of rows, this function wants a query resource i.e. $result = @mysql_query($query);

 

Also I'm a bit confused what the general problem is since you have got $_SESSION['balance'] from your first login script