Welcome to WebmasterWorld Guest from 54.205.119.93

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql query not executing

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

5+ Year Member



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 Jul 11, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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 Jul 11, 2007 (gmt 0)

5+ Year Member



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

WebmasterWorld Senior Member 10+ Year Member



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 Jul 11, 2007 (gmt 0)

5+ Year Member



"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 Jul 11, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



$query = (". . .

What are the brackets doing there anyway?

change to:

$query = " . . .

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

5+ Year Member



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

WebmasterWorld Senior Member 10+ Year Member



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 Jul 11, 2007 (gmt 0)

5+ Year Member



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

WebmasterWorld Senior Member 10+ Year Member



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

Copy it again

Hab

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

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



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 Jul 11, 2007 (gmt 0)

5+ Year Member



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 Jul 12, 2007 (gmt 0)

10+ Year Member



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 Jul 12, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month