Jump to content


Photo

Query problem with URLs


  • Please log in to reply
9 replies to this topic

#1 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 20 January 2006 - 04:00 AM

I tried searching for an answer to this question, but I guess my search queries are not any better than mysql queries are working.

I have a pretty simple table with 8 fields, one of them being a VARCHAR the holds URLs. If the urls are simple like:
http://www.carrscorner.com
http://www.carrscorner.com/index.php?option=whatever

and I do a query for one like:

$aedit = "http://www.carrscorner.com/index.php?option=whatever";

$sql = mysql_query("SELECT * FROM site_links WHERE url='$aedit'");
$link_check = mysql_num_rows($sql);

if($link_check > 0){
    while($row = mysql_fetch_array($sql)){
    foreach( $row AS $key => $val ){
        $$key = stripslashes( $val );
    }}}
echo $url;

All is fine. However, if the url is more complicated, like the following:
http://www.carrscorner.com/index.php?option=whatever&test=something

and use the same code only change the contents of the variable $aedit, it will not work.

$aedit = "http://www.carrscorner.com/index.php?option=whatever&test=something";

$sql = mysql_query("SELECT * FROM site_links WHERE url='$aedit'");
$link_check = mysql_num_rows($sql);

if($link_check > 0){
    while($row = mysql_fetch_array($sql)){
    foreach( $row AS $key => $val ){
        $$key = stripslashes( $val );
    }}}
echo $url;

The query only seems to see upto the "&" sign, it and everything after it are missing, so the query finds nothing. I'm sure there must be a way to save the url into the table, converting the & and then do a query that will find it. Any suggestion will be greatly appreciated.

Jack
Jack Carr

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 January 2006 - 06:24 AM

That seems quite strange indeed -- why do you say that it ignores part of the string? Have you echoed the query after the variable is substituted?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 20 January 2006 - 07:16 AM

