Can't insert value into database

Question

I'm trying to add some values to my db. I know it's reaching that block of code, but it won't actually add the data to the db. The odd thing is, the same code works fine on my WAMP server.

The data is sanitized further up. I know I should use prepared queries too, that's what I'll work on next.

I don't know why it's not working, I've gone over it twice.

From regform.php:

if($result->num_rows == 1)
{
    echo "updating records";
    $sql = "UPDATE tblAAA";
    $sql .= "SET memNumber='$memNumber',fName='$fname',lName='$lName', dob='$dob',
            country='$country',";
    $sql .= "street='$street',city='$city',state='$state', zip='$zip',email='$email',
             phone='$phone',club='$club')";
    $sql .= "WHERE memNumber = '$memNumber'";
}
else
{
    echo "adding new record";
    $sql = "INSERT INTO tblAAA "; 
    $sql .= "(memNumber,fName,lName,dob,country,street,city,state,zip,email,phone,club)";
    $sql .= "VALUES($memNumber,'$fname','$lname','$dob','$country','$street','$city','$state',
            '$zip','$email','$phone','$club')";

    $conn->query($sql);
}

$sql2 = "INSERT INTO tblBBB";
$sql2 .= "(year, memNumber)";
$sql2 .= "VALUES('2015','123')";

$conn->query($sql);
$conn->query($sql2);

From view.php:

$sql = "SELECT * FROM tblAAA";
$result = $conn->query($sql);
...
while($row = $result->fetch_assoc())
{
    echo "<tr>";
    echo "<td><span class=\"history glyphicon glyphicon-plus\"></span></td>";
    echo "<td>" . $row['fName'] . "</td>";
    echo "<td>" . $row['lName'] . "</td>";
    echo "<td>" . $row['dob'] . "</td>";

    echo "<td>" . $row['country'] . "</td>";
    echo "<td>" . $row['street'] . "</td>";
    echo "<td>" . $row['city'] . "</td>";
    echo "<td>" . $row['state'] . "</td>";
    echo "<td>" . $row['zip'] . "</td>";
    echo "<td>" . $row['phone'] . "</td>";
    echo "<td>" . $row['email'] . "</td>";

    echo "<td>" . $row['memNumber'] . "</td>";
    echo "<td>" . $row['club'] . "</td>";

    echo "<td><span class=\"edit glyphicon glyphicon-pencil\"></td>";
    echo "<td><span class=\"delete glyphicon glyphicon-remove\"></td>";

    echo "</tr>";
}

echo "Results " . $result->num_rows;

Edit: The problem was that I named a column dofb, not dob. Is there a way to get php to display an error message for that sort of thing?

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jul 22 '15 at 19:17
Not sure it will take care of the original issue, but you are running query() on the insert SQL statement twice. — Joseph, Jul 22 '15 at 19:21
@JayBlanchard, Earlier in my code, I run all the variables through real_escape_string(). That plus prepared statements should be enough to cover me against SQL injection, right? — zzxjoanw, Jul 23 '15 at 02:24
It should, but it doesn't look like you're using prepared statements. — Jay Blanchard, Jul 23 '15 at 11:29

score 4 · Answer 1 · answered Jul 22 '15 at 19:13

4

It should never work, period.

 $sql = "UPDATE tblAAA";
    $sql .= "SET memNumber=etc...";

would produce

UPDATE tblAAASET memNumber=etc...

since you have no space characters between the tablename and set keyword.

answered Jul 22 '15 at 19:13

Marc B

356,200
43
426
500

*Really* @JuanAntonioDelgado? – Jay Blanchard Jul 22 '15 at 19:17
Yes? Is there any problem with that? @JayBlanchard – NullPointerException Jul 22 '15 at 19:19
@JayBlanchard Okay. Here it's hard to know when someone is being sarcastic. No problem. – NullPointerException Jul 22 '15 at 19:26
Interesting, it was a string concat issue, but I missed which string. :) – raddevus Jul 22 '15 at 19:28
I am *never* sarcastic @JuanAntonioDelgado. Silly? Yes. Sarcastic? No. Facetious? Maybe. – Jay Blanchard Jul 22 '15 at 19:28
@JayBlanchard Sorry. My lack of english is bigger than I want. – NullPointerException Jul 22 '15 at 19:31

score 1 · Answer 2 · answered Jul 22 '15 at 19:09

1

Are those single-quotes supposed to be in the values line? Those are variables so why the single-quotes?

 $sql .= "VALUES($memNumber,'$fname','$lname','$dob','$country','$street','$city','$state',
            '$zip','$email','$phone','$club')";

Also there may be an issue because their is no space in your concatenation of the string from the first line (list of column names) to the Values item. I think the final insert string will look something like:

... )VALUES($memNumber, ...

Could cause the insert to fail.

answered Jul 22 '15 at 19:09

raddevus

8,142
7
66
87

mysql's smart enough to realize that `...)values(...` is `) values (`. – Marc B Jul 22 '15 at 19:15
The single quotes appear to be around string values. MySQL needs them there. – Joseph Jul 22 '15 at 19:19
Please do not use answers to ask questions. – Jay Blanchard Jul 22 '15 at 19:38
Even if I shouldn't have the quotes there, it would still end up inserting _something_. – zzxjoanw Jul 23 '15 at 02:34

score 0 · Answer 3 · answered Jul 22 '15 at 19:34

Make sure you use single quotes between each php variable. I noticed somewhere you wrote $memNumber instead of '$memNumber'. Also when using .= add a space to the start of the string.

EDIT: It seems you ran the same query twice so I modified the code.

Check if the following code works. It should fix most if not all of the problems.

if($result->num_rows == 1)
{
    echo "updating records";
    $sql = "UPDATE tblAAA";
    $sql .= " SET memNumber='$memNumber',fName='$fname',lName='$lName', dob='$dob',
            country='$country',";
    $sql .= " street='$street',city='$city',state='$state', zip='$zip',email='$email',
             phone='$phone',club='$club')";
    $sql .= " WHERE memNumber = '$memNumber'";
}
else
{
    echo "adding new record";
    $sql = "INSERT INTO tblAAA "; 
    $sql .= " (memNumber,fName,lName,dob,country,street,city,state,zip,email,phone,club)";
    $sql .= " VALUES('$memNumber','$fname','$lname','$dob','$country','$street','$city','$state',
            '$zip','$email','$phone','$club')";

}

$sql2 = "INSERT INTO tblBBB";
$sql2 .= " (year, memNumber)";
$sql2 .= " VALUES('2015','123')";

$conn->query($sql);
$conn->query($sql2);

Hope this helps!

Why should the OP "check this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. In addition `$memNumber` is likely meant to be a number, so not quotes would be needed. Some people, for readability, choose to leave a space at the *end* of each SQL statement. — Jay Blanchard, Jul 22 '15 at 19:39
I did try this, but there's still nothing showing in the database. — zzxjoanw, Jul 23 '15 at 02:39
+Jay Blanchard I see what you are saying about the SQL statement. I never thought about that. — David, Jul 27 '15 at 19:55

Can't insert value into database

3 Answers3