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.

Re: query woes 80 80

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

check your insert statements.

Re: query woes 80 80

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.

Re: query woes 80 80

getting auto-incremented, how ?

check if they are getting auto-incremented or not .

Re: query woes 80 80

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.

Re: query woes 80 80

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.