[RESOLVED] INSERT INTO error in PHP but able to execute in phpMyAdmin

gamber

Trying to perform what I thought was a simple insert into my database. After a tremendous time on debugging, I am still stumped. Forgive all the comments - part of my debugging process

Here are the details:

$addUser = $SERVER['PHP_SELF'];
if (isset($POST['Submit'])) {
$Username = $POST['Username'];
$Password = $POST['Password'];
$FirstName = $POST['FirstName'];
$LastName = $POST['LastName'];
$Email = $POST['Email'];
$AccessLevel = $POST['AccessLevel'];

mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');

$addUser = mysql_query("INSERT INTO user (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')");

//DEBUG - print the variables
	//echo $addUser;
	//echo "<h2>Testing the server to see if we have a mySQL connection.</h2><br />";
	//echo "<p>UserID: $UserID <br />Username: $Username <br />Password: $Password <br />Firstname: $FirstName <br />LastName: $LastName <br />AccessLevel: $AccessLevel <br /></p>";

//execute the query
if (@mysql_query ($addUser, $connMI)) {
	//echo "<p>The add user query was successfully executed.  The query was $addUser.</p>";
	header ("Location: ./index.php");			//redirect to User index page
	mysql_close();								//close the database
	exit();										//exit the script
	} else {
		echo "<p>Could not add the entry because: <b>" .mysql_error(). "</b>.<br />  The query was $addUser.</p>";
	}

}[/COLOR]

The result is:
Could not add the entry because: 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 '1' at line 1.
The query was 1.
The information is in my database, but the debugging error is triggered.

Thanks in advance.

Houdini

You never connected to the server you went straight to selecting the database at least with the code you are showing.

gamber

Connections are there, just not included for the sake of length.
Here they are:

include ('../../connections/connMI.php');
mysql_select_db($database_connMI, $connMI);

Houdini

Try changing this

 $addUser = mysql_query("INSERT INTO `user` (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')");

$addUser = "INSERT INTO `user` (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')";

also remove the @ until your script works and if necessary echo the above also to see what the querys is actually showing to mYSQL by

echo $addUser;

below the query definition.

gamber

Interesting - this a couple of tweaks, it now works.

I have a question and a small problem now however

Question:
What happened within the mysql_query function for it to fail?

Problem:
Script will not redirect, the page goes blank and the url stays with the add.php page.

Many thanks

Houdini

mysql_query() returns the result of a string query so when you assign a variable to the result it will be 1 or 0 (true or false)
you normally set a query to a variable like

$query="SELECT * FROM tablename WEHERE somecondition";// set the query here
$result =mysql_query($query,$connection); //execute the query here using the $query.

gamber

Here is where I am at now:

