dongzhen6554
2013-02-16 16:41
浏览 87
已采纳

SQL select语句中的语法错误

I am developing an Android app. Here in my app an AsyncTask sends a string to PHP. The PHP returns with an array of crop names.

My php file is:

 <?php

ini_set('default_charset', 'utf-8');
header('Content-Type: text/html; charset=UTF-8');



mysql_connect("localhost","root","");
mysql_select_db("farm_o_pedia");

mysql_set_charset('utf8');
$result1 = mysql_query("SET NAMES utf8");

$lang=$_POST['LanguageName'];

$query1="select lang_id from lang_selection where lang_name='$lang'";
$lang_id=mysql_query($query1) or die(mysql_error());


$query2="select crop_name from crop_master where lang_id=$lang_id";
$result2=mysql_query($query2) or die(mysql_error());

while($row=mysql_fetch_assoc($result2))
{
    $output[]=$row;
}

print(json_encode($output));

mysql_close();

?>

I get this sort of error in Logcat:

02-16 22:08:04.216: I/HTTP ok(1251): org.apache.http.message.BasicHttpResponse@4052f0b8 02-16 22:08:04.216 : I/JsonObj(1251): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id #4' at line 1

I don't know whats wrong with my syntax.

EDIT: Used PDO as suggested and its working fine: This is my new script.

<?php


$db = new PDO('mysql:host=localhost;dbname=farm_o_pedia;charset=utf8', 'root', '');

$db->query("SET NAMES utf8");

$lang=$_POST['LanguageName'];

$query1="select crop_name from crop_master where lang_id=(select lang_id from lang_selection where lang_name='$lang')";

$result2=$db->query($query1);

while(($row=$result2->fetch(PDO::FETCH_ASSOC))!=false)
{
    $output[]=$row;
}

print(json_encode($output));

$db=null;

?>
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doufu8127 2013-02-16 16:45
    已采纳

    I'm not sure about the syntax for your queries, but I do know that you are better off with one query instead of two. Something like this:

    select crop_name 
    from crop_master cm join lang_selection ls on cm.languageid = ls.languageid
    where lang_name = '$lang'
    

    If you are not familiar with joining tables, I've heard good things about the book, Teach Yourself SQL in 10 Minutes

    打赏 评论
  • dream3323 2013-02-16 16:43
    $query2="select crop_name from crop_master where lang_id=$lang_id";
    

    Try replacing it with:

    $query2="select crop_name from crop_master where lang_id='$lang_id'";
    

    It seems that you were trying to retrieve crop_name where the lang_id is equal to $lang_id (which would be an invalid identifier) and not the value of $lang_id itself.

    Note you may want to read up on SQL Injection Attacks:

    For instance if a user passed in 1;DROP TABLE USERS as the LanguageName parameter the following would be executed in SQL:

    $query1="select lang_id from lang_selection where lang_name=1;DROP TABLE USERS;
    
    打赏 评论

相关推荐 更多相似问题