Welcome to WebmasterWorld Guest from 23.20.110.176

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql query not executing

     
8:51 am on Jul 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 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.

8:57 am on July 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 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

9:29 am on July 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


With those changes implemented it goes through to the "successfully submitted" line but nothing has been entered into the database.
10:17 am on July 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 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]

11:12 am on July 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 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(

11:15 am on July 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 0


$query = (". . .

What are the brackets doing there anyway?

change to:

$query = " . . .

11:53 am on July 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


That doesn't seem to make any difference :o(
12:48 pm on July 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 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]

12:58 pm on July 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 0


that still gives me the same error.
1:00 pm on July 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 0


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

Copy it again

Hab

1:36 pm on July 11, 2007 (gmt 0)

Senior Member from US 

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

joined:Dec 9, 2003
posts:3416
votes: 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.

2:14 pm on July 11, 2007 (gmt 0)

Junior Member

5+ Year Member

joined:June 7, 2007
posts:82
votes: 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!

4:40 am on July 12, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 19, 2005
posts: 77
votes: 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().

6:36 am on July 12, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 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.