SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Passing ID to use in a SQL WHERE statement

    Hi,
    I have a page displying information about a customer, with an edit option to update their details. The update details are diplayed using tables and gathered by using sql with a where statement based on the customer ID. This works fine when i hard code a number in the sql statement, but when i try to substitute it for the customers related id I get nothing.
    I don't think the ID from the previous page is being passed through to the sql statement. Can someone have a look at my code and let me know if this is the case and how I can fix it?
    code from customer details
    Code:
    ...
    disply customer info in form and tables...
    ...
    <input type="hidden" name="u_id" value="<? "$id" ?>" >
    </form>
    code to update customer details
    Code:
    <?
    $ud_id = $_GET["$u_id"];
    ...
    connect to database etc...
    ...
    $query=" SELECT * FROM cust_details WHERE id='$ud_id'";
    Now this will work if I use : $query=" SELECT * FROM cust_details where id=1" so i know there is something wrong with the '$ud_id' section of the code (it doesn't seem to recognise the value of $id_id).

    Does anyone see the problem here?

  2. #2
    SitePoint Evangelist sysice's Avatar
    Join Date
    Oct 2004
    Location
    Hackettstown, NJ
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't know the single/double quote escape rules of the top of my head, but try this:

    PHP Code:
    $query=" SELECT * FROM cust_details WHERE id='".$ud_id."'"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, still nothing. Any other ideas?

  4. #4
    SitePoint Evangelist sysice's Avatar
    Join Date
    Oct 2004
    Location
    Hackettstown, NJ
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put this after your query line:

    PHP Code:
    print $query;exit; 
    What prints?

    Also is your form method get/post? This may be the problem, in which case change $_GET to $_POST.

  5. #5
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
      <?php
      
    /** just for testing to make sure $id has 
       a value before it's being used in the form 
       element.
       **/
      
    echo "<pre>";
      echo 
    'var_dump($id) = ';
      
    var_dump($id);
      echo 
    "</pre> <br><br> \n\n";
      
    ?>
      
      <input type="hidden" name="u_id" value="<?php echo "$id?>" >

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both the form and script are using the GET method.
    print $query returns: " SELECT * FROM elec_details where id='' "
    The code posted by coo_t2 shows me that my variable id has a value of 1.
    now even more confused and frustrated that it isn't working!!

  7. #7
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    ahh.

    change this:

    $ud_id = $_GET["$u_id"];

    to this:

    $ud_id = $_GET['u_id'];

    If your form method is using post instead of get then use $_POST
    instead of $_GET.

    --ed

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    still not working... i don't know what is going on.. I've posted my whole code in hope that it will shed some light on the issue. If needed I can post the code for the previous page that passes the id variable...
    Code:
    <?
    $ud_id = $_GET['$u_id'];
    
    $connection=mysql_connect("localhost","username","password"); 
    if (!$connection) { 
    echo "Could not connect to MySql server!"; 
    exit; 
    } 
    
    $db=mysql_select_db("mysql",$connection); 
    if (!$db) { 
    echo "Could not select database"; 
    exit; 
    } 
    $query=" SELECT * FROM elec_details where id=$ud_id";
    
    $result=mysql_query($query);
    $num=mysql_num_rows($result);
    
    // code suggested by coo_t2 to output the value of query
    print $query;exit; 
    
    $i=0;
    while ($i < $num) {
    
    $id=mysql_result($result,$i,"id");
    $Name=mysql_result($result,$i,"Name");
    $Address=mysql_result($result,$i,"Address"); 
    ?>
    <table width="464" border="2" align="center" cellpadding="10" cellspacing="0" bordercolor="#6600FF">
    <tr align="center" valign="top">
    <td width="438" colspan="1" rowspan="1" align="center">
    <h3 align="center" class="style6">Edit and Submit </h3>
    <form action="change_elec_record.php" method="post">
    
    <table width="366" border="0">
      <tr>
        <td width="116"><span class="style5">Name</span></td>
        <td width="240"><input name="ud_Name" type="text" value="<? print "$Name"?>" size="20"></td>
      </tr>
      <tr>
        <td><span class="style5">Address</span></td>
        <td><input name="ud_Address" type="text" value="<? print "$Address"?>" size="30"></td>
      </tr>
    </table>
    <input type="Submit" value="Update">
    </form>
    </td></tr></table>
    
    <?
    ++$i;
    }
    ?>

  9. #9
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    You didn't make the right change.

    change this:

    $ud_id = $_GET['$u_id'];


    to this:

    $ud_id = $_GET['u_id'];

    ^ notice there's no $ before u_id

    Also, don't forget your script's gonna end at that exit statement.

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, changed the code to remove the $. I understand the script will end at the exit but it should still pass my varible in the print $query; statement. It is still showing as " SELECT * FROM elec_details where id= "

  11. #11
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dearnne
    Sorry, changed the code to remove the $. I understand the script will end at the exit but it should still pass my varible in the print $query; statement. It is still showing as " SELECT * FROM elec_details where id= "
    Perhaps your variable isn't in the query string. Verify your form, and query string first.

  12. #12
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    change $_GET['u_id'] to $_POST['u_id'] .
    See in your <form> tag you have the method attribute set to "post". That means you use the $_POST array to access the variables.

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ARRGGHH! Still no outcome.
    One final attempt to resolve this situation. Here is the code for the customer details
    Code:
    <?php 
    $id = $_GET[id];
    
    $connection=mysql_connect("localhost","username","password"); 
    if (!$connection) { 
    echo "Could not connect to MySql server!"; 
    exit; 
    } 
    
    $db=mysql_select_db("mysql",$connection); 
    if (!$db) { 
    echo "Could not select database"; 
    exit; 
    } 
    
    $query="select * from (customers left join elec_details on customers.id = elec_details.id) 
            left join gas_details on customers.id = gas_details.id
    		WHERE customers.id='$id' ";
    
    $result=mysql_query($query);
    $num_rows=mysql_num_rows($result); 
    
    $i=0;
    
    while ($i < $num_rows) {
    
    $id=mysql_result($result,$i,"id");
    $TradingName=mysql_result($result,$i,"TradingName");
    $Title=mysql_result($result,$i,"Title"); 
    $GivenName=mysql_result($result,$i,"GivenName"); 
    $Surname=mysql_result($result,$i,"Surname");
    $ABN=mysql_result($result,$i,"ABN"); 
    $PhoneNumber=mysql_result($result,$i,"PhoneNumber"); 
    $MobileNumber=mysql_result($result,$i,"MobileNumber"); 
    $FaxNumber=mysql_result($result,$i,"FaxNumber"); 
    $Email=mysql_result($result,$i,"Email"); 
    $SiteAddress=mysql_result($result,$i,"SiteAddress"); 
    $SiteSuburb=mysql_result($result,$i,"SiteSuburb"); 
    $SiteState=mysql_result($result,$i,"SiteState"); 
    $SitePostcode=mysql_result($result,$i,"SitePostcode"); 
    $MailAddress=mysql_result($result,$i,"MailAddress"); 
    $MailSuburb=mysql_result($result,$i,"MailSuburb"); 
    $MailState=mysql_result($result,$i,"MailState"); 
    $MailPostcode=mysql_result($result,$i,"MailPostcode"); 
    $NMI=mysql_result($result,$i,"NMI");
    $ElecDistributor=mysql_result($result,$i,"ElecDistributor");
    $ElecCurrentTariff=mysql_result($result,$i,"ElecCurrentTariff");
    $ElecPeakUsage=mysql_result($result,$i,"ElecPeakUsage");
    $ElecNonPeakUsage=mysql_result($result,$i,"ElecNonPeakUsage");
    $ElecShoulderUsage=mysql_result($result,$i,"ElecShoulderUsage");
    $ElecBillingDays=mysql_result($result,$i,"ElecBillingDays");
    $MIRN=mysql_result($result,$i,"MIRN");
    $GasDistributor=mysql_result($result,$i,"GasDistributor");
    $GasCurrentTariff=mysql_result($result,$i,"GasCurrentTariff");
    $GasMJUsage=mysql_result($result,$i,"GasMJUsage");
    $GasBillingDays=mysql_result($result,$i,"GasBillingDays");
    
    //
    
    ?>
    
    <? $u_id=("$id"); ?>
    
    <style type="text/css">
    <!--
    .style3 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; }
    .style5 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: bold; }
    .style6 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; font-weight: bold; }
    .style7 {
    	font-family: Verdana, Arial, Helvetica, sans-serif;
    	font-style: italic;
    	font-weight: bold;
    	font-size: 12px;
    }
    -->
    </style>
    
    <div align="left">
      <p><span class="style7"><a href="agl_customer_list.php">Back</a></span></p>
            <div align="center">
              <p><span class="style6">Customer Information</span>  <a href='update_customer_details.php' class="style5"><em>(Edit)</em></a>
                
            </p>
      </div>
    </div>
    <form method="post">
    <table align="center" border="1">
    	<TR><td width="97" class="style5">ID</td>
    	<TD width="392" class="style3"><? print "$id"?></TD></TR>
    	<TR>
    	  <td class="style5">Trading Name</td>
    	<TD class="style3"><? print "$TradingName"?></TD></TR>
    	<TR><td class="style5">Title</td>
    	<TD class="style3"><? print "$Title"?></TD></TR>
    	<TR><td class="style5">Given Name</td>
    	<TD class="style3"><? print "$GivenName"?></TD></TR>
    	<TR><td class="style5">Surname</td>
    	<TD class="style3"><? print "$Surname"?></TD></TR>
    	<TR><td class="style5">ABN</td>
    	<TD class="style3"><? print "$ABN"?></TD></TR>
    	<TR><td class="style5">Phone Number</td>
    	<TD class="style3"><? print "$PhoneNumber"?></TD></TR>
    	<TR><td class="style5">Mobile Number</td>
    	<TD class="style3"><? print "$MobileNumber"?></TD></TR>
    	<TR>
    	  <td class="style5">Fax Number</td>
    	  <TD class="style3"><? print "$FaxNumber"?></TD></TR>
    	<TR><td class="style5">Email</td>
    	<TD class="style3"><? print "$Email"?></TD></TR>
    	<TR><td class="style5">Site Address</td>
    	<TD class="style3"><? print "$SiteAddress"?></TD></TR>
    	<TR><td class="style5">Site Suburb</td>
    	<TD class="style3"><? print "$SiteSuburb"?></TD></TR>
    	<TR><td class="style5">Site State</td>
    	<TD class="style3"><? print "$SiteState"?></TD></TR>
    	<TR><td class="style5">Site Postcode</td>
    	<TD class="style3"><? print "$SitePostcode"?></TD></TR>
    	<TR><td class="style5">Mail Address</td>
    	<TD class="style3"><? print "$MailAddress"?></TD></TR>
    	<TR><td class="style5">Mail Suburb</td>
    	<TD class="style3"><? print "$MailSuburb"?></TD></TR>
    	<TR><td class="style5">Mail State</td>
    	<TD class="style3"><? print "$MailState"?></TD></TR>
    	<TR><td class="style5">Mail Postcode</td>
    	<TD class="style3"><? print "$MailPostcode"?></TD></TR>
    </table>
    <p></p>
    <table width="743" border="0" align="center">
      <tr>
        <td width="285"><div align="center" class="style6">Electricity Details <a href='update_elec_details.php' class="style5"><em>(Edit)</em></a> </div></td>
        <td width="159">&nbsp;</td>
        <td width="285"><div align="center" class="style6">Gas Details <a href='update_gas_details.php' class="style5"><em>(Edit)</em></a></div></td>
      </tr>
      <tr>
        <td><table width="283" border="1" align="left">
          <tr>
            <td width="108" class="style5">NMI</td>
            <TD width="128" class="style3"><? print "$NMI"?></TD>
          </tr>
          <tr>
            <td class="style5">Distributor</td>
            <TD class="style3"><? print "$ElecDistributor"?></TD>
          </tr>
          <tr>
            <td class="style5">Current Tariff</td>
            <TD class="style3"><? print "$ElecCurrentTariff"?></TD>
          </tr>
          <tr>
            <td class="style5">Peak Usage</td>
            <TD class="style3"><? print "$ElecPeakUsage"?></TD>
          </tr>
          <tr>
            <td class="style5">Non Peak Usage</td>
            <TD class="style3"><? print "$ElecNonPeakUsage"?></TD>
          </tr>
          <tr>
            <td class="style5">Shoulder Usage</td>
            <TD class="style3"><? print "$ElecShoulderUsage"?></TD>
          </tr>
          <tr>
            <td class="style5">Bill Days</td>
            <TD class="style3"><? print "$ElecBillingDays"?></TD>
          </tr>
        </table></td>
        <td>&nbsp;</td>
        <td><table width="283" border="1" align="left" valign="middle">
          <tr>
            <td width="108" class="style5">MIRN</td>
            <TD width="128" class="style3"><? print "$MIRN"?></TD>
          </tr>
          <tr>
            <td class="style5">Distributor</td>
            <TD class="style3"><? print "$GasDistributor"?></TD>
          </tr>
          <tr>
            <td class="style5">Current Tariff</td>
            <TD class="style3"><? print "$GasCurrentTariff"?></TD>
          </tr>
          <tr>
            <td class="style5">MJ Usage</td>
            <TD class="style3"><? print "$GasMJUsage"?></TD>
          </tr>
          <tr>
            <td class="style5">Bill Days</td>
            <TD class="style3"><? print "$GasBillingDays"?></TD>
          </tr>
          <tr>
            <td height="20" ></td>
            <TD></TD>
          </tr>
          <tr>
            <td class="style5" height="20"></td>
            <TD class="style3"></TD>
          </tr>
        </table></td>
      </tr>
    </table>
    <input type="hidden" name="u_id" value=<? "$id" ?> >
    
    <?php
      /** just for testing to make sure $id has 
       a value before it's being used in the form 
       element.
       **/
      echo "<pre>";
      echo 'var_dump($u_id) = ';
      var_dump($u_id);
      echo "</pre> <br><br> \n\n";
    ?>
    
    </form>
    <hr noshade>
    <?
    ++$i;
    }
    ?>
    and code for the sql display page
    Code:
    <html><head><title>Update Electricity Details</title>
    <style type="text/css">
    <!--
    .style5 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; }
    .style6 {font-family: Verdana, Arial, Helvetica, sans-serif}
    -->
    </style>
    </head>
    <body>
    <?
    $u_id = $_POST['$id'];
    
      /** just for testing to make sure $id has 
       a value before it's being used in the form 
       element.
       **/
      echo "<pre>";
      echo 'var_dump($u_id) = ';
      var_dump($u_id);
      echo "</pre> <br><br> \n\n";
    
    
    
    $connection=mysql_connect("localhost","admin","admin"); 
    if (!$connection) { 
    echo "Could not connect to MySql server!"; 
    exit; 
    } 
    
    $db=mysql_select_db("mysql",$connection); 
    if (!$db) { 
    echo "Could not select database"; 
    exit; 
    } 
    //$query=" SELECT * FROM elec_details WHERE id='.$ud_id.'"; 
    $query=" SELECT * FROM elec_details where id=$ud_id";
    //$query=" SELECT * FROM elec_details where id=1";
    
    $result=mysql_query($query);
    $num=mysql_num_rows($result);
    
    print $query;exit; 
    
    $i=0;
    while ($i < $num) {
    
    $id=mysql_result($result,$i,"id");
    $NMI=mysql_result($result,$i,"NMI");
    $ElecDistributor=mysql_result($result,$i,"ElecDistributor"); 
    $ElecCurrentTariff=mysql_result($result,$i,"ElecCurrentTariff"); 
    $ElecPeakUsage=mysql_result($result,$i,"ElecPeakUsage");
    $ElecShoulderUsage=mysql_result($result,$i,"ElecShoulderUsage"); 
    $ElecNonPeakUsage=mysql_result($result,$i,"ElecNonPeakUsage"); 
    $ElecBillingDays=mysql_result($result,$i,"ElecBillingDays"); 
    ?>
    <table width="464" border="2" align="center" cellpadding="10" cellspacing="0" bordercolor="#6600FF">
    <tr align="center" valign="top">
    <td width="438" colspan="1" rowspan="1" align="center">
    <h3 align="center" class="style6">Edit and Submit </h3><? echo "$id" ?><? echo "$ud_id" ?>
    <form action="change_elec_record.php" method="post">
    
    <table width="366" border="0">
      <tr>
        <td width="116"><span class="style5">NMI</span></td>
        <td width="240"><input name="ud_NMI" type="text" value="<? print "$NMI"?>" size="20"></td>
      </tr>
      <tr>
        <td><span class="style5">Distributor</span></td>
        <td><input name="ud_ElecDistributor" type="text" value="<? print "$ElecDistributor"?>" size="30"></td>
      </tr>
      <tr>
        <td><span class="style5">Current Tariff </span></td>
        <td><input name="ud_ElecCurrentTariff" type="text" value="<? print "$ElecCurrentTariff"?>" size="30"></td>
      </tr>
      <tr>
        <td><span class="style5">Peak Usage </span></td>
        <td><input name="ud_ElecPeakUsage" type="text" value="<? print "$ElecPeakUsage"?>" size="15"></td>
      </tr>
      <tr>
        <td><span class="style5">Shoulder Usage </span></td>
        <td><input name="ud_ElecShoulderUsage" type="text" value="<? print "$ElecShoulderUsage"?>" size="15"></td>
      </tr>
      <tr>
        <td><span class="style5">Non Peak Usage </span></td>
        <td><input name="ud_ElecNonPeakUsage" type="text" value="<? print "$ElecNonPeakUsage"?>" size="15"></td>
      </tr>
      <tr>
        <td><span class="style5">Billing Days </span></td>
        <td><input name="ud_ElecBillingDays" type="text" value="<? print "$ElecBillingDays"?>" size="10"></td>
      </tr>
    </table>
    <input type="Submit" value="Update">
    </form>
    </td></tr></table>
    
    <?
    ++$i;
    }
    ?>
    </body>
    </html>
    Hopefully someone will see where I've gone wrong and point me in the right direction!

  14. #14
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone??

  15. #15
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dearnne
    Anyone??
    Look at your forms. First, you have this:

    <form method="post">

    But no action defined.

    Second, you have this:

    <input type="hidden" name="u_id" value=<? "$id" ?>

    Ok, now, in your "SQL" page, you have this:
    $u_id = $_POST['$id'];

    But earlier you were told that this was incorrect, so why is it still there? If you wanted to use the method that you have defined, you would have to do something of this nature:

    $id = "u_id";
    $uid = $_POST[$id]

    using a $ sign denotes a VARIABLE.

    What you need to do:

    1) Define an action for your form page
    2) $u_id = $HTTP_POST_VARS['u_id'];

  16. #16
    SitePoint Zealot
    Join Date
    Jul 2005
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also where it says

    <input type="hidden" name="u_id" value=<? "$id" ?>

    Don't you need to actually do <? echo($id); ?> or judging by your style
    <? print "$ID" ?> as the way it is now, nothing (or incorrect) info is being put into u_id.
    Escape Your Mind
    Director of Technology
    www.boxgods.com

  17. #17
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to everyone for your help.
    In the end it turned out all I needed to do was add a "submit" button (after making a few code mods) and it all works fine now.

  18. #18
    SitePoint Wizard mark_W's Avatar
    Join Date
    Mar 2004
    Location
    West Midlands, United Kingdom
    Posts
    2,631
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by EscapeYourMind
    also where it says

    <input type="hidden" name="u_id" value=<? "$id" ?>

    Don't you need to actually do <? echo($id); ?> or judging by your style
    <? print "$ID" ?> as the way it is now, nothing (or incorrect) info is being put into u_id.
    Could have been using the short syntax :

    PHP Code:
     
     <?= $id ?>
    and just missed the =



Bookmarks

Posting Permissions

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