CODEWITHSUNDEEP
phpmysqlsql
BondsNamesTheJames
BondsNamesTheJames

Reputation: 25

How can i use the id from one table item to connect another tables item

I am pretty new to php and sql and am learning as i create a web app so i'm wondering how this would be possible, if at all, or if there are better practices for this sort of task.

just as an example: Say i have a database with 2 tables one is author, the other book

author table
-----------
id | author
1 | jones
2 | jane

book table
----------
id | title | author
1 | Learning php | 1 <- i want this to reference the author from author table
2 | Foobar | 1
3 | How to give a proper high five | 2

i have a form that gets the authors name from a query as a select tag and i want to assign the id as the value for author in the book table when i submit the form

$authorQuery = mysqli_query($con, "SELECT * FROM author");

<form id="bookForm" action="books.php" method="POST">
<label for="title">Book Title</label>
        <input type="text" name="title">

<label for="author">Author</label>
        <select id="author" name="author">
                <?php
                    while($row = mysqli_fetch_array($authorQuery)) {            
                        //display as an array in a drop down list
                        echo '<option>' . $row['author'] . '</option>';
                            }
                ?>
</form>

How would i go about this?

Upvotes: 2

Views: 339

Answers (2)

Accountant م
Accountant م

Reputation: 7493

I will make the options values as the authors Ids, change this

echo '<option>' . $row['author'] . '</option>';

to this

echo '<option value="' . $row['id']. '">' . htmlspecialchars($row['author']) . '</option>';

Now, when this form is submitted , at the server you will receive the id of the author in the $_POST['author'] variable, so you can make your update query using the id instead of the name of the author.

$query = "update book set author_id = ? where title = ?";
$stmt = $con->prepare($query);
$stmt->bind_param("is", $_POST['author'], $_POST['title']);
$stmt->execute();

suggestions:

  1. this scheme means that your relationship is 1:m (1 author can write many books, but 1 book is must be written by only 1 author), this is not correct, as many books are written by many authors, so you need to use a third conjunction table to represent the many to many relationship m:m

  2. The search on MySQL is case insensitive by default, however it's better to make a <select> for the books instead of <input> and assign the IDs of the books to the <option>s instead of typing the title (unless you have too much books to fit in a select), so you make sure you avoid any typos in the title of the book when you need to fill the form to assign an author to a book

Upvotes: 1

Olaiwola Ayoola
Olaiwola Ayoola

Reputation: 31

The easiest way will be to

  <?php  $stmt = $conn->prepare("SELECT * from book")
    $stmt->execute();

     foreach($stmt as $row){
    $id = $row['author'];
$stmt = $conn->prepare("SELECT * from author WHERE id='$id'")
    $stmt->execute();
    foreach($stmt as $row){
    $name = $row['author'];
?><option value="<?php echo $name;?>"><?php echo $name;?></option>
    }
    }

The Complex Method is

    $stmt = $conn->prepare("SELECT *, book.author AS bookauthor FROM book LEFT JOIN author ON author.id=book.id");
        $stmt->execute();
foreach ($stmt as $row) {
?><option value="<?php echo $row['bookauthor'];?>"><?php echo $row['bookauthor'];?></option>
}

Upvotes: 0

Related Questions