I've been looking at this too long

[Pandolfo]

Hey everyone,

 

I've been up for about two days working on code...and i can't seem to get the syntax correct. I'm loading values from one database to another...this is just a quick and dirty solution so i won't have to cut and paste until doomsday. The code is listed below.

 

<?php
include 'config.php';

$yearid=$_POST['yearid'];
switch($yearid){
case 24:
$qno1=1;
$qno2=2;
$qno3=3;
break;
case 25:
$qno1=4;
$qno2=5;
$qno3=6;
break;
case 26:
$qno1=7;
$qno2=8;
$qno3=9;
break;
case 27:
$qno1=10;
$qno2=11;
$qno3=12;
break;
case 28:
$qno1=13;
$qno2=14;
$qno3=15;
break;
case 29:
$qno1=16;
$qno2=17;
$qno3=18;
break;
case 30:
$qno1=19;
$qno2=20;
$qno3=21;
break;
case 31:
$qno1=22;
$qno2=23;
$qno3=24;
break;
case 32:
$qno1=25;
$qno2=26;
$qno3=27;
break;
case 33:
$qno1=28;
$qno2=29;
$qno3=30;
break;
case 34:
$qno1=31;
$qno2=32;
$qno3=33;
break;
case 35:
$qno1=34;
$qno2=35;
$qno3=36;
break;
case 36:
$qno1=37;
$qno2=38;
$qno3=39;
break;
case 37:
$qno1=40;
$qno2=41;
$qno3=42;
break;
case 38:
$qno1=43;
$qno2=44;
$qno3=45;
break;
case 39:
$qno1=46;
$qno2=47;
$qno3=48;
break;
case 40:
$qno1=49;
$qno2=50;
$qno3=51;
break;
case 41:
$qno1=52;
$qno2=53;
$qno3=54;
break;
case 42:
$qno1=55;
$qno2=56;
$qno3=57;
break;
case 43:
$qno1=58;
$qno2=59;
$qno3=60;
break;
case 44:
$qno1=61;
$qno2=62;
$qno3=63;
break;
case 45:
$qno1=64;
$qno2=65;
$qno3=66;
break;
case 46:
$qno1=67;
$qno2=68;
$qno3=69;
break;
case 47:
$qno1=70;
$qno2=71;
$qno3=72;
break;
case 48:
$qno1=73;
$qno2=74;
$qno3=75;
break;
case 49:
$qno1=76;
$qno2=77;
$qno3=78;
break;
case 50:
$qno1=79;
$qno2=80;
$qno3=81;
break;
case 51:
break;
$qno1=82;
$qno2=83;
$qno3=84;
case 52:
break;
case 53:
$qno1=85;
$qno2=86;
$qno3=87;
break;
case 54:
$qno1=88;
$qno2=89;
$qno3=90;
break;
case 55:
$qno1=91;
$qno2=92;
$qno3=93;
break;
case 56:
$qno1=94;
$qno2=95;
$qno3=96;
break;
case 57:
$qno1=97;
$qno2=98;
$qno3=99;
break;
case 58:
$qno1=100;
$qno2=101;
$qno3=102;
break;
case 59:
$qno1=103;
$qno2=104;
$qno3=105;
break;
case 60:
$qno1=106;
$qno2=107;
$qno3=108;
break;
case 61:
$qno1=109;
$qno2=110;
$qno3=111;
break;
case 62:
$qno1=112;
$qno2=113;
$qno3=114;
break;
case 63:
$qno1=115;
$qno2=116;
$qno3=117;
break;
}







	mysql_connect($server,$db_user,$db_pass);
	mysql_select_db($database);

	$result = mysql_query("select * from management where Qno='$qno1'")or die(mysql_error());
	while($r=mysql_fetch_array($result))
        {
        $smq1text=$r['Qtext'];
        $smq1opt1=$r['Qopt1'];
        $smq1opt2=$r['Qopt2'];
        $smq1opt3=$r['Qopt3'];
        $smq1opt4=$r['Qopt4'];
        $smq1opt5=$r['Qopt5'];
	$smq1right=$r['Qright'];
	}

	$result2 = mysql_query("select * from management where Qno='$qno2'")or die(mysql_error());
	while($r=mysql_fetch_array($result2))
        {
        $smq2text=$r['Qtext'];
        $smq2opt1=$r['Qopt1'];
        $smq2opt2=$r['Qopt2'];
        $smq2opt3=$r['Qopt3'];
        $smq2opt4=$r['Qopt4'];
        $smq2opt5=$r['Qopt5'];
	$smq2right=$r['Qright'];
	}

	$result3 = mysql_query("select * from management where Qno='$qno3'")or die(mysql_error());
	while($r=mysql_fetch_array($result3))
        {
        $smq3text=$r['Qtext'];
        $smq3opt1=$r['Qopt1'];
        $smq3opt2=$r['Qopt2'];
        $smq3opt3=$r['Qopt3'];
        $smq3opt4=$r['Qopt4'];
        $smq3opt5=$r['Qopt5'];
	$smq3right=$r['Qright'];
	}

