SQL database not inserting data?

Question

I am working on a program that takes HTML code made by a WYSIWYG editor and inserting it into a database, then redirecting the user to the completed page, which reads the code off the database. I can manually enter code in phpmyadmin and it works but in PHP code it will not overwrite the entry in the code column for the ID specified. I have provided the PHP code to help you help me. The PHP is not giving me any parse errors. What is incorrect with the following code?

<?php
 //POST VARIABLES------------------------------------------------------------------------
   //$rawcode = $_POST[ 'editor1' ];
   //$code = mysqli_real_escape_string($rawcode);
   $code = 'GOOD';
   $id = "1";
   echo "$code"; 
 //SQL VARIABLES-------------------------------------------------------------------------
   $database = mysqli_connect("localhost" , "root" , "password" , "database");
 //INSERT QUERY DATA HERE----------------------------------------------------------------
   $queryw = "INSERT INTO users (code) VALUES('$code') WHERE ID = '" . $id . "'";
   mysqli_query($queryw, $database);
 //REDIRECT TO LOGIN PAGE----------------------------------------------------------------
   echo "<script type='text/javascript'>\n";
   echo "window.location = 'http://url.com/users/" . $id . "/default.htm';\n";
   echo "</script>";
?>

You are not checking for query errors using php.net/mysql_error also you're telling the browser to redirect away from the current page so if there were any errors, you would never get to see them. To debug, remove the redirection for a moment — Pekka
– Pekka, Commented Aug 20, 2013 at 2:54
I forgot about that echo "$code"; on line 4, that was just to make sure the code was POSTing correctly. — rn10950
– rn10950, Commented Aug 20, 2013 at 2:55
it says: Warning: mysqli_query() expects parameter 1 to be mysqli, string given in C:\Inetpub\wwwroot\edit\edit_NEW.php on line 13 — rn10950
– rn10950, Commented Aug 20, 2013 at 2:59

Kai Qing · Accepted Answer · 2013-08-20 03:03:32Z

4

Your problem is that mysql INSERT does not support WHERE. Change the query to:

INSERT INTO users (code) VALUES ('$code')

Then to update a record, use

UPDATE users SET code = '$code' WHERE id = $id

Of course, properly prepare the statements.

Additionally, mysqli_query requires the first parameter to be the connection and second to be the string. You have it reversed. See here: http://php.net/manual/en/mysqli.query.php

It should also be noted that this kind of procedure should be run before the output to the browser. If so, you can just use PHP's header to relocate instead of this js workaround. However, this method will still work as you want. It is just likely to be considered cleaner if queries and relocation is done at the beginning of the script.

edited Aug 20, 2013 at 3:03

answered Aug 20, 2013 at 2:57

Kai Qing

18.9k6 gold badges41 silver badges61 bronze badges

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

4 Comments

Kai Qing Over a year ago

insert into just inserts to the user's table. that will not affect other users as it is. However, if you do not properly protect that $id var it could be exploited to update all users if you're not careful. How are you determining the value of $id?

davidaam Over a year ago

you could use mysqli_insert_id() method to get the id of the last inserted row in case you need it

rn10950 Over a year ago

with a cookie created when a user logs in. When the cookie reading section of the script determines the ID it verifies multiple different sections of the cookie with the DB to make sure the user is really logged in and a valid user.

Kai Qing Over a year ago

well, if the cookie is cross referencing the db, you may want to consider using a temporary token that is used to retrieve an id associated with it and not relying on the actual user id being set in the cookie itself. Just a suggestion.

Collectives™ on Stack Overflow

SQL database not inserting data?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related