CODEWITHSUNDEEP
phpmysql
Gokigooooks
Gokigooooks

Reputation: 794

Inserting into MySQL using select statement and php variables

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.

Upvotes: 0

Views: 859

Answers (2)

spencer7593
spencer7593

Reputation: 108370

$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 ";

Upvotes: 1

Hendry Tanaka
Hendry Tanaka

Reputation: 464

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

Upvotes: 1

Related Questions