Page 1 of 2
SQL Error Message in Script
Posted: Tue Apr 06, 2004 3:17 pm
by Johnnymo
Hi guys, just found the site and hope someone can shed a little light for me. Not sure if anyone here can point me in the right direction, but I'm having problems with an index/link manager script I use on several of my sites. The script works well enough, but when you try to "Rate" or "Review" a site, it returns an error message that reads the following:
Could not execute query : SELECT * from links WHERE id=.You have an error in your SQL syntax near '' at line 1
I have emailed the company where I purchased the script (SnapDex.com) on three occassions and have yet to receive a response. I know very little SQL or PHP (more a front end guy) so I'm at a bit of a loss. Anyone have an idea of where or how I can start?
To check out the error for yourself, you can visit here:
http://www.jonko.com/auto_directory/index.php?go=new
And click on the rate or review tag for any link.
Cheers --
John
Posted: Tue Apr 06, 2004 7:08 pm
by kettle_drum
What is on line 1 of the script? Have you set up the database correctly? Does the script use global variables? Does your server?
Just need a few more details from you to help.
Posted: Tue Apr 06, 2004 7:11 pm
by markl999
Think you're right in saying it's a globals issue.
I'm guessing the query is SELECT * from links WHERE id=$id when it needs to be SELECT * from links WHERE id={$_GET['id']}
My money's on that anyway

Posted: Tue Apr 06, 2004 7:14 pm
by kettle_drum
Yep, that was my thought too

