Problem - detecting if value is already present in MySQL DB

[JimmyP]

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!

    $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!

[AnthonySterling]

Try this:-

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

SilverB.

[JimmyP]

Thanks for helping.... I'm testing it on localhost so that I can see the errors and this is what it shows:

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.

[Mark Baker]

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

[AnthonySterling]

What about with this 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 ) > 0 ) ? 'Record exists.' : 'No record currently exists.' ;
?>

SilverB.

[felgall]

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.

[JimmyP]

What about with this 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 ) > 0 ) ? '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

[JimmyP]

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...

[AnthonySterling]

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.

[JimmyP]

I am replacing $rConnection with CONNECTION which I have defined at the top of the file here:

define('CONNECTION',mysql_connect("localhost",$username,$password));

[AnthonySterling]

Can you define a Object as a constant?!? That's news to me, does this work in previous code?

*intrigued*

SilverB

[r937]

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

[JimmyP]

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.

[JimmyP]

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!

[JimmyP]

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).

[r937]

in that case, you want the UNIQUE constraint on (url,pointer)

[JimmyP]

Rudy, what's the exact syntax for that?

ALTER TABLE urls
ADD CONSTRAINT pointer UNIQUE ( url,pointer )

^ is this right?

[r937]

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

[JimmyP]

Eh oh..

Getting an error:

BLOB/TEXT column 'url' used in key specification without a key length

[r937]

whoa... you expect urls that are 4 megs long?????

try VARCHAR(255) -- that oughta cover most urls, yes?

[JimmyP]

You never know...

http://www.google.com/search?hl=en&lr=&c2coff=1&rls=GGLG%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!

[r937]

that's an excellent idea with your specially-tailored shorturl

keep up the good work

[longneck]

you might want to consider md5() or sha1() instead.

[Young Twig]

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.

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 ]]]]] )

[Jake Arkinstall]

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.