I'm new to PHP and MySQL, and I've been having some terrible difficulty with setting a $query string that contains a variable in the WHERE clause. I understand this is a common problem, but after three days of searching, I didn't find any of the solutions to fit my same problem. After staring at the screen for several hours, I suddenly remembered something very important, which I'll get to. I just wanted to post all the things I've learned, so that people can benefit from my tortuous trial and error and relentless Googling...
It doesn't work. First, (as a newbie) you start out trying to figure out if it's a syntax problem, but you've copied and pasted till your right CTRL key quits working. Then you start thinking, maybe there's something wrong with the connection, but you can't find one. You spend a couple of days searching and asking, and nobody's solution seems to work. You've tried escape characters, "." concatenation, interpolation, etc... I can't tell you how much I learned about PHP just searching for the solution to this problem. And you can't go far with databases until you fix it. Here are some of the solutions I found:
$query = "SELECT `password` FROM `customers` WHERE `username` = '$userName'";
Setting the literal inside an "if()" statement-- be sure you didn't forget the "==" in "if($str=='some value')" instead of using "if($str='some value')". That is easy to forget for newbies and will trip you up.
Check the syntax of your SELECT statement and quotation. Make sure you're using interpolating, double quotes (") for the entire string and single quotes (') around the variable.
Make sure your variable is actually set... echo it right before or after the query string is set. Also, echo your query string to see what you're actually sending.
Ok, now to my solution. If you're getting back an empty string, you may not be setting the literal correctly, but there's one more thing to remember.. Let's see, where's the super big, giant font.
Sorry, that was bigger than I expected, and completely unnecessary. But I'll keep it.
Or simply give your function an argument and call the function with the variable enclosed-- getData($str);. Anyway, I hope this helps some poor soul of a newbie in the future from the problems I ran into. This just goes to show... Follow good practices whether they're necessary in your current language or not. Someday, they will be.
$str = stripslashes($_POST['username']);
$dbname = 'customersLogin';
global $str; /*DECLARE GLOBALS!!!*/
$query = "SELECT `password` FROM `customers` WHERE `username`=\"$str\"";
$result = mysql_query($query) or die('Error getting data');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
setcookie($sessionName, $sessionID, $expiry);
echo "some HTML and a saying... blah blah blah";
if(isset($_POST['username']) && $_POST['username']!="" && isset($_POST['password']) && $_Post['password']!="")
Veterans, please do let me know if I made a mistake here. I'm a newbie myself. Also, remember that I did not include an anti-injection techniques here, so learn that too! Hope I got all that right.
I'm such an idiot sometimes...
Last edited by TheBearMay; 12-13-2008 at 09:23 AM.
Reason: Merge posts
This is much better than using global, as the global tightly couples the function to the application, making it more difficult to re-use either in other applications or for different uses in the same application, plus it also can cause difficult to debug problems.
Originally Posted by jamesbcox1980
Also, to some reading on "SQL injection" and the mysql_real_escape_string() function.
You're not kidding. This all started because I declared a variable outside the function, in effect creating a global, which took me 3 days to debug. If only I would have used the argument. Ok, so when you say,
...difficult to debug problems.
what do you mean? Even if I use this function in a separate PHP file and include it? Thanks for the links to MySQL injection, that's my next project...
the global tightly couples the function to the application
The "tightly couples" means that the function effectively is only usable within that specific application and within a specific circumstance: namely that there is a variable named "$str" defined and populated within the main body of the script before the function is called.
So, what happens if you want to use the function in another script and that script does not use a variable of that name for the same purpose? For that matter, what if as your script grows you want to use that function in another situation for a different variable? Or how about after your script grows to a thousand lines and for whatever reason you decide to rename $str to a more meaningful variable name to help keep your code more readable and things mysteriously stop working correctly?
The goal should therefore be to make your functions "loosely coupled", i.e. so that they can stand by themselves and be relatively easy to re-use in any situation where you need to perform the same functionality without having any dependencies restricting their use (such as what variable names you choose to use in your code).
Well, that makes perfect sense. That settles it, I will use arguments instead of the global scope, in this matter. I learned a valuable lesson this week. Thanks so much! I learned how to use them (I know they're pretty simple) I just didn't learn WHEN to use them, and I haven't had to worry about them in so long...
In fact, the general consensus amongst experts seems to be that there is no proper "WHEN to use them".
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread