SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    checking if a number or entry exists

    Hello all,
    I'm writing a bookwriter application were users can create chapters and write content in those chapters. My database consists of three tables. One of those tables is the chapters table which has the following columns:

    ID: int, not null, primary key, auto increment
    chap_title : varchar(250)
    chap_number: int, not null

    I want to be able to assign a chapter title a number which will be my descriptor id, so I can display the chapter number with the chapter title. But I also wan't my code to check and display an error if the chap_number is a non-numeral or already an existing chapter. Here my form:

    chapter_add_form.php
    PHP Code:
    <?php
    include("header.php" );
    ?>

    Please add new chapter:

    <form name="addchapter" method="post" action="chapter_add_qry.php">
    Chapter Number:
    <input type="text" name="chap_number" size="3" maxlength="5"><br><br>
    Chapter Title:
    <input type="text" name="new_chapter" size="50" maxlength="100">
    <p>
    <input type="submit" value="submit" name="submitchapter">

    </form>


    <?php
    include("footer.php" );
    ?>
    heres the query page:

    chapter_add_qry.php:
    PHP Code:
     <?php
    include("header.php" );
    ?>

    <?php
    //connect to the database
    $dbcnx mysql_connect('localhost','username','password');
    mysql_select_db('bookwriter');
    //query the database to submit the chapter
    if(isset($_POST['submitchapter'])) {
    $new_chapter $_POST['new_chapter'];
    $chap_number $_POST['chap_number'];
    $sql "INSERT INTO CHAPTERS SET
    chap_title= '
    $new_chapter'
    chap_number = '
    $chap_number'"
    If (@mysql_query($sql)) {
    echo(
    'Your chapter has been added');
    }
    else {
    echo(
    'problems adding joke:' mysql_error());
    }
    }
    ?>
    <P>
    <a href="chapter_form_act.php">Add Chapter</a>
    <?php
    include("footer.php" );
    ?>
    how do I add such code?

  2. #2
    SitePoint Wizard rozner's Avatar
    Join Date
    Oct 2002
    Location
    Paris
    Posts
    1,058
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    before inserting into the database, you can check it with mySQL:

    PHP Code:
    $sql "SELECT * FROM CHAPTERS WHERE chap_title = '$new_chapter' AND chap_number = '$chap_number'";
     
    $result mysql_query($sql);
     
    if (
    mysql_num_rows($result) >0) echo "already exists";
    else {
      
    // add to table

    I forgot you also wanted to check for non-numeric. I think PHP has an is_numeric() function. You can use that.

    http://ca3.php.net/is_numeric

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    Chicago, USA
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For checking to see if a variable is numeric:

    Code:
    if (is_numeric ($num) == true) {
        ...
    }

  4. #4
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shouldn't this responsibility be left to the database system?
    If you declare the descriptor_id numeric and unique, you cannot put in non-numeric values or duplicates.
    So if an insert query fails, you can act accordingly.

    Just a thought.
    Per
    Everything
    works on a PowerPoint slide

  5. #5
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I've tried it and it doesn't seem to be working. I am able to post the same number without the error message occuring:

    chapter_edit_form.php:

    PHP Code:
     
    <?php
    include("header.php");
    ?>
    <?php
    //connect to the database
    $dbcnx mysql_connect('localhost','jive','zeroone');
    $bookconnect mysql_select_db('bookwriter');
    $chapID $_GET['chapID'];
     
    $chapters mysql_query("SELECT ID, chap_title, chap_number FROM chapters WHERE ID='$chapID'");
    $chapter mysql_fetch_array($chapters);
    $chapID $chapter['ID'];
    $chap_title $chapter['chap_title'];
    $chap_number $chapter['chap_number'];
    ?>
    Please edit new chapter:
    <form name="editchapter" method="post" action="chapter_edit_qry.php">
    chapter number:<input type="text" name="chap_number" value="<?=$chap_number?>" size="3" maxlength="5"><br><br>
    Chapter title: <input type="text" name="chap_title" value="<?=$chap_title?>" size="50" maxlength="100">
    <input type="hidden" name="chapID" value="<?=$chapID?>"
    <p>
    <input type="submit" value="submit" name="edit_chapter">
    </form>
    <?php
    include("footer.php");
    ?>
    chapter_edit_qry.php:

    PHP Code:
    <?php
    include("header.php");
    ?>
    <?php
    $dbcnx 
    mysql_connect('localhost','username','password');
    $bookconnect mysql_select_db('bookwriter');
     
    if(isset(
    $_POST['edit_chapter'])) {
    $chap_title addslashes($_POST['chap_title']);
    $chapID $_POST['chapID'];
    $chap_number $_POST['chap_number'];
    $checknum "SELECT * FROM chapters WHERE chap_title = '$new_chapter' AND chap_number = '$chap_number'";
    $result mysql_query($checknum);
    if (
    mysql_num_rows($result)> ){
        echo(
    'chapter number taken');
    }
    else {
    $sql "UPDATE chapters SET
            chap_title = '
    $chap_title',
            chap_number = '
    $chap_number'
            WHERE ID='
    $chapID'";
    }
    if(
    mysql_query($sql)) {
    echo(
    'The chapter has been edited');
    }
    else {
    echo(
    'error editing chapter:' mysql_error());
    }
    }
    ?>
    <?php
    include("footer.php");
    ?>
    perhaps I'm checking it in the wrong place? Also, where can I add this is_numeric function in the code above?

  6. #6
    SitePoint Wizard rozner's Avatar
    Join Date
    Oct 2002
    Location
    Paris
    Posts
    1,058
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is with your query, I think you forgot to replace the variable $new_chapter with $chap_title

    PHP Code:
    $checknum "SELECT * FROM chapters WHERE chap_title = '$chap_title' AND chap_number = '$chap_number'"
    you may also want to try been's suggestion
    If you declare the descriptor_id numeric and unique, you cannot put in non-numeric values or duplicates.
    So if an insert query fails, you can act accordingly.
    for the is_numeric check, you'd probably want that in the chapter_edit_qry.php file around here:

    PHP Code:
    if (isset($_POST['edit_chapter'])) {
       if (!
    is_numeric($_POST['chap_number'])) {
          
    // some sort of error message
          // you may want to have an error variable to check against after
       
    } else {
          
    // add to table
     
       
    }
     


  7. #7
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's still not working:
    PHP Code:
    <?php
    include("header.php" );
    ?>
    <?php
    $dbcnx 
    mysql_connect('localhost','username','password');
    $bookconnect mysql_select_db('bookwriter');

    if(isset(
    $_POST['edit_chapter'])) {
    $chap_title addslashes($_POST['chap_title']);
    $chapID $_POST['chapID'];
    $chap_number $_POST['chap_number'];
    $checknum "SELECT * FROM chapters WHERE chap_title = '$chap_title' AND chap_number = '$chap_number'";
    $result mysql_query($checknum);
    if (
    mysql_num_rows($result)> ){
    echo(
    'chapter number taken');
    }
    else {
    $sql "UPDATE chapters SET
    chap_title = '
    $chap_title',
    chap_number = '
    $chap_number'
    WHERE ID='
    $chapID'";
    }
    if(
    mysql_query($sql)) {
    echo(
    'The chapter has been edited');
    }
    else {
    echo(
    'error editing chapter:' mysql_error());
    }
    }
    ?>
    <?php
    include("footer.php" );
    ?>
    how do I make the chap_number field unique?
    Last edited by jive; Jul 22, 2003 at 10:05.

  8. #8
    SitePoint Wizard rozner's Avatar
    Join Date
    Oct 2002
    Location
    Paris
    Posts
    1,058
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    still not working? do you have phpMyAdmin on your server, if so you it's pretty easy to alter the table to have a unique field. Otherwise you have to do it by command line, I've been using phpMyAdmin for too long so I'm not sure off the top of my head how to do that.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok so the column is unique now, but the code is not checking for an already existing chapter and displaying the "chapter number taken" echo when I add a chapter with the same number. Mysql is however not allowing me to add a duplicate number. What am I doing wrong in the code above?

  10. #10
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the column in your database is unique, you don't have to perform the first SELECT query in your code, that's were it's going wrong:
    If you try to insert a chap_number that already exists, your code performs the part of the following if() statement (and NOT it's else{} part where the UPDATE query is situated...)

    You can just perform the UPDATE query without the whole code for the SELECT part:
    PHP Code:
    if (isset(...)) {
        
    // addslashes etc...
        
    $result mysql_query(...);
        if (!
    $result) {
            
    // ERROR ERROR ERROR number taken...
            //....
        
    }
        
    //... 
    something like that
    Per
    Everything
    works on a PowerPoint slide

  11. #11
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ?? I don't understand why I wouldn't use my query??

  12. #12
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only reason that you're doing the SELECT query in your code, is to check if a chap_id already exists, right?
    You do this because you don't want any duplicate chap_id's in your table, right?
    Please note, I'm not trying to be arrogant, I'm just confirming with you.

    By defining the chap_id unique in your database, the database system won't allow a client (your code) to insert records with a chap_id that already exists, hence you can just do the update query, if it fails, the chap_id already existed. Of course there could be other problems; the database could be temporarily offline for example, but that's not the point; fact is that the SELECT query to check for unique id now becomes obsolete, and if it's obsolete, why still use it?
    Per
    Everything
    works on a PowerPoint slide

  13. #13
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the reason I want to check for a unique id is to display the error message I wan't and not the regular my sql message that comes if the user enters a already existing Id. All I want is for the error message to display "chapter number already taken" if the person enters and already existing chapter number.

  14. #14
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In reply to pm,
    I really haven't had time to look at the problem but from a quick reading, this is somewhat like I think it should be...
    PHP Code:
    <?php
    include("header.php" );
    ?>
    <?php
    $dbcnx 
    mysql_connect('localhost','username','password');
    $bookconnect mysql_select_db('bookwriter');

    if(isset(
    $_POST['edit_chapter'])) {
    $chap_title addslashes($_POST['chap_title']);
    $chapID = (int) $_POST['chapID'];
    $chap_number = (int) $_POST['chap_number'];
    $checknum "SELECT COUNT(*) FROM chapters WHERE  chap_number = '$chap_number'";
    $result mysql_query($checknum);
    $num mysql_fetch_row($result);
    if (
    $num[0] > 0){
    echo(
    'chapter number taken');
    }
    else {
    $sql "UPDATE chapters SET
    chap_title = '
    $chap_title',
    chap_number = '
    $chap_number'
    WHERE ID='
    $chapID'";
    if(
    mysql_query($sql)) {
    echo(
    'The chapter has been edited');
    }
    else {
    echo(
    'error editing chapter:' mysql_error());
    }
    }

    }
    ?>
    <?php
    include("footer.php" );
    ?>
    Hope this helps...
    - website

  15. #15
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Greenville, SC
    Posts
    388
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •