Is It Big Crime If I Wrap Integer Valeues Into Quotes?

[rain13]

I am trying to make php script that loads my text file to SQL.

<?phpToSQL("permissions.txt");function ToSQL($sFile){    $data = file_get_contents($sFile);//~ echo $data;    $data = str_replace("\r","",$data);    $data = explode("\n",$data);    for($i = 1;$i < count($data);$i++) {	    $list = explode("|",$data[$i]);  $str = "";  for($j = 0;$j < count($list);$j++) {   //~ exec('$test = '$list[$j]';')   if (is_int($list[$j])) {    $str .= $list[$j].",";   }else{    $str .= "'".$list[$j]."',";   }  }  $str = StringTrimRight($str,1);	    $query = "INSERT INTO ".$data[0]." VALUES (".$str.")";	    echo $query."\n";    }}function StringTrimRight($sString,$iCount){return substr ( $sString ,0 , strlen($sString) - $iCount);}?>

here' my txt file

Permissions1|user|U_VIEWPROFILES|02|user|U_IGNOREFLOOD|03|user|U_DOWNLOADFILES|04|user|U_ATTACH|05|user|U_USESIG|06|user|U_VIEWPROFILE|07|user|U_EDITOWNPROFILE|08|user|U_CANUSEAVATAR|09|user|U_CHANGEDEFAULTGROUP|010|moderator|M_APPROVEPOSTS|011|moderator|M_MANAGEbANS|012|moderator|M_MOVEPOSTS|013|moderator|M_CHANGEPOSTAUTHOR|015|moderator|M_EDITPOSTS|016|moderator|M_DELETEPOSTS|017|moderator|M_VIEWPOSTDETAILS|018|moderator|M_LOCKPOSTS|019|moderator|M_CLOSEREPORTS|020|moderator|M_ISSUEWARNING|021|administrator|A_GROUPPERMISSIONS|022|administrator|A_MANAGEBANS|023|administrator|A_DEFINEBBCODE|024|administrator|A_MANAGEBOARDSETTINGS|025|administrator|A_MANAGEGROUPS|026|administrator|A_MANAGEFORUMS|027|administrator|A_MANAGEUSERS|028|administrator|A_MANAGESTYLES|029|administrator|A_MANAGEIMAGES|030|forum|F_POSTANN|031|forum|F_POSTBB|032|forum|F_DELETEOWN|033|forum|F_EDITOWN|034|forum|F_POSTIMAGES|035|forum|F_POSTLINKS|036|forum|F_STARTNEW|037|forum|F_NOAPPROVAL|038|forum|F_CANREPLY|039|forum|F_READFORUM|040|forum|F_USESIG|041|forum|F_LOCKOWN|042|forum|F_POSTSTICKY|043|forum|F_CANREPORT|044|forum|F_CANVIEW|045|forum|F_CANATTACH|046|forum|F_POSTHTML|047|forum|F_META|0

And that's generated SQL code

INSERT INTO Permissions VALUES ('1','user','U_VIEWPROFILES','0')INSERT INTO Permissions VALUES ('2','user','U_IGNOREFLOOD','0')INSERT INTO Permissions VALUES ('3','user','U_DOWNLOADFILES','0')INSERT INTO Permissions VALUES ('4','user','U_ATTACH','0')INSERT INTO Permissions VALUES ('5','user','U_USESIG','0')INSERT INTO Permissions VALUES ('6','user','U_VIEWPROFILE','0')INSERT INTO Permissions VALUES ('7','user','U_EDITOWNPROFILE','0')INSERT INTO Permissions VALUES ('8','user','U_CANUSEAVATAR','0')INSERT INTO Permissions VALUES ('9','user','U_CHANGEDEFAULTGROUP','0')INSERT INTO Permissions VALUES ('10','moderator','M_APPROVEPOSTS','0')INSERT INTO Permissions VALUES ('11','moderator','M_MANAGEbANS','0')INSERT INTO Permissions VALUES ('12','moderator','M_MOVEPOSTS','0')INSERT INTO Permissions VALUES ('13','moderator','M_CHANGEPOSTAUTHOR','0')INSERT INTO Permissions VALUES ('15','moderator','M_EDITPOSTS','0')INSERT INTO Permissions VALUES ('16','moderator','M_DELETEPOSTS','0')INSERT INTO Permissions VALUES ('17','moderator','M_VIEWPOSTDETAILS','0')INSERT INTO Permissions VALUES ('18','moderator','M_LOCKPOSTS','0')INSERT INTO Permissions VALUES ('19','moderator','M_CLOSEREPORTS','0')INSERT INTO Permissions VALUES ('20','moderator','M_ISSUEWARNING','0')INSERT INTO Permissions VALUES ('21','administrator','A_GROUPPERMISSIONS','0')INSERT INTO Permissions VALUES ('22','administrator','A_MANAGEBANS','0')INSERT INTO Permissions VALUES ('23','administrator','A_DEFINEBBCODE','0')INSERT INTO Permissions VALUES ('24','administrator','A_MANAGEBOARDSETTINGS','0')INSERT INTO Permissions VALUES ('25','administrator','A_MANAGEGROUPS','0')INSERT INTO Permissions VALUES ('26','administrator','A_MANAGEFORUMS','0')INSERT INTO Permissions VALUES ('27','administrator','A_MANAGEUSERS','0')INSERT INTO Permissions VALUES ('28','administrator','A_MANAGESTYLES','0')INSERT INTO Permissions VALUES ('29','administrator','A_MANAGEIMAGES','0')INSERT INTO Permissions VALUES ('30','forum','F_POSTANN','0')INSERT INTO Permissions VALUES ('31','forum','F_POSTBB','0')INSERT INTO Permissions VALUES ('32','forum','F_DELETEOWN','0')INSERT INTO Permissions VALUES ('33','forum','F_EDITOWN','0')INSERT INTO Permissions VALUES ('34','forum','F_POSTIMAGES','0')INSERT INTO Permissions VALUES ('35','forum','F_POSTLINKS','0')INSERT INTO Permissions VALUES ('36','forum','F_STARTNEW','0')INSERT INTO Permissions VALUES ('37','forum','F_NOAPPROVAL','0')INSERT INTO Permissions VALUES ('38','forum','F_CANREPLY','0')INSERT INTO Permissions VALUES ('39','forum','F_READFORUM','0')INSERT INTO Permissions VALUES ('40','forum','F_USESIG','0')INSERT INTO Permissions VALUES ('41','forum','F_LOCKOWN','0')INSERT INTO Permissions VALUES ('42','forum','F_POSTSTICKY','0')INSERT INTO Permissions VALUES ('43','forum','F_CANREPORT','0')INSERT INTO Permissions VALUES ('44','forum','F_CANVIEW','0')INSERT INTO Permissions VALUES ('45','forum','F_CANATTACH','0')INSERT INTO Permissions VALUES ('46','forum','F_POSTHTML','0')INSERT INTO Permissions VALUES ('47','forum','F_META','0')

as you can see numbers are wrapped in quotes. Would it cause problem if they are in quotes or is it ok to have numbers like that? (first col is ID, last col is bool) Also can I connect it onto 1 query? as I have 47 rows there it would cause huge spamm to send it all. would be nice if I could send it all as 1 chunk. Also what happens if value is already there? I want to have option to overwrite it if row already exists or skip it..Just wonderin if INSERT owerwrites it or not.

[davej]

Numbers can't be enclosed in quotes if they are actually defined as numbers in the database. Try;INSERT INTO Permissions VALUES ('1','user','U_VIEWPROFILES','0'),('2','user','U_IGNOREFLOOD','0'),('3','user','U_DOWNLOADFILES','0');Attempting to insert a duplicate primary key would yield an error.

[Krewe]

ha yes. It is a pretty big crime.When you put a number inside quotes you are changing it from an Integer to a String. Two Different types of Variables.

[rain13]

Attempting to insert a duplicate primary key would yield an error.

So I would have to download whole table to php server and then see what should be added to query and what not? Or is there some SQL command to do it better way something like: IF NOT EXISTS TNEN INSERT INTO Permissions VALUES ('1','user','U_VIEWPROFILES','0'),('2','user','U_IGNOREFLOOD','0'),('3','user','U_DOWNLOADFILES','0');? edit: REPLACE is what I want. lol this editor doesnt allow me to remove bold tags from that text :S

[birbal]

http://www.google.co.in/url?sa=t&rct=j&q=replace+mysql&source=web&cd=1&ved=0CCYQFjAA&url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.0%2Fen%2Freplace.html&ei=Ndz1Tv3AB8qqrAfdh8TSDw&usg=AFQjCNFdGx82IRJS5Zu-yVfVT8OUp-U_gg&cad=rja you can use REPLACE instead it will update if there is anu duplicate key.

[davej]

I'm not sure if I've ever used REPLACE. I was thinking of UPDATE. I will have to think about this. Actually it looks a little odd that you are inserting the user number (primary key?) rather than using autonumber /AUTO_INCREMENT to generate the next available number. You might also set up a UNIQUE constraint in the database.