SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem - detecting if value is already present in MySQL DB

    Hello,

    I have a MySQL database and a table ('urls') with several records in.

    Before I submit a new one I need to check if a record with the submitted data already exists - in which case there is no point in creating a new one because it already exists!

    Code PHP:
        $query = "SELECT * FROM urls WHERE url = '{$url}'";
        $result = mysql_query($query,CONNECTION);
        if($result) echo 'Already exists!!! :)';
        else echo 'Sorry, it doesn\'t exist yet so you\'ll have to create a new record. :(';

    When I enter the query (SELECT * FROM urls WHERE url = 'example value') directly in the mySQL it returns several rows of data which is what it should do.

    The problem is that the above function does not work! The if($result) condition is not being met for some reason - I cannot understand this because the query should be returning several rows of data!
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Try this:-

    PHP Code:
    <?php
    $sSQL 
    "SELECT url FROM urls WHERE url = '{$url}'";
    $oResult mysql_query$sSQL $rConnection );
    echo ( 
    mysql_num_rows$oResult ) > ) ? 'Record exists.' 'No record currently exists.' ;
    ?>
    SilverB.

  3. #3
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for helping.... I'm testing it on localhost so that I can see the errors and this is what it shows:

    Code:
    Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in H:\wamp\www\test.php on line 30
    No record currently exists.
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code PHP:
    if($result) echo 'Already exists!!! :)';
    else echo 'Sorry, it doesn\'t exist yet so you\'ll have to create a new record. :(';
    No, $result means the query executed successfully, not whether it returned data rows or not. If $result is false, it means that there was a problem with the db connection, or the SQL query was malformed, or some similar error.

    Once you've determined that $result is true, you then need to count how many records were returned in $result. If it's 0, then there was no matching record on the database

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What about with this code?

    PHP Code:
    <?php
    $sSQL 
    "SELECT url FROM urls WHERE url = '{$url}'";
    $oResult mysql_query$sSQL $rConnection ) or die(mysql_error('Query Error: ' mysql_error($rConnection)));
    echo ( 
    mysql_num_rows$oResult ) > ) ? 'Record exists.' 'No record currently exists.' ;
    ?>
    SilverB.

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,817
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A more efficient way would be to just try to insert it. If it already exists then it will return an error. That cuts down on the number of database calls and still has the same overall end result.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    What about with this code?

    PHP Code:
    <?php
    $sSQL 
    "SELECT url FROM urls WHERE url = '{$url}'";
    $oResult mysql_query$sSQL $rConnection ) or die(mysql_error('Query Error: ' mysql_error($rConnection)));
    echo ( 
    mysql_num_rows$oResult ) > ) ? 'Record exists.' 'No record currently exists.' ;
    ?>
    SilverB.
    eh oh

    :

    Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in H:\wamp\www\test.php on line 29
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  8. #8
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    A more efficient way would be to just try to insert it. If it already exists then it will return an error. That cuts down on the number of database calls and still has the same overall end result.
    But it will get inserted even if it's unique because other fields (such as the id with auto-increments) are not unique... In the MySQL board Rudy said something about adding a unique constraint but I'm not too sure about it...
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It appears you are either not passing your MySQL connection to the functions properly, or you haven't successfully connected to the database.

    Are you replacing $rConnection with your MySQL link?

    SilverB.

  10. #10
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am replacing $rConnection with CONNECTION which I have defined at the top of the file here:

    Code PHP:
    define('CONNECTION',mysql_connect("localhost",$username,$password));
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  11. #11
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Can you define a Object as a constant?!? That's news to me, does this work in previous code?

    *intrigued*

    SilverB

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JimmyP View Post
    In the MySQL board Rudy said something about adding a unique constraint but I'm not too sure about it...
    why aren't you sure about it?

    you really oughta try it

    it does actually work

    tip: try it outside of php first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    Can you define a Object as a constant?!? That's news to me, does this work in previous code?
    Yep it works perfectly. Well, it's worked before...

    I've just tested assigning the connection object to a local variable instead - the same error occurs.

    The actual connection object does work - I have other queries working correctly with it. ... It's very odd that it doesn't work - especially seeing as the query itself functions correctly when directly entered into a mysql console.
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  14. #14
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why aren't you sure about it?

    you really oughta try it

    it does actually work

    tip: try it outside of php first
    Ok ... I'm gonna try it out now!
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  15. #15
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, I've done that for the "pointer" field we talked about in the other thread and it seems to work perfectly but the unique constraint method will not work for this "url" field (discussing in this thread) since it is not necessarily unique.

    The user can specify a different pointer per URL. But if the user does not specify a "pointer" then it defaults to a standard one.

    User 1's url is "http://www.google.com" and his selected pointer is "hello". > A new record is created.

    User 2's url is also "http://www.google.com" but he has not specifed a pointer so it defaults to "defaultPointer" > A new record is created.

    User 3's url is also "http://www.google.com" and just like user 2 he does not choose a pointer, so it defaults to "defaultPointer" > A record should NOT be created in this situation (already exists).
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in that case, you want the UNIQUE constraint on (url,pointer)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, what's the exact syntax for that?

    Code:
    ALTER TABLE urls
    ADD CONSTRAINT pointer UNIQUE ( url,pointer )
    ^ is this right?
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the constraint name is optional, but if you're going to use it, make it a meaningful name

    it's the two columns inside the parentheses that defines the constraint, so you got that part right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Eh oh..

    Getting an error:

    BLOB/TEXT column 'url' used in key specification without a key length
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    whoa... you expect urls that are 4 megs long?????

    try VARCHAR(255) -- that oughta cover most urls, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    Function Curry'er JimmyP's Avatar
    Join Date
    Aug 2007
    Location
    Brighton, UK
    Posts
    2,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You never know...

    Code:
    http://www.google.com/search?hl=en&lr=&c2coff=1&rls=GGLG&#37;2CGGLG%3A2005-26%2CGGLG%3Aen&q=http%3A%2F%2Fwww.google.com%2Fsearch%3Fhl%3Den%26lr%3D%26c2coff%3D1%26rls%3DGGLG%252CGGLG%253A2005-26%252CGGLG%253Aen%26q%3Dhttp%253A%252F%252Fwww.google.com%252Fsearch%253Fhl%253Den%2526lr%253D%2526c2coff%253D1%2526rls%253DGGLG%25252CGGLG%25253A2005-26%25252CGGLG%25253Aen%2526q%253Dhttp%25253A%25252F%25252Fwww.google.com%25252Fsearch%25253Fsourceid%25253Dnavclient%252526ie%25253DUTF-8%252526rls%25253DGGLG%25252CGGLG%25253A2005-26%25252CGGLG%25253Aen%252526q%25253Dhttp%2525253A%2525252F%2525252Fwww%2525252Egoogle%2525252Ecom%2525252Fsearch%2525253Fsourceid%2525253Dnavclient%25252526ie%2525253DUTF%2525252D8%25252526rls%2525253DGGLG%2525252CGGLG%2525253A2005%2525252D26%2525252CGGLG%2525253Aen%25252526q%2525253Dhttp%252525253A%252525252F%252525252Fuk2%252525252Emultimap%252525252Ecom%252525252Fmap%252525252Fbrowse%252525252Ecgi%252525253Fclient%252525253Dpublic%2525252526GridE%252525253D%252525252D0%252525252E12640%2525252526GridN%252525253D51%252525252E50860%2525252526lon%252525253D%252525252D0%252525252E12640%2525252526lat%252525253D51%252525252E50860%2525252526search%252525255Fresult%252525253DLondon%25252525252CGreater%252525252520London%2525252526db%252525253Dfreegaz%2525252526cidr%252525255Fclient%252525253Dnone%2525252526lang%252525253D%2525252526place%252525253DLondon%252525252CGreater%252525252BLondon%2525252526pc%252525253D%2525252526advanced%252525253D%2525252526client%252525253Dpublic%2525252526addr2%252525253D%2525252526quicksearch%252525253DLondon%2525252526addr3%252525253D%2525252526scale%252525253D100000%2525252526addr1%252525253D%2526btnG%253DSearch%26btnG%3DSearch&btnG=Search
    Just kidding! ... but I think it's best to have support for longer ones.

    What I've ended up doing is creating a shorturl field which is a VARCHAR : - shorturl is a shortened version of the large URL - it takes 100 characters off the front and 100 off the back - I figure if two urls have the same first 100 characters and the same last 100 characters then there's a 99.9999% chance that they're exactly the same URL.

    This works out better actually because I only have to reference the full url at one time throughout the whole process - I heard VARCHAR is quicker to access/process than TEXT so it should speed things up nicely.

    Thanks for your help rudy!
    James Padolsey
    末末末末末末末末末末末末末末末末末末末
    Awesome JavaScript Zoomer (demo here)
    'Ajaxy' - Ajax integration solution (demo here)

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's an excellent idea with your specially-tailored shorturl

    keep up the good work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you might want to consider md5() or sha1() instead.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  24. #24
    SitePoint Wizard Young Twig's Avatar
    Join Date
    Dec 2003
    Location
    Albany, New York
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    Can you define a Object as a constant?!? That's news to me, does this work in previous code?

    *intrigued*

    SilverB
    I'm intrigued as well.
    Quote Originally Posted by php.net
    Only scalar data (boolean, integer, float and string) can be contained in constants. Do not define resource constants.
    ...
    resource mysql_connect ([ string $server [, string $username [, string $password [, bool $new_link [, int $client_flags ]]]]] )

  25. #25
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If it does work, it shouldn't.

    Besides, if you have it as a constant, then you're only working with one database, so you don't need to keep referencing which db to use every query.

    See where I'm going here?

    If you want to use multiple databases, build the app in full OOP, that way you can pass instances of database connections, allowing multiple databases with no fuss.

    But yeah, constants should ONLY be used for simple scalar types.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •