Selecting individual records in mySQL

[BOMEz]

I'm trying to check a user name in a database and get it into a variable so I can perform later checks on it. Only problem is that for some reason when I use a variable in the SQL command, I get back nothing.

$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE 'John' ";
 
// Perform the query
$result = mysql_query($query) ;
 
$name = mysql_result($result,0,"user_name");
 
echo $name;

The code above gives me back the user name entered in the SQL select query, but when I try to use a $_POST variable in place of 'John' in there I get nothing. I've also tried putting the $_POST var into a new variable, but to no avail.

Can someone kindly show me where I'm going astray?

[hsriat]

Can you tell how did you try that with $_POST['variable']?

[TheServant]

Yeah, that won't work because you are probably opening and closing MySQL statements unintentionally when you replace John with $_POST['name']. You should declare it first and avoid any confusion:

 $input_name = $_POST['name'];
 
$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE '$input_name' ";
 
// Perform the query
$result = mysql_query($query) ;
 
$name = mysql_result($result,0,"user_name");
 
echo $name;

I'm not sure if you were saying you have tried this, but just check you have it all like I have it.

[BOMEz]

Yeah, that won't work because you are probably opening and closing MySQL statements unintentionally when you replace John with $_POST['name']. You should declare it first and avoid any confusion:

 $input_name = $_POST['name'];
 
$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE '$input_name' ";
 
// Perform the query
$result = mysql_query($query) ;
 
$name = mysql_result($result,0,"user_name");
 
echo $name;

I'm not sure if you were saying you have tried this, but just check you have it all like I have it.

I do have a variable set to the $_POST value higher up just as above.

@ hsriat: to use the post value I just replace John with $_POST['user_name']

[dlite922]

I'm trying to check a user name in a database and get it into a variable so I can perform later checks on it. Only problem is that for some reason when I use a variable in the SQL command, I get back nothing.

$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE 'John' ";
 
// Perform the query
$result = mysql_query($query) ;
 
$name = mysql_result($result,0,"user_name");
 
echo $name;

The code above gives me back the user name entered in the SQL select query, but when I try to use a $_POST variable in place of 'John' in there I get nothing. I've also tried putting the $_POST var into a new variable, but to no avail.

Can someone kindly show me where I'm going astray?

Add a mysql Error Number with it and print the SQL like so then give us the result:

 
$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE 'John' ";
 
// Perform the query
$result = mysql_query($query)  or die(mysql_error() . "\nThe SQL is: " . $query);
 
$name = mysql_result($result,0,"user_name");
 
echo $name;

that way when the query fails, it will halt and print the error and what the SQL was.

[TheServant]

I do have a variable set to the $_POST value higher up just as above.

Are you sure in the MySQL statement you have inverted commas around teh variable like I have? If you echo your $_POST['input_name'] variable does it exist?

[ronverdonk]

@ hsriat: to use the post value I just replace John with $_POST['user_name']

When this is true the statement will never work, because the WHERE clause will become

WHERE `user_name` LIKE '$_POST['user_name']' ";
 

and that is incorrect. But why are you reluctant to show your code and keep us guessing? Just show the code you did using the $_POST variable in the WHERE clause and I mean the code, not a description.

Ronald

[BOMEz]

When this is true the statement will never work, because the WHERE clause will become

WHERE `user_name` LIKE '$_POST['user_name']' ";
 

and that is incorrect. But why are you reluctant to show your code and keep us guessing? Just show the code you did using the $_POST variable in the WHERE clause and I mean the code, not a description.

Ronald

Below is all th code I have in the file:

<?php
session_start();
 
$user_login = $_POST['user_name'];
$pass_login = $_POST['password'];
 
echo $_POST['user_name']; // Post value echos just fine
echo "<br>";
echo $pass_login; // Post value stored in variable echos just fine
echo "<br>";
 
$user = myuser_name_dot ;  //database user name
$pass = my_pass; // database password
$db = my_database; // name of database
 
mysql_connect(localhost,$user,$pass); // connects to database
@mysql_select_db($db) or die ("Could not connect"); //selects database
 
$query = " SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE '$user_login' ";
 
// Perform the query
$result = mysql_query($query) or die(mysql_error() . "\nThe SQL is: " . $query); ;
 
$name = mysql_result($result,0,"user_name");
 
echo $name;
 
?>

The error I am getting is :

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 2 in /home/mysite/public_html/ravey/login_check.php on line 26

[TheServant]

mysql_connect(localhost,$user,$pass); // connects to database
@mysql_select_db($db) or die ("Could not connect"); //selects database
 
// Perform the query
$result = mysql_query(" SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE '$user_login' ") or die(mysql_error() . "\nThe SQL is: " . $query);
$name = mysql_fetch_array($result);
echo $name['user_name'];
?>

Try that. I basically used msql_fetch_array rather than mysql_result so that rows are not important. The $name is an array, so while you're only calling one variable from the table you can just call it $name, but if you call more than one variable, you will need to specify which part of the array you want: $name['user_name'].

[BOMEz]

mysql_connect(localhost,$user,$pass); // connects to database
@mysql_select_db($db) or die ("Could not connect"); //selects database
 
// Perform the query
$result = mysql_query(" SELECT `user_name`
FROM `users`
WHERE `user_name` LIKE '$user_login' ") or die(mysql_error() . "\nThe SQL is: " . $query);
$name = mysql_fetch_array($result);
echo $name['user_name'];
?>

Try that. I basically used msql_fetch_array rather than mysql_result so that rows are not important. The $name is an array, so while you're only calling one variable from the table you can just call it $name, but if you call more than one variable, you will need to specify which part of the array you want: $name['user_name'].

Thanks alot. I got it to work, but I had to include CONVERT( _utf8 and COLLATE latin1_swedish_ci or else it wouldn't work for some reason...possibly my server configuration. I figured I would just copy the code phpMyAdmin gave me and it worked.

[TheServant]

Thanks alot. I got it to work, but I had to include CONVERT( _utf8 and COLLATE latin1_swedish_ci or else it wouldn't work for some reason...possibly my server configuration. I figured I would just copy the code phpMyAdmin gave me and it worked.

Glad you got it to work. See you next time.