MySQL & PHP Insert Query Failing

Question

I know there isn't enough validation in here just going through some testing. $result always returns empty? Is my query bad? I'm new to PHP and concatenating variables into strings is not something I have grasped full. Going with the OOP form since I'm pretty familiar with it and the concepts.

Also, I know this code is terribly sloppy... just trying to dive right in =) `

$page = new Page();

$page->title = "Add a New Item";
$page->DisplayHeader();
$page->DisplaySidebar();

if (isset($_POST['submit']))
{
    // make short variable names
    $name = trim($_POST['name']);
    $level = intval($_POST['level']);
    $slot = strtolower($_POST['slot']);
    $hp = intval($_POST['hp']);
    $mana = intval($_POST['mana']);
    $mvs = intval($_POST['mvs']);
    $int = intval($_POST['int']);
    $wis = intval($_POST['wis']);
    $str = intval($_POST['str']);
    $dex = intval($_POST['dex']);
    $con = intval($_POST['con']);
    $p_ac = intval($_POST['p_ac']);
    $m_ac = intval($_POST['m_ac']);
    $saves = intval($_POST['saves']);
    $hit = intval($_POST['hit']);
    $dam = intval($_POST['dam']);


    $queryOk = 1;

    if (empty($name) || empty($level) || empty($slot))
    {
        echo '<h3>Please enter all the required fields</h3>';
        $queryOk = 0;
    }

    // Instantiate database object and connect
    @ $db = new mysqli('*host*', '*user*', '*pass*', '*database*');

    // Check connection to 
    if (mysqli_connect_errno()) {
        echo 'Error:  Could not connect to database, try again later';
    }

    $query = "INSERT INTO items (name, level, slot, hp, mana, mvs, int, wis, str, dex, con, p_ac, m_ac, saves, hit, dam)".
    "V ALUES ('$name', $level, '$slot', $hp, $mana, $mvs, $int, $wis, $str, $dex, $con, $p_ac, $m_ac, $saves, $hit, $dam)";

    $result = $db->query($query);

    if (!$result)
    {
        echo '<h3>Error:  Item was not entered.  (Your webmaster sucks)</h3>';
    }
    else    {
        echo "<p>The items \"$name\" was successfully entered into the database.  <a href=\"equipment.php\>Back to Equipment or add another item.</a></p>";
    }

    $db->close();
}`

why is there a space between V and ALUES, isn't VALUES one word? 0.o — Grigor
– Grigor, Commented Jun 15, 2012 at 3:31

John Conde · Accepted Answer · 2012-06-15 03:10:57Z

2

If the space in V ALUES is actually in your code that would cause your query to fail

UPDATE

If that isn't the cause of the error use $mysqli->error to see what error occurred.

if (!$result)
{
    echo '<h3>'$mysqli->error'  (Your webmaster sucks)</h3>';
}

edited Jun 15, 2012 at 3:10

answered Jun 15, 2012 at 3:03

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sachleen Over a year ago

Ah you beat my by just a few seconds! +1

Ryan_K Over a year ago

Oh sorry, not sure why it's like that here, but It isn't in the source.

Ryan_K Over a year ago

That space isn't in the source. Removed the ampersand and no error were shown.

Ryan_K Over a year ago

(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 'int, wis, str, dex, con, p_ac, m_ac, saves, hit, dam)VALUES ('test', 7, 'light',' at line 1 Item was not entered.

John Conde Over a year ago

Ok, it's a SQL error. Try putting a space before the word VALUES and see what happens.

Marc B · Accepted Answer · 2012-06-15 03:39:04Z

0

int is a reserved word in mysql, and you're using it as a fieldname. You'll have to escape it with backticks:

INSERT INTO ... (..., `int`, ...)
                      ^---^-- escapes

your query:

INSERT INTO items (name, level, slot, hp, mana, mvs, int, wis, str, dex, con, p_ac, m_ac, saves, hit, dam)
                                                     ^^^^--- problem here
VALUES ('$name', $level, '$slot', $hp, $mana, $mvs, $int, $wis, $str, $dex, $con, $p_ac, $m_ac, $saves, $hit, $dam)";
                                                   ^^^^^---NOT here

edited Jun 15, 2012 at 3:39

answered Jun 15, 2012 at 3:27

Marc B

362k44 gold badges433 silver badges508 bronze badges

4 Comments

Marc B Over a year ago

uh, no, not the value you're inesrting. the literal field name. It may mean "intelligence" to you, but to MySQL, it's "integer".

Ryan_K Over a year ago

I replaced it in the values in the query like you explained? Is that not correct?

Marc B Over a year ago

no, it's not correct. see my edit above. Note the lack of a $ in my original sample. e.g. I mean the name of the field you're using, not the value you're inserting into that field. FIELD... not variable.

user1452106 Over a year ago

@Marc B: 'It may mean "intelligence" to you, but to MySQL, it's "integer"'. I seriously laughed out loud. :)

Collectives™ on Stack Overflow

MySQL & PHP Insert Query Failing

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related