homepage Welcome to WebmasterWorld Guest from 54.211.34.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql query not executing
Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 8:51 am on Jul 11, 2007 (gmt 0)

Okay, so this is something I've done a million times before but with this one I just can't get it to work.

I know my database connection is okay because I can do select statements and they run fine.

So here's my statement (sorry, it's a bit long)

$query = ("INSERT INTO `subcontractors`.`subcontractors` (`title`, `firstname`, `lastname`, `address1`, `address2`, `address3`, `town`, `county`, `postcode`, `phone`, `fax`, `mobile`, `email`, `empstatus`, `notes`, `availability`, `company`, `vat`, `reg`, `companyadd`, `accountsadd`, `companyinfo`, `commercial`, `housing`, `industrial`, `leisure`, `eastanglia`, `london`, `midlands`, `north`, `scotland`, `southeast`, `southwest`, `wales`, `buildingsurveying`, `catsurveying`, `cdm`, `conditionsurveymanagement`, `conditionsurveys`, `consultancy`, `environmentalhealth`, `gpsurveying`, `partywallsurveys`, `projectmanagement`, `quantitysurveying`, `valuations`, `amcih`, `ba`, `bsc`, `diparch`, `dipenv`, `fnaea`, `fricsb`, `fricsgp`, `mcieh`, `mcioba`, `mciobb`, `mricsb`, `mricsgp`, `mricsq`, `ond`, `riba`) VALUES ('".$_POST['title']."', '".$_POST['firstname']."', '".$_POST['lastname']."', '".$_POST['address1']."', '".$_POST['address2']."', '".$_POST['address3']."', '".$_POST['town']."', '".$_POST['county']."', '".$_POST['postcode']."', '".$_POST['phone']."', '".$_POST['fax']."', '".$_POST['mobile']."', '".$_POST['email']."', '".$_POST['empstatus']."', '".$_POST['notes']."', '".$_POST['availability']."', '".$_POST['company']."', '".$_POST['vat']."', '".$_POST['reg']."', '".$_POST['companyadd']."', '".$_POST['accountsadd']."', '".$_POST['companyinfo']."', '".$_POST['commercial']."', '".$_POST['housing']."', '".$_POST['industrial']."', '".$_POST['leisure']."', '".$_POST['eastanglia']."', '".$_POST['london']."', '".$_POST['midlands']."', '".$_POST['north']."', '".$_POST['scotland']."', '".$_POST['southeast']."', '".$_POST['southwest']."', '".$_POST['wales']."', '".$_POST['buildingsurveying']."', '".$_POST['catsurveying']."', '".$_POST['cdm']."', '".$_POST['conditionsurveymanagement']."', '".$_POST['conditionsurveys']."', '".$_POST['consultancy']."', '".$_POST['environmentalhealth']."', '".$_POST['gpsurveying']."', '".$_POST['partywallsurveys']."', '".$_POST['projectmanagement']."', '".$_POST['quantitysurveying']."', '".$_POST['valuations']."', '".$_POST['amcih']."', '".$_POST['ba']."', '".$_POST['bsc']."', '".$_POST['diparch']."', '".$_POST['dipenv']."', '".$_POST['fnaea']."', '".$_POST['fricsb']."', '".$_POST['fricsgp']."', '".$_POST['mcieh']."', '".$_POST['mcioba']."', '".$_POST['mciobb']."', '".$_POST['mricsb']."', '".$_POST['mricsgp']."', '".$_POST['mricsq']."', '".$_POST['ond']."', '".$_POST['riba']."'");

mysql_query($query) or die('Error, insert query failed');

So I copied this straight out of my php myadmin and just replaced the values with the values from my form. Every time I try this I just get the "Error, insert query failed", so what's that all about? I thought it may have been that I was using a reserved mysql word but I'm not sure what it is if that's the case. I'm sure the priveleges are set up for the database access so I'm a bit stumpted.

Any help would be appreciated.

 

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 8:57 am on Jul 11, 2007 (gmt 0)

Remove the die and see what error it gives you:
This:
mysql_query($query) or die('Error, insert query failed');
to this:
mysql_query($query);

