Query problem with URLs

[jrcarr]

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:
[code]http://www.carrscorner.com
http://www.carrscorner.com/index.php?option=whatever[/code]

and I do a query for one like:

[code]$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;[/code]

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

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

[code]$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;[/code]

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

[fenway]

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?

[jrcarr]

[!--quoteo(post=338293:date=Jan 20 2006, 07:24 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 20 2006, 07:24 AM) [snapback]338293[/snapback][/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

[fenway]

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.

[jrcarr]

[!--quoteo(post=338483:date=Jan 20 2006, 07:47 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 20 2006, 07:47 PM) [snapback]338483[/snapback][/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

[jrcarr]

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.

[code]

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;
}
[/code]

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

Jack

[jrcarr]

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:

[code]<?
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;

?>[/code]

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]
[code]http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks&topid=0[/code]

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]
[code]http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com[/code]
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]
[code]http://www.carrscorner.com/link/test.php?new=http://www.carrscorner.net/websites/home.php?site=1[/code]
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;
[code]$new="http://www.carrscorner.com/link/test.php?new=http://www.marinecanvas.com/index.php?option=weblinks&topid=0"[/code]
the script works just fine.
Any suggestions now and/or should this topic now be in a new section?

[ryanlwh]

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]

[jrcarr]

[!--quoteo(post=339326:date=Jan 24 2006, 07:17 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Jan 24 2006, 07:17 AM) [snapback]339326[/snapback][/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

[fenway]

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.