bigsilk Posted October 22, 2009 Share Posted October 22, 2009 I have a db that's supposed to have 236 records in it. For some odd reason, it suddenly has 634 and most of them are duplicates. It's as though the table is replicating itself and appending itself to the beginning of that table. I know it's the beginning because the first four hundred or so don't have the password included in any of the fields and it's not on my input form, yet. Here's my form to retrieve records from a query based on a username: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head> <title>Edit Profile</title> <link href="CSS/profile.css" rel="stylesheet" type="text/css"> <meta http-equiv="content-type" content="text/html;charset=utf-8"> <meta http-equiv="Content-Style-Type" content="text/css"></head><body><div class="centered_div"><div class="header">My Will Made Easy - Attorney Profile Editor</div><div class="profile_text"><?php$con = mysql_connect("localhost","###","###");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("bigsilkd_attorneys", $con);if (!empty($_POST['users'])){ $result = mysql_query('SELECT * FROM attorneys WHERE users=\'' . mysql_real_escape_string($_POST['users']) . '\'') or exit(mysql_error()); if ($row = mysql_fetch_assoc($result)) { ?>User Name: <input type="text" size="20" name="users" value="<?php echo $row['users']; ?>">Password: <input type="text" size="10" name="password" value="<?php echo $row['password']; ?>"> <br />Law Firm Name: <input type="text" size="60" name="LawFirmName" value="<?php echo $row['LawFirmName']; ?>"> <br />Main Office Address: <input type="text" size="60" name="MainOfficeAddress" value="<?php echo $row['MainOfficeAddress']; ?>"> <br />City: <input type="text" name="MainOfficeCity" value="<?php echo $row['MainOfficeCity']; ?>">ST: <input type="text" size="3" name="MainOfficeState" value="<?php echo $row['MainOfficeState']; ?>">Zip: <input type="text" size="10" name="MainOfficeZip" value="<?php echo $row['MainOfficeZip']; ?>"> <br />Main Phone: <input type="text" name="MainOfficePhone" value="<?php echo $row['MainOfficePhone']; ?>"> <br />Main Email Address: <input type="text" name="MainOfficeEmail" value="<?php echo $row['MainOfficeEmail']; ?>"> <br />Website Address: <input type="text" size="60" name="WebAddress" value="<?php echo $row['WebAddress']; ?>"> <br />Firm Overview: <textarea name='Overview' rows='5' cols='40'value="<?php echo $row['Overview']; ?>"></textarea><br />Years of Experience: <input type="text" name="YearsofExperience" value="<?php echo $row['YearsofExperience']; ?>"> <br />Languages Spoken: <textarea name='Languages' rows='5' cols='40'value="<?php echo $row['Languages']; ?>"></textarea><br />Geographical Areas Serving: <textarea name='GeographiesServing' rows='5' cols='40'value="<?php echo $row['GeographiesServing']; ?>"></textarea><br />Areas of Legal Practice: <textarea name='PracticeAreas' rows='5' cols='40'value="<?php echo $row['PracticeAreas']; ?>"></textarea><br />Primary Attorney's Name: <input type="text" name="MainAttorneyName" value="<?php echo $row['MainAttorneyName']; ?>"> <br />Primary Attorney's Phone Number: <input type="text" name="MainAttorneyPhone" value="<?php echo $row['MainAttorneyPhone']; ?>"> <br />Primary Attorney's Email Address: <input type="text" name="MainAttorneyEmail" value="<?php echo $row['MainAttorneyEmail']; ?>"> <br /> <?php } else { echo 'Username not found.'; ?><div class="profile_text"><form action="retrieve2.php" method="post"> User name: <input type="text" size="90" name="users" value="<?php echo $_POST['users']; ?>"></form> <?php }}else{ echo 'You must enter a user';}?></div></div></body></html> And here's what I'm using to input records: <?php$con = mysql_connect("localhost","###","###");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("bigsilkd_attorneys", $con);$sql="INSERT INTO attorneys (LawFirmName, MainOfficeAddress, MainOfficeCity, MainOfficeState, MainOfficeZip, MainOfficePhone, MainOfficeEmail, WebAddress, Overview, YearsofExperience, Languages, GeographiesServing, PracticeAreas, MainAttorneyName, MainAttorneyPhone, MainAttorneyEmail)VALUES('$_POST[LawFirmName]','$_POST[MainOfficeAddress]','$_POST[MainOfficeCity]','$_POST[MainOfficeState]','$_POST[MainOfficeZip]','$_POST[MainOfficePhone]','$_POST[MainOfficeEmail]','$_POST[WebAddress]','$_POST[Overview]','$_POST[YearsofExperience]','$_POST[Languages]','$_POST[GeographiesServing]','$_POST[PracticeAreas]','$_POST[MainAttorneyName]','$_POST[MainAttorneyPhone]','$_POST[MainAttorneyEmail]')";if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); }echo "1 record added";mysql_close($con)?> Link to comment Share on other sites More sharing options...
justsomeguy Posted October 22, 2009 Share Posted October 22, 2009 Your insert code doesn't check if it already exists, should it? Insert adds a new record to the table. If you want to update an existing record, use an update query, not insert. Link to comment Share on other sites More sharing options...
bigsilk Posted October 22, 2009 Author Share Posted October 22, 2009 I haven't created the UPDATE form, yet. Only a form to return a record associated with a username and a form to insert a new record. I'm trying to wrap my head around validation right now.I deleted the duplicate records. I'll keep trying to duplicate the error and see what I did to make it happen.Stay tuned...A Link to comment Share on other sites More sharing options...
chibineku Posted October 22, 2009 Share Posted October 22, 2009 Look into ON DUPLICATE KEY UPDATE in light of what jsg said - it'll save you a lot of effort. Link to comment Share on other sites More sharing options...
Kameloh Posted October 22, 2009 Share Posted October 22, 2009 Also are you using anything like phpMyAdmin to manage databases? And did you already make a 'delete' page or a 'display entries' page? Link to comment Share on other sites More sharing options...
bigsilk Posted October 22, 2009 Author Share Posted October 22, 2009 Also are you using anything like phpMyAdmin to manage databases? And did you already make a 'delete' page or a 'display entries' page?I'm using phphMyAdmin, and no, neither of those, yet. I'm really just getting into php and MySQL and am taking small, incremental steps towards creating a robust, safe db. As for display entries, couldn't I just:<?php$con = mysql_connect("localhost","peter","abc123");if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db("my_db", $con);$result = mysql_query("SELECT * FROM Persons");while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br />"; }mysql_close($con);?> As for deletion, not all records were dups. It was 'appending' only a portion of the table, sometimes 80 records, sometimes 104. I would still have had to go into phpMyAdmin and make sure all bad things were gone.I do have a page that returns a record based on a username, though. I'm going to use that as the edit record page, eventually. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 22, 2009 Share Posted October 22, 2009 MySQL isn't going to arbitrarily decide to add a bunch of records. I guarantee that your insert form is being used more often than you think. That's the only way to add a record to the table, unless you have another page somewhere inserting records into that table. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.