PHP and MySQL query based on user input not working

Question

I am creating a users database where there are 4 fields: ID, username, password, and occupation. This is a test database. I tried querying the db table and it worked but i have a lot of trouble having a user input and a MySQL query based off of it. I run an Apache server in Linux (Debian, Ubuntu).

I have 2 pages. The first one is a bare-bone test index page. this is where there are textboxes for people to input easy info to register their info in the db. Here is the code for it:

<html>
<form action="reg.php" method="POST">
  Username:
  <input type="text" name="u">Password:
  <input type="password" name="p">Occupation:
  <input type="text" name="o">
  <input type="submit" value="register">


</form>

</html>

After the submit button is clicked. It goes to the reg.php file. This is where it gets complicated. The page goes blank!!! Nothing is displayed or inputted in the db. Normal queries work well, but when user interaction is added, something is wrong. Here is the code for reg.php:

<?php
$un = $_POST["u"]
$pk = $_POST["p"]
$ok = $_POST["o"]
$u = mysql_real_escape_string($un);
$p = mysql_real_escape_string($pk);  
$o = mysql_real_escape_string($ok);    

$link = mysql_connect('localhost', 'root', 'randompassword');
if (!$link){
die(' Oops. We Have A Problem Here: ' . mysql_error());
}

if ($link){
echo 'connected succesfully';
}

mysql_select_db("forum") or die(' Oops. We Have A Problem Here: ' . mysql_error());
$data = mysql_query("INSERT INTO users (username, password, occupation) VALUES ('{$u}', '{$p}', '{$o}')");

?>

Can anyone hep me to correct this code to make this work? Thank you so much for your time. Much appreciated.

EDIT: I noticed that i did not add semicolons in the first 3 lines. after doing so i got this error: "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 '{'', '', '')' at line 1." Can someone explain why?

EDIT: the website is just on my local machine... on an apache server on linux

Blank page means: Add error reporting to the top of your file(s) right after your opening PHP tag for example <?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything. Also add or die(mysql_error()) to mysql_query(). — Funk Forty Niner
– Funk Forty Niner, Commented Mar 16, 2015 at 21:11
Good. Store hashes, and not string literals for password storage. Plus, error reporting would have signaled the parse error. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 16, 2015 at 21:13

Dimitry · Accepted Answer · 2015-03-16 21:17:36Z

4

You are missing semi-colons in the first three lines.

$un = $_POST["u"];
$pk = $_POST["p"];
$ok = $_POST["o"];

edited Mar 16, 2015 at 21:17

answered Mar 16, 2015 at 21:12

Dimitry

6,6332 gold badges24 silver badges21 bronze badges

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

7 Comments

Surya Mani Over a year ago

oops... Did not notice that.

Surya Mani Over a year ago

0 down vote accept I noticed that i did not add semicolons in the first 3 lines. after doing so i got this error: "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 '{'', '', '')' at line 1." Can someone explain why?

Dimitry Over a year ago

Have you changed your SQL statement in the last PHP line?

Surya Mani Over a year ago

how and what should i change @Dimitry

Dimitry Over a year ago

Can you post the last line of your PHP code and provide the values of $o, $p and $u? That would help.

|

Ram Sharma · Accepted Answer · 2015-08-24 13:34:29Z

1

mysql_real_escape_string() requires a db connection. Try this ....

<?php
$un = $_POST["u"];
$pk = $_POST["p"];
$ok = $_POST["o"];

$link = mysql_connect('localhost', 'root', 'randompassword');
if (!$link){
  die(' Oops. We Have A Problem Here: ' . mysql_error());
}

if ($link){
  echo 'connected succesfully';
}

mysql_select_db("forum") or die(' Oops. We Have A Problem Here: ' . mysql_error());

$u = mysql_real_escape_string($un);
$p = mysql_real_escape_string($pk);
$o = mysql_real_escape_string($ok);
$sql = "INSERT INTO users (username, password, occupation) VALUES ('$u', '$p', '$o')";
$ins_sql = mysql_query($sql);
IF($ins_sql) {
  echo 'Inserted new record.';
}ELSE{
  echo 'Insert Failed.';
}
?>

edited Aug 24, 2015 at 13:34

Ram Sharma

8,8157 gold badges48 silver badges59 bronze badges

answered Mar 16, 2015 at 21:31

Brian

1,0357 silver badges14 bronze badges

4 Comments

Surya Mani Over a year ago

For some reason, the text "connected successfully" shows up but the query does not get executed...

Brian Over a year ago

Fixed ... sorry about that.

Surya Mani Over a year ago

Thank you so much Brian! It worked! Now i can finally begin work on the rest of my code!

Brian Over a year ago

No problem, glad I could help. I'd like to also recommend that you Sanitize/Validate user input. And create a dbconnect.php file and use require_once('path/to/dbconnect.php'); for your connections. Don't forget mysql_close($link);

Philipp Meissner · Accepted Answer · 2015-03-16 21:12:07Z

0

Try adding this to the top of your script:

error_reporting(E_ALL);
ini_set("display_errors", 1);

This way you will see all errors that you made syntactically or even within your SQL.

answered Mar 16, 2015 at 21:12

Philipp Meissner

5,4925 gold badges38 silver badges61 bronze badges

5 Comments

Funk Forty Niner Over a year ago

This isn't considered a solution, but troubleshooting/debugging; more a comment in my books, which was already outlined in "comments".

Surya Mani Over a year ago

I noticed that i did not add semicolons in the first 3 lines. after doing so i got this error: "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 '{'', '', '')' at line 1." Can someone explain why?

Philipp Meissner Over a year ago

If I remember correctly your SQL should look like so: mysqli_query("INSERT INTO users (username, password, occupation) VALUES ('$u', '$p', '$o')"); Also notice I used mysqli_query instead of mysql_query as the second one is deprecated and will eventually throw more errors at you afterwards.

Surya Mani Over a year ago

No Phil M. It is not the mysqli extension. I know this for sure beccause when i performed test queries, it worked perfectly. The test queries did not use mysqli.

Philipp Meissner Over a year ago

You got me wrong then. I did not only change the command, but further I changed the variables and how they were passed inside this string pattern. Read over the code again.

Collectives™ on Stack Overflow

PHP and MySQL query based on user input not working

3 Answers 3

7 Comments

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related