SQL Error Message in Script

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

SQL Error Message in Script

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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 ;)
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Yep, that was my thought too :)
Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

Post 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.   <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
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Added

Code: Select all

text for readability. Good thing is: now that you know that you can do it yourself next time
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Your form sends via POST and so the variables need to be $_POST[name] etc, then everything should work.
Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

Post 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
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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']};
Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

Post 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
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

$q= "SELECT * FROM links WHERE id={$_POST['idrate']}";


that should work
Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

Post 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...
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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
Johnnymo
Forum Newbie
Posts: 8
Joined: Tue Apr 06, 2004 3:17 pm
Location: Las Vegas

Post 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
Post Reply