Trouble pulling an integer from MySQL for display!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Trouble pulling an integer from MySQL for display!!

Post by WillUK »

Hiya

I'm building an application that requires users to be able to see their account balances. The respective data (integers) are stored in a MySQL table.

The code I am using is as follows:

Code: Select all

 
 
session_start();
include ('inc/header.php');
include ('inc/left.php');
include ('inc/footer.php'); //Include the HTML footer.
 
$balance = "SELECT balance FROM at_client_profile WHERE email = ('$e') AND password = ('$p')";
$result = mysql_num_rows ($balance); //Assigns balance from SQL query to $result variable.
 
if ($result > 0) {  //If it ran OK, display the records.
 
    echo "<p><big> <b> The outstanding balance on your account is $result </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 pleace call a member of staff.</p>'; //If it did not run ok.
    
}
mysql_close();
if(isset($_SESSION['client_id']) AND ($_SERVER['PHP_SELF'] -10) != 'client_profile.php') { //Had to edit the call to '(subs($_SERVER['PHP_SELF'], as was regarded as being an undefined function. Reverted to ($_SERVER[PHP_SELF'] amd logout notation was implemented on header
        echo '<a href="client_profile.php">Return to my Client Area<br /></a>';
        } else {
        echo '<a href="client_login.php"></a>';
        }
 
The error message I am getting is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Atkinsons\account_balance.php on line 10

Do I need to include the 'require_once ()'? Am I missing an 'include ()' at the top of the script??...

Any help would again be appreciated :)
Last edited by WillUK on Thu Jan 15, 2009 10:13 am, edited 1 time in total.
Cirdan
Forum Contributor
Posts: 144
Joined: Sat Nov 01, 2008 3:20 pm

Re: Trouble pulling an integer from MySQL for display!!

Post by Cirdan »

Firstly, you need to put php tags around your code so it is easier to read.

Add this before the mysql_num_rows

Code: Select all

 
$result = mysql_query($balance); // You need to have this line to get the data from the db
 
mysql_num_rows only returns the number of rows that the query returned...not the actual data. Look up mysql_fetch_array or mysql_fetch_assoc
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

I'm sorry...I didn't miss them out intentionally...

Thanks for your help...I'll implement your suggestions :)
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

I've followed the advice given, but with no success I'm afraid....Although there was a slight improvement: No error messages!!

However, the message generated was as per the echo statement in the script: 'You do not have an outstanding balance....etc'

This initially appeared to be working until I tested it on a user that does have a balance greater than 0 (as per the 'at_account_profile' table). Unfortunately, the same echo message still appeared each time...

I then changed the script a little to make it simpler, and decided to get rid of the echo statement mentioned above because it seems to be unecessary....
But, the underlying problem still persists, except now the echo message generated each time is:
' The outstanding balance on your account is 0 '

As was the case before, this message is generated, even for users with balances greater than 0.

Code: Select all

 
 
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')";
$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.
 
 
    echo "<p><big> <b> The outstanding balance on your account is $balance </b></big></p>";
    mysql_free_result ($result); //Free up the resources.
    
mysql_close();
if(isset($_SESSION['client_id']) AND ($_SERVER['PHP_SELF'] -10) != 'client_profile.php') { 
        echo '<a href="client_profile.php">Return to my Client Area<br /></a>';
        } else {
        echo '<a href="client_login.php"></a>';
        }
 
 
Could it be an SQL issue perhaps??
Last edited by WillUK on Thu Jan 15, 2009 5:58 am, edited 4 times in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trouble pulling an integer from MySQL for display!!

Post by califdon »

You will find very few people willing to try to read your code because you have not placed your code example within [syntax=php]and[/syntax] tags to make them more readable. Please return to your original post and edit it.
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

hmm...this is getting more interesting by the second....Ok, I've edited the original script, so that it now reads as follows:

Code: Select all

 
 
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')";
$result = mysql_query($query); // You need to have this line to get the data from the db
 
echo "<p><big> <b> The outstanding balance on your account is $result </b></big></p>";
mysql_free_result ($result); //Free up the resources.
    
mysql_close();
if(isset($_SESSION['client_id']) AND ($_SERVER['PHP_SELF'] -10) != 'client_profile.php') { 
        echo '<a href="client_profile.php">Return to my Client Area<br /></a>';
        } else {
        echo '<a href="client_login.php"></a>';
        }
 
But now I am getting the following error message:

'The outstanding balance on your account is Resource id #8'

Does this make any sense to anybody???
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trouble pulling an integer from MySQL for display!!

Post by califdon »

