SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 25
-
Oct 11, 2008, 13:48 #1
- 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)
-
Oct 11, 2008, 14:00 #2
- 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 ) > 0 ) ? 'Record exists.' : 'No record currently exists.' ;
?>
-
Oct 11, 2008, 14:25 #3
- 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)
-
Oct 11, 2008, 14:29 #4
- 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. :(';
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
-
Oct 11, 2008, 14:30 #5
- 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 ) > 0 ) ? 'Record exists.' : 'No record currently exists.' ;
?>
-
Oct 11, 2008, 14:32 #6
- Join Date
- Sep 2005
- Location
- Sydney, NSW, Australia
- Posts
- 16,875
- 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="^$">
-
Oct 11, 2008, 14:43 #7
- Join Date
- Aug 2007
- Location
- Brighton, UK
- Posts
- 2,006
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
★ James Padolsey
–––––––––––––––––––––––––––––––––––––––
Awesome JavaScript Zoomer (demo here)
'Ajaxy' - Ajax integration solution (demo here)
-
Oct 11, 2008, 14:45 #8
- Join Date
- Aug 2007
- Location
- Brighton, UK
- Posts
- 2,006
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
★ James Padolsey
–––––––––––––––––––––––––––––––––––––––
Awesome JavaScript Zoomer (demo here)
'Ajaxy' - Ajax integration solution (demo here)
-
Oct 11, 2008, 14:46 #9
- 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.
-
Oct 11, 2008, 14:48 #10
- 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)
-
Oct 11, 2008, 14:52 #11
- 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
-
Oct 11, 2008, 15:00 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Oct 11, 2008, 15:00 #13
- Join Date
- Aug 2007
- Location
- Brighton, UK
- Posts
- 2,006
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
-
Oct 11, 2008, 15:01 #14
- Join Date
- Aug 2007
- Location
- Brighton, UK
- Posts
- 2,006
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
★ James Padolsey
–––––––––––––––––––––––––––––––––––––––
Awesome JavaScript Zoomer (demo here)
'Ajaxy' - Ajax integration solution (demo here)
-
Oct 11, 2008, 15:17 #15
- 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)
-
Oct 11, 2008, 15:57 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
in that case, you want the UNIQUE constraint on (url,pointer)
-
Oct 11, 2008, 16:04 #17
- 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 )
★ James Padolsey
–––––––––––––––––––––––––––––––––––––––
Awesome JavaScript Zoomer (demo here)
'Ajaxy' - Ajax integration solution (demo here)
-
Oct 11, 2008, 16:08 #18
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Oct 11, 2008, 16:23 #19
- 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)
-
Oct 11, 2008, 17:06 #20
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
whoa... you expect urls that are 4 megs long?????
try VARCHAR(255) -- that oughta cover most urls, yes?
-
Oct 11, 2008, 18:14 #21
- 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%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
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)
-
Oct 11, 2008, 18:24 #22
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
that's an excellent idea with your specially-tailored shorturl
keep up the good work
-
Oct 11, 2008, 21:09 #23
- 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
-
Oct 12, 2008, 10:04 #24
-
Oct 12, 2008, 11:59 #25
- 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