Jump to content


Photo

inserting form data into multiple tables


  • Please log in to reply
2 replies to this topic

#1 crag

crag
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 09 March 2006 - 02:32 PM

Hi I am a newbie to php and sql

I have two tables:

bandname(bid, bandname, contactid)
contactinfo(cid, name, tel, email)

When I insert data for the first time into the tables it works fine but when i try to insert a new record my code inserts 3 new records, one record that is correct and another two records that are a mix of the correct entries.

below is my php code. Can anyone tell me where I am going wrong please.

<?php if(isset($_GET['addcontact'])): // If user wants to add a gig listing ?>
    <fieldset>
    <legend>Manage band contacts</legend>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    <p><label>Enter band name:</label><input name="bandname" type="text" size="40" maxlength="40" />
    <p><label>Enter name:</label><input name="name" type="text" size="25" maxlength="20" /></p>
    <p><label>Enter tel#:</label><input name="tel" type="text" size="25" maxlength="20" /></p>
    <p><label>Enter e-mail:</label><input name="email" type="text" size="25" maxlength="25" /></p>
    <p><input name="submit" type="submit" value="submit" class="submit" /></p>
    </form>
    </fieldset>
    <?php else:  //  Display default page.
                
    // Connect to database server
    $dbcnx = @mysql_connect('localhost', 'root', '*******');
    if(!$dbcnx) {
        exit('<p>Unable to connect to the server at this time</p>');
    }
                    
    // Connect to the halt bar database.
    if(!@mysql_select_db('thehaltdb')) {
        exit ('<p>Unable to connect to the halt bar database at this time</p>');
    }
                    
    // Insert contact info into contactinfo table.
    if(isset($_POST['name'])) {
        $name = $_POST['name'];
        $tel = $_POST['tel'];
        $email = $_POST['email'];
        $sql1 = "INSERT INTO contactinfo SET
            name = '$name',
            tel = '$tel',
            email = '$email'";
        if(isset($_POST['bandname'])) {
            $bandname = $_POST['bandname'];
            $contactid = mysql_insert_id();
            $sql2 = "INSERT INTO bandname SET
                bandname = '$bandname',
                contactid = '$contactid'";
            if((@mysql_query($sql1) && (@mysql_query($sql2)) {
                echo '<p>The band name has been added to the database</p>';
            } else {
                echo '<p>Error submitting band name: ' . mysql_error() . '</p>';
            }
        }
    }
                    
                    
    // If a giglisting has been deleted,
    // remove it from the database.
    if(isset($_GET['deletecontact'])) {
        $cid = $_GET['deletecontact'];
        $bid = $_GET['bid'];
        $sql1 = @mysql_query("DELETE FROM bandname WHERE contactid = '$cid'");
        $sql2 = @mysql_query("DELETE FROM contactinfo WHERE cid = '$cid'");
    if($sql1 && $sql2) {
        echo '<p>The contact info & band name has been deleted.</p>';
    } else {
        echo '<p>Error deleting contact info & band name' . mysql_error() . '</p>';
    }
}
                    
// Display all gig listings to page.
$result = @mysql_query('SELECT cid, name, tel, email, bandname, contactid FROM contactinfo, bandname');
if(!$result) {
    exit ('<p>Error performing query ' . mysql_error() . '</p>');
}
                    
while($row = mysql_fetch_array($result)) {
    $cid = $row['cid'];
    $bandname = htmlspecialchars($row['bandname']);
    $name = htmlspecialchars($row['name']);
    $tel = htmlspecialchars($row['tel']);
    $email = htmlspecialchars($row['email']);
    echo '<p><b>' . $bandname . '  |  ' .
    $name . '</b><br />' .
    $tel . '<br /><b>' .
    $email . '</b>  |  ' .
                                        '<a href="' . $_SERVER['PHP_SELF'] . '?deletecontact=' . $cid . '">' .  'delete contact</a> | ' .
                                        '<a href="editcontact.php?cid=' . $cid . '">' . 'edit contact</a></p>';
}    
echo '<p><a href="'. $_SERVER['PHP_SELF'] . '?addcontact=1">Add a contact!</a></p>';
                    
endif; ?>

Thanks in advance

#2 mem0ri

mem0ri
  • Members
  • PipPipPip
  • Advanced Member
  • 110 posts

Posted 09 March 2006 - 03:25 PM

        
// Display all gig listings to page.
$result = @mysql_query('SELECT cid, name, tel, email, bandname, contactid FROM contactinfo, bandname');
if(!$result) {
    exit ('<p>Error performing query ' . mysql_error() . '</p>');
}
                    


Warning: I'm also newish to PHP and MySQL...

...but...is appears that the problem might be in your query to select the list. It doesn't seem like you're qualifying to which bandname a contact-info will associate itself. Something like...

SELECT cid, name, tel, email, bandname, contactid FROM contactinfo, bandname WHERE cid = contactid;

...seems to make more sense to me. That way you're qualifying how each row from the two tables is related...

#3 crag

crag
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 09 March 2006 - 03:49 PM

Thanks. Don't know why I didn't think of that.

Guess thats why im a newbie

solved




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users