insert with a select statement and php variables in 1 MySQL query

Question

i have something like this to insert data from a form to my MySQL table. is my use of select statements in the insert valid? please enlighten me.

if(isset($_POST['date']) && isset($_POST['docName']) && isset($_POST['docSpec']) && isset($_POST['time']) && isset($_POST['symptoms']) )
{   
    $nameOfUser = $_COOKIE['userlogin'];

    $docName = $_POST['docName'];

    $date = $_POST['date'];

    $symptoms = $_POST['symptoms'];

    $time = date('H:i:s',strtotime($_POST['time'])); 

    $id = mt_rand(1000,9999);  //generate random appointment id

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

    if(mysqli_query($conn,$insertQuery)===true)
    {
        echo "<script>alert('success');</script>";
    }
    else
    {
        die('Invalid query: ' . mysql_error()); 
        $message .= 'Whole query: ' . $query;
        die($message);  
    }
}

it says invalid query. the columns in the insert statement is already in right order. can anyone help me?

Your query is open to SQL injection attacks. Take a look at http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php to see how to do your queries safely. — Andy Lester, Sep 29 '14 at 17:12
i know. im trying to build its functionalities first. please stick to question — Gokigooooks, Sep 29 '14 at 17:18

Jay Blanchard · Answer 1 · 2014-09-29T17:02:50.250

0

You have to specify the columns that you are inserting into -

insert into appointment (col1, col2, col3, ...) values
($id,(select doctorid from doctors where doctorName like '$docName' ), $date,$symptoms,(select patientid from patient where patientFName like '$nameOfUser'),$time)";

It looks like you have 6 columns.

EDIT: This syntax may help to clear things up -

$insertQuery = "INSERT INTO `appointment` (`col1`, `col2`, `col3`,`col4`,`col5`,`col6`) ";
$insertQuery .= "VALUES (";
$insertQuery .= "'" . $id . "'";
$insertQuery .= ", '" . "(SELECT `doctorid` FROM `doctors` WHERE `doctorName` LIKE '%" . $docName . "%')" . "'";
$insertQuery .= ", '" . $date . "'";
$insertQuery .= ", '" . $symptoms . "'";
$insertQuery .= ", '" . "(SELECT `patientid` FROM `patient` WHERE `patientName` LIKE '%" . $nameOfUser . "%')" . "'";
$insertQuery .= ", '" . $time . "'";
$insertQuery .= ")";

You're also using LIKE without giving it the chance to find other elements because you're not using wildcards.

edited Sep 29 '14 at 17:02

answered Sep 29 '14 at 16:11

Jay Blanchard

34,243
16
77
119

do i have to specify? i inserted the values the same order as when i created the table. – Gokigooooks Sep 29 '14 at 16:21
Yes, MySQL requires that you specify. – Jay Blanchard Sep 29 '14 at 16:21
added column specification but to no avail. i also tried putting percent signs `where doctorName like '%$docName%'` still, not working. – Gokigooooks Sep 29 '14 at 16:25
Can you run the query outside of the PHP? If not, what are the errors? – Jay Blanchard Sep 29 '14 at 16:28
I have a feeling that your variables are not being populated correctly due to quoting. I am preparing to edit my answer. – Jay Blanchard Sep 29 '14 at 16:36
Yep - that is from bad quotes. It thinks 'Dr. Richard' is a column name. – Jay Blanchard Sep 29 '14 at 16:39
1

nvm my last answer, i got it to insert. – Gokigooooks Sep 29 '14 at 16:40

insert with a select statement and php variables in 1 MySQL query

1 Answers1