crag

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

I managed to work it out for myself - maybe I should have tried harder in the first place before posting here. A lesson learnt

I am using this code to output the formatted date to screen which produces the result: "Saturday 1st April" for example. [code] <?php                  include_once 'db.inc.php';                      // Display all gig listings to page. $result = @mysql_query('SELECT id, bandname, giginfo, gigtime, DATE_FORMAT(gigdate, "%W %D %M") AS gigdate FROM giglist ORDER BY gigdate'); if(!$result) {      exit ('<p>Error performing query ' . mysql_error() . '</p>'); }                      while($row = mysql_fetch_array($result)) {     $gigid = $row['id'];     $bandname = htmlspecialchars($row['bandname']);     $giginfo = htmlspecialchars($row['giginfo']);     $gigtime = htmlspecialchars($row['gigtime']);     $gigdate = $row['gigdate'];     echo '<fieldset><p><b>' . $gigdate . ' | ' .         $bandname . '<br /></b>' . $giginfo . '<br /><b>' .         $gigtime . '</b></p></fieldset><br />';     }                     ?> [/code] My problem is when using "ORDER BY gigdate" it orders by the day (Saturday) and not the actual full date itself so giving me the result where all mondays are grouped together then tuesdays and so on. Also in my form: [code] <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">     <p><label>Enter date:</label><input name="gigdate" type="text" size="20" maxlength="20" /></p>     <p><label>Enter band name:</label><input name="bandname" type="text" size="40" maxlength="40" />     <p><label>Enter gig details:</label><textarea name="giginfo" cols="50" rows="10"></textarea></p>     <p><label>Enter gig time:</label><input name="gigtime" type="text" size="20" maxlength="20" /></p>     <p><input name="submit" type="submit" value="submit" class="submit" /></p> </form> [/code] I have to enter the date as YYYY/MM/DD where I would like the user to be able to enter the date as DD/MM/YYY. Can anyone tell me how to achieve this? Thanks.

I have a number of forms on my site that the user will have to enter a date but they have to enter this using the format YYYY-MM-DD. As im from the uk I would like them to enter the date in the format DD-MM-YY. I am using the date_format() function when I output the date to screen but I need a way to format the date before the user has to enter it into the form. Is there an in built mysql date column type that will do this or will I have to this using my own php script?

solved

Thanks. Don't know why I didn't think of that. Guess thats why im a newbie solved

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

Hi. I am a newbie to php mysql and I hope you can help me with this small problem. I have 2 tables one named bandname with ID, bandname and contactID a 2nd table named contactinfo with ID, name, tel, email. The data for inserting into the tables is taken from a form. ->I firstly insert the contactinfo data into its table ->then i assign the bandname ID to a variable (contactid) using mysql_insert_id() ->then i insert the bandname and contactid into bandname table. My problem is this: When I submit the form data my code inserts the first record fine but when I try to submit a second record it inserts 3 records. 1 record that is fine and another two that are a mix of the two correct records. 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 contact info has been added to the database</p>'; } else { echo '<p>Error submitting contact info: ' . mysql_error() . '</p>'; } } } Can anyone please tell me what is wrong here sorry I didn't use a code tag. [code] 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>';                             }                         }                     } [/code]