I'm having trouble getting my queries to insert properly.

this is my table structure

books(ISBN(pk), authorID(fk), formatID(fk), publisherID(fk), titleID(fk), yearPublished)

the ISBN and year are past directly via a form but the foreign keys are all auto incremented in their own tables. I was trying this:

mysql_query("insert into authors(authorName, authorBio) values('$author', '$bio')") or die(mysql_error());
	$authorID = mysql_query("select authorID from authors where authorName = '$author' and authorBio = '$bio'") or die(mysql_error());
	mysql_query("insert into publishers(pname) values('$publisher')") or die(mysql_error());
	$publisherID = mysql_query("select publisherID from publishers where pname = '$publisher'") or die(mysql_error());
	$formatID = mysql_query("select formatID from format where formatType = '$format'") or die(mysql_error());
	mysql_query("insert into titles(authorID, title, summary) values('$authorID', '$title', '$summary')") or die(mysql_error());
	$titleID = mysql_query("select titleID from titles where authorID = '$authorID' and title = '$title'") or die(mysql_error());
	mysql_query("insert into books values('$ISBN', '$formatID', '$publisherID', '$year', '$titleID')") or die(mysql_error());

but all the foreign keys are getting inserted as zeros rather than the value they should be.

I was looking into select queries inside the insert but I'm not sure how that would work with the two values that don't need selecting.

6 Years
Discussion Span
Last Post by dyingatmidnight

You are not inserting the Id columns (authorID, publisherID,..............) into any of the tables.

check your insert statements.


I'm not inserting them because they are auto-incremented when I insert values into table.

Also I've been erroneously referring to the secondary keys are foreign keys when in fact they are not set up as such. As this was a feature of mysql I was actually unaware of, I think by default all my tables before were in a format that didn't even allow foreign keys. So actually all the keys were created as PRIMARY when created in phpMyAdmin.

Edited by dyingatmidnight: n/a


I hit the auto-increment check mark in phpMyAdmin when setting up the keys... (sorry that sounded way less snotty in my head, entirely not my intent)

and yeah when I select everything from the authors table for example I can see the keys in incrementing. I've attached a screenshot of the table. I haven't gotten to the part yet where I need to display table values with php, been trying to get the insert working properly first.

Attachments incrementingkeys.png 3.26 KB

What I think I need is some way to return the auto incremented values from the three different tables and store them so that I can then insert them all into the books table. But that's proving difficult to google.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.