query based on $_POST[] not working

ejwf

Hello

I am having problems with simple querys that are dependant on results from $_POST[].

I have set the variable $Client based on

$Client = $_POST[client];

$_POST[client]; come from a query on previous pages, it isn't actually typed in by the user it comes from a drop down menu list.
I know that this is OK as it will echo out.

The problem is the query based on this variable - it doesn't give an error, but nor will it output anything when I echo it out.

The query is ;

$query = "SELECT ClientId FROM tblClient WHERE CoName = '$Client'";
$result = mysql_query($query)
	or die (mysql_error());
$row = mysql_fetch_array($result);
$ClientId = $row["ClientId"];

Am I missing something simple?
Can anyone see what is wrong?

regards

MrPixel

Probably asking the obvious, but do you have any rows in your table where column CoName equals the given value?

Try printing out $query to make sure it actually is what you think it is ...

ejwf

Firstly, Yes there is a record where CoName does = $Client.
I took no offence at being asked this as sometimes it is the most obvious thing that I have missed ! But I had already checked that one!

I have tested $query

$query = "SELECT ClientId FROM tblClient WHERE CoName = '$Client'";
$result = mysql_query($query)
	or die (mysql_error());
//$row = mysql_fetch_array($result);
//$ClientId = $row["ClientId"];

print_r ($result);

I was exepcting the output
4
which is the ClientId for the company I am using in the test, but instead I got
Resource id #5

I have no idea what this is - do you?

===
I have since tried

$query = "SELECT ClientId FROM tblClient WHERE CoName = '$Client'";
$result = mysql_query($query)
	or die (mysql_error());
$row = mysql_fetch_array($result);
//$ClientId = $row["ClientId"];

print_r ($row);

and I get no output at all.

this is driving me nuts![/b]

thanks

MrPixel

Originally posted by ejwf
I was exepcting the output
4
which is the ClientId for the company I am using in the test, but instead I got
Resource id #5

I have no idea what this is - do you?

The function mysql_query() returns a resource. You can't print_r() it , it's a not a printable data type.

There's three things I see that I don't think are the problem, but .... you never know:

$Client = $POST[client];
//The constant 'client' is probably not defined, so that should be this, instead:
$Client = $POST['client'];
//Yes it usually works without quotes - but syntactically, it's incorrect. 

//Double quotes in SQL variable tests are slightly safer, and in my experience cause no problems.
//Also adding a database name (important), and backticks on table names (not so important), avoids some problems.
$query = 'SELECT ClientId FROM dbName.`tblClient` WHERE CoName = "' . $Client . '"';

Come to think of it, I made the assumption that you've already made a connection to the database with mysql_connect(), and subsequently selected a database with mysql_select_db(). Have you?

ejwf

thanks for taking the time to look at this. Going through your reply point by point

Yes I am connected to the database!

I put quotes around this so it now reads;

 $Client = $POST['client'];

got correct / expected output when I echoed $Client

so keeping that in place I then tried your second suggestion for the $query changing it to;

$query = 'SELECT ClientId FROM `tblClient` WHERE CoName = "' . $Client . '"';
$result = mysql_query($query)
	or die (mysql_error());
$row = mysql_fetch_array($result);
$ClientId = $row["ClientId"];

echoed out $ClientId - got nothing

any more thoughts?

I really am at a loss with this and need to get it sorted very quickly (under BIG pressure from bossman!)

TIA

MrPixel

You missed the database name in the query ... or did you try that?

And/Or, did you select your database with mysql_select_db()?

I always do both.

Hey ... I just noticed $POST is not the superglobal that POST variables are stored in. Should be $_POST ... though you did say you're getting what you thought you should ... hmmm...

Just to be sure your query is what I think it is, I sometimes echo out the query string instead of running it ...

Any data validation going on?

Scope issues? I mean, is the query going on inside a function while $Client is outside it?

ejwf

thanks

the $POST was my typo - I have got $_POST in the code.

Using your suggestion I have selected the the database

mysql_select_db(db_name);

Plus I have added the database name into the query

$query = 'SELECT ClientId FROM db_name.`tblClient` WHERE CoName = "' . $Client . '"';
$result = mysql_query($query)
	or die (mysql_error());
$row = mysql_fetch_array($result);
$ClientId = $row["ClientId"];

Still nothing happening - I echo out $ClientId and get nothing
No error message - nothing

Can you explain a bit more about scope - I don;t get what you mean...........

thanks & regards

MrPixel

Scope: variables that are created inside a function are not available outside it, and neither are variables that are created outside it available inside it. For a simple example:

$my_nick = 'MrPixel';
function get_my_nick() {
    return $my_nick;
}
print get_my_nick();

This will print nothing because as far as the function get_my_nick() is concerned, the variable $my_handle is completely new, and thus has no value; it is outside the function scope. You can change this by using the global keyword:

function get_my_nick() {
    global $my_nick;
    return $my_nick;
}

I asked about this because it looks like you are getting an empty result set. This is also why I suggested printing out the whole query string, because then you'll see what query is actually being sent to the database, and get some clues why it might be returning an empty result set.

ejwf

thanks for your reply

I have kind of come at the problem from a different angle.
I have gone back a couple of pages in my code to where I can set the variable $ClientId and taken it foward using hidden fields in the forms.
I can now set the variable for the code where I need it by using

$ClientId = $_POST['ClientId'];

I have echoed it all out and it works.

HOWEVER as always is the case with me and php I solve one problem and come to another ......
I was setting this variable to use as a WHERE clause in a DELETE function - and can I get the record to delete? - can I heck !?!

If you fancy having a look for me the whole code is as follows;

session_start ();					
$_SESSION['username'] = $username;	 	
require_once ('../mysql_connect.php');							
mysql_select_db(db_name);

$User = $_POST['user'];
$Client = $_POST['client'];
$ClientId = $_POST['clientId'];

$sql = "DELETE FROM `tblUser` WHERE CName = '$User' AND ClientRef = '$ClientId'";
$result = mysql_query($sql)
or die(mysql_error());

echo '<div align="center">
  The user has been deleted from the system.<br>
</div>';

MrPixel

That page that submits to the delete script ... does it have both user and client id hidden in the form?

btw, this method is really insecure ... validate those $_POST variables before using them. Another precaution is to add a LIMIT clase to your delete query.

ejwf

thanks

only the ClientId is a hidden field
User is selected from a drop down box