I know this is confusing to someone just beginning to work with PHP and a database. You're missing a vital step. Here's how I suggest you think of the process:
  1. Connect to the database server: mysql_connect('host','user','pwd');
  2. Select a particular database at that server: mysql_select_db('dbname');
  3. Send a query to the database that you've connected to and selected, and assign the result to a "resource" (in effect, a pointer to an array of rows from the database): $result=mysql_query("SELECT * FROM `tablename` WHERE `this`='that'); (note that you can use any variable name you want, but $result is very commonly used)
  4. All you have at this point is the name of an array, so you can't just echo that, you have to fetch each row and do whatever it is you're going to do with it (in general, it may be many rows). There are numerous ways to do this, but just to show you one common method:
    while($row=mysql_fetch_array($result)) {
    echo "0 = " . $row[0] . " -- 1 = " . $row[1] . "<br />";
    }
What you're missing is that fetch step. Thus, what prints out is just the fact that it's a resource.
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

Thanks Califdon.
I am pretty sure that the connection to the database is sound....I know that because the login script that I have coded works just fine. I am also able to send data to the database.
So I am 99% sure that everything in that respect is correct.

I'll implement the fetch step as you recommend; or I'll least try to implement it, and then see if that resolves the issue.

It is frustrating, for me as a newbie...but then I guess it's even more frustrating for experienced coders such as yourself having to explain all this basic stuff to countless numbers of novices.

Anyway...fingers crossed...

I appreciate your help :)
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

I've composed the script yet again, taking into account the fetch array....But I'm still having trouble...
I'm getting the following error message:
Parse error: syntax error, unexpected '{' in C:\wamp\www\Atkinsons\account_balance.php on line 18

This doesn't seem to make sense, because the parentheses appear to be in the right places....

Code: Select all

 
 
session_name ('clientlive');
session_start();
include ('inc/header.php');
include ('inc/left.php');
include ('inc/footer.php'); //Include the HTML footer.
 
require_once ('inc/mysql_connect.php'); // Connect to the database.
$query = "SELECT * FROM at_client_profile WHERE email = ('$e') AND password = ('$p')"; 
[color=#80FF00]//where variables $e and $p have been set in the login.php file contained within the same directory.
[/color]
 
$result = mysql_query($query) or die(mysql_error()); // You need to have this line to get the data from the db
while($row = mysql_fetch_array($result)
{
echo $row['trading_name']."-".$row['balance'];
echo "<br />";
}
 
mysql_free_result ($result); //Free up the resources.
mysql_close();
 
 
Thanks again, for your help...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trouble pulling an integer from MySQL for display!!

Post by califdon »

The error message tries to spot where the error occurs, but often (as in this case) the error actually is much earlier, but it is of a nature that the parser doesn't recognize it until later. Take a look at the code you just posted. Notice the way our forum BBCode parser colors the syntax around line 11 (in our display). Right after the line that defines the SQL string $query. What are those next 2 lines supposed to be? They are certainly not PHP. That's the problem. The parser didn't know what they are, either, but it bravely tried to figure it out for a few more lines, when it finally ran across a bracket (not a parenthesis, by the way) that didn't fit, so it threw the error.

I can't even think of a syntax that would look like . That's not HTML, it's not CSS, I don't where it came from, and one thing is certain, it's not PHP, which is why it doesn't work. First of all, just remove those 2 lines and see if the rest of the script runs. Later you can come back and try to figure out what you were trying to do there.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: Trouble pulling an integer from MySQL for display!!

Post by Stryks »

I think the tags might actually be from these forums. I mean, it fits the format. Perhaps it doesn't work when nested inside code tags.

If they are in your actual code though, you'll have to remove them, as advised by califdon.

I'd also advise you to take a look at line 15. califdon is dead on when he says that the the error messages try to guess the location of the problem, but often passes the problem by before it identifies an issue. It has instead picked up the next line, where indeed, the { is unexpected.

Line 15 holds your answer.
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

You are right - those two lines of unusual code were generated by this forum....I was trying to highlight the comment that I had made...But I have removed now.

I've since re-run the script, after adding an extra closing bracket at the end of the line here:

while($row = mysql_fetch_array($result))

I'd missed it out.

But now the code, while processing without tangible error, is rendering a blank page i.e. as if the echo statement has had no effect at all...I wonder if I should use an error handler...

Do you think that maybe my SQL query line is invalid? i.e. WHERE email = ('$e') AND password = ('$p')
Maybe the query cannot find the data for the particular user who is signed in.....

I'll keep playing around and see what I can find out....

Cheers
WillUK
Forum Newbie
Posts: 24
Joined: Fri Nov 21, 2008 11:08 am

Re: Trouble pulling an integer from MySQL for display!!

Post by WillUK »

Just to let you know, I've solved the problem.

It was related to the session variables that I hadn't declared in entirety, or included correctly.
The syntax I was using, was pretty much spot on :)

Thanks anyway.
Post Reply