Query error - very wired

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Query error - very wired

Post by crazytopu »

This is so wired, hope someone can help me:

This is the problem part:

Code: Select all

$insert = "INSERT INTO buyer ( `id` , `surname` , `firstname` , `email` , `phone` , `position` , `companyname` , `company_type` , `addressline1` , `addressline2` , `city` , `postcode` , `country` , `username` , `password` , `approve` , `last_login` ) 
			VALUES ('',
			'".$_POST['surname_txt']."', 
			'".$_POST['firstname_txt']."', 
			'".$_POST['email_txt']."', 
			'".$_POST['phone_txt']."', 
			'".$_POST['position_txt']."', 
			'".$_POST['companyname_txt']."', 
			'".$_POST['companytype_txt']."', 
			'".$_POST['addressline1_txt']."', 
			'".$_POST['addressline2_txt']."', 
			'".$_POST['city_txt']."', 
			'".$_POST['postcode_txt']."', 
			'".$_POST['country_txt']."', 
			'".$_POST['username_txt']."', 
			'".$_POST['password_txt']."',
			'',''
			
			 
			)";

			echo $insert;
		$insert_result = $connector->query($insert);
		
				
		echo mysql_affected_rows();	
		if(mysql_affected_rows()) {
		
		
			echo   "An email has been sent to your email address. Your account is now
			subject to approval. As soon as your account is approved you will receive
			a confirmation email.";
			
		}

		else{
			echo print_mysql_error();
		
		}

And when I fill in the form and hit submit button and echo $insert I get this:


Code: Select all

