Frustrating MySQL/PHP issue i cant seem to comprehend

Question

Please someone help me, why is my code not speaking to mySQL table? I really have no clue why it doesn't work and i've spent days looking for solutions, and help would be greatly appreciated......

my html...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script type="text/javascript" src="Locus.js"></script> <!-- LINKS TO THE EXTERNAL JAVASCRIPT -->

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Locus of control test</title>
<link href="Locus.css" rel="stylesheet" type="text/css" /> <!-- LINKS TO THE EXTERNAL CS SHEET -->

</head>

<body>

<form action="Locus.php" id="Locus" method="post" name="Locus" onsubmit="validateForm()"> <!-- GIVES A LINK TO THE PHP FORM AND THE METHOD -->

<h4>Surname: <input type="text" name="surname"/> <br/>Prison: <input type="text" name="prison"/><br/>National Insurance: <input type="text" name="NI"/></h4>
<h3> Please carefully read all the instructions given on the left hand panel.</h3>
<h1 class="Header"> LOCUS OF CONTROL</h1>
<h2>Please click submit ONLY when all questions have been completed <br/> <input name="submit" id="submit" type="submit" value="Check & Submit" /></h2>

<p>1. I can anticipate difficulties and take action to avoid them. <br />
<input type="radio" name="Q1" value="4" /> Always Agree <input type="radio" name="Q1" value="3" /> Agree <input type="radio" name="Q1" value="2" /> Unsure <input type="radio" name="Q1" value="1" /> Disgree <input type="radio" name="Q1" value="0" /> Always Disagree
</p>


<p>2. A great deal of what happens to me is just a matter of chance.  <br />
<input type="radio" name="Q2" value="0" /> Always Agree <input type="radio" name="Q2" value="1" /> Agree <input type="radio" name="Q2" value="2" /> Unsure <input type="radio" name="Q2" value="3" /> Disgree <input type="radio" name="Q2" value="4" /> Always Disagree
</p>

<p>3. Everyone knows that luck or chance determines the future.  <br />
<input type="radio" name="Q3" value="0" /> Always Agree <input type="radio" name="Q3" value="1" /> Agree <input type="radio" name="Q3" value="2" /> Unsure <input type="radio" name="Q3" value="3" /> Disgree <input type="radio" name="Q3" value="4" /> Always Disagree
</p>

<p>4. I can control my problems only if I have outside support.  <br />
<input type="radio" name="Q4" value="0" /> Always Agree <input type="radio" name="Q4" value="1" /> Agree <input type="radio" name="Q4" value="2" /> Unsure <input type="radio" name="Q4" value="3" /> Disgree <input type="radio" name="Q4" value="4" /> Always Disagree
</p>

<p>5. When I make plans I am almost certain I can make them work. <br />
<input type="radio" name="Q5" value="4" /> Always Agree <input type="radio" name="Q5" value="3" /> Agree <input type="radio" name="Q5" value="2" /> Unsure <input type="radio" name="Q5" value="1" /> Disgree <input type="radio" name="Q5" value="0" /> Always Disagree
</p>

<p>6. My problems will dominate all my life. <br />
<input type="radio" name="Q6" value="0" /> Always Agree <input type="radio" name="Q6" value="1" /> Agree <input type="radio" name="Q6" value="2" /> Unsure <input type="radio" name="Q6" value="3" /> Disgree <input type="radio" name="Q6" value="4" /> Always Disagree
</p>

<p>7. My mistakes and problems are my responsibility to deal with. <br />
<input type="radio" name="Q7" value="4" /> Always Agree <input type="radio" name="Q7" value="3" /> Agree <input type="radio" name="Q7" value="2" /> Unsure <input type="radio" name="Q7" value="1" /> Disgree <input type="radio" name="Q7" value="0" /> Always Disagree
</p>

