[SOLVED] NOOB having trouble with INSERT, Query was empty.

[bpburrow]

I'm trying to set up a simple form to populate a table for new clients.  I'm sure the problem is staring directly at me, however no matter what I do I keep receiving the error "Could not execute query : .Query was empty".  Please Help.

 

Here's my form:

<form action="insertclient.php" method="post">
	<div  id="menu" class="mainMenu">
		<fieldset>
		  <legend>New Client</legend>
		  <ul>
			<li>
			  <label for="username">Username:</label>
			  <input type="text" id="username" />
			</li>
			<li>
			  <label for="password">Password:</label>
			  <input type="text" id="passwd" />
			</li>
			<li>
			  <label for="email">Email:</label>
			  <input type="text" id="email" />
			</li>
		  </ul>
		<input type="submit" type="submit" value="add" />
		</fieldset>
	</div>
</form>

 

<?php 

$username = $_POST['username'];
$passwd = $_POST['passwd'];
$email = $_POST['email'];

$username = addslashes ($username);
$passwd = addslashes ($passwd);
$email = addslashes ($email);

$hostname='localhost'; 
$user='user'; 
$pass='pass'; 
$dbase='admin'; 

$connection = mysql_connect("$hostname", "$user", "$pass") 
	or die ("Can't connect to MySQL");
mysql_select_db($dbase , $connection) or die ("Can't select database.");

mysql_query("INSERT INTO Client (username, passwd, email) VALUES ('$_POST[username]', '$_POST[passwd]', '$_POST[email]')");

$results = mysql_query($query) or die 
("Could not execute query : $query." . mysql_error());

if ($results)
{
	do_html_header('Client Added');
	do_menu_main2('');
	echo "A new Client has been added.";
}
mysql_close();

?>

[Maq]

The error is correct.  You pass an undeclared variables "$query" into mysql_query().

 

Where is "$query" defined?

 

   $results = mysql_query($query) or die 
   ("Could not execute query : $query." . mysql_error());

 

Before that you have the code that is actually inserting something:

 

   mysql_query("INSERT INTO Client (username, passwd, email) VALUES ('$_POST[username]', '$_POST[passwd]', '$_POST[email]')");

 

This query should work if you take the other one out.  It will never execute and code after the "or die()" because that's what die() is meant to do.

[PFMaBiSmAd]

You know, I have seen code like that a lot lately, where there is an initial mysql_query() with the SQL statement in it, followed by a mysql_query() that is attempting to do some error checking but where the SQL statement is a variable that has not been set.

 

Where are you getting this bogus code? Is some instructor giving you that either because he does not know what he is doing or because he wants to see if you can troubleshoot some basic code?

[Maq]

You know, I have seen code like that a lot lately, where there is an initial mysql_query() with the SQL statement in it, followed by a mysql_query() that is attempting to do some error checking but where the SQL statement is a variable that has not been set.

 

Where are you getting this bogus code? Is some instructor giving you that either because he does not know what he is doing or because he wants to see if you can troubleshoot some basic code?

 

That's interesting.  I vaguely remember seeing code similar to this before too.  It amazes me how such a fundamental error goes undetected.

[bpburrow]

I pulled the example from some tutorial, probably messed it up with all the cut & paste.  So I made some changes without receiving errors, however, the data isn't reaching the table.  When I submit the form a second time I get the following error:

 

Could not execute query : .Duplicate entry '' for key 1

 

Here's the code I with my fix (still broken):

<?php 

$username = $_POST['username'];
$passwd = $_POST['passwd'];
$email = $_POST['email'];

$username = addslashes ($username);
$passwd = addslashes ($passwd);
$email = addslashes ($email);

$hostname='localhost'; 
$user='user'; 
$pass='pass'; 
$dbase='admin'; 

$connection = mysql_connect("$hostname", "$user", "$pass") 
	or die ("Can't connect to MySQL");
mysql_select_db($dbase , $connection) or die ("Can't select database.");

$sql = "INSERT INTO Client (username, passwd, email) VALUES ('$_POST[username]', '$_POST[passwd]', '$_POST[email]')";

$results = mysql_query($sql) or die 
("Could not execute query : $query." . mysql_error());

if ($results)
{
	do_html_header('Client Added');
	do_menu_main2('');
	echo "A new Client has been added.";
}
mysql_close();

?>

[Maq]

Can I see your table structure?  Seems like you have a primary key on username.

[bpburrow]

I did use username as the primary key. 

 

create table user (

  username varchar(20) primary key,

  passwd char(20) not null,

  email varchar(100) not null

);

 

I used the example almost exactly from a book called "PHP and MySQL Web Development" by Welling and Thomson.  Should I have created a separate field for a unique id or something?

[bpburrow]

I can't seem to figure this out.  When I submit the data into an empty table  I don't show any errors, however, the data doesn't seam to appear in the table.  Every additional submission shows the following error:

 

  Could not execute query : .Duplicate entry '' for key 1

 

I used the following to create my table:

 

create table user (
  username varchar(20) primary key,
  passwd char(20) not null,
  email varchar(100) not null
);

 

Still using the same form above.  Here's my script:

<?php 

$username = $_POST['username'];
$passwd = $_POST['passwd'];
$email = $_POST['email'];

$username = addslashes ($username);
$passwd = addslashes ($passwd);
$email = addslashes ($email);

