Pfthb

I am a student and I have just started the journey with php and mysql. If there is any mistake regarding to this post, my apologies.

I have spent hours trying to do this by myself.. feeling dumb.
Sorry I haven't found any other question that could solve this problem.
Please, how can I get this working?

I have 2 tables with auto_increment ids:

Table 1

bookid | bookTitle | authorid

1 | book1 | 1

Table 2

authorid | authorname| bookid

1 | author1 | 1

I have a form with input values for the book and input values for the author, how can I INSERT this new book and get the information of the existent author?
find below some pieces of my code, that's not the whole code, I think I am just missing the logical query to find the author name in table 2 and insert the book with it.
I would like include the existent authorid from t2 to this book.

// dbFunction file

function addBook($booktitle, $name)
$sql = "INSERT INTO book(bookTitle) VALUES (:booktitle)";

$sql = "INSERT INTO author(Name) VALUES (:name)


// controller

require ("dbFunctions.php");

if($_POST['action_type'] == 'add'){
 $query = $conn->prepare( "SELECT authorname FROM author WHERE authorname = :name");

 $query->bindvalue(":name", $name);
 $query->execute();

 if( $query->rowCount() > 1 ) {

 $querySuccess = addBook($booktitle, $authorname) // calling function

if( $query->rowCount() > 1, It will insert the book and author information even if it exists, it will duplicate authors and ids.

if( $query->rowCount() < 1, it will insert the book and the author.

Thank you.

As you edited your post I think I understand what you mean. But before I can answer your question you need to change some things.

• Your book table and author table both link to eachother. You want only one to link to the other. Because a book always has an author you need to give the book an author id but an author has (almost always) more books. Because you can only save one value per column you should remove the book id from the author. If you ever find yourself in a situation where both tables can have more than one of eachother (you will), you'd need to make a many-to-many relation.


• If you want people to 'select' an author, you should not make them type the name (unless you use jquery ajax calls but I'd not recommend those if you just started with all this). I'd recommend using php to create a dropdown list with all the authors, if there are many, you can also look into datalists. (Keep in mind that datalists can be submitted without actually selecting an item from the list, so you need to check if what they chose actuallu exists)


• At last, you do not need to add another author every time you add a book. Because you can link that book to an author using it's id.

So the steps I'd recommend you do are:

1. Recreate the author input field to a HTML select box or datalist


2. Check if the author selected exists (Checking if the query resulted in one row)
   
   (SELECT * FROM authors WHERE id = :id) Use the IDs as values in the datalist or select statement


3. Add a book with the selected id from the found author