<p>8. Becoming a success is a matter of hard work, luck has little or nothing to do with it. <br />
<input type="radio" name="Q8" value="4" /> Always Agree <input type="radio" name="Q8" value="3" /> Agree <input type="radio" name="Q8" value="2" /> Unsure <input type="radio" name="Q8" value="1" /> Disgree <input type="radio" name="Q8" value="0" /> Always Disagree
</p>

<p>9. My life is controlled by outside actions and events.  <br />
<input type="radio" name="Q9" value="0" /> Always Agree <input type="radio" name="Q9" value="1" /> Agree <input type="radio" name="Q9" value="2" /> Unsure <input type="radio" name="Q9" value="3" /> Disgree <input type="radio" name="Q9" value="4" /> Always Disagree
</p>

<p>10. I believe people are victims of circumstances beyond their control.   <br />
<input type="radio" name="Q10" value="0" /> Always Agree <input type="radio" name="Q10" value="1" /> Agree <input type="radio" name="Q10" value="2" /> Unsure <input type="radio" name="Q10" value="3" /> Disgree <input type="radio" name="Q10" value="4" /> Always Disagree
</p>

<p>11. To continually manage my problems I need professional help. <br />
<input type="radio" name="Q11" value="0" /> Always Agree <input type="radio" name="Q11" value="1" /> Agree <input type="radio" name="Q11" value="2" /> Unsure <input type="radio" name="Q11" value="3" /> Disgree <input type="radio" name="Q11" value="4" /> Always Disagree
</p>

<p>12. When I am under stress the tightness in my muscles is due to things outside my control.<br />
<input type="radio" name="Q12" value="0" /> Always Agree <input type="radio" name="Q12" value="1" /> Agree <input type="radio" name="Q12" value="2" /> Unsure <input type="radio" name="Q12" value="3" /> Disgree <input type="radio" name="Q12" value="4" /> Always Disagree
</p>

<p>13. 1 believe a person really can be master of his own fate.<br />
<input type="radio" name="Q13" value="4" /> Always Agree <input type="radio" name="Q13" value="3" /> Agree <input type="radio" name="Q13" value="2" /> Unsure <input type="radio" name="Q13" value="1" /> Disgree <input type="radio" name="Q13" value="0" /> Always Disagree
</p>

<p>14. It is impossible to control irregular fast breathing when I am having difficulties.<br />
<input type="radio" name="Q14" value="0" /> Always Agree <input type="radio" name="Q14" value="1" /> Agree <input type="radio" name="Q14" value="2" /> Unsure <input type="radio" name="Q14" value="3" /> Disgree <input type="radio" name="Q14" value="4" /> Always Disagree
</p>

<p>15. I understand why my problems vary so much from one occasion to another. <br />
<input type="radio" name="Q15" value="4" /> Always Agree <input type="radio" name="Q15" value="3" /> Agree <input type="radio" name="Q15" value="2" /> Unsure <input type="radio" name="Q15" value="1" /> Disgree <input type="radio" name="Q15" value="0" /> Always Disagree
</p>

<p>16. I am confident of being able to deal successfully with future problems.<br />
<input type="radio" name="Q16" value="4" /> Always Agree <input type="radio" name="Q16" value="3" /> Agree <input type="radio" name="Q16" value="2" /> Unsure <input type="radio" name="Q16" value="1" /> Disgree <input type="radio" name="Q16" value="0" /> Always Disagree
</p>

<p>17. In my case maintaining control over my problems is mainly due to luck. <br />
<input type="radio" name="Q17" value="0" /> Always Agree <input type="radio" name="Q17" value="1" /> Agree <input type="radio" name="Q17" value="2" /> Unsure <input type="radio" name="Q17" value="3" /> Disgree <input type="radio" name="Q17" value="4" /> Always Disagree
</p>

<p>18. I have often been blamed for events beyond my control. <br />
<input type="radio" name="Q18" value="0" /> Always Agree <input type="radio" name="Q18" value="1" /> Agree <input type="radio" name="Q18" value="2" /> Unsure <input type="radio" name="Q18" value="3" /> Disgree <input type="radio" name="Q18" value="4" /> Always Disagree
</p>
<h5>Please click 'submit' at the start of this test</h5>
</form>
</body>
</html>