//echo "$smq1text<br>";
$insertsmq1text=mysql_query("UPDATE ams SET smq1text='$smq1text' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq1opt1=mysql_query("UPDATE ams SET smq1opt1='$smq1opt1' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq1opt2=mysql_query("UPDATE ams SET smq1opt2='$smq1opt2' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq1opt3=mysql_query("UPDATE ams SET smq1opt3='$smq1opt3' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq1opt4=mysql_query("UPDATE ams SET smq1opt4='$smq1opt4' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq1opt5=mysql_query("UPDATE ams SET smq1opt5='$smq1opt5' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq1right=mysql_query("UPDATE ams SET smq1right='$smq1right' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());  


$insertsmq2text=mysql_query("UPDATE ams SET smq2text='$smq2text' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq2opt1=mysql_query("UPDATE ams SET smq2opt1='$smq2opt1' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq2opt2=mysql_query("UPDATE ams SET smq2opt2='$smq2opt2' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq2opt3=mysql_query("UPDATE ams SET smq2opt3='$smq2opt3' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq2opt4=mysql_query("UPDATE ams SET smq2opt4='$smq2opt4' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq2opt5=mysql_query("UPDATE ams SET smq2opt5='$smq2opt5' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq2right=mysql_query("UPDATE ams SET smq2right='$smq2right' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 
   

$insertsmq3text=mysql_query("UPDATE ams SET smq3text='$smq3text' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq3opt1=mysql_query("UPDATE ams SET smq3opt1='$smq3opt1' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq3opt2=mysql_query("UPDATE ams SET smq3opt2='$smq3opt2' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 

$insertsmq3opt3=mysql_query("UPDATE ams SET smq3opt3='$smq3opt3' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq3opt4=mysql_query("UPDATE ams SET smq3opt4='$smq3opt4' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());

$insertsmq3opt5=mysql_query("UPDATE ams SET smq3opt5='$smq3opt5' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error());  

$insertsmq3right=mysql_query("UPDATE ams SET smq3right='$smq3right' where ams.yearid='$yearid'")or die("Could not insert data because of the following error. Please contact site Admin if this persists ".mysql_error()); 
    
$gotonext ='insertone.html';
header('Location: '. $gotonext);
?>

 

Everything works fine unless the body of text in any of the "Qtext" fields of the source table contains a contraction...(can't, won't, manager's etc.). If that is the case, instead of inserting the data, it generates a mysql error and termintates the script. An example of the error is included below:

 

Could not insert data because of the following error. Please contact site Admin if this persists You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A manager's ability to think in the abstract requires ________ skills.'''' where' at line 1

 

 

I'm sure that the answer is quite simple....and i feel sheepish asking for help. I've been up way too long just trying to meet a deadline. Any help would really be great.

 

Thanks!

[Daukan]

You need to use mysql_real_escape_string() on the data before inserting it.

[kenrbnsn]

use the function mysql_real_escape_string() on all strings you're putting into the database. It will escape things like single quotes and double quotes.

 

Ken

[Pandolfo]

Thanks for the help. It looks like that will work. Can it be used with an UPDATE query, or only with INSERT? I'm having trouble figuring out the best place to use the function. Does it happen inside the query itself, or can i use it after the variables are first defined from the array?

Thanks!

[kenrbnsn]

It should be used anytime you pass strings to be put into a database, insert or update. You can use it at anytime, either prior to creating the query or while creating the query.

 

Ken