[!--quoteo(post=338293:date=Jan 20 2006, 07:24 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 20 2006, 07:24 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
That seems quite strange indeed -- why do you say that it ignores part of the string? Have you echoed the query after the variable is substituted?
[/quote]
Process of elimination! I entered a variety of urls and the only ones that I could not query were the ones with an & sign in it. I also took a couple with the & sign and trimmed them down below the symbol and then they would work.

Jack
Jack Carr

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 January 2006 - 06:47 PM

I'm not sure I understand -- just because it doesn't find the rows, doesn't mean the query didn't work. Once you have a string quoted in MySQL, the parser "doesn't know" about what's inside the string. So if the DB says "no rows returned", it's probably right. There is not thing magical about the "&" character within a quoted string.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 22 January 2006 - 04:43 AM

[!--quoteo(post=338483:date=Jan 20 2006, 07:47 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 20 2006, 07:47 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I'm not sure I understand -- just because it doesn't find the rows, doesn't mean the query didn't work. Once you have a string quoted in MySQL, the parser "doesn't know" about what's inside the string. So if the DB says "no rows returned", it's probably right. There is not thing magical about the "&" character within a quoted string.
[/quote]
I had the same mis-understanding too! However, if you create a small table and insert 3-4 rows with data, one of them being a URL with an & in it, and use my code above, it will return no results. At least that is what I found and unless I'm doing something wrong.... but I did try several different URLs all with the same result.

Jack

Jack Carr

#6 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 23 January 2006 - 12:37 AM

Thought I would try clarifying my problem with a little additional code. Maybe something in my code is causing the variable not to be quoted text, so the query is not reading correctly. There are additional fields/columns in the table that are actually out put, so this is just a shortened version of my script, but gives you the parts that would effect the query. My understanding of MySQL, there are things that need to be stored in just a special way to be readable in a query, adding slashes to quotes and such, but since there are no quotes in a URL, this should be a factor. However, there might be something else in MySQL I'm not aware of that is casing this problem.


function Show_Form(){

<form method="post">
<b>Enter the URL to your site</b><br>
<input type="text" size="50" name="url" value="'.$_POST['url'].'"> <font color=red size="1">Required for adding, editing or deleting a link</font>
<font face="Tahoma" size="2" color="blue">
<br>Example: www.your-site.com or http://www.your-site.com</font><br><br>
<input type="submit" name="submit" value="Submit">
</form>
}

if (!isset($_POST['submit'])) {

Show_Form();

}else{
 if (!stristr($_POST['url'], '://'))
     $_POST['url'] = 'http://' . $_POST['url'];

 $sql_link_check = mysql_query("SELECT url FROM site_links WHERE url='$_POST[url]'"); 
 $link_check = mysql_num_rows($sql_link_check); 

if($link_check > 0){
    while($row = mysql_fetch_array($sql)){
    foreach( $row AS $key => $val ){
        $$key = stripslashes( $val );
    }}}
echo $url;
}

Now, am I still nuts or is there some factor here that I'm over looking. Thanks

Jack
Jack Carr

#7 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 24 January 2006 - 01:17 AM

Ok, I'm starting to narrow this down some, but still haven't solved what is now in a new issue.

I have a small script setup at:
[a href=\"http://www.carrscorne.com/link/test.php\" target=\"_blank\"]http://www.carrscorne.com/link/test.php[/a] as seen below:

<?
echo 'New variable: '.$new.' <br>';
$sql = mysql_query("SELECT * FROM site_links WHERE url='$new'");
$link_check = mysql_num_rows($sql);

if($link_check > 0){
    while($row = mysql_fetch_array($sql)){
    foreach( $row AS $key => $val ){
        $$key = stripslashes( $val );
    }}}
echo 'URL variable from a table query: '.$url;

?>

Now, if I go to this link with the following URL:
[a href=\"http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks&topid=0\" target=\"_blank\"]http://www.carrscorner.com/link/test.php?n...eblinks&topid=0[/a]
http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks&topid=0

I figured it should echo everything after the ?new= , but as you can see from the output at that link it only outputs everthing up to the "&" so the query to the table does not bring up the row that would match the the query. So why doesn't this work and why is it dropping everything from the & symbol and after.?
I have the another URL in the table that matches the following link, without any & symbol and it works fine.

[a href=\"http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com\" target=\"_blank\"]http://www.carrscorner.com/link/test.php?n...arinecanvas.com[/a]
http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com
One more scenerio:
Here is a link that includes a ?, but no & symbol that matches another row in the table:
[a href=\"http://www.carrscorner.com/link/test.php?new=http://www.carrscorner.net/websites/home.php?site=1\" target=\"_blank\"]http://www.carrscorner.com/link/test.php?n...home.php?site=1[/a]
http://www.carrscorner.com/link/test.php?new=http://www.carrscorner.net/websites/home.php?site=1
This is why this is leading me to think it has to do with the & symbol, though now it doesn't seem to have to do with the Query itself. If I assign the same variable name inside the script like below;
$new="http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks&topid=0"
the script works just fine.
Any suggestions now and/or should this topic now be in a new section?

Jack Carr

#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 24 January 2006 - 06:17 AM

it's not picking up & because it's interpreted as part of the first query, not the second one. When you pass a url to a query string, you have to rawurlencode() it first. try this link:
[a href=\"http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks%26topid=0\" target=\"_blank\"]http://www.carrscorner.com/link/test.php?n...links%26topid=0[/a]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#9 jrcarr

jrcarr
  • Members
  • PipPip
  • Member
  • 20 posts
  • LocationBeautiful Arkansas Ozarks

Posted 24 January 2006 - 02:43 PM

[!--quoteo(post=339326:date=Jan 24 2006, 07:17 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Jan 24 2006, 07:17 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
it's not picking up & because it's interpreted as part of the first query, not the second one. When you pass a url to a query string, you have to rawurlencode() it first. try this link:
[a href=\"http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks%26topid=0\" target=\"_blank\"]http://www.carrscorner.com/link/test.php?n...links%26topid=0[/a]
[/quote]
Thank you very much, that looks like the solution. You are a scholar and a gentleman.

Jack
Jack Carr

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 January 2006 - 07:25 PM

You didn't say anything about it coming from a POST variable directly, so there was no way to "guess" that URL encoded was involved. You should be more explicit next time -- your original post simply was passing a string variable, which is VERY different. Glad you got it working, though.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users