here is my php....

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="Locus.css" rel="stylesheet" type="text/css" /> <!-- LINKED ATTACHED STYLE SHEET-->
<title>Locus Test Complete</title>
</head>
<h4>THANK YOU FOR SUBMITTING THIS TEST, PLEASE CHOOSE ANOTHER FROM THE MENU BAR.</h4>

<body>
<?php
/*DECLARING THE VARIABLES FOR ACCESS*/
$username="root";
$password="";
$database_server="localhost";

/*CONNECTING TO THE SERVER*/
$database="locus";
mysql_connect($database_server,$username,$password) or die("cannot connect");

/*CONNECTING TO THE DATABASE*/
@mysql_select_db($database) or die( "Unable to select database");


/*CHECK THE STATUS AND DECLARE THE VARIABLES*/
$_POST = array( );

if( isset( $_POST['surname'])) { 
    $Surname = $_POST['surname'];
} else { 
    $Surname = "";
} 

if( isset( $_POST['prison'])) { 
    $Prison = $_POST['prison'];
} else { 
    $Prison = "";
}
/*SHORTHAND WAY TO CHECK THE STATUS AND DECLARE THE VARIABLES*/
$NI = isset( $_POST['NI']) ? $_POST['NI'] : "";
$Q1 = isset( $_POST['Q1']) ? $_POST['Q1'] : "";
$Q2 = isset( $_POST['Q2']) ? $_POST['Q2'] : "";
$Q3 = isset( $_POST['Q3']) ? $_POST['Q3'] : "";
$Q4 = isset( $_POST['Q4']) ? $_POST['Q4'] : "";
$Q5 = isset( $_POST['Q5']) ? $_POST['Q5'] : "";
$Q6 = isset( $_POST['Q6']) ? $_POST['Q6'] : "";
$Q7 = isset( $_POST['Q7']) ? $_POST['Q7'] : "";
$Q8 = isset( $_POST['Q8']) ? $_POST['Q8'] : "";
$Q9 = isset( $_POST['Q9']) ? $_POST['Q9'] : "";
$Q10 = isset( $_POST['Q10']) ? $_POST['Q10'] : "";
$Q11 = isset( $_POST['Q11']) ? $_POST['Q11'] : "";
$Q12 = isset( $_POST['Q12']) ? $_POST['Q12'] : "";
$Q13 = isset( $_POST['Q13']) ? $_POST['Q13'] : "";
$Q14 = isset( $_POST['Q14']) ? $_POST['Q14'] : "";
$Q15 = isset( $_POST['Q15']) ? $_POST['Q15'] : "";
$Q16 = isset( $_POST['Q16']) ? $_POST['Q16'] : "";
$Q17 = isset( $_POST['Q17']) ? $_POST['Q17'] : "";
$Q18 = isset( $_POST['Q18']) ? $_POST['Q18'] : "";


/*PLAVE THE VALUES OF THE VARIABLES WITHIN THE DATABASE TABLE*/
$query = "INSERT INTO locus.locusofcontrolscores VALUES 
('','$Surname','$Prison','$NI','$Q1','$Q2','$Q3','$Q4','$Q5','$Q6','$Q7','$Q8','$Q9','$Q10','$Q11','$Q12','$Q13','$Q14','$Q15','$Q16','$Q17','$Q18')";

/*CREATE A QUERY FROM THE VARIABLE*/
mysql_query($query);

/*CLOSE THE SERVER CONNECTION*/
mysql_close();



?>



</body>
</html>

