using php mysql to insert into two tables

Question

im trying to insert data into two tables, i need to have an auto-increment in both tables one is book_id which is in the books table, and the other is auth_id in the authors table.

i need the author to be traced to a book.

here is my html code and php code that i am using.

any help would be much appreciated.

the HTML code is:-

 <form id="contact-form" method="post" action="database3.php" >
      <label for="fname">First Name: <span class="required">*</span></label>  
      <input type="text" id="fname" name="fname" value="" placeholder="John" required="required" />
      <label for="auth-name">Second Name: <span class="required">*</span></label>  
      <input type="text" id="sname" name="sname" value="" placeholder="Doe" required="required" /><br />
      <label for="email">Email Address: <span class="required">*</span></label>  
      <input type="email" id="email" name="email" value="" placeholder="[email protected]" required="required" />  
      <label for="telephone">Telephone: <span class="required">*</span></label>
      <input type="text" id="telephone" name="telephone" value="" placeholder="" required="required" /><br />

      <label for="genre">Genre:  <span class="required">*</span></label>  
      <select id="genre" name="genre" placeholder="please select one..." required="required">
        <option value="please-select">Please select one..</option>
        <option text="murder_mystery" value="1">Murder Mystery</option>  
        <option text="romance" value="2">Romance</option>  
        <option text="sci_fi" value="3">Sci-Fi</option>  
        <option text="horror" value="4">Horror</option>  
        <option text="thriller" value="5">Thriller</option>  
        <option text="screen_plays" value="6">Screen Play's</option>  
        <option text="poetry" value="7">Poetry</option>  
        <option text="childrens" value="8">Children's</option>  
        <option text="non_fiction" value="9">Non-Fiction</option>  
        <option text="comedy" value="11">Comedy</option>  
        <option text="other" value="10">Other</option>
      </select>
      <label for="numpages">Number of Pages: <span class="required">*</span></label>  
      <select id="numpages" name="numpages">  
        <option value="num-page">Please Select one..</option>  
        <option value="0-100">0-100</option>  
        <option value="100-300">100-300</option>  
        <option value="300-500">300-500</option>
        <option value="500">500+</option>
      </select><br />
      <label for="booktitle">Book Title: <span class="required">*</span></label>  
      <input type="text" id="booktitle" name="booktitle" value="" placeholder="John Doe" required="required" />
      <br />
      <label for="description">Book Description: <span class="required">*</span></label>  
      <textarea id="bookdescription" name="description" placeholder="max 40 words describing your work, this will effectively be how you sell your bookto the reader" required="required" data-minlength="20"></textarea>
      <br />
      <label for="synopsis">Book Synopsis: <span class="required">*</span></label>  
      <textarea id="synopsis" name="synopsis" placeholder="max 100 words of the synopsis of your book,to the reader"
       required="required" data-minlength="20"></textarea>
      <br />
      <label for="file">Filename:</label>
      <input type="file" name="file" id="file">
      <br />
      <label for="check_tc">Do you agree to the T&C<span class="required">*</span></label>
      <input type="checkbox" id="check_tc" name="check_tc" required="required" />
      <br />

      <input type="submit" value="submit" id="submit-button" />  
</form>

the php code is:-

<?php
$connection = mysql_connect("localhost", "root", "");
if(!$connection)
{
 die("database connection failed: " . mysql_error());
}

$db_select = mysql_select_db("good-read", $connection);
if(!$db_select)
{
die("database selection failed: " . mysql_error());
}

$sql = "INSERT INTO author (auth_first, auth_second, auth_email, auth_telephone)
VALUES
('$_POST[fname]','$_POST[sname]','$_POST[email]', '$_POST[telephone]')";

mysql_query($sql, $connection);

echo "1 record added";

$sql_book = "INSERT INTO book (book_title, book_num_pages, book_genre_id, book_description, book_synopsis)
VALUES
('$_POST[booktitle]', '$_POST[numpages]', '$_POST[genre]','$_POST[description]', '$_POST[synopsis]')";


mysql_query($sql_book, $connection);

echo "1 record added";

mysql_close($connection);
?>

What's your question? Are you asking how to fill in auth_id when inserting the book? — Barmar
– Barmar, Commented Oct 2, 2013 at 20:37
Two things: 1.) You should seriously consider switching to PDO 2.) To answer, what I think is your question, if you want to relate the author to books, you should use some sort of ID. Depending on how complex your database structure is you might want to use a foreign-key set up. So, for instance, you'd have an Author with an ID of 1, and every book hes written will have a property of "author_id". — mmcclannahan
– mmcclannahan, Commented Oct 2, 2013 at 20:39
Obligatory notice: mysql_*() functions are deprecated. Use mysqli or PDO instead. Your code is susceptible to SQL injection attacks. Make sure you use 'real_escape_string()` on all your $_POST variables, or usde prepared statements. — user1864610
– user1864610, Commented Oct 2, 2013 at 20:40
Please, before you write any more SQL interfacing code, you must read up on proper SQL escaping to avoid severe SQL injection bugs. Also, mysql_query should not be used in new applications. It's a deprecated interface that's being removed from future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way will help you avoid making mistakes like this. — tadman
– tadman, Commented Oct 2, 2013 at 20:43

Tim Biegeleisen · Accepted Answer · 2019-10-05 00:53:06Z

-3

mysql_insert_id() returns the auto-increment ID assigned in the last INSERT. Use that to fill in the auth_id column in the book row. Also, note carefully that I have refactored your code to use mysqli prepared statements. This will help to avoid things like injection attacks.

$auth_id = mysql_insert_id($connection);
$sql_book = "INSERT INTO book (auth_id, book_title, book_num_pages, book_genre_id, book_description, book_synopsis)
    VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql_book);
$stmt->bind_param("ississ", $auth_id, $_POST[booktitle], $_POST[numpages], $_POST[genre], $_POST[description], $_POST[synopsis]);
$stmt->execute();
$stmt->close();

edited Oct 5, 2019 at 0:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Oct 2, 2013 at 20:40

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

tadman Over a year ago

This, like the original, is completely reckless due to a complete lack of escaping.

Barmar Over a year ago

Why the downvote? Because I didn't fix all the SQL injection problems? I'm just answering the question, I'm not teaching him how to rewrite his code to solve all the other problems. The comments above address that adequately.

tadman Over a year ago

You can omit the query. Your one line about using mysql_insert_id is sufficient. Quoting that code is akin to endorsing it, saying, in effect, "This is how I would do it."

tadman Over a year ago

Putting $_POST variables in queries is a habit that needs to be stomped out completely. I'm sorry about being such a curmudgeon, but unless people like you that know better take the small amount of time to demonstrate the correct way of doing it, people won't break their old habits.

Barmar Over a year ago

Why don't YOU post a better answer, instead of complaining about mine?

|

Collectives™ on Stack Overflow

using php mysql to insert into two tables

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related