SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble With MYSQL UPDATE Command Revisited)))

    My earlier post "Trouble With MYSQL UPDATE Command" contained three scripts, two of which worked and one of which, the third, did not. With help received on this forum I was able to simplify the second script, but the third script still does not work and gives the "Couldn't Execute Querry" error...The first script is contained in the earlier post. It allows me to select a record in the MYSQL database for modification. It works fine. That script is called jl2.php.

    Here is a shortened second script, jl2s.php. I have deleted repetitive code to shorten this sement, all of which can be seen in my earlier post on the forum:
    ----------------------------------------------------------------------------------
    <?
    if (!$sel_job) {
    header("Location: http://www.neatjobs-washdc-baltmd.com/AADS/jl2.php");
    exit;
    }

    $connection = mysql_connect("localhost", "jwing", "xxxxxxxx")
    or die ("Couldn't connect to server.");


    $db = mysql_select_db("jwing", $connection)
    or die ("Couldn't select database.");

    $sql = "SELECT * FROM jobs WHERE job_id = $sel_job";

    $sql_result = mysql_query($sql)
    or die("Query Error: ".mysql_error());

    if (!$sql_result) {
    echo "<P>Couldn't get ad information!";
    } else {

    $row = mysql_fetch_array($sql_result);

    $keyword1 = $row["keyword1"];
    $keyword2 = $row["keyword2"];
    $keyword3 = $row["keyword3"];
    $keyword4 = $row["keyword4"];
    $keyword5 = $row["keyword5"];
    $comp_id = $row["comp_id"];
    $job_id = $row["job_id"];
    $recv_date = $row["recv_date"];
    $show_date = $row["show_date"];
    $job_cat = $row["job_cat"];
    $job_loc = $row["job_loc"];
    $job_title = $row["job_title"];
    $job_desc = $row["job_desc"];
    $job_salary = $row["job_salary"];
    $ad_expire = $row["ad_expire"];
    $first_name = $row["first_name"];
    $last_name = $row["last_name"];
    $ad_email = $row["ad_email"];
    $cntct_email = $row["cntct_email"];
    $usr_name = $row["usr_name"];
    $p_word = $row["p_word"];
    $ad_phone = $row["ad_phone"];
    $cntct_phn = $row["cntct_phone"];
    $ad_faxphone = $row["ad_faxphone"];
    $web_site = $row["web_site"];
    $visible = $row["visible"];
    $ccname = $row["ccname"];
    $ccnumber = $row["ccnumber"];
    $monthexp = $row["monthexp"];
    $yearexp = $row["yearexp"];
    $cctype = $row["cctype"];
    $street1A = $row["street1A"];
    $street2A = $row["street2A"];
    $street3A = $row["street3A"];
    $cc_city = $row["cc_city"];
    $cc_state = $row["cc_state"];
    $cc_zip = $row["cc_zip"];
    $trblfone = $row["trblfone"];

    echo "
    <HTML>
    <HEAD>
    <TITLE></TITLE>

    </HEAD>

    <BODY background=\"rimmabg.jpg\">

    <h2 align=\"center\">Modify The Following Ad/h2>

    <FORM method=\"POST\" action=\"jl2s1.php\">

    <hr>
    <p>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 align=\"center\" bgcolor=\"silver\">
    <tr><td>Company ID: </td><td>
    <input type=\"text\" name=\"aa\" size=10 value=\"$comp_id\">
    </td></tr>
    </table>

    <p>

    <hr>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 align=\"center\" bgcolor=\"silver\"><TR><TD>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 bgcolor=\"silver\">
    <tr><th><B>KEYWORD<br>#</B></th>
    <th>WORD<BR>ENTRY</th>
    </tr>

    <tr><td>1</td>
    <td><input type=\"text\" name=\"a1\" size=15 value=\"$keyword1\"></td>
    </TR>
    <tr><td>2</td>
    <td><input type=\"text\" name=\"a2\" size=15 value=\"$keyword2\"></td>
    </tr>
    <tr><td>3</td>
    <td><input type=\"text\" name=\"a3\" size=15 value=\"$keyword3\"></td>
    </tr>
    <tr><td>4</td>
    <td><input type=\"text\" name=\"a4\" size=15 value=\"$keyword4\"></td>
    </tr>
    <tr><td>5</td>
    <td><input type=\"text\" name=\"a5\" size=15 value=\"$keyword5\"></td>
    </tr>
    </table>

    <p>

    <TD>

    </TR>
    </TABLE>
    <p>
    <p>
    <hr>
    ---deleted code begins here
    ----
    ----
    ----
    ----deleted code ends here

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 align=\"center\" bgcolor=\"silver\"><TR><TD>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 bgcolor=\"silver\"><TR><TD>
    City/td>
    <td><input type=\"text\" name=\"a31\" size=40 value=\"$cc_city\"></td></tr>
    </table>
    </td>
    <td>
    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 bgcolor=\"silver\"><TR><TD>
    State/td>
    <td><input type=\"text\" name=\"a32\" size=2 value=\"$cc_state\"></td></tr>
    </table>
    </td>
    <td>
    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 bgcolor=\"silver\"><TR><TD>
    Zipcode/td>
    <td><input type=\"text\" name=\"a33\" size=10 value=\"$cc_zip\"></td></tr>
    </table>
    </td>
    </tr>
    </table>
    <p>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 align=\"center\" bgcolor=\"silver\"><TR><TD>

    <table border=3 bordercolor=\"\#66CCFF\" cellspacing=7 bgcolor=\"silver\"><TR><TD>
    Telephone/td>
    <td><input type=\"text\" name=\"a34\" size=12 value=\"$trblfone\"></td></tr>
    </table>
    </td>

    </tr>
    </table>

    <p>
    <hr>


    <input name=\"var1\" value=\"Modify Database\" Type=\"submit\">
    <p>
    <input name=\"var2\" value=\"Return To Admin Home Page\" Type=\"submit\">
    <p>

    </form>


    </BODY>
    </HTML>

    ";
    }
    ?>
    ----------------------------------------------------------------------------
    The above code works fine. it takes the value of a variable $sel_job which is passed from the first script using a SELECT statement to pick a record and displays the record fields on a HTML form to allow modification of the data in the record. There is no problem with the display of information. The above script works fine until I click the "Modify Database" button.

    When I click the "Modify Database" button on the second script I now get a "Couldn't execute query" message.

    The third script which is called when I click the Modify Database button is shown it its entireity (jl2s1.php):

    ------------------------------------------------------------------------------

    <?php
    If ($var2=="Return To Admin Home Page"){
    header("Location:index.htm");
    exit;
    }elseif ($var1=="Modify Database"){

    $connection = mysql_connect("localhost", "jwing", "xxxxxxxx")
    or die ("Couldn't connect to server.");


    $db = mysql_select_db("jwing", $connection)
    or die ("Couldn't select database.");

    $sql = "update jobs
    set
    keyword1 = '".$http_post_vars['a1']."',
    keyword2 = '".$http_post_vars['a2']."',
    keyword3 = '".$http_post_vars['a3']."',
    keyword4 = '".$http_post_vars['a4']."',
    keyword5 = '".$http_post_vars['a5']."',
    comp_id = '".$http_post_vars['aa']."',
    recv_date = '".$http_post_vars['ab']."',
    show_date = '".$http_post_vars['a6']."',
    job_cat = '".$http_post_vars['a7']."',
    job_loc = '".$http_post_vars['a8']."',
    job_title '".$http_post_vars['a9']."',
    job_desc = '".$http_post_vars['jobdesc']."',
    job_salary = '".$http_post_vars['a10']."',
    ad_expire = '".$http_post_vars['a11']."',
    first_name = '".$http_post_vars['a12']."',
    last_name = '".$http_post_vars['a13']."',
    ad_email = '".$http_post_vars['a14']."',
    cntct_email = '".$http_post_vars['a15']."',
    usr_name = '".$http_post_vars['a16']."',
    p_word = '".$http_post_vars['a17']." ',
    ad_phone = '".$http_post_vars['a18']."',
    cntct_phone = '".$http_post_vars['a19']."',
    ad_faxphone = '".$http_post_vars['a20']."',
    web_site = '".$http_post_vars['a21']."',
    visible = '".$http_post_vars['a22']."',
    ccname = '".$http_post_vars['a23']."',
    ccnumber = '".$http_post_vars['a24']."',
    monthexp = '".$http_post_vars['a25']."',
    yearexp = '.".$http_post_vars['a26']."',
    cctype = '".$http_post_vars['a27']."',
    street1A = '".$http_post_vars['a28']."',
    street2A = '".$http_post_vars['a29']."',
    street3A = '".$http_post_vars['a30']."',
    cc_city = '".$http_post_vars['a31']."',
    cc_state = '".$http_post_vars['a32']."',
    cc_zip = '".$http_post_vars['a33']."',
    trblfone = '".$http_post_vars['a34']."'
    where job_id = \"$sel_job\"";

    $sql_result = @mysql_query($sql) or die ("Couldn't execute query");
    echo "
    <HTML>
    <HEAD>
    <TITLE></TITLE>
    </HEAD>
    <BODY background=\"rimmabg.jpg\">
    <FORM METHOD=\"POST\" ACTION=\"jl2s1s.php\">

    <input name=\"var1\" value=\"Return To Admin Home Page\" Type=\"submit\">

    </form>
    </BODY>
    </HTML>
    ";
    };
    ?>
    -----------------------------------------------------------------------------

    Why is the update statement and/or the SQL statement not working? Thanks for the earlier help I received and any help I receive now to finish the problem up...... Jerry Wing
    Last edited by jwing; May 16, 2002 at 06:59.

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well as queries go, it looks OK but there may be something like a typo in a column name or something like that.

    While you're developing, recommend a couple of things;

    PHP Code:
    $sql_result = @mysql_query($sql) or die ("Couldn't execute query"); 
    Here you're killing the real error message from MySQL. For development recommend;

    PHP Code:
    $sql_result mysql_query($sql) or die ("Error in update:" mysql_error() . "<br>Query was: " $sql); 
    You then get a more detailed error message and the $sql variable which you could then try running manually against MySQL in phpMyAdmin, if the error message doesn't help

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Harry, let me try the error message function and then I will get back to you about the error message if i do not understand it... Jerry Wing

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Harry, with the error code syntax I get no error message at all but it does not update.... there are no parse errors.... I wrote the scripts to add the record and it adds to all fields, i wrote the scripts to delete a record and it deletes without a problem, the only thing that does not work is the update script, and only the short script (above) does not work...

  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like the Update syntax is correct but what it's actually doing is wrong - perhaps updating the wrong row.

    Why not put in an

    PHP Code:
    echo ( $sql ); 
    after the update anyway, which will show you what the query was. You can then probably see what's going wrong.

    Main thing when you come across problems like this is not to do it blind.

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where job_id = \"$sel_job\"";
    As far as I can see, you don't transmit this var ($sel_job) over to the php script where you want to UPDATE your table. Put it in a hidden form field, and also get it via HTTP_POST_VARS.

    Also, Perhaps it's safer to NOT use spaces for vars and select buttons, just use ONE word. That's hardly the real problem here, but safe is safe.


    If ($var2=="Return To Admin Home Page"){

    By the way: I'd strongly recommend to use the "mysql_fetch_object" syntax, this makes life easier, especially with so many vars and fields ;-)

    One last thing: Did you try the same UPDATE query in phpMyAdmin? If it is successful there (or via commandline), the query itself is OK. This further helps to determine the error (php or sql).


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
  •