PHP: Insert into sql

Question

I've been trying to get this to work for almost a week, and I'm not sure why it doesn't work. I have another page very similar and it works no problem. Am I missing something simple here? It connects to the database, but no data gets inserted. I checked the sql syntax 3 times and used an online checker, everything looks fine to me.

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Authorizing Login</title>
</head>
<body>
<?php
    session_start();
    $username = $_SESSION["username"];
    echo "'$username'";
    $address = $_POST["address"];
    $city = $_POST["city"];
    $state = $_POST["state"];
    $ccno = $_POST["CCNo"];
    $ccexpm = $_POST["CCexpM"];
    $ccexpy = $_POST["CCexpY"];
    $query = "INSERT INTO Users (address,city,state,ccno,ccexpm,ccexpy)   VALUES (?,?,?,?,?,?) WHERE username=$username";
    echo "$query";
    connectdb();
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("ssssss", $address,$city,$state,$ccno,$ccexpm,$ccexpy);
    $stmt->execute();
?>

<?php 


function getDbParms()
{

    $trimmed = file('parms/dbparms.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    $key = array();
    $vals = array();
    foreach($trimmed as $line)
    {
        $pairs = explode("=",$line);
        $key[]=$pairs[0];
        $vals[]=$pairs[1];
    }

    $mypairs = array_combine($key,$vals);

    $myDbparms = new DBparmsClass($mypairs['username'],$mypairs['password'],
                    $mypairs['host'],$mypairs['db']); 
    return $myDbparms;     
}

function connectdb()
{
    $mydbparms = getDbParms();
    $mysqli = new mysqli($mydbparms->getHost,$mydbparms->getUsername(),
                    $mydbparms->getPassword(),$mydbparms->getDb()); 

    if($mysqli->connect_error)
    {
        die('Connect Error (' . $mysqli->connect_errno . ')' . $mysqli->connect_error);
    }
    else
    {
        echo"Connected";
    }

    return $mysqli;

}

class DBparmsClass
{
    private $username="";
    private $password="";      
    private $host="";      
    private $db="";

    // Constructor      
    public function __construct($myusername,$mypassword,$myhost,$mydb)      
    {       
        $this->username = $myusername;        
        $this->password = $mypassword;     
        $this->host = $myhost;    
        $this->db = $mydb;     
    } 

    // Get methods     
    public function getUsername ()      
    {       
        return $this->username;      
    }  

    public function getPassword ()      
    {       
        return $this->password;      
    }      
    public function getHost ()      
    {       
        return $this->host;      
    }      
    public function getDb ()      
    {       
        return $this->db;      
    }    

    // Set methods       
    public function setUsername ($myusername)      
    {       
        $this->username = $myusername;           
    }      
    public function setPassword ($mypassword)      
    {       
        $this->password = $mypassword;           
    }      
    public function setHost ($myhost)      
    {       
        $this->host = $myhost;           
    }      
    public function setDb ($mydb)      
    {       
        $this->db = $mydb;          
    }            
}


?>


</body>
</html>

you need to set the `session_start` before ANY content is sent to the browser — Professor Abronsius, Dec 05 '15 at 16:30
probably outputting before header. Add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Dec 05 '15 at 16:31
also INSERT doesn't have a WHERE clause, not like that anyway. You may have wanted to use UPDATE, hard to say. http://dev.mysql.com/doc/en/insert.html --- http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html --- check for errors http://php.net/manual/en/mysqli.error.php — Funk Forty Niner, Dec 05 '15 at 16:31
`$stmt->execute();` change that to `if(!$stmt->execute()){trigger_error("there was an error....".$mysqli->error, E_USER_WARNING);}` and you'll see the syntax error. Plus, your HTML form's unknown. You need to debug your code. — Funk Forty Niner, Dec 05 '15 at 16:34
and *technically speaking*, `WHERE username=$username";` username is most likely a string and should be quoted `WHERE username='$username'";` yet, even by doing that, it's still going to fail, because that's not how INSERT works. — Funk Forty Niner, Dec 05 '15 at 16:41
@Fred-ii- I updated my query to UPDATE Users SET address=?,city=?,state=?,ccno=?,ccexpm=?,ccexpy=? WHERE username=$username, I didn't know insert didn't have a where clause, thanks! I also added the error reporting and get this: Fatal error: Call to a member function bind_param() on a non-object in /var/www/html/week4/orderauth.php on line 30. Is it saying my $mysqli object is not declared? — Rwarfield, Dec 05 '15 at 17:00
`WHERE username='$username'` like I said earlier, that's a string (most likely) and not an integer. Quote strings; always. — Funk Forty Niner, Dec 05 '15 at 17:03
Wow I can't believe I missed that, thanks for all your help! I really appreciate it, I'm really new to php and sql. Thanks! — Rwarfield, Dec 05 '15 at 17:07
I'll post an answer to close this one, and you're welcome. Will make a mention of something else also. Give me a minute and I'll formulate something for you. — Funk Forty Niner, Dec 05 '15 at 17:12

score 0 · Accepted Answer · edited May 23 '17 at 11:50

Firstly, you may be outputting before header having session_start(); in its present position.

Consult:

How to fix "Headers already sent" error in PHP

Put it before any output.

<?php 
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>
<!DOCTYPE html>
... rest of your code.

Then, INSERT doesn't have a WHERE clause, not like that anyway.

You may have wanted to use UPDATE.

References:

Then you need to quote your username variable, since it's a string.

WHERE username = '$username'";

However, that still leaves you open to an SQL injection, so parametrize that also.

WHERE username = ? and add a bind to it.

Always check for errors on your query when testing:

http://php.net/manual/en/mysqli.error.php

PHP: Insert into sql

1 Answers1