SQL without a WHERE that uses a key column

Question

My script is quitting on SQL calls that should not have an issue. One of the queries that is failing to update is:

UPDATE dist_comp.private_schools SET mail_address = '1351 Royalty Dr', city = 'Alabaster', state = 'AL',zip_code = 35007,phone = '2056633973' WHERE school_name = 'Kingwood Christian School' AND city = 'Alabaster'

When I run the same query in MySQL workbench, I get

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

Is this the reason why my script is quitting?

<?php
require_once('connect.php');

function schoolInfo($school_name,$city){
    $data = array();
    $counter = 0;  
    $handle = fopen("k12privateschools_loc_0813.csv","r") or exit('doesnt work');
    fgetcsv($handle) or exit('fgetcsv issue');

    while( ($line = fgetcsv($handle)) !== false) {
        $data[] = $line;

        ///echo ("Does schoolname and city match: " . addslashes($data[$counter][2]). ":" . $school_name . " ; " . addslashes($data[$counter][4]). ":" . $city . "\n");

        if (addslashes($data[$counter][2])==$school_name && addslashes($data[$counter][4])==$city){
            //echo ('match');       
            if($data[$counter][13] != ""){
                $mail_address = $data[$counter][12];
                $city= $data[$counter][13];
                $state= $data[$counter][14];
                $zip_code= $data[$counter][15];
                $zip_4= $data[$counter][16];
            }else{
                $mail_address = $data[$counter][3];
                $city= $data[$counter][4];
                $state= $data[$counter][5];
                $zip_code= $data[$counter][6];
                $zip_4= $data[$counter][7];
            }
            $phone= $data[$counter][8];

            $query= "UPDATE dist_comp.private_schools SET  
                    mail_address = '".$mail_address."', 
                    city = '".$city."', 
                    state = '".$state."',"; 
            if($zip_code != ""){
                    $query.="zip_code = ".$zip_code.",";
            }
            if($zip_4 != ""){
                $query.="zip_4 = ".$zip_4.",";
            }
                    $query.= "phone = '".$phone."' 
                    WHERE school_name = '".$school_name."' AND city = '" .$city . "'";

            mysqli_query($con,$query);
            if(mysqli_affected_rows($con)==0){
                exit($query . "\n ");
            }
            //echo $query;
        }//end if counter \
        else{
            //echo("no match");
            }
        $counter++;
    }//end read lines from file
}
    echo "starting import \n";

        //Query for all school names
        $sql2 = mysqli_query($con,"SELECT school_name,city FROM dist_comp.private_schools") or exit('query issue second');
            while($row = mysqli_fetch_array($sql2)){ //this line is making it take a really long time
                $school_name= addslashes($row['school_name']);
                $city = addslashes($row['city']);
                schoolInfo($school_name,$city);
            }//end while fetch array

    //}
  echo "Import finished";
?>

read this http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench — Charaf JRA, Sep 09 '13 at 18:52
Is this the same for running a script though? Add SET SQL_SAFE_UPDATES=0; to query ? — CaitlinHavener, Sep 09 '13 at 18:54

score 1 · Accepted Answer · edited Feb 19 '17 at 22:42

1

Try to disable safe update using this line before your query :

 mysqli_query($con,"SET sql_safe_updates=0");

Or use :

$query="SET sql_safe_updates=0";

$query.= "UPDATE dist_comp.private_schools SET  
                mail_address = '".$mail_address."', 
                city = '".$city."', 
                state = '".$state."';"; 


mysqli_multi_query($con,$query);

or in MySQL WorkBench:

Edit -> Preferences -> SQL Queries
Uncheck Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)
Query --> Reconnect to Server

edited Feb 19 '17 at 22:42

abc123

17,855
7
52
82

answered Sep 09 '13 at 19:01

Charaf JRA

8,249
1
34
44

It should be set to 0 – M Khalid Junaid Sep 09 '13 at 19:11
1

it's 0 , 1 is for enabling sorry – Charaf JRA Sep 09 '13 at 19:16
Thank you. I'm trying it out. – CaitlinHavener Sep 09 '13 at 19:19

score 0 · Answer 2 · answered Sep 17 '13 at 19:42

0

$query = 'SET SQL_SAFE_UPDATES=0;';

$query .= 'custom query here;';

$query .= 'SET SQL_SAFE_UPDATES=1;';

answered Sep 17 '13 at 19:42

MagePal Extensions

17,646
2
47
62

SQL without a WHERE that uses a key column

2 Answers2