0

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.

2
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by dyingatmidnight
0

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

check your insert statements.

0

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

0

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
0

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.