Code:
$addUser = $SERVER['PHP_SELF'];
if (isset($POST['Submit'])) {
$Username = $POST['Username'];
$Password = $POST['Password'];
$FirstName = $POST['FirstName'];
$LastName = $POST['LastName'];
$Email = $POST['Email'];
$AccessLevel = $POST['AccessLevel'];
mysql_select_db($database_connMI, $connMI) or die ('Could not select the database because ' .mysql_error().'');

$insert = "INSERT INTO `user` (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')";
$addUser = mysql_query($insert);

//DEBUG - print the variables
echo "The insert variable is $insert ";
echo "The add user query result is $addUser ";

echo "Testing the variables. They are: Username: $Username Password: $Password Firstname: $FirstName LastName: $LastName AccessLevel: $AccessLevel ";

//execute the query
if (@ ($addUser)) {
//echo "The add user query was successfully executed. The query was $addUser.";
header ("Location: ./index.php"); //redirect to User index page
mysql_close(); //close the database
exit(); //exit the script
} else {
echo "Could not add the entry because: " .mysql_error(). ". The query was $addUser.";
}
}
[/COLOR]
Results:
The insert variable is INSERT INTO user (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('Skoki', 'woof', 'Skoki', 'Dog', 'woof@woofmail.com', '3')

The add user query result is 1

Testing the variables. They are:
Username: Skoki
Password: woof
Firstname: Skoki
LastName: Dog
Email: woof@woofmail.com
AccessLevel: 3

Could not add the entry because: 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 '1' at line 1.
The query was 1.

What is interesting is that everything is in the query variable appears to be correct, the query appears to be correct, and the result is correct.

If all is correct, I cannot understand the error in my SQL syntax - I have used it in the past without fail. Having touble determining what "near '1' at line 1" is referring to.

The query simply determines if it has been executed. Now if the returned value exists as either a 0 or a 1 , it should work through the conditional statement. Am I not correct here?

If you wish, I will send some code of an example that works just fine if that would assist.

Thanks again!

Houdini

Try changing this

if (@mysql_query ($addUser)) {

if (($addUser) {

and see what you get.

gamber

Houdini,
Thanks for all your help it is very much appreciated. The little things can but a halt on progress.

Now the issue is my header will not work - I will pursue in other posts (unless you may have an idea or two)

Thanks,
gamber

Houdini

One is enything echoed before you use the header, if so then that will cause it to fail, and two have you tried a full URL like header("Location: http://yourhost.com/index.php");

gamber

no echo prior - first thing to check
where the header redirect should be executed, the url remains add.php page, but the page is blank - this should be my greatest clue
this also occurs with the full url

If I replace the header with an echo, it executes but also remains on the add.php page

does it matter if I have already executed a redirect with header leading into this page? It is the redirect from the admin login page to the admin index page

here is my code for this portion

//execute the query
if (@ ($addUser)) {
I will either comment out the echo or the header when testing
echo "The add user query was successfully executed.";
//header ("Location: ./index.php"); //redirect to User index page
mysql_close(); //close the database
exit(); //exit the script
} else {
echo "Could not add the entry because: " .mysql_error(). ".";
}

gamber

Houdini, what about

echo "<script type=\"text/javascript\">window.location=\"index.php?page=login\"</script>";

as I have seen you responded to another?

I would prefer not to use javascript, but ...

Houdini

//DEBUG - print the variables //these cause the redirect to fail
echo "The insert variable is $insert ";
echo "The add user query result is $addUser ";
echo "Testing the variables. They are: Username: $Username Password: $Password Firstname: $FirstName LastName: $LastName AccessLevel: $AccessLevel ";

//execute the query
if (@ ($addUser)) {//remove the @ and one)
//just just if($addUser){
//echo "The add user query was successfully executed. The query was $addUser.";
header ("Location: ./index.php"); //redirect to User index page

Try making the commented changes and note that a header can not be called if there is any output even a space.

gamber

Houdini,
Just about had enough for today?

Just wanted to start somewhat fresh so things are not confusing any more than they have to be.

I have been going back and examining my coding that has been working 100% and tweaking this code. I am going to give you an example of the one that is working and then my revised code.

Working:

<?php														//DELETE the information script	
	if (isset ($_POST['submit'])) { 						// Handle the form.
		$editUserType = $_SERVER['PHP_SELF'];
		include ('../../connections/connMI.php'); 			//establish connections to database
		//select the database
		mysql_select_db ($database_connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
		//define the query
		$query = "DELETE FROM usertype
			WHERE TypeID={$_GET['TypeID']} LIMIT 1";
		//execute the query
		if (@mysql_query ($query)) {
			header ("Location: ./index.php");			//redirect to UserType index page
			mysql_close();								//close the database
			exit();										//exit the script
			} else {
				echo "<p>Could not delete the entry because: <b>" .mysql_error(). "</b>.<br />  The query was $query.</p>";
			}
	}
?>

Not working:

<?php
//get the information from the form
$addUser = $_SERVER['PHP_SELF'];					//trigger
if (isset($_POST['Submit'])) {						//handle the form
	//define the variables
		$Username = $_POST['Username'];
		$Password = $_POST['Password'];
		$FirstName = $_POST['FirstName'];
		$LastName = $_POST['LastName'];
		$Email = $_POST['Email'];
		$AccessLevel = $_POST['AccessLevel'];
	//connect to the database
	mysql_select_db($database_connMI, $connMI) or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the string and the query
	$query = "INSERT INTO `user` (Username, Password, FirstName, LastName, Email, AccessLevel) VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')";
	//execute the query
	if (@mysql_query ($query)) {
		header ("Location: ./index.php");			//redirect to UserType index page
		mysql_close();								//close the database
		exit();										//exit the script									//exit the script
	} else {
			echo "<p>Could not add the entry because: <b>" .mysql_error(). "</b>.</p>";
		}
}
?>

In this second example, note that I have already established the connection to the server and have selected the database. Between these two is some code that allows for a dynamic drop down box to select my UserType (a couple of record sets, no html, print or echo).

Houdini

This code was tested with the index.php one directory below the following script (note the ../index.php, not ./index.php)

<?php
//get the information from the form
$addUser = $_SERVER['PHP_SELF'];   //trigger
if (isset($_POST['Submit'])) {	   //handle the form
	//define the variables
	$Username = $_POST['Username'];
	$Password = $_POST['Password'];
	$FirstName = $_POST['FirstName'];
	$LastName = $_POST['LastName'];
	$Email = $_POST['Email'];
	$AccessLevel = $_POST['AccessLevel'];
	$connMI=mysql_connect('localhost','root')
		or die("Could not connect to the MySQL server!");
	//connect to the database
	mysql_select_db($database_connMI,$connMI) 
		or die ('<p>Could not select the database because <b>' .mysql_error().'</b></p>');
	//define the string and the query
	$query = "INSERT INTO `user` (Username, Password, FirstName, LastName, Email, AccessLevel) 
	VALUES ('$Username', '$Password', '$FirstName', '$LastName', '$Email', '$AccessLevel')";
	//echo $query;
	//execute the query
	$result = mysql_query($query);
	if ($result) {
		header ("Location: ../index.php");		//redirect to UserType index page
		mysql_close();							//close the database
		exit();									//exit the script	
	} 
		else {
			echo "<p>Could not add the entry because: <b>" .mysql_error(). "</b></p>";
		}
	}
?>

I set up the fields in a database created a form and sent the values to this script then it went to the index.php that was in a folder that used the folder this script was in and showed what I had written into the file which was just a centerd It worked

gamber

Here is my $connMI variable

$connMI = mysql_pconnect($hostname_connMI, $username_connMI, $password_connMI) or trigger_error(mysql_error(),E_USER_ERROR);

gamber

Here is my include file

<?php

FileName="Connection_php_mysql.htm"

Type="MYSQL"

HTTP="true"

$hostname_connMI = "localhost";
$database_connMI = "marketinginsider";
$username_connMI = "xxxx";
$password_connMI = "xxxx";
$connMI = mysql_pconnect($hostname_connMI, $username_connMI, $password_connMI) or trigger_error(mysql_error(),E_USER_ERROR);
?>

Houdini

The $variable that you are setting up with your connection will be a resource result like #4 or #5, it is merely a connection. I would suggest not to use pconnect and instead use just connect.

If I were to write a query result like this
$result =mysql_query($query,$$connMI) it will use that particular resource result connection for the query andif I used
$result =mysql_query($query) then MySQL will use the last used resource which most likely will be $connMI but not necessarily. So the $connMI only lets MySQL know what connection you are using for any given operation.

The code I have just written will work using the values that you are using the code you had was never getting to the if condition and this is why it stayed on the same page, and it never executed the query. When writing new code it is best not to use the @ in front of queries because it supresses errors and your script will fail with no error shown as was happening with what you were using so you had no clue as to the failed query.

To check for if a query passed then the simple if($result) will do do not run the query again with if(mysql_query($result)) that was messing up the script especially with the @ in front of it so it failed and stayed there.

gamber

Houdini, it is now resolved. Thanks for your patience and assistance!!

RESOLVED
Went back and deleted changes to code, copied the original code from the initial posting and it works.

The problem was empty space between my php include code and my php update code - though I examined all the usual reasons, I somehow missed this one.