Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209

    My Sql php insert/edit errors

    Hi,

    I am trying to create a form to insert/edit into a mysql database. I used the template from here:Basic PHP System: View, Edit, Add, Delete records with MySQLi - Killersites Community

    I got the delete working, but I cant seem to get the Edit/Create working.

    There are 2 errors for both create and edit.
    The edit errors are as follows:

    Warning: mysqli::repare() [mysqli.prepare]: (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out = ?, outbywho = ? WHERE id=?' at line 1 in /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 86
    ERROR: could not prepare SQL statement.

    Warning: Cannot modify header information - headers already sent by (output started at /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php:86) in/home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 99
    Line 86 is in blue, 99 is in red.

    The Create New error is:
    Warning: mysqli::repare() [mysqli.prepare]: (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out, outbywho) VALUES (?, ?, ?, ?)' at line 1 in /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 174
    ERROR: Could not prepare SQL statement.
    Warning: Cannot modify header information - headers already sent by (output started at /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php:174) in/home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 187
    Line 174 is in green, 187 is in orange

    Wupps, due to large amount of words, I'll have to post the code as a comment...
    Last edited by yoshee08; Oct 11th, 2013 at 08:53 AM.

  2.  

  3. #2
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209
    My code is:

    [CODE]<?php
    /*
    Allows the user to both create new records and edit existing records
    */


    // connect to the database
    include("connect-db.php");


    // creates the new/edit record form
    // since this form is used multiple times in this file, I have made it a function that is easily reusable
    function renderForm($name = '', $type = '', $out = '', $outbywho = '', $error = '', $id = '')
    { ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    <?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?>
    </title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    </head>
    <body>
    <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
    <?php if ($error != '') {
    echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
    . "</div>";
    } ?>

    <form action="" method="post">
    <div>
    <?php if ($id != '') { ?>
    <input type="hidden" name="id" value="<?php echo $id; ?>" />
    <p>ID: <?php echo $id; ?></p>
    <?php } ?>

    <strong>Name: *</strong> <input type="text" name="name"
    value="<?php echo $name; ?>"/><br/>
    <strong>Type: *</strong> <input type="text" name="type"
    value="<?php echo $type; ?>"/><br/>
    <strong>Out: *</strong> <input type="text" name="out"
    value="<?php echo $out; ?>"/><br/>
    <strong>Out By Who: *</strong> <input type="text" name="outbywho"
    value="<?php echo $outbywho; ?>"/>
    <p>* required</p>
    <input type="submit" name="submit" value="Submit" />
    </div>
    </form>
    </body>
    </html>

    <?php }






    /*


    EDIT RECORD


    */
    // if the 'id' variable is set in the URL, we know that we need to edit a record
    if (isset($_GET['id']))
    {
    // if the form's submit button is clicked, we need to process the form
    if (isset($_POST['submit']))
    {
    // make sure the 'id' in the URL is valid
    if (is_numeric($_POST['id']))
    {
    // get variables from the URL/form
    $id = $_POST['id'];
    $name = htmlentities($_POST['name'], ENT_QUOTES);
    $type = htmlentities($_POST['type'], ENT_QUOTES);
    $out = htmlentities($_POST['out'], ENT_QUOTES);
    $outbywho = htmlentities($_POST['outbywho'], ENT_QUOTES);

    // check that firstname and lastname are both not empty
    if ($name == '' || $type == '' || $out == '' || $outbywho == '')
    {
    // if they are empty, show an error message and display the form
    $error = 'ERROR: Please fill in all required fields!';
    renderForm($name, $type, $out, $outbywho, $error, $id);
    }
    else
    {
    // if everything is fine, update the record in the database
    if ($stmt = $mysqli->prepare("UPDATE equipment SET name = ?, type = ?, out = ?, outbywho = ?
    WHERE id=?"))
    {
    $stmt->bind_param("ssi", $name, $type, $out, $outbywho, $id);
    $stmt->execute();
    $stmt->close();
    }
    // show an error message if the query has an error
    else
    {
    echo "ERROR: could not prepare SQL statement.";
    }

    // redirect the user once the form is updated
    header("Location: view.php");
    }
    }
    // if the 'id' variable is not valid, show an error message
    else
    {
    echo "Error!";
    }
    }
    // if the form hasn't been submitted yet, get the info from the database and show the form
    else
    {
    // make sure the 'id' value is valid
    if (is_numeric($_GET['id']) && $_GET['id'] > 0)
    {
    // get 'id' from URL
    $id = $_GET['id'];

    // get the recod from the database
    if($stmt = $mysqli->prepare("SELECT * FROM equipment WHERE id=?"))
    {
    $stmt->bind_param("i", $id);
    $stmt->execute();

    $stmt->bind_result($id, $name, $type, $out, $outbywho);
    $stmt->fetch();

    // show the form
    renderForm($name, $type, $out, $outbywho, NULL, $id);

    $stmt->close();
    }
    // show an error if the query has an error
    else
    {
    echo "Error: could not prepare SQL statement";
    }
    }
    // if the 'id' value is not valid, redirect the user back to the view.php page
    else
    {
    header("Location: view.php");
    }
    }
    }






    /*


    Great...Theres still too much...Another post

  4. #3
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209
    NEW RECORD


    */
    // if the 'id' variable is not set in the URL, we must be creating a new record
    else
    {
    // if the form's submit button is clicked, we need to process the form
    if (isset($_POST['submit']))
    {
    // get the form data
    $name = htmlentities($_POST['name'], ENT_QUOTES);
    $type = htmlentities($_POST['type'], ENT_QUOTES);
    $out = htmlentities($_POST['out'], ENT_QUOTES);
    $outbywho = htmlentities($_POST['outbywho'], ENT_QUOTES);

    // check that firstname and lastname are both not empty
    if ($name == '' || $type == '' || $out == '' || $outbywho == '')
    {
    // if they are empty, show an error message and display the form
    $error = 'ERROR: Please fill in all required fields!';
    renderForm($name, $type, $out, $outbywho, $error);
    }
    else
    {
    // insert the new record into the database
    if ($stmt = $mysqli->prepare("INSERT equipment (name, type, out, outbywho) VALUES (?, ?, ?, ?)"))
    {
    $stmt->bind_param("ss", $name, $type, $out, $outbywho);
    $stmt->execute();
    $stmt->close();
    }
    // show an error if the query has an error
    else
    {
    echo "ERROR: Could not prepare SQL statement.";
    }

    // redirec the user
    header("Location: view.php");
    }

    }
    // if the form hasn't been submitted yet, show the form
    else
    {
    renderForm();
    }
    }

    // close the mysqli connection
    $mysqli->close();
    ?>
    [/CODE]
    Thanks so much!
    yosh

  5. #4
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209
    I fixed the new row errors, Now, I'm getting another error for the edit part. Its totaly diffrent. Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in/home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 88

    Warning: mysqli_stmt::execute() [mysqli-stmt.execute]: (HY000/2031): No data supplied for parameters in prepared statement in /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 89

    Warning: Cannot modify header information - headers already sent by (output started at /home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php:88) in/home/content/26/8887926/html/Yoshee08/troop3/equipment/2/records.php on line 99

    I'll see if I can fix it.

  6. #5
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209
    I Got it folks!

    Thanks nyway.

  7. #6
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,483
    Member #
    425
    Liked
    2783 times
    Cool. Care to share with others?
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  8. #7
    Member
    Join Date
    Mar 2012
    Posts
    55
    Member #
    31209
    The first error-"out" is a reserved word. I changed it to "out 1"

    The seccond-the line "$stmt->bind_param("ssi", $name, $type, $out, $outbywho, $id);", as you add columns, you need to add the equivalent number of s's. "i" stands for "id". Hope that helps people!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:44 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com