$hostname='localhost'; 
$user='user'; 
$pass='pass'; 
$dbase='admin'; 

$connection = mysql_connect("$hostname", "$user", "$pass") 
	or die ("Can't connect to MySQL");
mysql_select_db($dbase , $connection) or die ("Can't select database.");

$sql = "INSERT INTO Client (username, passwd, email) VALUES ('$_POST[username]', '$_POST[passwd]', '$_POST[email]')";

$results = mysql_query($sql) or die 
("Could not execute query : $query." . mysql_error());

if ($results)
{
	do_html_header('Client Added');
	do_menu_main2('');
	echo "A new Client has been added.";
}
mysql_close();

?>

 

What am I doing wrong and is there a better way to do this?

[PFMaBiSmAd]

the data doesn't seam to appear in the table.

How are you determine that the data is not in the table?

 

The INSERT query is working and when you attempt to insert the same username the primary key setting is causing the 'Duplicate entry' error, which it should (I'll assume you don't want to permit duplicate username entries.)

[bpburrow]

I used browse in phpmyadmin to determine there was no data, at least none that's visible.  The results showed 1 empty row after the first time I enter data into the table.  Every time thereafter, regardless of what I use as a username I receive the same error:

 

Duplicate entry '' for key 1

 

Without knowing the right terms, it appears as if empty data is being passed to the table which would explain why I'm having issues with duplicate primary keys.  So how do I fix this?

[bpburrow]

Just to recap the issue at hand, I'm having difficulty entering data from my from.  I used

error_reporting(E_ALL);

and came up with the following errors:

 

Notice: Undefined index: username in /home/brittao1/public_html/admin/insertclient.php on line 7

 

Notice: Undefined index: passwd in /home/brittao1/public_html/admin/insertclient.php on line 8

 

Notice: Undefined index: email in /home/brittao1/public_html/admin/insertclient.php on line 9

 

Notice: Undefined index: username in /home/brittao1/public_html/admin/insertclient.php on line 24

 

Notice: Undefined index: passwd in /home/brittao1/public_html/admin/insertclient.php on line 24

 

Notice: Undefined index: email in /home/brittao1/public_html/admin/insertclient.php on line 24

 

Notice: Undefined variable: query in /home/brittao1/public_html/admin/insertclient.php on line 27

Could not execute query : .Duplicate entry '' for key 1

 

Here's my form, newclient.php

<form action="insertclient.php" method="post">
	<div  id="menu" class="mainMenu">
		<fieldset>
		  <legend>New Client</legend>
		  <ul>
			<li>
			  <label for="username">Username:</label>
			  <input type="text" id="username" />
			</li>
			<li>
			  <label for="password">Password:</label>
			  <input type="text" id="passwd" />
			</li>
			<li>
			  <label for="email">Email:</label>
			  <input type="text" id="email" />
			</li>
		  </ul>
		<input type="submit" type="submit" value="add" />
		</fieldset>
	</div>
</form>

Here's my script, insertclient.php

<?php 
error_reporting(E_ALL);

require_once('../output.php');

$username = $_POST['username']; //line 7
$passwd = $_POST['passwd']; //line 8
$email = $_POST['email']; //line 9

$username = addslashes ($username);
$passwd = addslashes ($passwd);
$email = addslashes ($email);

$hostname='localhost'; 
$user='user'; 
$pass='pass'; 
$dbase='admin'; 

$connection = mysql_connect("$hostname", "$user", "$pass") 
	or die ("Can't connect to MySQL");
mysql_select_db($dbase , $connection) or die ("Can't select database.");

$sql = "INSERT INTO Client (username, passwd, email) VALUES ('$_POST[username]', '$_POST[passwd]',  '$_POST[email]')"; //line 24

$results = mysql_query($sql) or die 
("Could not execute query : $query." . mysql_error()); //line27

if ($results)
{
	do_html_header('Client Added');
	do_menu_main2('');
	echo "A new Client has been added.";
};
mysql_close();

?>

 

Can anyone see what I'm doing wrong?

 

 

[PFMaBiSmAd]

Your form fields don't have any name="..." attributes. You should change the id=".." attributes to name="..." attributes.

 

You will also need to use mysql_real_escape_string() instead of addslashes() on each piece of external data being put into a query to prevent sql injection and to prevent any sql special characters in them from breaking your query syntax. You need to do this after you have a connection to the database server. I would move the lines 7-13 to be right before the $sql = "..." statement.

 

Edit: Your $sql = ".." statement is also using the $_POST variables directly. You should be using your internal variable names  $username, $passwd, and $email after they have had mysql_real_escape_string() applied to them.

 

Also, are should not be storing the password as plain-text in the database in case your database gets compromised. Look into useing md5() or sha1() and a 'salt' string (search if you don't know what that means.)

[Andy-H]

You are using addslashes on the postdata and assigning the return values to a variable for each of them.

 

 

Then you use raw postdata in your query, and you dont specify the post-array keys as strings.

 

Also, dont change the id's to names in your input, just add the extra attributes with the same values or else your labels will not work.

[bpburrow]

Problem Solved!!!!  You are awesome!!  Thank You. 

 

Now how do I close this forum????

[PFMaBiSmAd]

The "Topic Solved" button is near the bottom-left side of the page.