Search code examples
phpmysqlsqlinnodbmariadb

INSERT INTO statement won't insert a specific value correctly?

I am trying to use the INSERT INTO SQL statement in php. It will input everything correctly up until the last value ($bands_bio). Instead of putting in the correct information, it leaves the value blank. I have looked over everything and can't seem to find any sort of syntax errors.

$page_title = "Create a new band";

require ('includes/database.php');
require_once 'includes/bandsHeader.php';

$band_name = $conn->real_escape_string(trim(filter_input(INPUT_GET, 'band_name', FILTER_SANITIZE_STRING)));
$band_photo = $conn->real_escape_string(trim(filter_input(INPUT_GET, 'band_photo', FILTER_SANITIZE_STRING)));
$genre = $conn->real_escape_string(trim(filter_input(INPUT_GET, 'genre', FILTER_SANITIZE_STRING)));
$band_bio = $conn->real_escape_string(trim(filter_input(INPUT_GET, 'band_bio', FILTER_SANITIZE_STRING)));

echo $band_bio;

if (($band_name === "") OR ($genre === "") OR ($band_photo === "") OR ($band_bio = "")) {
    $errno = $conn->errno;
    $errmsg = $conn->error;
    echo "<div id='contentWrapper'>";
    echo "<div class='contentBox'>";
    echo "Insertion failed with: ($errno) $errmsg<br/>\n";
    echo "</div></div>";
    $conn->close();
    include 'includes/searchFooter.php';
    exit;
}

$albums = 0;

$sql = "INSERT INTO bands VALUES (NULL, '$band_name', '$genre', '$albums', '$band_bio')";

$query = @$conn->query($sql);

if (!$query) {
    $errno = $conn->errno;
    $errmsg = $conn->error;
    echo "<div id='contentWrapper'>";
    echo "<div class='contentBox'>";
    echo "Insertion failed with: ($errno) $errmsg<br/>\n";
    echo "</div></div>";
    $conn->close();
    include 'includes/footer.php';
    exit;
}

As you can see, I echoed out $band_bio in order to see if it was getting the right value from my form that uses the GET method, which it is so that's not the issue. It has no problem inserting everything correctly up until the last value, which is supposed to be the last column called band_bio in my bands table in my database. It will not output any errors or anything, either. It's almost as if it's taking the string data from the variable and removing all of the text before it inserts the information.

I have been working on this website for a few weeks now and have used the INSERT INTO statement the exact same way on other pages and it works just fine. This is the first thing that has really stumped me and I can't figure it out. Any help is appreciated.

Solution

  • When inserting, ensure that your pk (id) field is set to auto-increment. This way, you can exert more control over your queries. You should be more successful with:

    $sql = "INSERT INTO bands "
 . "(`band_name`,`genre`,`numof_albums`,`band_bio`) "
 . "VALUES ('$band_name', '$genre', '$albums', '$band_bio')";

    By not specifying the pk field, INNODB will automatically increment and insert it for you.

    The idea is that you want to specify which columns are being inserted into. Relying on column ordering by mysql is fine, but there may be something at play in your case.

    There should be no reason why band_bio would be "left off". You would get a column-mismatch error.