Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51

    Unanswered: MySQL update ONLY with ID search term

    OK I figured out how to use php to update my DB ... but here is the BIG BUT.
    I use 3 pages:
    1 - the search form (one entry field)
    2 - the page with fields they will be populated with the data found in that row, to be altered with the changes and then
    3 - the page that will do the actual update.

    AS SAID... it works with the ID column.

    Let's say I search for ID "14". the second page' fields will be filled with the data. I then change any data I like, not touching the others, click update and - voila, it's done ...

    So where is the problem?

    A) It works only on one table - not on another (how is exactly the same, everything ... it's just another spare table to do the same experiments on, that's all.

    B) The moment I change from the ID column to ANY other column, I get a strange knock telling me "Could not connect: Unknown column 'Peter' in 'where clause"

    So my search for "Peter" which is now under the "name-column" has now turned into the name-column itself ... strange
    (before I searched for "14" inder ID and it gave me all data from row 14. I can't see any reaso why it shouldn't do the same with row "Peter"???)

    And here is my magic code, in the sequences of 1-2-3

    PHP Code:
    <form action="update1.php" method="post">
    Input ID &nbsp;<input type="text" name="ID" size="22" />
    <
    input type="submit" value="Update" />
    </
    form
    HTML Code:
    <html>
    <head>
    <title></title>
    </head>
    <body>
    
    
    <?php
    // get ID variable from previous form (the one that selects which record to update) to
    // ensure we are updating the correct record
    $id = ($_POST['ID']);
    if (!$id)
    {
        die("Variable id not defined. Script terminating.");
    }
    ?>
    
    
    <?php
    // set database variables
    include 'dbcall.php';
    ?>
    
    
    <?php
    // connect to MySQL
    $con = mysql_connect($hostname, $user, $password);
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    ?>
    
    
    <?php
    // select database on $con
    $db_selected = mysql_select_db($database, $con) or die(mysql_error
        ());
    ?>
    
    
    
    <?php
    // set string containing query
    $sql = "SELECT * FROM clients WHERE ID=" . $id . "";
    ?>
    
    
    <?php
    // query table (grab the data)
    $result = mysql_query($sql, $con) or die('Could not connect: ' . mysql_error());
    if (!$result)
    {
        die('Result did not function:' . mysql_error());
    }
    ?>
    
    
    <?php
    //close the connection
    mysql_close();
    ?>
    
    
    <?php
    // display the form with details obtained from database
    while ($row = mysql_fetch_assoc($result))
    {
        echo "<form action='zagga2.php' method='post'>";
        echo "<br /><br />";
        echo "<input type='hidden' name='ID' value='$id' />";
        echo "<input type='text' name='first_name' value='$row[first_name]' />first name<br />";
        echo "<input type='text' name='last_name' value='$row[last_name]' />last name<br />";
        echo "<input type='text' name='dob' value='$row[dob]' />DOB<br />";
        echo "<input type='text' name='country_origin' value='$row[country_origin]' />Country<br />";
        echo "<input type='text' name='primary_email' value='$row[primary_email]' />Country<br />";
        echo "<br /><br />";
        echo "<input type='submit' value='Update Event' />";
        echo "</form>";
    }
    ?>
    
    
    </body>
    </html>
    PHP Code:
    <?php
    // get variables from previous form
    $id = ($_POST['ID']);
    $first = ($_POST['first_name']);
    $last = ($_POST['last_name']);
    $dob = ($_POST['dob']);
    $country = ($_POST['country_origin']);
    $email =($_POST['primary_email']);
    ?>



    <html>
    <head>
    <title></title>
    </head>
    <!-- open a page (in this case, displaying the database) once this page has loaded -->
    <body onload="location.href='display.php'">



    <?php
    // set database variables
    include 'dbcall.php';
    ?>



    <?php
    // connect to MySQL
    $con mysql_connect($hostname$user$password);
    if (!
    $con)
    {
        die(
    'Could not connect: ' mysql_error());
    }
    ?>



    <?php
    // select database on $con
    $db_selected mysql_select_db($database$con) or die(mysql_error
        
    ());
    ?>



    <?php
    // update the database
    $sql = ("UPDATE clients SET first_name='$first', last_name='$last', dob='dob', country_origin='$country', primary_email='$email' WHERE ID='$id'");

    // show error message if update fails
    if (!mysql_query($sql$con))
    {
        die(
    'Error: ' mysql_error());
    }
    ?>



    <?php
    // close the database
    mysql_close($con);
    ?>
    Someone has any idea what is missing or stuffed up, better?
    "Defying critics is a disagreement to growth through improvement"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    (before I searched for "14" inder ID and it gave me all data from row 14. I can't see any reaso why it shouldn't do the same with row "Peter"???)
    because of the very basic difference between numeric constants and string constants

    string constants, by definition, have to be written as strings, i.e. with enclosing quotes

    peter is a column name

    'peter' is a string constant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    Hi R937

    Thanks, makes sense but between

    WHERE ID=" . $id . ""
    and
    WHERE Name=" . $id . ""
    what's the difference? Both are columns, only
    when I type 14 into the form - (as ID identifying a row data), I get the data
    but
    when I type Peter - I get the error message that Peter is not a Column ??

    Sorry, I don't get it ... Because all I did was replace the Upper Case "ID" with "Name" ad then into the form I typed 14 and then Peter
    "Defying critics is a disagreement to growth through improvement"

  4. #4
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    OK I wrapped Name to
    WHERE 'Name'=" . $id . ""
    because Peter is a name and should be wrapped as a string while 14 is numeric. Well, besides not making sense because with the "WHERE" clause I look up the column to find that row with that specific value (either 14 or Peter) - it didn't make any difference anyway still get my error saying;
    Could not connect: Unknown column 'BR' in 'where clause'

    So WHY doesn't it then say the same for:
    Could not connect: Unknown column '14' in 'where clause'

    Sorry I really don't get that .
    "Defying critics is a disagreement to growth through improvement"

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude!

    no quotes needed around $id --
    Code:
    WHERE ID=" . $id . ""
    quotes very much needed around $id --
    Code:
    WHERE Name='" . $id . "'"
    always test your queries outside of php, my friend

    that way you will know where the quotes go
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    I knew it's something really stupid, just didn't look into the mirror

    Thanks again, r937

    BTW, I always check the sql outside php - the strange thing here is that when generating a php code from that, it gave me this ...
    $sql = "SELECT * FROM `HK_Social` WHERE first_name=\"amy\"";
    my query within phpMyadmin worked, so I used it, which is but complete different to what you told me now...!

    Any idea why it would create such a wrong sql? Is this a phpMyadmin bug? Also, I must have searched 3 days on the net without any luck... so

    YOUR INPUT IS HIGHLY APPRECIATED AND INDEED.
    "Defying critics is a disagreement to growth through improvement"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    phpmyadmin is pretty crappy, isn't it

    instead of this --
    Code:
    $sql = "SELECT * FROM `HK_Social` WHERE first_name=\"amy\"";
    you should be using this --
    Code:
    $sql = "SELECT * FROM HK_Social WHERE first_name='amy' ";
    and you really should not be using the dreaded, evil "select star"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    "select star"
    ?

    Yep, agree with DESCRIBE phpMyadmin!

    I tried to download and use HEIDI but my disk space is very low and Heidi wouldn't agree with taking a comfortable seat in D:\, so I need to get her another computer first ... women, always demanding!
    "Defying critics is a disagreement to growth through improvement"

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case consider MySQL GUI tools from MySQL
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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