Posted: Wed Apr 07, 2004 2:27 am
by Johnnymo
Thanks so far for the help guys, here's what is in the two files rate.php and ratelink.php:
RATE.PHP
Code: Select all
require_once ('config.inc.php');
require_once ('dbconn.php');
$q= "SELECT * FROM links WHERE id='$idrate'" ;
$result= mysql_query($q);
if (!$result) { die ("Could not execute query : $q." . mysql_error()); }
while ($row = mysql_fetch_array($result))
{
$id=$rowї"id"];
$Filename=$rowї"name"];
$Num_Votes=$rowї"Num_Votes"];
$Votes =$rowї"Votes"];
$Rating=$rowї"Rating"];
$new_Votes=$Num_Votes+1;
$Votes=$Votes+$Rate;
$Rating=round(($Votes/$new_Votes),2);
}
$q="UPDATE links SET ";
$q = $q . " Num_Votes='$new_Votes', Votes='$Votes', Rating='$Rating' ";
$q = $q . " WHERE id='$idrate' ";
$result= mysql_query($q) or die
("Could not execute query : $q." . mysql_error());
if ($result) {
echo "Thank you. The website has rating of $Rating after your vote. &nbsp; <a href=index.php>Go back</a>";
}
RATELINK.PHP
Code: Select all
require_once ('config.inc.php');
require_once ('dbconn.php');
?>
<html>
<head></head>
<body>
<?php
$q="SELECT * from links WHERE id=$idrate";
$result= mysql_query($q) or die
("Could not execute query : $q." . mysql_error());
if ($row=mysql_fetch_array($result))
{
$id=$row["id"];
$FileName=$row["name"];
} else {
echo "cant't find this link<br>";
}
?>
Rating <?php echo "$FileName"; ?>: <form action="<?php echo "index.php?go=rate&id=$idrate"; ?>" method="post">
<input type=hidden name=idrate value=<?=$idrate?>>
<select name="Rate">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
</select>
<input type="submit" value="Rate">
</form>
</body>
</html>
I tried to change the line:
$q= "SELECT * FROM links WHERE id='$idrate'" ;
in both to:
$q= "SELECT * FROM links WHERE id={$_GET['idrate']};
but when I do so I get the following error when trying to rate:
Parse error: parse error in /home/www/jonko/auto_directory/ratelink.php on line 16
Sorry for being ignorant on all of this, I'm just a bit green on PHP/SQL... thanks again for the assistance...
JM
Posted: Wed Apr 07, 2004 2:43 am
by patrikG
Added
Code: Select all
text for readability. Good thing is: now that you know that you can do it yourself next time
Posted: Wed Apr 07, 2004 2:49 am
by kettle_drum
Your form sends via POST and so the variables need to be $_POST[name] etc, then everything should work.
Posted: Wed Apr 07, 2004 6:42 pm
by Johnnymo
Thank for the help so far guys...
Kettle, not sure I'm doing what you suggested correctly....
I changed all of the text like this:
$id=$row["id"];
to:
$id=$_POST["id"];
in both scripts, but that had no effect. I tried it with both the original Select statement, and the Select suggested by Mark that I mentioned earlier. As I said, I'm totally green at PHP so my apologies for being a bit remedial. Anyway, feel free to let me know what I'm missing if it is abundantly obvious... thanks again,
JM
Posted: Wed Apr 07, 2004 7:14 pm
by tim
no no, the $row variable are actually arrays from the sql command.
notice:
while ($row = mysql_fetch_array($result))
mysql_fetch_array
what he was saying u need to change
$q= "SELECT * FROM links WHERE id={$_GET['idrate']};
to:
$q= "SELECT * FROM links WHERE id={$_POST['idrate']};
Posted: Wed Apr 07, 2004 9:24 pm
by Johnnymo
Tim -- thanks.... switched that out but now all I get is the previously mentioned "parse error"
Parse error: parse error in /home/www/jonko/auto_directory/ratelink.php on line 16
That's this line:
$q=SELECT * from links WHERE id={$_POST['idrate']};
I've tried playing around with quotes putting them around the whole select statement and ending before the semi... then without any... then after the =and before semi....
Not too sure.... have to love when I have no real understanding of something and I'm just guessing at it.... wish the vendor offered any response or support as I'm sure I'm not the only person who has experienced this issue...
Anyway, thanks again and I'll keep plugging.... let me know if you have ideas....
JM
Posted: Wed Apr 07, 2004 9:27 pm
by tim
$q= "SELECT * FROM links WHERE id={$_POST['idrate']}";
that should work
Posted: Wed Apr 07, 2004 9:44 pm
by Johnnymo
Tim.... no love there either.... back to the original SQL error:
Could not execute query : SELECT * FROM links WHERE id=.You have an error in your SQL syntax near '' at line 1
sorry to be a pain... if I had half a clue what it was I'd make the changes... any additional ideas would be great....
Any reason why this error always reads "Line 1" while the parse error spits out line 14 or 16?
Thanks again...
Posted: Thu Apr 08, 2004 9:15 am
by twigletmac
Line 1 is the line number of where the error occurs in the SQL statement, not in the PHP script.
Basically that idrate value is not getting passed across - I don't see one in the URL and the page is not getting sent via POST so you won't find it in the $_POST array. However, there is an id value in the URL - perhaps this is what you should be using, so try changing:
Code: Select all
$q= "SELECT * FROM links WHERE id={$_POST['idrate']}";
to
Code: Select all
$q = "SELECT * FROM links WHERE id={$_GET['id']}";
as you only need the name and id values from the query it would be good practise to specify these instead of using *, so the SQL statement can be further refined to:
Code: Select all
$q = "SELECT id, name FROM links WHERE id={$_GET['id']}";
Mac
Posted: Thu Apr 08, 2004 4:34 pm
by tim
twig, mark already suggested such a method ($_GET) - as well as he said he tried it in a variety of different ways with no luck.
so - my guess is the variable isnt being sent correctly, try echoing the var. If it dont appear, the variable lost is elsewhere in your script n u should focus attention on that.
the * replacing is also a good idea
Posted: Thu Apr 08, 2004 7:06 pm
by Johnnymo
tim wrote:so - my guess is the variable isnt being sent correctly, try echoing the var. If it dont appear, the variable lost is elsewhere in your script n u should focus attention on that.
the * replacing is also a good idea
I went ahead and tried the few things twig mentioned, to no avail... either get teh same SQL error, or the parse error if I axe the quotation marks....
As to "Echoing the var" I'm not really sure what that entails or how to do so.... if you could tell me or point me to an example somewhere I'll try that and see if I can fix it that way....
Thanks again for all the assistance...
JM