Jump to content

Archived

This topic is now archived and is closed to further replies.

crag

problem with duplicate entries

Recommended Posts

I am working on a script to hald a library of books and it has 5 tables:

library: that holds
id - authorid - publisherid - donatedbyid - amongst other fields but these are the fields I need help with.

author: id - name

publisher: id - name

donatedby: id - name

When the user enters the required info from the from the data is submitted to the database and the authorid,
publisherid, and donatedbyid are set with the mysql_insert_id() function.

Within the latter tables I have not allowed duplicate fields to be entered when the form is submitted - so far all works fine.

[code]
<?php if(isset($_GET['addbook'])): // If user wants to library ?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   <p><label>Enter Shelfmark:</label><br /><input type="text" name="shelfmark" size="40" /></p>
   <p><label>Enter Congress ID:</label><br /><input type="text" name="congressid" size="40" /></p>
   <p><label>Enter Category:</label><br /><input type="text" name="subject" size="40" /></p>
   <p><label>Enter Author:</label><br /><input type="text" name="authorname" size="40" /></p>
   <p><label>Enter Title:</label><br /><input type="text" name="title" size="40" /></p>
   <p><label>Enter Publisher:</label><br /><input type="text" name="publisher" size="40" /></p>
  <p><label>Enter Publish date:</label><br /><input type="text" name="publishdate" size="40" /></p>
  <p><label>Enter Donated by:</label><br /><input type="text" name="name" size="40" /></p>
  <p><label>Enter ISBN:</label><br /><input type="text" name="isbn" size="40" /></p>
  <p><input name="submit" type="submit" value="submit" class="submit" /></p>
</form>
<?php else: // Display default page

// Connect to database
include_once 'db.inc.php';

$result = @mysql_query('SELECT DISTINCT author.id, author.name AS authorname, library.id,   library.shelfmark, library.congressid, library.title, library.publishdate, library.isbn,
library.donid, donatedby.id, donatedby.name AS donatedby, publisher.id, publisher.name AS publisher,
category.id, category.subject FROM author, library, donatedby, publisher, category');
if(!$result) {
    exit ('<p>Error performing query ' . mysql_error() . '</p>');
}
            
// If a  donated by name has been submitted
// add it to the database.
if(isset($_POST['name'])) {
    $donatedby = $_POST['name'];
    // Check for duplicate entries
    $result = mysql_query("SELECT * FROM donatedby WHERE name = '$donatedby'");
    if(!mysql_num_rows($result)){ // No duplicates found
        $sql = "INSERT INTO donatedby SET
        name = '$donatedby'";
    if(@mysql_query($sql)) {
        echo '<p>Donated by has been added to the database</p>';
    } else {
        echo '<p>Error submitting donated by: ' . mysql_error() . '</p>';
    }
}
}
// Hold dontatedby.id for future use
$donatedid = mysql_insert_id();

// If a publisher has been submitted
// add it to the database.
if(isset($_POST['publisher'])) {
    $publisher = $_POST['publisher'];
    // Check for duplicates
    $result = mysql_query("SELECT * FROM publisher WHERE name = '$publisher'");
    f(!mysql_num_rows($result)){
        $sql = "INSERT INTO publisher SET
        name = '$publisher'";
    if(@mysql_query($sql)) {
        echo '<p>Publisher has been added to the database</p>';
    } else {
        echo '<p>Error submitting publisher: ' . mysql_error() . '</p>';
    }
}
}
// Hold publisher.id for future use
$pubid = mysql_insert_id();

// If an author has been submitted
// add it to the database.
if(isset($_POST['authorname'])) {
    $authorname = $_POST['authorname'];
    //Check for duplicate entries
    $result = mysql_query("SELECT id, name FROM author WHERE name = '$authorname'");
    if(!mysql_num_rows($result)){ // No duplicates found
        $sql = "INSERT INTO author SET
        name = '$authorname'";
    if(mysql_query($sql)) {
        echo '<p>The author has been added to the database</p>';
    } else {
        echo '<p>Error submitting author: ' . mysql_error() . '</p>';
    }
}
}

// If a book has been submitted
// add it to the database.
if(isset($_POST['title'])) {
    $title = $_POST['title'];
    $shelfmark = $_POST['shelfmark'];
    $congressid = $_POST['congressid'];
    $publishdate = $_POST['publishdate'];
    $isbn = $_POST['isbn'];
    $authorid = mysql_insert_id();
    $sql = "INSERT INTO library SET
        title = '$title',
        shelfmark = '$shelfmark',
        congressid = '$congressid',
        publishdate = '$publishdate',
        isbn = '$isbn',
        donid = '$donatedid',
        publishid = '$pubid',
        authorid = '$authorid'";
    if(mysql_query($sql)) {
        echo '<p>The book has been added to the database</p>';
    } else {
        echo '<p>Error submitting book: ' . mysql_error() . '</p>';
    }
}
endif; ?>
[/code]

My problem is that when a duplicate field for say the author has been detected instead of "$author = mysql_insert_id()" being inserted into the library table. I want to hold the id of the detected "duplicate" field to use to insert into the library.authorid field.

I am not sure how I can hold this id in a variable globally so that I can use to insert into the library

Can anyone help please?
Thanks

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.