Inserting into MySQL using select statement and php variables

Question

I have something that looks like this. It's supposed to insert these values but I can't get the ID's to be inserted by php. I cant get the right syntax for this, please help.

  $insertQuery = "insert into appointment (appointmentID, doctorid, appointmentDate, symptoms, patientid, time) 
  values($id,(select doctorid from doctors where doctorName like '$docName'),$date,$symptoms,
  (select patientid from patient where patientFName like '$nameOfUser'),$time)";

I get an invalid query error and but when I vardump thes variables($docName, $id, $nameOfUser) they turn out to be in right format. I already tried manual inputting in the MySQL table, and it was successfully inserted.

Do you define 'appointmentID' with PRIMARY_KEY and AUTO_INCREMENT? If yes, you can remove 'appointmentID' field or set to 'null' — Junyoung LEE
– Junyoung LEE, Commented Sep 30, 2014 at 1:20

Hendry Tanaka · Accepted Answer · 2014-09-30 01:48:26Z

1

First, you make a mistake by using select the id that had already used by using (select patientid from patient where patientFName like '$nameOfUser'). I suggested that patientid is a primary key and integer data type.

When you create a table. Use this syntax to make it auto increment:

CREATE TABLE example (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

And when you insert into table, you do not have to insert the id. The database engine will count the last id automatically.

INSERT INTO example(name)values('example');

But! If you already create this table without auto_increment command and you have using this table 'too far', just use this solution:

mysql_connect('your host','database user','password');
mysql_select_db('your database name');
$query=mysql_query('SELECT MAX(patientid) FROM yourtable;');
$read_id = mysql_fetch_row($query));
$next_id = $read_id[0] + 1;
$query = mysql_query('INSERT INTO yourtable(patientid)values('.$next_id.');');

For more Information, learn it here

answered Sep 30, 2014 at 1:48

Hendry Tanaka

4643 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gokigooooks Over a year ago

this is useful info! although i would like to clarify, is ('.$next_id.') the right way to enclose it when putting it in values with php? because i solved my problem and it was because i enclosed every php variable in my insert with a quotation values('$id','$date' .. etc)

Hendry Tanaka Over a year ago

dot symbol is for inserting a variable into string. For example $insert = 'Hi'; $concat = $insert.'. How are you?'; print $concat => Hi. How are you?. The reason I'm using the dot symbol because in coding my program I always type them in Notepad++ so it will give a different color that will make me easier to know. It will make no change? But I recommend you to use dot.

spencer7593 · Accepted Answer · 2014-09-30 02:46:51Z

$insertQuery = "INSERT INTO appointment 
     ( appointmentID
     , doctorid
     , appointmentDate
     , symptoms
     , patientid
     , time 
     ) 
SELECT '" . $id . "'
     , n.doctorid
     , '" . $date . "'
     , '". $symptoms ."'
     , p.patientid
  FROM ( SELECT e.doctorid
           FROM doctors e
          WHERE e.doctorName LIKE '" . $docName . "'
          LIMIT 1
       ) d
 CROSS
  JOIN ( SELECT q.patientid
           FROM patient q
          WHERE q.patientName LIKE '" . $nameOfUser ."'
          LIMIT 1
       ) p ";

This statement is subject to SQL Injection. To mitigate that, you'd either need to escape "unsafe" values that are included in the SQL text, or use a prepared statement with bind placeholders.

Assuming that you are using procedural style functions of the mysqli interface, and the connection is named $con

$insertQuery = "INSERT INTO appointment 
     ( appointmentID
     , doctorid
     , appointmentDate
     , symptoms
     , patientid
     , time 
     ) 
SELECT '" . mysqli_real_escape_string($con, $id) . "'
     , n.doctorid
     , '" . mysqli_real_escape_string($con, $date) . "'
     , '" . mysqli_real_escape_string($con, $symptoms) ."'
     , p.patientid
  FROM ( SELECT e.doctorid
           FROM doctors e
          WHERE e.doctorName LIKE '" . mysqli_real_escape_string($con, $docName) . "'
          LIMIT 1
       ) d
 CROSS
  JOIN ( SELECT q.patientid
           FROM patient q
          WHERE q.patientName LIKE '" . mysqli_real_escape_string($con, $nameOfUser) ."'
          LIMIT 1
       ) p ";

A prepared statement would replace the literals with bind placeholders:

$insertQuery = "INSERT INTO appointment 
     ( appointmentID
     , doctorid
     , appointmentDate
     , symptoms
     , patientid
     , time 
     ) 
SELECT ?
     , n.doctorid
     , ?
     , ?
     , p.patientid
  FROM ( SELECT e.doctorid
           FROM doctors e
          WHERE e.doctorName LIKE ?
          LIMIT 1
       ) d
 CROSS
  JOIN ( SELECT q.patientid
           FROM patient q
          WHERE q.patientName LIKE ?
          LIMIT 1
       ) p ";

Collectives™ on Stack Overflow

Inserting into MySQL using select statement and php variables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related