Insert statement not working using GET statements

Question

I believe that my code is wrong as the selected fields aren't inserting into table/fields at all.

how do i get the following to work with the following refernces from the URL.

 $sql="INSERT INTO Instruction (PhysioReference, Physio, PhysiosAddress, postcode, Physiomobile, Number, Physiofax, PhysiosEmail WHERE Reference='$Reference') 
SELECT PhysioReference, Name, Line1, Postcode, Mobile, Tel, Fax, Email from `Physio` WHERE PhysioReference='$PhysioReference'";

example URL: http://test.com/Physiotoinstruction.php?PhysioReference=100099&a‌mp;Reference=456789

Page.php

<?php

require_once('auth.php');

$host=""; // Host name 
$username=""; // Mysql username
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name="Instruction"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");



$Reference=mysql_real_escape_string($_GET['Reference']);
$PhysioReference=mysql_real_escape_string($_GET['PhysioReference']);

$sql="INSERT INTO Instruction (PhysioReference, Physio, PhysiosAddress, postcode, Physiomobile, Number, Physiofax, PhysiosEmail WHERE Reference='$Reference') 
SELECT PhysioReference, Name, Line1, Postcode, Mobile, Tel, Fax, Email from `Physio` WHERE PhysioReference='$PhysioReference'";

$result=mysql_query($sql);



//$sql="INSERT INTO Triage (Reference, Forename)  
//SELECT Reference, Forename FROM `Instruction` 
//WHERE Reference='$Reference' LIMIT 1";

 echo "Successful";
 echo "<BR>";
 echo "<a href='insert.php'>View result</a>";
// mysql_error()



 ?>

I don't get an error, its just the information doesn't go into the field I want it to. e.g. where the 'patientreference=123456' from table 1, insert physioreference 100001 from table 2 into physiorefernce in table 1. — user3301611, Mar 25 '14 at 12:45
@user3301611 see the answer by Sergei Beregov. From that you can get the error msg. — Kamehameha, Mar 25 '14 at 12:47
Note that, while consistent use of `mysql_real_escape_string` _will_ make your code safe, it's still [recommended to use prepared statements](http://security.stackexchange.com/questions/47816/is-mysql-real-escape-string-enough/47820#47820). — Clockwork-Muse, Mar 25 '14 at 14:33

score 4 · Answer 1 · edited Mar 25 '14 at 14:11

4

MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail.

Try to substitute PhysiosEmail WHERE Reference='$Reference') with PhysiosEmail)

edited Mar 25 '14 at 14:11

Alexander

3,129
2
19
33

answered Mar 25 '14 at 12:40

opensource-developer

2,826
4
38
88

score 1 · Answer 2 · edited Mar 25 '14 at 13:57

1

What you are probably trying to do is an update:

$sql="UPDATE Instruction SET PhysioReference='".$PhysioReference."' WHERE Reference='".$Reference."';";

It is still unclear what you are exactly trying to achieve, but from your comment I guess what you are trying to do is (with simplified names):

$reference1=mysql_real_escape_string($_GET['reference1']);
$reference2=mysql_real_escape_string($_GET['reference2']);

$select="SELECT name, tel, email FROM table1 WHERE reference1='".$reference1."';";
$result = mysql_query($select);
$row = mysql_fetch_assoc($result);

$update = "UPDATE table2 SET name='".$row['name']."', tel='".$row['tel']."', email='".$row['email']."' WHERE reference2='".$reference2."';";
$result = mysql_query($update);

Update

Physio (table 1) Instruction (table 2)

$reference1=mysql_real_escape_string($_GET['PhysioReference']);
$reference2=mysql_real_escape_string($_GET['Reference']);

$select="SELECT PhysioReference FROM Physio WHERE PhysioReference='".$reference1."';";
$result = mysql_query($select);
$row = mysql_fetch_assoc($result);

$update = "UPDATE Instruction SET PhysioReference='".$row['PhysioReference']."' WHERE Reference='".$reference2."';";
$result = mysql_query($update);

$result=mysql_query($sql);

edited Mar 25 '14 at 13:57

user3301611

79
8

answered Mar 25 '14 at 12:44

m.pons

140
1
10

how would i define the where statement to use another table e.g "physio" – user3301611 Mar 25 '14 at 12:56
The UPDATE statement gives you the possibility to change one or more values from one table. In the where statement you define which line from this same table you want to update. Could it be that you want to do 2 different updates? – m.pons Mar 25 '14 at 13:04
potentially,i will try and explain the best I can. (still learning PHP). 2 tables, one a customer and the second a supplier. customers get created by the form and the suppliers table is pre populated. I have 6 fields in the customer table, for example (reference,name,address,physiorefernce,physioname,physioaddress). and then I have 3 fields in the suppliers table (physiorefernce,physioname,physioaddress). the statement I need is to update the customer record where the reference=reference from the url, with the information from the suppliers table where physioreference=physioreference from URl – user3301611 Mar 25 '14 at 13:13
I don't think the second part is working I have changed it to suit the fields in the table and only used one field to test. – user3301611 Mar 25 '14 at 13:52

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

Remove where condition from insert query. It will never work. You can used it in select query.

change your insert query by this

$sql="INSERT INTO Instruction (PhysioReference, Physio, PhysiosAddress, postcode, Physiomobile, Number, Physiofax, PhysiosEmail ) 
SELECT PhysioReference, Name, Line1, Postcode, Mobile, Tel, Fax, Email from `Physio` WHERE PhysioReference='$PhysioReference'";

compare your question here

edited May 23 '17 at 10:31

Community

1
1

answered Mar 25 '14 at 12:44

Mitul Shah

1,556
1
12
34

how could I change this to an update statement and update the record (reference from URL) from the table with information from another table where the physioreference = physioreference from URL – user3301611 Mar 25 '14 at 13:02

score 0 · Answer 4 · answered Mar 25 '14 at 12:48

0

    $sql="INSERT INTO Instruction (PhysioReference, Physio, PhysiosAddress, postcode, Physiomobile, Number, Physiofax, PhysiosEmail)
    (SELECT PhysioReference, Name, Line1, Postcode, Mobile, Tel, Fax, Email from `Physio` WHERE PhysioReference='".$PhysioReference."'");

answered Mar 25 '14 at 12:48

wake-up-neo

814
7
9

how could I change this to an update statement and update the record (reference from URL) from the table with information from another table where the physioreference = physioreference from URL – user3301611 Mar 25 '14 at 13:03

Insert statement not working using GET statements

4 Answers4