Also try changing this:
$query = ("INSERT INTO `subcontractors`.`subcontractors` ....
to this:
$query = ("INSERT INTO subcontractors ....

Habtom

Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 9:29 am on Jul 11, 2007 (gmt 0)

With those changes implemented it goes through to the "successfully submitted" line but nothing has been entered into the database.

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 10:17 am on Jul 11, 2007 (gmt 0)

Change the or die(... to the following:

or die(mysql_error());

and see what errors you get.

Hab

[edited by: Habtom at 10:20 am (utc) on July 11, 2007]

Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 11:12 am on Jul 11, 2007 (gmt 0)

"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"

Of course the whole statement was on one line so I broke it up and it refers to the last line of the statement... but I still can't see a problem there :o(

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 11:15 am on Jul 11, 2007 (gmt 0)

$query = (". . .

What are the brackets doing there anyway?

change to:

$query = " . . .

Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 11:53 am on Jul 11, 2007 (gmt 0)

That doesn't seem to make any difference :o(

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 12:48 pm on Jul 11, 2007 (gmt 0)

Try the following:

$query = "INSERT INTO subcontractors (title, firstname, lastname, address1, address2, address3, town, county, postcode, phone, fax, mobile, email, empstatus, notes, availability, company, vat, reg, companyadd, accountsadd, companyinfo, commercial, housing, industrial, leisure, eastanglia, london, midlands, north, scotland, southeast, southwest, wales, buildingsurveying, catsurveying, cdm, conditionsurveymanagement, conditionsurveys, consultancy, environmentalhealth, gpsurveying, partywallsurveys, projectmanagement, quantitysurveying, valuations, amcih, ba, bsc, diparch, dipenv, fnaea, fricsb, fricsgp, mcieh, mcioba, mciobb, mricsb, mricsgp, mricsq, ond, riba) VALUES ('".$_POST[title]."', '".$_POST[firstname]."', '".$_POST[lastname]."', '".$_POST[address1]."', '".$_POST[address2]."', '".$_POST[address3]."', '".$_POST[town]."', '".$_POST[county]."', '".$_POST[postcode]."', '".$_POST[phone]."', '".$_POST[fax]."', '".$_POST[mobile]."', '".$_POST[email]."', '".$_POST[empstatus]."', '".$_POST[notes]."', '".$_POST[availability]."', '".$_POST[company]."', '".$_POST[vat]."', '".$_POST[reg]."', '".$_POST[companyadd]."', '".$_POST[accountsadd]."', '".$_POST[companyinfo]."', '".$_POST[commercial]."', '".$_POST[housing]."', '".$_POST[industrial]."', '".$_POST[leisure]."', '".$_POST[eastanglia]."', '".$_POST[london]."', '".$_POST[midlands]."', '".$_POST[north]."', '".$_POST[scotland]."', '".$_POST[southeast]."', '".$_POST[southwest]."', '".$_POST[wales]."', '".$_POST[buildingsurveying]."', '".$_POST[catsurveying]."', '".$_POST[cdm]."', '".$_POST[conditionsurveymanagement]."', '".$_POST[conditionsurveys]."', '".$_POST[consultancy]."', '".$_POST[environmentalhealth]."', '".$_POST[gpsurveying]."', '".$_POST[partywallsurveys]."', '".$_POST[projectmanagement]."', '".$_POST[quantitysurveying]."', '".$_POST[valuations]."', '".$_POST[amcih]."', '".$_POST[ba]."', '".$_POST[bsc]."', '".$_POST[diparch]."', '".$_POST[dipenv]."', '".$_POST[fnaea]."', '".$_POST[fricsb]."', '".$_POST[fricsgp]."', '".$_POST[mcieh]."', '".$_POST[mcioba]."', '".$_POST[mciobb]."', '".$_POST[mricsb]."', '".$_POST[mricsgp]."', '".$_POST[mricsq]."', '".$_POST[ond]."', '".$_POST[riba]."')";

[edited by: Habtom at 12:59 pm (utc) on July 11, 2007]

Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 12:58 pm on Jul 11, 2007 (gmt 0)

that still gives me the same error.

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 1:00 pm on Jul 11, 2007 (gmt 0)

The query is updated. There was a missing ")".

Copy it again

Hab

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3391338 posted 1:36 pm on Jul 11, 2007 (gmt 0)

If the post values aren't escaped using mysql_real_escape_string [php.net], any input that contains a quote will break the insert string.

Also, you might have a field name in there that is a reserved word [dev.mysql.com]. Wrapping your field names with `back ticks` will force mysql to ignore any other meaning for those words.

Robert Poole

5+ Year Member



 
Msg#: 3391338 posted 2:14 pm on Jul 11, 2007 (gmt 0)

Thanks for your help Hab, that finally got it working!

What a chore! Don't you just love it when you spend hours trying to break down a problem and all you've done is missed a bracket or a comma or a quote mark someone.

Good persevearance!

LBmtb

5+ Year Member



 
Msg#: 3391338 posted 4:40 am on Jul 12, 2007 (gmt 0)

Like the other guy a couple posts up said, don't take things straight from post or get and put them into a query. Bad things can happen.

At least make sure you run it through mysql_real_escape_string().

Habtom

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3391338 posted 6:36 am on Jul 12, 2007 (gmt 0)

Glad you got it working. Don't forget to clean up the data before adding it to your database. Never trust data coming externally through forms.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved