Syntax Error

[anevins]

I'll just post the query because I can't figure out the syntax error:

 

$query = "INSERT INTO report, location, person, stick (organisation, phoneNo, firstName, middleName, lastName, street, town, city, size, colour, make)

VALUES ('$organisation', '$phone', '$firstname', '$middlename', '$lastname', '$street', '$town', '$city','$size', '$colour', '$model')

WHERE report.sightingStick = stick.sid AND report.reporter = person.pid AND report.missingLocation = location.lid";

 

Any ideas?

[bh]

Im a little confused. WHERE statement after the values?

[anevins]

Ah yes, I need some guidance please

[kickstart]

Hi

 

You appear to be trying to insert to multiple tables (with the WHERE clause seeming to be for a JOIN) with a single insert statement. Don't beleive that MySQL supports this kind of statement.

 

All the best

 

Keith

[anevins]

Okay I've separated them out since I couldn't join them up with WHERE...

Here's the new code:

 

	$query = "INSERT INTO stick (sid, size, colour, make) VALUES ('','$size', '$colour', '$model')";
	$query = "INSERT INTO report (rid, organisation, phoneNo) VALUES ('','$organisation', '$phone')
				SELECT sid FROM stick, report
				WHERE report.sightingStick = stick.sid";
	$query = "INSERT INTO person (pid, firstName, middleName, lastName) VALUES ('','$firstname', '$middlename', '$lastname') 
				SELECT reporter FROM report, person
				WHERE report.reporter = person.pid";
	$query = "INSERT INTO location(lid, street, town, city) VALUES ('', '$street', '$town', '$city')
				SELECT missingLocation FROM report, location
				WHERE report.missingLocation = location.lid";

[anevins]

Forgot to say, there's still a syntax error with that

[kickstart]

Hi

 

What are the selects for in each insert?

 

Also I presume the id fields are numeric fields. Set them to NULL rather than ''.

 

All the best

 

Keith

[anevins]

I originally wanted to INSERT data but I realised I needed the data which was inserted, to be relative to the table in its relationship.

 

What I think its supposed to do :

 

The SELECT grabs the two keys, one foreign and one primary which are ready for the WHERE statement.

 

 

[anevins]

Here's the code again but with explanations of each SELECT statement

 

No SELECT:

		$query = "INSERT INTO stick (sid, size, colour, make) VALUES ('','$size', '$colour', '$model')";

 

Grab the foreign key of 'sightingStick' in table 'stick' and match it with the report:

		$query = "INSERT INTO report (rid, organisation, phoneNo) VALUES ('','$organisation', '$phone')
				SELECT sid,  sightingStick FROM stick, report
				WHERE report.sightingStick = stick.sid";

 

Grab the foreign key of 'reporter' in the table 'report' and match it with that person

$query = "INSERT INTO person (pid, firstName, middleName, lastName) VALUES ('','$firstname', '$middlename', '$lastname') 
				SELECT pid, reporter FROM report, person
				WHERE report.reporter = person.pid";

 

Grab for foreign key of 'missingLocation' in the table 'location' and match it with that report

		$query = "INSERT INTO location(lid, street, town, city) VALUES ('', '$street', '$town', '$city')
				SELECT lid, missingLocation FROM report, location
				WHERE report.missingLocation = location.lid";

[anevins]

 

== Table structure for table stick

 

|------

|Field|Type|Null|Default

|------

|//**sid**//|int(11)|No|

|type|enum('MB', 'GB')|No|

|size|int(3)|No|

|colour|varchar(20)|No|

|make|char(220)|No|

|missingLocation|int(11)|Yes|NULL

|belongs|int(11)|Yes|NULL

 

== Table structure for table report

 

|------

|Field|Type|Null|Default

|------

|//**rid**//|int(11)|No|

|organisation|char(220)|No|

|rfc_date|date|No|

|phoneNo|varchar(18)|No|

|sightingStick|int(11)|Yes|NULL

|Reporter|int(11)|Yes|NULL

 

== Table structure for table location

 

|------

|Field|Type|Null|Default

|------

|//**lid**//|int(11)|No|

|street|char(220)|No|

|town|varchar(20)|No|

|city|varchar(20)|No|

 

== Table structure for table person

 

|------

|Field|Type|Null|Default

|------

|//**pid**//|int(11)|No|

|firstName|char(220)|No|

|middleName|char(220)|No|

|lastName|char(220)|No|

[kickstart]

Hi

 

You appear to be wanting to get the foreign key from one table which refers to the primary key in other tables where you haven't yet inserted the rows.

 

I think you need to reverse the order of your inserts and use mysql_insert_id to get the last inserted key fields.

 

All the best

 

Keith

[anevins]

Thanks Keith, I'll get right on it.