Two Queries one statement

[wmeredith]

The the last half of following code doesn't work can anyone explain why? 

 

$sql = "

UPDATE session_notes

SET

notes = '$notes',notes2 = '$notes2',notes3 = '$notes3'

WHERE appointment_id = '$appointment_id'";"

 

INSERT INTO provider_submit

SET

provider_id = $provider_id

";

 

Thanks!

[denno020]

For INSERT, you write it like this:

INSERT INTO table_name VALUES ("value1", "value2", "value3");

If you have an auto incrementing ID as the first field (which you probably should), then you leave the first value blank:

VALUES("", "value1", "value2", "value3");

Also, your semi-colon should still be part of the query string, not outside as it appears you have written it.

 

......  = '$appointment_id'";"
should be
......  = '$appointment_id';

Hope that helps.

 

Denno

Edited July 6, 2013 by denno020

[trq]

denno20, the ops INSERT statement us perfectly valid syntax.

 

To the op, you cannot (with the mysql extension) execute multiple queries in one call.

[denno020]

denno20, the ops INSERT statement us perfectly valid syntax.

 

I thought that might have been the case after I posted that comment.. But I thought I'd leave it there anyway, shows a different way of running the sql query I guess..

[wmeredith]

Ok so now that I've learned that you can not make mutilple queries with one call how do you suggest I rewrite the code so that I can update on one table and insert on another once the submit button is clicked? 

 

Thanks!!!

[trq]

Just execute two different queries.

[thara]

as @trq have mentioned you need to use two different queries like this

$sql1 = "UPDATE session_notes
         SET notes     = '$notes',
            notes2     = '$notes2',
            notes3     = '$notes3'
         WHERE appointment_id = '$appointment_id'";
 
$sql2 = "INSERT INTO provider_submit
         SET provider_id = $provider_id";

[wmeredith]

That didn't work. Infact when I remove the 1 from $sql1= the first query work and the second didn't . Also when removing the 2 for the $sql2= again the second query worked and the 1st didn't. Its either one or the other. What gives? 

[thara]

How do you execute these two queries? Post your PHP code also.

 

If you use mysql extension, mysql_query only allows you to make one query at a time.

 

From the docs.

 

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

 

Edited July 7, 2013 by thara

[wmeredith]

<?php

session_start();

include('/Applications/xampp/htdocs/connect.php');

 

// echo "<pre>";

// print_r($_SESSION);

// echo "</pre>";

 

if(isset($_SESSION['provider_id'])) {

$provider_id = $_SESSION['provider_id'];

$password = $_SESSION['password'];

 

$sql = "

SELECT

COUNT(*) as count

FROM providers

WHERE provider_id = '$provider_id'

AND password = '$password'

";

 

$res = mysql_fetch_assoc(mysql_query($sql));

 

if($res['count'] == 1){

$logged_in = true;

} else {

$_SESSION = array();

session_destroy();

$logged_in = false;

}

} else {

$logged_in = false;

}

 

