Help with Sql Query

lol,

I'm stuck on my second sql query. Not sure what I'm doing wrong here. The error is thrown on the prepare statement. It says unexpected "", but there's no extra quotes. It also says expecting a variable of some kind.

I know that I couldn't get my insert statement to work inside phpmyadmin under sql, so there's probably something wrong with the statement all together..

 $db = new PDO($dsn, $dbUserName, $dbPassword);
       
 $sqlStatement = $db->prepare("INSERT INTO `userinfo`(`userFirstName`, `userLastName`, `userDateOfBirth`, `userCountry`, `userCity`, `userBio`)VALUES(?, ?, ?, ?, ?, ?) WHERE `userName` = $_SESSION['userName']");
       
$sqlStatement->execute([$entryFirstName, $entryLastName, $entryDOB, $entryCountry, $entryCity, $entryBio]);

PHP:

 

Try it Now

 $db = new PDO($dsn, $dbUserName, $dbPassword);
     
$sqlStatement = $db->prepare("INSERT INTO userinfo('userFirstName', 'userLastName', 'userDateOfBirth', 'userCountry', 'userCity', 'userBio)VALUES(?, ?, ?, ?, ?, ?) WHERE userName = $_SESSION['userName']");
     
$sqlStatement->execute([$entryFirstName, $entryLastName, $entryDOB, $entryCountry, $entryCity, $entryBio]);

PHP:

 

Remove the variable from the query string and replace it with a question mark as well.

WHERE `userName` = ?

PHP:

... then add the session variable to the execute() statement. Another fix is this:

userName = '{$_SESSION['userName']}'"

PHP:

(single quotes around the value and curly brackets)

EDIT:

It's usually a good practice to rely on the user ID instead of the user name for things like this. If you want to stick with the name, make sure the "userName" field is marked as UNIQUE (recommended), or add LIMIT 1 to your query. Otherwise it will match ALL rows against the username and it's slow and unnecessary.

EDIT 2:

Wait, what are you doing? You can't INSERT data with WHERE clauses? I thought this was an UPDATE query. Do you want to update the table or insert new data?

 

I want to add data to a row with existing data... I was actually confused as to whether or not to use update or insert. I take it if theirs already data in the row then I have to use update, eh?

 

Yap! INSERT inserts a whole new row, UPDATE updates an existing one.

 

hmm. Thanks Nico. Once I figured out how to run a update statement I tried putting it in my code, but it doesn't seem to affect the database. I've tried a few different ways...Maybe someone can take a look and tell me what I'm doing wrong.

$db = new PDO($dsn, $dbUserName, $dbPassword);
      
$sqlStatement = $db->prepare("UPDATE `userinfo` SET `userFirstName`= ? ,`userLastName`= ?,`userDateOfBirth`= ?, `userCountry`= ?,`userCity`= ?,`userBio`= ?, `userRegStep`= ? WHERE `userName` = ?");
      
       $sqlStatement->execute([$entryFirstName, $entryLastName, $entryDOB, $entryCountry, $entryCity, $entryBio, $dbEntryRegStep, $sesUserName]);

PHP:

I tried with and without backticks around the statement after WHERE, and tried with $sesUserName inside and outside of the statement after WHERE...

actually sesUSerName is only there because I gave the $_SESSION['userName'] variable to it after trying four or five different ways, lol. I know it's redundant, but just wanted it to work..

 

Depending on the version of PHP you're running try changing the last line to this:

$sqlStatement->execute(array($entryFirstName,$entryLastName,$entryDOB,$entryCountry,$entryCity,$entryBio,$dbEntryRegStep,$sesUserName));

PHP:

 

hmm, I'll try it, but I don't think that's it because all my other statements worked... I've done an insert and select statement using the same method as above already.

 

Okay - then I suggest doing as follows: Replace all the '?' in the query with the actual $variables, within ''-single quotes, and then just echo the $sqlStatement out - then you copy that statement, and put it into whichever MySQL-manager you use (I'm guessing PHPMyadmin) - and test to see if it works there. You'll also be able to see if any of the variables are empty or contain the wrong datatype.

 

hmm, good idea. I'll try that. Thanks.

 

It's a good way to weed out errors in queries, get a look on the actual content of the variables, etc. And when you don't have a proper error-message system in place to see what goes wrong with the query, it's more or less the only way to test.

 

you were exactly right. I had messed up some logic by echoing out the statement I found that it was empty. I then echoed something out in that block of code, and it didn't go, lol. I had to fix some logic. The entire block wasn't firing, lol.

Yeah, I haven't learned much about error handling. Once the site is finished I'm still going to have to go through and implement things that I didn't...silent certain errors that could give me problems...I do things a way most don't....I'm pretty much building the framework, and then go in to add to it...make it more secure...change code....lol.

I would hire a professional but don't have the money, and the sheer scale of the site I'm making would be ridiculous in price...so I figured I would build the entire thing myself and then pay a professional to secure my creation.... That way it would be cheaper.