firstly: please stop using the `mysql_xx()` functions -- they are obsolete and insecure. You should switch to the `mysqli_xx()` functions or the PDO library -- see http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php for more info. — SDC, Nov 28 '12 at 14:19
I'm not sure how to test that? I have the files in htdocs and when i type localhost into my browser i connect to the server — Arr Connor, Nov 28 '12 at 14:20
Ignoring the fact you shouldn't be using mysql_* extension anymore try changing this `mysql_query($query);` to this `mysql_query($query) or die(mysql_error() . ' ' . $query);`, it should hopefully shed some light on the issue — Dale, Nov 28 '12 at 14:20
Personally I've never seen a database referenced in an sql `INSERT INTO locus.locusofcontrolscores` you could try removing the `locus.` part of that — Dale, Nov 28 '12 at 14:25
-1. Provide us a clue. Error/warning messages? And what exactly do you mean by _does not work_? — Salman A, Nov 28 '12 at 14:28
theres no error messages, just nothing going into the database, i cant understand how my database is not populating. heres a screenshot of the database http://imgur.com/MuN5j — Arr Connor, Nov 28 '12 at 14:30

score 5 · Answer 1 · edited Nov 28 '12 at 14:27

5

$_POST = array( );

is wrong, remove this line.

$_POST is an array() you do not have to declare this.

edited Nov 28 '12 at 14:27

NullPoiиteя

56,591
22
125
143

answered Nov 28 '12 at 14:20

jussi

2,166
1
22
37

ok I have done this, issue still remains tho. – Arr Connor Nov 28 '12 at 14:21
Provide more Information/Error messages like Dale said in his comment – jussi Nov 28 '12 at 14:22
theres no error messages, just nothing going into the database. heres a screenshot of the database http://imgur.com/MuN5j – Arr Connor Nov 28 '12 at 14:26

score 0 · Answer 2 · answered Nov 28 '12 at 14:28

0

according the screed shot in comment you are checking the structure ... click on the Browse tab than you will see the content

also try

mysql_query($query) or die(mysql_error());

to see the error in query

Warning : your code is vulnerable to SQL Injunction

also MySql_* function are deprecated use pdo or mysqli

answered Nov 28 '12 at 14:28

NullPoiиteя

56,591
22
125
143

browse is not active because there is nothing in the table. – Arr Connor Nov 28 '12 at 14:31
@ArrConnor than try `mysql_query($query) or die(mysql_error());` and let us know if you getting any error – NullPoiиteя Nov 28 '12 at 14:32
@ArrConnor try only `INSERT INTO locusofcontrolscores VALUES bla bla...` – NullPoiиteя Nov 28 '12 at 14:37
yeah I have removed the locus. part, it still is having none of it. – Arr Connor Nov 28 '12 at 14:40

Ian Overton · Answer 3 · 2012-11-28T14:51:03.960

0

Things that could solve your problem:

mysql_query($query) or die(mysql_error() . ' ' . $query);
don't clear out the post array
don't suppress the database connection or at least put the error in the die

Things you should probably fix, but probably isn't your problem:

$sql injection by putting the variables in the sql without escaping
them Using mysql instead of mysqli Validating the data, doing
controller stuff after the html started using radio buttons like
checkboxes not using an absolute path for your url
don't use root as the username to you db and have no password
no html should be in between the head and body.

Also I'm not sure you can have "" as the auto increment id. I would put Null there instead. Even better I'd explicitly put the columns you want to add to the table. You will have a of headaches later if you don't....say you have that same insert into a 1000 places...well you add a column and now you have to update a 1000 places.

Also I would echo out the sql statement you made and try and run it via command prompt or phpmyadmin.

edited Nov 28 '12 at 14:51

answered Nov 28 '12 at 14:28

Ian Overton

1,060
7
17