if(!$logged_in){

header("Location: index.php");

} else {

//Create Edit Form Here (Should verify if 'appointment_id' is associated with 'provider_id')

if(isset($_POST['appointment_id'])){

$appointment_id = mysql_real_escape_string($_POST['appointment_id']);

$provider_id = mysql_real_escape_string($_SESSION['provider_id']);

 

$sql = "

SELECT

COUNT(*) as count

FROM session_notes

WHERE appointment_id = '$appointment_id'

AND provider_id = '$provider_id'

";

 

$res = mysql_fetch_assoc(mysql_query($sql));

 

if($res['count'] == 1){

if(isset($_POST['edit'])){

$_SESSION['appointment_id'] = $appointment_id;

//OUTPUT UPDATE FORM

$sql = "

SELECT

provider_id,

patient_id,

DATE_FORMAT(appointment,'%b %d %Y %h:%i %p') as appointment,

notes,notes2,notes3

FROM session_notes

WHERE appointment_id = '$appointment_id'

";

 

$row = mysql_fetch_assoc(mysql_query($sql));

 

$tpl_vars = array();

$tpl_vars['{{title}}'] = "Session Notes Update Form";

$tpl_vars['{{appointment_id}}'] = $appointment_id;

$tpl_vars['{{provider_id}}'] = $row['provider_id'];

$tpl_vars['{{patient_id}}'] = $row['patient_id'];

$tpl_vars['{{appointment}}'] = $row['appointment'];

$tpl_vars['{{notes}}'] = $row['notes'];

$tpl_vars['{{notes2}}'] = $row['notes2'];

$tpl_vars['{{notes3}}'] = $row['notes3'];

 

//get template & load data

$tpl = file_get_contents('tpl/update_form.html');

echo str_replace(array_keys($tpl_vars),array_values($tpl_vars),$tpl);

} elseif(isset($_POST['update'])){

$notes = mysql_real_escape_string($_POST['notes']);

$notes2 = mysql_real_escape_string($_POST['notes2']);

$notes3 = mysql_real_escape_string($_POST['notes3']);

$tpl_vars = array();

$tpl_vars['{{title}}'] = "Session Notes Update Complete";

 

$sql = "UPDATE session_notes

        SET notes     = '$notes',

            notes2     = '$notes2',

            notes3     = '$notes3'

        WHERE appointment_id = '$appointment_id'";

 

$sql = "INSERT INTO provider_submit

        SET provider_id = $provider_id";

 

if(mysql_query($sql)){

$tpl_vars['{{message}}'] = "Note Edited Successfully";

$Name = "Session Notes Entry App"; //senders name 

$email = "email@adress.com"; //senders e-mail adress 

$recipient = "wm@nipinst.org"; //recipient 

$mail_body = "Appointment ID: $appointment_id\nProvider ID: $provider_id"; //mail body 

$subject = "Note For AppID[$appointment_id] Updated"; //subject 

$header = "From: ". $Name . " <" . $email . ">\r\n"; //optional headerfields 

 

mail($recipient, $subject, $mail_body, $header); //mail command  

//mail("wm@nipinst.org","Note For AppID[$appointment_id] Updated","Appointment ID: $appointment_id\nProvider ID: $provider_id");

} else {

//$tpl_vars['{{message}}'] = "Error! Please contact administrator.";

die('Error: ' . mysql_error()); 

}

 

//get template & load data

$tpl = file_get_contents('tpl/update_complete.html');

echo str_replace(array_keys($tpl_vars),array_values($tpl_vars),$tpl);

} else {

header("Location: index.php");

}

} else {

header("Location: index.php");

}

} else {

header("Location: index.php");

}

}

[thara]

As I said in my previous post, you need to execute these 2 queries separately. Again please read the manual page to mysql_query function

 

Your code should be something similar to this.

$sql1 = "UPDATE session_notes
         SET notes     = '$notes',
            notes2     = '$notes2',
            notes3     = '$notes3'
         WHERE appointment_id = '$appointment_id'";
 
$sql2 = "INSERT INTO provider_submit
         SET provider_id = $provider_id";

$result1 = mysql_query($sql1);
$result2 = mysql_query($sql2); 

if ( $result1 ) {

   // your staff

} else if ( $result2 ) {

   // your staff

}

Edited July 7, 2013 by thara

[jcbones]

As Thara stated, another way (in keeping with your code) is: Edit: this forum software doesn't like linux firefox.  It keeps dropping half of what I write!

Change

$sql = "UPDATE session_notes
          SET notes     = '$notes',
             notes2     = '$notes2',
             notes3     = '$notes3'
          WHERE appointment_id = '$appointment_id'";
 
$sq2 = "INSERT INTO provider_submit
          SET provider_id = $provider_id";
 
if(mysql_query($sql1) && msyql_query($sql2)){

Edited July 7, 2013 by jcbones

[wmeredith]

Hey Panda

 

That worked thanks!