Jump to content

Archived

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

crag

inserting form data into multiple tables

Recommended Posts

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.

[code]
<?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; ?>
[/code]

Thanks in advance

Share this post


Link to post
Share on other sites
[code]
        
// 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>');
}
                    
[/code]


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...

Share this post


Link to post
Share on other sites
Thanks. Don't know why I didn't think of that.

Guess thats why im a newbie

solved

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.