ok I have tried the null instead of '' but it still wont populate. – Arr Connor Nov 28 '12 at 14:46
Alright sorry cHao, hopefully it's better now? Also Arr would you echo out the sql statement to the screen and post the results here as well as run it in phpmyadmin or command line? – Ian Overton Nov 28 '12 at 14:52
mysql_query($query) or die(mysql_error() . ' ' . $query); - Done. don't clear out the post array - I don't understand this don't suppress the database connection or at least put the error in the die - Im not sure what this means – Arr Connor Nov 28 '12 at 14:57
The @ means suppress the error. Try and remove the @. Also please move the h4 and view source to make sure there isn't something hidden in the code you aren't seeing. – Ian Overton Nov 28 '12 at 14:59
I have removed the @ and there is no error, when i fill out the locus.html it simply shows the php file with no error, then when I check my table it's empty. – Arr Connor Nov 28 '12 at 15:02
h4 removed and nothing is being returned. – Arr Connor Nov 28 '12 at 15:04
so is locus.html the first page or the 2nd page? If you are not going to the thank you page that would explain why it never runs anything. – Ian Overton Nov 28 '12 at 15:04
the first page is the form, when it's filled out, and submit is pressed it returns the php file and I see the thank you text, but no thing is sent to the table. – Arr Connor Nov 28 '12 at 15:05
i reference the locus.php in theform action of the locus.html – Arr Connor Nov 28 '12 at 15:06
please echo $query and show me or run it in command line or phpmyadmin. – Ian Overton Nov 28 '12 at 15:07
does this mean I simply place 'echo $query' into the php code before ?> I have done this and nothing is returned. – Arr Connor Nov 28 '12 at 15:12
i don't know how to run it in command line. – Arr Connor Nov 28 '12 at 15:12
Then run it in the sql tab of phpmyadmin...or show me the echo. – Ian Overton Nov 28 '12 at 15:15
Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 1 STR: SQL: – Arr Connor Nov 28 '12 at 15:19
no....after line $query = "INSERT INTO locus...put echo "SQL STATEMENT:" . $query; run the page and find SQL STATEMENT copy the sql statement and put it in phpmyadmin. – Ian Overton Nov 28 '12 at 15:35
it doesn't return anything at all. this is what i have - – Arr Connor Nov 28 '12 at 15:41
$query = "INSERT INTO locusofcontrolscores VALUES('','$Surname','$Prison','$NI','$Q1','$Q2','$Q3','$Q4','$Q5','$Q6','$Q7','$Q8','$Q9','$Q10','$Q11','$Q12','$Q13','$Q14','$Q15','$Q16','$Q17','$Q18')"; echo "SQL STATEMENT:"; – Arr Connor Nov 28 '12 at 15:41
you need `echo "SQL STATEMENT:" . $query;` not `echo "SQL STATEMENT:";` – Ian Overton Nov 28 '12 at 15:47
this returned nothing either? $query = "INSERT INTO locusofcontrolscores VALUES(Null,'$Surname','$Prison','$NI','$Q1','$Q2','$Q3','$Q4','$Q5','$Q6','$Q7','$Q8','$Q9','$Q10','$Q11','$Q12','$Q13','$Q14','$Q15','$Q16','$Q17','$Q18')"; echo "SQL STATEMENT:" . $query; – Arr Connor Nov 28 '12 at 15:50
what shgould i be looking for, because absolutely nothing is being returned on the screen...? – Arr Connor Nov 28 '12 at 15:51
If nothing is returned to the screen, then you have a a fatal error and errors aren't turned on. Make sure in apache has display errors on and error_reporting is set to E_ALL – Ian Overton Nov 28 '12 at 15:55
everything is default, Ian i've not changed anything at all. – Arr Connor Nov 28 '12 at 16:00
Please update your question and answer respectively with the needed information. Comments aren't a good place for it. – George Stocker Nov 28 '12 at 16:03
put this in your htaccess `php_value display_errors 1` `php_value display_startup_errors 1` and `error_reporting(E_ALL);` at the top of your php file. – Ian Overton Nov 28 '12 at 16:05
php_value display_errors 1 php_value display_startup_errors 1 and error_reporting(E_ALL); this is giving me a syntax error – Arr Connor Nov 28 '12 at 16:10
there is no htaccess. this is getting ridiculous, it's simple html with php to a table, I see no reason why this won't work. I'm gonna have to give up on this. It's just giving me an aneurism. – Arr Connor Nov 28 '12 at 16:22

Frustrating MySQL/PHP issue i cant seem to comprehend

3 Answers3

Warning : your code is vulnerable to SQL Injunction