INSERT INTO buyer ( `id` , `surname` , `firstname` , `email` , `phone` , `position` , `companyname` , `company_type` , `addressline1` , `addressline2` , `city` , `postcode` , `country` , `username` , `password` , `approve` , `last_login` ) VALUES ('', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', '', 'a', 'a', '', 'as', 'f970e2767d0cfe75876ea857f92e319b', '','' )1An email has been sent to your email address. Your account is now subject to approval. As soon as your account is approved you will receive a confirmation email.
Clearly a record was added as mysql_affected_rows() returns 1

and to make more sense, when the page is refreshed it says, the username exist please take a different name to show that this query worked just fine.

But when I go the database I see nothing there, and even from admin panel where the buyer details goes for approval it does not appear there either.

I thought something could be wrong with my insert statement (even though I was 100% sure there cant be as it returned 1) I copied the insert statement (I echoed it remember) and then open phpmyadmin, run that exact same query and only then it inserts a record and only then from admin panel you can see a new buyer joining is subject to approval.

Here is my database table :

Code: Select all

CREATE TABLE `buyer` (
  `id` int(11) NOT NULL auto_increment,
  `surname` varchar(50) NOT NULL default '',
  `firstname` varchar(50) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `phone` int(20) NOT NULL default '0',
  `position` varchar(50) NOT NULL default '',
  `companyname` varchar(70) NOT NULL default '',
  `company_type` varchar(40) NOT NULL default '',
  `addressline1` varchar(70) NOT NULL default '',
  `addressline2` varchar(70) default NULL,
  `city` varchar(20) NOT NULL default '',
  `postcode` varchar(10) NOT NULL default '',
  `country` varchar(20) NOT NULL default '',
  `username` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `approve` int(1) NOT NULL default '0',
  `last_login` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

If anybody is interested to run this code on their own server I can give you the full code.

Please help me fix this, it's driving me crazy!
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

That's really WIRED! I don't have the time at the moment to look to closely at what's going on, but I did notice this... you're ASKING for trouble by just putting whatever the user submites, unchecked into the database. It's a nice way to get your database hosed or worse... at the very least, you should use mysql_real_escape_string

Code: Select all

$insert = "INSERT INTO buyer ( `id` , `surname` , `firstname` , `email` , `phone` , `position` , `companyname` , `company_type` , `addressline1` , `addressline2` , `city` , `postcode` , `country` , `username` , `password` , `approve` , `last_login` )
                        VALUES ('',
                        '".mysql_real_escape_string($_POST['surname_txt'])."',
                        '".mysql_real_escape_string($_POST['firstname_txt'])."',
                        '".mysql_real_escape_string($_POST['email_txt'])."',
                        '".mysql_real_escape_string($_POST['phone_txt'])."',
                        '".mysql_real_escape_string($_POST['position_txt'])."',
                        '".mysql_real_escape_string($_POST['companyname_txt'])."',
                        '".mysql_real_escape_string($_POST['companytype_txt'])."',
                        '".mysql_real_escape_string($_POST['addressline1_txt'])."',
                        '".mysql_real_escape_string($_POST['addressline2_txt'])."',
                        '".mysql_real_escape_string($_POST['city_txt'])."',
                        '".mysql_real_escape_string($_POST['postcode_txt'])."',
                        '".mysql_real_escape_string($_POST['country_txt'])."',
                        '".mysql_real_escape_string($_POST['username_txt'])."',
                        '".mysql_real_escape_string($_POST['password_txt'])."',
                        '',''
                       
                        
                        )";
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Bets are on "You're looking at the wrong database or table"
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Post by crazytopu »

Dont think so, it's probably my local mysql server. I am working on a remote server now lets see.

Will update you if you get a clue.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

dont specify the 'id' field in the list of fields and dont put the '' in the values list for that first field. Shouldn't need those as it is autoincrement.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I count a different number of fields and values in your INSERT statement. Checking for errors using mysql_affected_rows() is the first of several mistakes you are making. You should use mysql_errno() or mysql_error() instead. If this query fails then mysql_affected_rows() may be left over from the last successful query. Add some real error checking and see what you find out.
(#10850)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

arborint wrote:I count a different number of fields and values in your INSERT statement.
17 fields, 17 values, that's ok. mysql_affected_rows() return -1 if the previous operation failed.
Nevertheless you're absolutely right about the error handling.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

volka wrote:17 fields, 17 values, that's ok.
Ah you are right ... the last has two values ...
(#10850)
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

I agree with volka check your connection and in which database, table you insert the data. There can be another problem you didn' t set the right to insert data in the table for that user (the error message isn' t shown).
Check for a similar bug on MySQL website and what is the solution (a friend was complaining how buggy is MySQL when using more advance features).
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Post by crazytopu »

Do you see what is the differene between these two insert statements? I dont see any. First one I inserted through phpmyadmin tool to get the right sql, the second is what my form generates when submitted. When I run the second insert statement from phpmyadmin it does insert a row. How strang!

I took off the id column and the value as someone suggested, but dont know why it is necessary.


Code: Select all

  


INSERT INTO `member` ( `id` , `usertype` , `surname` , `firstname` , `email` , `phone` , `position` , `companyname` , `company_type` , `addressline1` , `addressline2` , `city` , `postcode` , `country` , `username` , `password` , `approve` , `last_login` ) 
VALUES ( '', 'Select', 'sn', 'fn', 'e', 'p', 'pos', 'cn', 'ct', 'a1', 'a2', 'c', 'pc', 'c', 'un', '83878c91171338902e0fe0fb97a8c47a', '0', '05,03,07'

), ( '', '', '', '', '', '0', '', '', '', '', NULL , '', '', '', '', '', '0', NULL 

);

Code: Select all

  

INSERT INTO `member` (`usertype`, `surname` , `firstname` , `email` , `phone` , `position` , `companyname` , `company_type` , `addressline1` , `addressline2` , `city` , `postcode` , `country` , `username` , `password` , `approve` , `last_login` ) VALUES ( 'Select', 'sn', 'fn', 'e', 'p', 'pos', 'cn', 'ct', 'a1', 'a2', 'c', 'pc', '', 'un', '83878c91171338902e0fe0fb97a8c47a', '0','05,03,07', )


My php cod

Code: Select all

if($insert_result = $connector->query($insert)) {
		
		echo "ok";
		
		}
		
		else{
		
			echo mysql_error();
		
		}
And it does print ok when I hit the submit button. So, no error!

Now, I would be very very veeeeeeeeeeeeery grateful if somebody could download this zip file (which I've made for you guys) and see what's going on.


http://www.kikorben.com/test/work.zip


You will have to change the db settings in includes/SystemComponent.php file


Everything is ready, I just need you to test it and if you find anything please let me know.

This is driving me nuts!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

add

Code: Select all

function affected_rows() {
  return mysql_affected_rows($this->link);
}

function insert_id() {
  return mysql_insert_id($this->link);
}

function error() {
  return mysql_error($this->link);
}
to your DBConnector class (even better use an existing class/abstraction/whatever).

Change userlogin_test.php

Code: Select all

echo '<div>', htmlentities($insert), "</div>\n";
if($insert_result = $connector->query($insert)) {
  echo "affected rows:", $connector->affected_rows(), "<br />\n";
  echo "id:", $connector->insert_id(), "<br />\n";
}
else{
  echo 'error: ', $connector->mysql_error(), "<br />\n";
}
What does it print?
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Post by crazytopu »

:D

you are the man!!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

A curious man. What was the actual problem?
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Post by crazytopu »

It's the most wired thing I have ever seen.

It worked fine when I added your code to the test login page. Then I went back to the original file and made the changes as you suggested. I didnot forget to replace the Dbconnector.

I started to get the output - like affected row and insert id as 45.

So, I checked the database and there was no entry!!

Then I used the test login page again and it worked fine and i got insert id as 30,31 etc and record was indeed inserted.

So, then I thought lets show these files to the people if they can trace anything. So, I created a sub folder, put my original file + all the includes files + images and flash stuff.

Before copressed the folder I thought lets check if all files are right. Only then I realised it is working from this sub folder!! like inserting rows were generating insert id 32,33 etc.


No idea man what was wrong, no clue yet.

If you want I can upload the files as zip.
Post Reply