mysql query not executing

[Robert Poole]

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]

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]

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

[Habtom]

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]

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

$query = (". . .

What are the brackets doing there anyway?

change to:

$query = " . . .

[Robert Poole]

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

[Habtom]

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]

that still gives me the same error.

[Habtom]

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

Copy it again

Hab

[whoisgregg]

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]

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]

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]

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.