0

I am new to android programming and I am having trouble with queries. I tried to search for similar questions but I found nothing :) so, here..

given this patientinfo table

patient_id | name | gender | age 
      1    | jen  | female | 20
      2    | jay  |  male  | 19

and records table

patient_id |   date   | description
      1    | 01-01-14 | healthy!
      1    | 02-01-14 | healthy!
      2    | 01-01-14 | needs medication.

I'd like to get all the records of a certain patient. like for example, if I choose patient1.. all records of patient1 will be displayed.

this is my query..

$query = "SELECT * FROM records INNER JOIN patientinfo ON records.patient_id = patientinfo.patient_id WHERE records.patient_id = $patient_id";

try{
    $stmt = $dbname->prepare($query);
    $result = $stmt->execute(); 

}catch(PDOException $ex){
        $response["success"] = 0;
        $response["message"] = $ex;

        die(json_encode($response));
    }


but it gives me an error in sql syntax, I think it's in the $patient_id

errorInfo: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use.

Thank you in advance! hoping for an answer! Thanks :)

Mary
  • 197
  • 1
  • 15
superJen
  • 63
  • 6
  • *"but it spits error in sql syntax..."* - Being? – Funk Forty Niner Feb 06 '14 at 19:10
  • 1
    Where did you set your patient_id variable? – Dan Bracuk Feb 06 '14 at 19:14
  • 2
    Not related to your question, but, first, don't store someone's age. It changes. Store their birth date and calculate the age. Second, select just the fields you need instead of select *. Third, many databases get persnickety when you use keywords as column names. In this case, the field is date. – Dan Bracuk Feb 06 '14 at 19:16
  • There are few things more frustrating than someone saying that there is an error, then not telling you what that error is. Grrrr. – Simon Feb 06 '14 at 19:19
  • @DanBracuk thanks! i'll take this, but still i dont know what to do. – superJen Feb 06 '14 at 19:21
  • @Simon oops sorry, here. "errorInfo: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use." – superJen Feb 06 '14 at 19:23
  • 1
    I think this kind of questions shouldn't be downvoted. The OP has at least had a try and is requesting for help. – nKn Feb 06 '14 at 19:37
  • So you're using MySQL. Why is this an Android question? – Simon Feb 06 '14 at 19:37
  • 2
    Whether it's php, coldfusion, .net, whatever, it is often a good idea to get your query working directly against the database first. If it works there but not in your application, you know that the problem is not your sql. If you think the problem might be your variable, hard code a value instead and try that. – Dan Bracuk Feb 06 '14 at 20:09

1 Answers1

1

Try by:

SELECT *
FROM patientinfo, records
WHERE (patientinfo.patient_id = $patient_id) AND (patientinfo.patient_id = records.patient_id);

You may also specifically list the columns to show, and assign them an alias, for instance: 

SELECT records.date AS the_date

The WHERE records.patient_id = $patient_id seems ok as long as $patient_id is not an empty string. For that, I'd echo the query prior to executing it and checking whether it's ok.

nKn
  • 13,691
  • 9
  • 45
  • 62
  • thank you but it's getting all the data in the records table, even the data that is not included to the patient i have clicked. :) – superJen Feb 06 '14 at 19:32
  • 1
    I've updated my answer, check now please. As I stated, check whether $patient_id is null or not. If not, and is a number, this should work just fine. – nKn Feb 06 '14 at 19:34