www.webdeveloper.com
Results 1 to 6 of 6

Thread: Solution to MySQL WHERE variable problems for newbies

  1. #1
    Join Date
    Dec 2008
    Posts
    488

    Lightbulb

    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...

    Problem:

    PHP Code:
    $query "SELECT `password` FROM `customers` WHERE `username` = '$userName'"
    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:

    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.

    GLOBAL VARIABLES!

    Sorry, that was bigger than I expected, and completely unnecessary. But I'll keep it.

    I couldn't believe I forgot about this. I had been playing around with Javascript for so long (where globals aren't much of an issue with functions) I completely forgot all my training about globals. I was just sitting there and decided to stick the value inside the function instead of out, and VOILA! After 3 days of crap, it finally works. Why didn't I think of that 3 days ago??? I should have already been using global rules. If you're setting your literal for the variable that's going into the "WHERE" clause outside of a function, be sure to declare your globals inside the function.

    PHP Code:
    <?php


    $str 
    stripslashes($_POST['username']);


    function 
    $getData()
    {
      
    $dbname 'customersLogin';
      include 
    'configdb.php';
      include 
    'opendb.php';
      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($resultMYSQL_ASSOC))
      {
        if(
    $row['password']==$_POST['password'])
        {
          
    setcookie($sessionName$sessionID$expiry);
          echo 
    "some HTML and a saying... blah blah blah";
          
    someRedirect();
        }
       else
        {
          
    somethingElse();
        }

      include 
    'closedb.php';
      
    someMoreFunctions();

    }

    if(isset(
    $_POST['username']) && $_POST['username']!="" && isset($_POST['password']) && $_Post['password']!="")
    {
      
    getData();
    }
    else
    {
      
    somethingElse();
    }
    ?>
    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.

    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.

    James

    I'm such an idiot sometimes...
    Last edited by TheBearMay; 12-13-2008 at 09:23 AM. Reason: Merge posts

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,246
    Quote Originally Posted by jamesbcox1980 View Post
    ...Or simply give your function an argument and call the function with the variable enclosed-- getData($str);....
    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.

    Also, to some reading on "SQL injection" and the mysql_real_escape_string() function.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Dec 2008
    Posts
    488
    ...difficult to debug problems.
    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,
    the global tightly couples the function to the application
    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...

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,246
    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).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Dec 2008
    Posts
    488
    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...

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,246
    In fact, the general consensus amongst experts seems to be that there is no proper "WHEN to use them".
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles