SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: sql query fails

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    Melbourne Australia
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query fails

    Hi,

    I'm trying to use the following php script to build a media contacts database. I borrowed it from another site. In its original form, it works well. However, when I put my variables in it gives me this message

    SQL query failed: update enquiries set date = '0000-00-00', taken_by = 'chalk', details_recorded = 'chalkchalkchalkchalkchalkchalkchalk', journalist = 'chalkchalkchalk', media_outlet = 'chalkchalkchalk', phone_no = 'chalkchalkch', where id = '25'

    when I go to update the enquiry form (this uses the edit_commit function). (The whole thing works pretty well until I get to this step) I have tried numerous things to rectify it, but am not getting anywhere.

    Any help would be appreciated.


    <!doctype html public "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    <head>
    <title>DB Admin Modules</title>

    </head>
    <body bgcolor=#5A8FA0>

    <?php

    switch($action) {

    case "add_record":
    add_record();
    break;

    case "add_commit":
    add_commit($f_date, $f_taken_by, $f_details_recorded, $f_journalist, $f_media_outlet, $f_phone_no);
    break;

    case "delete_record":
    delete_record($f_id, $f_journalist);
    break;

    case "delete_commit":
    delete_commit($f_id, $f_journalist);
    break;

    case "edit_commit":
    edit_commit($f_id, $f_date, $f_taken_by, $f_details_recorded, $f_journalist, $f_media_outlet, $f_phone_no);
    break;

    default:
    edit_record($f_id);
    break;
    }

    function edit_record($f_id) {

    $hostname = 'localhost';
    $dbname = 'media';
    $user = '';
    $passwd = '';
    $debug = '1'; # set to 0 to turn off debug

    $sql = "select * from enquiries where id = \"".$f_id."\"";

    $dblink = mysql_connect($hostname, $user, $passwd)
    or die ("Error: No connection to MySQL server\n");
    mysql_select_db($dbname,$dblink)
    or die ("Error: MySQL database not selected\n");

    $result = mysql_query($sql, $dblink)
    or die ("SQL query failed: $sql");

    if ($myrow = mysql_fetch_array($result)) {
    # Display the SQL query
    if ($debug) {
    echo "\n<p><font color=\"red\"><tt>$sql</tt></font></p>\n\n";
    }
    # Display the Intro and Add_record option at top
    echo "<center>\n";
    echo "<h1>DB Edit/Delete Form</h2>\n";
    echo "<h2><font color=\"red\">Record: $f_id</font></h2>";

    # Start form header
    echo "<form method=\"post\" action=\"journoadmin.php\">\n";
    # Build the table header
    echo "<table border=\"1\" width=\"95%\" cellpadding=\"3\" cellspacing=\"0\">\n";
    # Populate the table
    echo "<!-- Begin MySQL data presentation -->\n";

    echo "<tr>\n";
    echo "\t<td>ID</td>\n";
    echo "\t<td>".$myrow["id"]."</td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Date/td>\n";
    echo "\t<td><Input type=\"text\" size=\"40\" name=\"f_date\" ";
    echo "value=\"".$myrow["date"]."\"></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Taken By/td>\n";
    echo "\t<td><Input type=\"text\" size=\"40\" name=\"f_taken_by\" ";
    echo "value=\"".$myrow["taken_by"]."\"></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Details Recorded</td>\n";
    echo "<td><textarea name=\"f_details_recorded\" rows=\"5\" cols=\"35\">";
    echo "".$myrow["details_recorded"]."</textarea></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Journalist</td>\n";
    echo "\t<td><input type=\"text\" size=\"40\" name=\"f_journalist\" ";
    echo "value=\"".$myrow["journalist"]."\"></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Media Outlet</td>\n";
    echo "\t<td><input type=\"text\" size=\"40\" name=\"f_media_outlet\" ";
    echo "value=\"".$myrow["media_outlet"]."\"></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Phone Number</td>\n";
    echo "\t<td><input type=\"text\" size=\"40\" name=\"f_phone_no\" ";
    echo "value=\"".$myrow["phone_no"]."\"></td>\n";
    echo "</tr>\n";

    # Close the database link
    mysql_close($dblink);
    # Close the table and post the summary
    echo "<!-- End MySQL data presentation -->\n\n";
    echo "</table>\n";

    # Close Update Form
    echo "<p>";
    echo "<input type=\"hidden\" name=\"action\" value=\"edit_commit\">\n";
    echo "<input type=\"hidden\" name=\"f_id\" value=\"".$f_id."\">\n";
    echo "<input type=\"submit\" value=\"Update this Record\">";
    echo "</form></p>\n\n";

    # Set Delete Form
    echo "<p><form method=\"post\" action=\"journoadmin.php\">\n";
    echo "<input type=\"hidden\" name=\"action\" value=\"delete_record\">\n";
    echo "<input type=\"hidden\" name=\"f_id\" value=\"".$f_id."\">\n";
    echo "<input type=\"hidden\" name=\"f_journalist\" value=\"".$myrow["journalist"]."\">\n";
    echo "<input type=\"submit\" value=\"Delete this Record\">";
    echo "</form></p>\n\n";
    echo "</center>\n";

    } else {
    echo "<h1>Edit_Form</h1>\n";
    echo "<p>Sorry, no record found ...</p>\n";
    }
    }

    function edit_commit($f_id, $f_date, $f_taken_by, $f_details_recorded, $f_journalist, $f_media_outlet, $f_phone_no) {

    $hostname = 'localhost';
    $dbname = 'media';
    $user = 'root';
    $passwd = 'guardian';
    $debug = '0'; # set to 0 to turn off debug

    $sql = "update enquiries ";
    $sql .= "set date = '".$f_date."', ";
    $sql .= "taken_by = '".$f_taken_by."', ";
    $sql .= "details_recorded = '".$f_details_recorded."', ";
    $sql .= "journalist = '".$f_journalist."', ";
    $sql .= "media_outlet = '".$f_media_outlet."', ";
    $sql .= "phone_no = '".$f_phone_no."', ";
    $sql .= "where id = '".$f_id."'";
    $dblink = mysql_connect($hostname, $user, $passwd)
    or die ("Error: No connection to MySQL server\n");
    mysql_select_db($dbname,$dblink)
    or die ("Error: MySQL database not selected\n");

    $result = mysql_query($sql, $dblink)
    or die ("SQL query failed: $sql");

    if ($debug) {
    echo "<p>$sql</p>\n";
    }

    echo "<center><h2>Record $f_idbr>\n";
    echo "<i>$f_taken_by</i><br>\n";
    echo "$f_journalist<br>\n";
    echo "$f_media_outlet<br>\n";
    echo "$f_phone_no<br>\n";
    echo "has been updated!! Thank you!</h2>\n";
    echo "<p><a href=\"Journoform.html\">Return to DB Search/Add Form</a></p>\n";
    echo "</center>\n";
    }

    function delete_record($f_id, $f_ti) {

    if ($debug) {
    echo "<p>$sql</p>\n";
    }

    echo "<center><h2>Caution!!<br>\n";
    echo "<i>$f_ti</i><br>\n";
    echo "Is about to be deleted!! Do you meant it?</h2>\n";
    echo "<p><form method=\"post\" action=\"journoadmin.php\">\n";
    echo "<input type=\"hidden\" name=\"f_id\" value=\"$f_id\">\n";
    echo "<input type=\"hidden\" name=\"action\" value=\"edit_record\">\n";
    echo "<input type=\"submit\" value=\"NO, Get me outa here fast!\">\n";
    echo "</form></p>\n";

    echo "<p><form method=\"post\" action=\"journoadmin.php\">\n";
    echo "<input type=\"hidden\" name=\"f_id\" value=\"$f_id\">\n";
    echo "<input type=\"hidden\" name=\"f_journalist\" value=\"$f_journalist\">\n";
    echo "<input type=\"hidden\" name=\"action\" value=\"delete_commit\">\n";
    echo "<input type=\"submit\" value=\"YES, Delete the sucker!\">\n";
    echo "</form></p>\n";

    }

    function delete_commit($f_id, $f_journalist) {

    $hostname = 'localhost';
    $dbname = 'media';
    $user = '';
    $passwd = '';
    $debug = '0'; # set to 0 to turn off debug

    $sql = "delete from enquiries ";
    $sql .= "where id = '".$f_id."'";
    $dblink = mysql_connect($hostname, $user, $passwd)
    or die ("Error: No connection to MySQL server\n");
    mysql_select_db($dbname,$dblink)
    or die ("Error: MySQL database not selected\n");

    $result = mysql_query($sql, $dblink)
    or die ("SQL query failed: $sql");

    if ($debug) {
    echo "<p>$sql</p>\n";
    }

    echo "<center><h2>Record $f_idbr>\n";
    echo "<i>$f_journalist</i><br>\n";
    echo "Has been deleted!! Hope you meant it!</h2>\n";
    echo "<p><a href=\"journoform.html\">Return to DB Search/Add Form</a></p>\n";
    }

    function add_record() {

    $hostname = 'localhost';
    $dbname = 'media';
    $user = '';
    $passwd = '';
    $debug = '0'; # set to 0 to turn off debug

    # Display the Intro and Add_record option at top
    echo "<center>\n";
    echo "<h1>New Enquiry</h2>\n";

    # Start form header
    echo "<form method=\"post\" action=\"journoadmin.php\">\n";
    # Build the table header
    echo "<table border=\"1\" cellpadding=\"3\" cellspacing=\"0\">\n";
    # Populate the table
    echo "<!-- Begin MySQL data presentation -->\n";

    echo "<tr>\n";
    echo "\t<td>Date</td>\n";
    echo "\t<td><input type=\"text\" size=\"20\" name=\"f_date\"> </td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Taken By</td>\n";
    echo "\t<td><input type=\"text\" size=\"20\" name=\"f_taken_by\"> </td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Details Recorded</td>\n";
    echo "\t<td><textarea name=\"f_details_recorded\" rows=\"5\" cols=\"35\"></textarea></td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td>Journalist</td>\n";
    echo "\t<td><input type=\"text\" size=\"20\" name=\"f_journalist\"> </td>\n";
    echo "</tr>\n";
    echo "\t<td>Media Outlet</td>\n";
    echo "\t<td><input type=\"text\" size=\"20\" name=\"f_media_outlet\"> </td>\n";
    echo "</tr>\n";
    echo "<tr>\n";
    echo "\t<td width>Phone Number</td>\n";
    echo "\t<td><input type=\"text\" size=\"20\" name=\"f_phone_no\"></td>\n";
    echo "</tr>\n";
    echo "</table>\n";
    echo "<input type=\"hidden\" name=\"action\" value=\"add_commit\">\n";
    echo "<p><input type=\"submit\" value=\"Add Record\"></p>\n";
    echo "</center>\n";
    }

    function add_commit($f_date, $f_taken_by, $f_details_recorded, $f_journalist, $f_media_outlet, $f_phone_no) {

    $hostname = 'localhost';
    $dbname = 'media';
    $user = '';
    $passwd = '';
    $debug = '0'; # set to 0 to turn off debug

    $sql = "insert into enquiries (date, taken_by, details_recorded, journalist, media_outlet, phone_no) ";
    $sql .= "values ( '".$f_date."','".$f_taken_by."', '".$f_details_recorded."','".$f_journalist."','".$f_media_outlet."','".$f_phone_no."')";

    # Display the SQL query
    if ($debug) {
    echo "\n<p><font color=\"red\"><tt>$sql</tt></font></p>\n\n";
    }

    $dblink = mysql_connect($hostname, $user, $passwd)
    or die ("Error: No connection to MySQL server\n");
    mysql_select_db($dbname,$dblink)
    or die ("Error: MySQL database not selected\n");

    # Insert the new record
    $result = mysql_query($sql, $dblink)
    or die ("SQL query failed: $sql");

    # Obtain the new record ID
    $f_id = mysql_insert_id($dblink);

    # Close the database link
    mysql_close($dblink);
    # Close the table and post the summary
    if ($debug) {
    echo "<p>$f_id = ".$f_id."</p>\n";
    }
    echo "<center>\n";
    echo "<h2>Record Successfully Added!!</h2>\n";

    echo "<p><i>$f_date</i><br>\n";
    echo "$f_taken_by<br>\n";
    echo "$f_journalist<br>\n";
    echo "$f_media_outlet</p>\n";
    echo "<p>[ <a href=\"journoadmin.php?f_id=".$f_id."&action=edit_record\">View Added Record</a> | \n";
    echo "<a href=\"journoform.html\">Return to DB Search/Add Form</a> ]</p>\n";
    echo "</center>\n";
    }

    ?>

    </body>
    </html>

    signature

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    phone_no = 'chalkchalkch', where id = '25'
    did you see comma before where id='25'? Remove that
    Are you sure id is not integer based? if it is integer then change it to id=25

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    Melbourne Australia
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query failed

    Thank you.

    That worked.
    signature


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
  •