SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  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

    I have read the Juli Meloni book, PHP Essentials, and I am doing a web site that has records that are added, modified, and deleted. I have written the add records scripts with no problem using her book. I have written the record selector script and display current information script for modifying records... but the script that UPDATES the information in the modification form does not work...

    I would appreciate it very much if someone would be willing to let me e-mail them the three scripts and see where I am wrong... it is not a big deal but I do not know how to do it..... my e-mail is jwing@erols.com and I would appreciate it if someone would take a couple of minutes to see if you can help me.....I will e-mail you the three scripts..... thank you very much.....

    Jerry wing

  2. #2
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you posted the code I am 100% sure someone here could help you out
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maelstrom... I do not know what the problem is... I would like someone to look at the three short scripts to see if they can isolate the problem.... I have an update command in a script that is both not updating and giving off a parse error....I think the first two scripts are fine... at least they are doing what they are supposed to do ... the third script is not....

    I am trying to write scripts for a small jobs web site where people can come to look at jobs... using the Meloni book PHP Essentials and several other books (including Kevin's) I have written code that (1) adds a record, (2) modifies a record..... I cannot get the modification script to update the mysql database .... and I am not sure why it won't....if someone can look at the three scripts it maybe that i have left out some important thing in the earlier scripts, but i don't think so. The scripts are very simple since that is all i know how to write....

  4. #4
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the script that updates. Or at least the update part where you define the query, and run the query.

    Another way to debug this yourself is to acho the $sql and read it. I usually create a function called debug()

    function debug()
    {
    echo $sql;
    exit;
    }

    This will break the code but echo the query you are running. Copy that query and go to the command line and play with the line directly in the mysql console.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maelstrom, I really appreciate your help. I am going to try and post the three scripts here if i can, by copying them over to this block:

    <?PHP
    // Connect to the server
    $dbcnx = @mysql_connect("localhost", "jwing", "xxxxxxxx");
    if (!$dbcnx) {
    echo( "<p>Unable to connect to the " .
    "server at this time.</p>" );
    exit();
    }

    // Select the jwing database
    if (!@mysql_select_db("jwing") ) {
    echo( "<p>Unable to locate the " .
    "jwing database at this time.</p >" );
    exit();
    }

    $sql = "select job_id, comp_id, job_title From jobs Order By comp_id Asc";

    $sql_result = mysql_query($sql) or die ("Couldn't execute query");

    if (!$sql_result) {
    echo "<p>Couldn't Get List!";
    } else {
    echo "
    <HTML>
    <HEAD>
    <TITLE></TITLE>

    </HEAD>
    <BODY background=\"rimmabg.jpg\">
    <h2 align=\"center\">Select A Job Ad From Job Data Base For Information Change</h2>
    <form method= \"Post\" action=\"jl2s.php\">

    <table cellspacing=5 cellpadding=5>
    <tr>
    <td align=right><strong>Job ID/Company ID/Job Title/strong></td>
    <td valign=top>

    <select name=\"sel_job\">
    <option value=\"\"> - - Select A Job Ad For Modifying - - </option>
    ";
    while ($row = mysql_fetch_array($sql_result)) {

    $job_id = $row["job_id"];
    $comp_id = $row["comp_id"];
    $job_title = $row["job_title"];
    echo "
    <option value=\"$job_id\">$job_id : $comp_id : $job_title</option>
    ";
    }

    echo "
    </select>
    </td>
    </tr>
    <tr>

    <td align=center colspan=2><input type=\"submit\" value=\"Select Job Ad\"></td>
    </tr>
    </table>



    </form>
    </BODY>
    </HTML>
    ";
    }
    --------------------------------------------------------------------
    This is script one which displays the pick menu for picking records from a mysql data base. it works fine. next reply is script two.
    Last edited by jwing; May 11, 2002 at 04:58.

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maelstrom, here is script two. It takes the unique job number from script one that is passed in variable $sel_job and uses it to pull up all fields of the record in the database. This script works fine also up until the modify button on the bottom of the page is pressed. At that point the third script is called and that script is not working right. I wonder Maelstrom if I am missing from this script a hidden statement that would carry a variable to the third script...this script works fine.

    <?


    $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);

    ?>

    <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="<? echo $row[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="<? echo $row[keyword1]; ?>"></td>
    </TR>
    <tr><td>2</td>
    <td><input type="text" name="a2" size=15 value="<? echo $row[keyword2]; ?>"></td>
    </tr>
    <tr><td>3</td>
    <td><input type="text" name="a3" size=15 value="<? echo $row[keyword3]; ?>"></td>
    </tr>
    <tr><td>4</td>
    <td><input type="text" name="a4" size=15 value="<? echo $row[keyword4]; ?>"></td>
    </tr>
    <tr><td>5</td>
    <td><input type="text" name="a5" size=15 value="<? echo $row[keyword5]; ?>"></td>
    </tr>
    </table>

    <p>

    <TD>

    </TR>
    </TABLE>
    <p>
    <p>
    <hr>

    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Show Date: </td>
    <td>
    <input type="text" name="a6" size=8 value="<? echo $row[show_date]; ?>">
    </td>

    <td>
    Job Category: </td>
    <td>
    <input type="text" name="a7" size=3 value="<? echo $row[job_cat]; ?>">
    </td>

    <td>
    Job Location: </td>
    <td>
    <input type="text" name="a8" size=3 value="<? echo $row[job_loc]; ?>">
    </td></tr>
    </table>
    <p>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Job Title/td>
    <td>
    <input type="text" name="a9" size=80 value="<? echo $row[job_title]; ?>">
    </td></tr>
    </table>
    <p>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Job Description/td>
    <td>
    <textarea name=\"jobdesc\" rows=40 cols=60 wrap="hard"><? echo $row[job_desc]; ?></textarea>
    </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>
    Job Salary/td>
    <td>
    <input type="text" name="a10" size=11 value="<? echo $row[job_salary]; ?>">
    </td></tr>
    </table>
    </td>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Ad Expire/td>
    <td>
    <input type="text" name="a11" size=8 value="<? echo $row[ad_expire]; ?>"><br>
    </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>
    First Name/td>
    <td>
    <input type="text" name="a12" size=40 value="<? echo $row[first_name]; ?>"><br>
    </td></tr>
    </table>
    </td>
    </tr>
    <tr>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Last Name/td>
    <td>
    <input type="text" name="a13" size=40 value="<? echo $row[last_name]; ?>"><br>
    </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>
    E-Mail For Job Ad/td>
    <td>
    <input type="text" name="a14" size=40 value="<? echo $row[ad_email]; ?>"><br>
    </td></tr>
    </table>
    </td>
    </tr>
    <tr>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Private Contact E-Mail/td>
    <td>
    <input type="text" name="a15" size=40 value="<? echo $row[cntct_email]; ?>">
    </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>
    User Name/td>
    <td>
    <input type="text" name="a16" size=15 value="<? echo $row[usr_name]; ?>">
    </td></tr>
    </table>
    </td>
    </tr>
    <tr>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Password/td>
    <td>
    <input type="text" name="a17" size=15 value="<? echo $row[p_word]; ?>">
    </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>
    Advertisement Phone No./td>
    <td>
    <input type="text" name="a18" size=12 value="<? echo $row[ad_phone]; ?>"><br>
    </td>
    </table>
    </td>


    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <td>
    Private Contact Phone No./td>
    <td>
    <input type="text" name="a19" size=12 value="<? echo $row[cntct_phone]; ?>">
    </td>
    </table>
    </td>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <td>
    Advertisement Fax Phone No./td>
    <td>
    <input type="text" name="a20" size=12 value="<? echo $row[ad_faxphone]; ?>"><br>
    </td>
    </table>
    </td></tr>
    </table>
    <P>


    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver">
    <tr><td>
    Web Site/td>
    <td>
    <input type="text" name="a21" size=50 value="<? echo $row[web_site]; ?>"><br>
    </td>
    </table>

    <hr>
    <h3 align="center">Credit Card Information</h3>
    <hr>
    <p>

    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Name On Card/td>
    <td><input type="text" name="a22" size=70 value="<? echo $row[ccname]; ?>"></td></tr>
    </table>
    <p>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Credit Card Number/td>
    <td><input type="text" name="a23" size=30 value="<? echo $row[ccnumber]; ?>"></td></tr>
    </table>
    <p>

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

    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Expiration Month/td>
    <td><input type="text" name="a24" size=2 value="<? echo $row[monthexp]; ?>"></td></tr>
    </table>
    <p>
    </td>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Expiration Year/td>
    <td><input type="text" name="a25" size=2 value="<? echo $row[yearexp]; ?>"></td></tr>
    </table>
    <p>
    </td>
    <td>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    (V)isa,(M)astercard,(A)merican Express/td>
    <td><input type="text" name="a26" size=2 value="<? echo $row[cctype]; ?>"></td></tr>
    </table>
    <p>
    </td>
    </tr>
    </table>
    <p>
    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Street 1/td>
    <td><input type="text" name="a27" size=80 value="<? echo $row[street1A]; ?>"></td></tr>
    </table>
    <p>

    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Street 2/td>
    <td><input type="text" name="a28" size=80 value="<? echo $row[street2A]; ?>"></td></tr>
    </table>
    <p>

    <table border=3 bordercolor="\#66CCFF" cellspacing=7 align="center" bgcolor="silver"><TR><TD>
    Street 3/td>
    <td><input type="text" name="a29" size=80 value="<? echo $row[street3A]; ?>"></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>
    City/td>
    <td><input type="text" name="a30" size=40 value="<? echo $row[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="a31" size=2 value="<? echo $row[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="a32" size=10 value="<? echo $row[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="a33" size=12 value="<? echo $row[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>
    <?
    }

    ?>
    Last edited by jwing; May 11, 2002 at 04:38.

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok Maelstrom, this is a shorter script but does not work... the problem is that when i hit the modify button on the second script it goes to this script and does not work:
    ------------------------------------------------------
    <?php
    If ($var2=="Return To Admin Home Page"){
    header("Location:index.htm");
    exit;
    }elseif ($var1=="Modify Database"){
    }
    ?>
    <HTML>
    <HEAD>
    <TITLE></TITLE>
    <META NAME="generator" CONTENT="">
    </HEAD>
    <BODY background="rimmabg.jpg">
    <FORM METHOD="POST" ACTION="jl2s1s.php">
    <?PHP

    // Connect to the server
    $dbcnx = @mysql_connect("localhost", "jwing", "xxxxxxxxxxx");
    if (!$dbcnx) {
    echo( "<p>Unable to connect to the " .
    "cedar server at this time.</p>" );
    exit();
    }

    // Select the jwing database
    if (! @mysql_select_db("jwing") ) {
    echo( "<p>Unable to locate the " .
    "jwing database at this time.</p >" );
    exit();
    }

    $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"]."',
    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"]."',
    ccname = '".$http_post_vars["a22"]."',
    ccnumber = '".$http_post_vars["a23"]."',
    monthexp = '".$http_post_vars["a24"]."',
    yearexp = '".$http_post_vars["a25"]."',
    cctype = '".$http_post_vars["a26"]."',
    street1A = '".$http_post_vars["a27"]."',
    street2A = '".$http_post_vars["a28"]."',
    street3A = '".$http_post_vars["a29"]."',
    cc_city = '".$http_post_vars["a30"]."',
    cc_state = '".$http_post_vars["a31"]."',
    cc_zip = '".$http_post_vars["a32"]."',
    trblfone = '".$http_post_vars["a33"]."',
    where job_id = \"sel_job\"
    ";

    $sql_result = @mysql_query ($sql,$dbcnx) or die (mysql_error());

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

    </form>
    </BODY>
    </HTML>
    ?>

    ---------------------------------------------------------------------
    I have a couple of books I have been using to write database code. The major one is PHP Essentials by Julie Meloni and also her second book, PHP Fast and Easy....in the book they have scripts for adding a record, modifying a record, and deleteing a record. Originally I could not get the modification form to display the current data in the database, so I worked with that and finally got it to display okay. Now the problem is that I cannot get it to read the data in the modification form and update the database. I am not an experienced PHP MYSQL writer but I have amazed myself at how well I have been able to do to this point, but I really need some help here.....Could you puruse the the three scripts... and correct my error(s)? Thanks for your help Maelstrom... I think in the past you may have helped me in some ways...

    I think maybe to fix the problem I need to add one or two statements to script two and change the UPDATE statement in script three....next script is just the redirection script back to the main admin panel..... script four......
    Last edited by jwing; May 11, 2002 at 04:54.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A script to redirect me back to the main panel for the admin scripts.....

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



    No problem here.

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would help to see the exact error messages / lines from mysql or php.

    I just noticed this typo in your update query:

    UPDATE ... where job_id = \"sel_job\"


    try:
    UPDATE ... where job_id = '$sel_job'

    (missing '$')

    add for debug an echo line like

    echo "affected rows: " . mysql_affected_rows();
    if this is 0, nothing was updated
    Last edited by TheDude; May 12, 2002 at 08:48.

  10. #10
    (****** or Deleted)
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, try changing the following:

    PHP Code:
    keyword1 '".$http_post_vars["a1"]."'
    to:

    PHP Code:
    keyword1 '".$http_post_vars['a1']."'
    Note the double quotes changed to single quotes in the array...

    Do this for all of them...

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Location
    washington dc
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wizard, thank you very much ... will do. Jerry


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
  •