1

I'm trying to insert a JSON entry into my table, but the catch is that this JSON string has a single quote character. The below code works perfectly when the string for mainIdea is Its nice but what I want is It's nice with an apostrophe. What would I have to change about the below code to make it work with an apostrophe? I've tried It\'s nice but that doesn't work either. 

$jsonDic='{"mainName": "Steve Jobs","mainIdea": "Its nice"}';
$dictionaryToBeAdded=json_decode($jsonDic);
var_dump($dictionaryToBeAdded);
$data=mysql_query("SELECT arrayOfRequests FROM users WHERE email='$email'");
if($result = mysql_fetch_array( $data )) {
   //get json encoded arrayOfNotifs
    $decodeArray=$result['arrayOfRequests']; //this is empty
    //decode it
    $arrayOfRequests=json_decode($decodeArray);
    //add dictionary to be added
    $arrayOfRequests[]=$dictionaryToBeAdded;
    $sendBackArray=json_encode($arrayOfRequests);
    //update db
    mysql_query("UPDATE users SET arrayOfRequests ='$sendBackArray' WHERE email='$email'");
}
Snowman
  • 31,411
  • 46
  • 180
  • 303
  • "I'm trying to insert a JSON entry into my table" that's the problem, db normalisation 101 - don't do that! –  Feb 16 '12 at 23:06
  • you should have a new table for arrayOfRequests, with each in its own 'cell' joined to the users table . –  Feb 16 '12 at 23:10
  • I shouldn't have JSON strings in my table? What then should I have? – Snowman Feb 16 '12 at 23:10
  • You're storing encoded subfields in a database field, which makes it harder to search for values in the subfields. – Jonathan M Feb 16 '12 at 23:10
  • Break your JSON into fields, and create matching fields in your db. This may involve new tables in order to normalize the data. – Jonathan M Feb 16 '12 at 23:11
  • Oh god..I'm new to MySQL..what's wrong with my method? Will it slow down performance or something? – Snowman Feb 16 '12 at 23:11
  • Yeah, it's gonna bite you. Lemme look for some db tutorials... – Jonathan M Feb 16 '12 at 23:12
  • A JSON entry can be seen as an entity of its own. Inserting one entity means that your database design is at 1NF, or 1 Normal Form. – mauris Feb 16 '12 at 23:12
  • poor performance, poor maintenance, poor scalability ... –  Feb 16 '12 at 23:12
  • hey @mohabitar - try using MySQL Workbench's EER Diagram designer it'll help. – mauris Feb 16 '12 at 23:13
  • So I should have a table called arrayOfRequests, but I don't understand then what this table should have? – Snowman Feb 16 '12 at 23:16
  • @mohabitar, google "normalizing data" and learn from some tutorials. Here's one to get you started. It's using Access, but the concepts are the same in mySql: http://www.fabalou.com/Access/General/Normalization.asp. The basic idea is you have a table for each type of object or idea, and you link them together with record Ids. – Jonathan M Feb 16 '12 at 23:17
  • See here: http://stackoverflow.com/questions/2275359/jquery-single-quote-in-json-response – Madbreaks Feb 16 '12 at 23:17
  • Ok I know this is crazy..but how important is this? How big of a mistake will I be making if I keep my current structure? The most any user will have is probably say 500 dictionaries in the array. – Snowman Feb 16 '12 at 23:21
  • o geze do not put json into your database. if you have a one to many relationship of users to dictionary entries, dictionary entries should have it's own table, and the records should be related to each other by a key field. for instance user would have a unique "id" field, and dictionary entries would have an indexed field called "user_id" or something like that. then you can use query with joins to pull up all the records for both tables. – dqhendricks Feb 17 '12 at 00:03
  • @mohabitar, you'll be sooooooooorrrryyyy! It's not much more effort to do it right. Think of it this way: How are you going to query to do the following: Get a list of all users with mainName equal to the name of another user? Or how about, Get a list of users with mainIdea containing "automobile" (upper or lowercase) and sort the mainNames that match those mainIdeas. Finally, a good reason to do it right is that apps *always* grow. *Always*. – Jonathan M Feb 17 '12 at 00:07
  • Also, you'll never be able to index on the fields mainName or mainIdea, which will make searching on those fields even more slow. – Jonathan M Feb 17 '12 at 00:10
  • I've posted a new question about this, in case any one wants to give a full answer and earn some points: http://stackoverflow.com/questions/9321399/normalizing-mysql-data – Snowman Feb 17 '12 at 00:46

2 Answers2

1

You need to escape your data before you attempt to use it in a database query:

mysql_query("UPDATE users SET arrayOfRequests ='$sendBackArray' WHERE email='$email'");
// ---------------------------------------------^                            ^
// --------------------------------------------------------------------------+

Imagine what would happen if $sendBackArray contains ', email =' and $email contains ' OR '' = '.

mysql_query("UPDATE users SET arrayOfRequests ='" . mysql_real_escape_string($sendBackArray) . "' WHERE email='" . mysql_real_escape_string($email) . "'");
Salman A
  • 262,204
  • 82
  • 430
  • 521
0

You have to escape your slash as well:

$jsonDic='{"mainName": "Steve Jobs","mainIdea": "It\\\'s nice"}';
mauris
  • 42,982
  • 15
  • 99
  • 131