SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to check if multiple values exists

    Situation. I record invoice: amount, partner name, invoice date and invoice number.
    Need to check if the invoice is not already recorded. So need to check if all the mentioned values are not already recorded (one value is not enough).
    At the moment I have solution that at first I check if each of the values exists. Then next code if all the values exists, then warn about duplicate entry.
    But there one problem, code check through all rows in certain table. So may be situation that all values are entered, but in multiple rows.

    The question is: how to check if multiple values are not already entered in one row

    Code:
    $query1 = mysql_query("SELECT * FROM $tbl_name WHERE tutorial_author='$tutorial_author'"); //tutorial_title without $ is name of record (column in MySql table)
    if(mysql_num_rows($query1) != 0)
    {
    echo $tutorial_author ." already exists <br>";
    //echo $query1 ." query1 <br>";
    }
    else
    {
    echo $tutorial_author ." does not exist <br>";
    .........
    
    if((mysql_num_rows($query) != 0) or (mysql_num_rows($query1) != 0) or (mysql_num_rows($query2) != 0)) {
    
    echo $submission_date ." or' .$tutorial_title .'or' .$tutorial_author .'already exists <br>";
    }

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rigaconnect View Post
    The question is: how to check if multiple values are not already entered in one row
    could you illustrate this with an example, please

    right now "multiple values in one row" doesn't make sense to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For example, in database are columns

    Date | Amount | Company | Invoice number
    first row
    05/01/2013 | 100 | 1stCompany | 11
    second row
    04/01/2013 | 200 | 2ndCompany | 12
    third row
    03/01/2013 | 300 | 3rdCompany | 13

    I check if values for Date Amount Company and Invoice number exists
    With my script if I enter
    Date: 05/01/2013
    Amount: 200
    Company: 3rdCompany
    Invoice: 11

    I get output that these values are already entered (as if duplicate invoice for the same transaction).
    But I need warning only if I enter, for example,
    05/01/2013 | 100 | 1stCompany | 11
    or
    04/01/2013 | 200 | 2ndCompany | 12
    or
    03/01/2013 | 300 | 3rdCompany | 13

    so all values already exist in any of the rows....

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you totally lost me, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excuse me (my text seems not understandable; English not my native language; and I almost know nothing in MySQL; I try to learn it)

    For example, two rows exist in a database
    The first row with values: First value | Second value
    The second row with values: Third value | Fourth value

    In a html form (with 2 fields) visitor can type values to add to the database (table). If visitor types in the form values: First value and Second value, and try to add to the database I need to prevent it, because in the database there is already a row with values: First value | Second value.

    But if visitor types First value and Fourth value I need to allow it to add to the database, because in the database there is no row with values First value | Fourth value. So as a result visitor adds to the database new row with values First value | Fourth value. And after that if a visitor types values First value and Fourth value then I need to prevent it, because there is a row with values First value | Fourth value

    With above showed code I can only check if in whole table exists any of values

    Sorry for waste of your time if does not understandable....
    May be latter I will find solution myself

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rigaconnect View Post
    For example, two rows exist in a database
    The first row with values: First value | Second value
    The second row with values: Third value | Fourth value
    this is completely different from what you said earlier is in your table --
    Date | Amount | Company | Invoice number
    first row
    05/01/2013 | 100 | 1stCompany | 11
    second row
    04/01/2013 | 200 | 2ndCompany | 12
    can you understand why i'm confused? there's only one value on each row, not two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some misunderstanding... may be because my English.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm pretty sure the misunderstanding is because of your hasty examples
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    As you already noted yourself, the check on each separate value makes no sense, because there will surely be more invoices on the same day, or more invoices for the same amount, or more invoices for the same company.

    If you want any combination of the four values to exist only once in your table, then create a unique key on Date + Amount + Company + Invoice number.
    But I don't understand why you mention the invoice number. Isn't that generated by the application? And shouldn't it always be unique?

  10. #10
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am new to programming. Could you advice how to make the key (any link with information)?

    Regarding invoice number I mean if I receive bill (invoice) from other company. In such case I must record it manually.

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I see, so the invoice number is their invoice number, not yours. Ok, that's part of the key

    You can find all your answers in this thread: http://www.sitepoint.com/forums/show...out-unique-key

    If something isn't clear after you read that, don't hesitate to ask.

  12. #12
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, 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
  •