Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts

    $row['id'] gets for from wrong table

    Hopefully this should be a quick one. I have a query that joins 2 tables and echo's the result, as part of that I have a link that requires the id from one table however it's getting the id from the wrong table. My code is below.

    PHP Code:
    $strSql "SELECT * FROM army JOIN users ON army.email = users.email WHERE users.id='".$_GET['id']."'";
    $rs mysql_query($strSql) or die(mysql_error());
        
        
    // Loop the recordset $rs
        
    while($row mysql_fetch_array($rs))
      {

           
    // Name of the person
          
    $strName $row['regname'];
          
    $val $row['id'];

           
    // Create a link to person.php with the id-value in the URL
           
    $strLink "<a href = 'regiment.php?id=$val' target='_parent'>" $strName "</a>";
        
            
    // List link
                    
           
    echo "<li>" $strLink "</li>";
           

          }

        
    // Close the database connection
        
    mysql_close(); 

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,092
    Thanks
    2
    Thanked 322 Times in 314 Posts
    When you get to the point of using JOIN'ed queries, it is best to stop using the * to select all columns and specifically list out the columns you want, from each table. This will remove any ambiguity (when there are two or more columns selected with the same name, the last value overwrites any earlier value.)

    You would use SELECT army.id, ...
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Nov 2011
    Posts
    250
    Thanks
    8
    Thanked 4 Times in 4 Posts
    You mean like this? I'm getting a syntax error from it.

    PHP Code:
    $strSql "SELECT army.id, army.regname, army.email, users.email, users.id JOIN users.email ON army.email = users.email WHERE users.id='".$_GET['id']."'";
    $rs mysql_query($strSql) or die(mysql_error()); 

  • #4
    New Coder
    Join Date
    Oct 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Even better than selecting "table.id" is to stop using "id" in *every* table.
    Use a sensible name the identifies the id for what it is; userid, groupid, forumid etc. that eliminates this annoying problem alltogether.

    About the syntax error: if you post what the error is then maybe we can tlel you where thr problem is...

    ps: never use $_GET directly in a query, *always* escape every value you use.

  • #5
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,092
    Thanks
    2
    Thanked 322 Times in 314 Posts
    The syntax error is because you hacked up your own query. Your goal was to change the * to the list of table.columns you want to select. Everything in your previous query starting with the FROM ... keyword to the end should still be the same. Yet you manged to even remove the FROM keyword.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    In other words:
    Code:
    $sql = "SELECT army.id, army.regname, army.email, users.email, users.id "
         . " FROM army JOIN users "
         . " ON army.email = users.email WHERE users.id= ". $_GET['id'];
    Assuming that users.id is a NUMBER, then DO NOT put apostrophes around $_GET['id'] !!

    But, more importantly, you really should *VALIDATE* that $_GET['id'] is a legitimate value. You NEED to protect against SQL injection attacks!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    The best way to protect against injection attacks is to use prepare and bind instead of query.

    All $_GET and $_POST vaqriables should be validated and moved to a different variable so as to keep a clear distinction between valid and possibly invalid content.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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