Inserting an array into MySQL

PHP
1

I am trying to put some JSON from the twitter REST API into a SQL database. My goal is to be able to look up account information from multiple accounts in one fell swoop.

I have been successful in echoing out the right data, but am not able to write it to the database.

I have a feeling there is something wrong with my array, specifically that each account I am looking up is in an array that does not share a common key, something like:

Array ( [twitterhandle being used as the key] => Array ( [screen_name] => value [name] => value [image] => value [following] => value [listed] => value [followers] => value [tweets] =>value ) [a different twitter handle being used a key]…

Her’s the code that I am using:

    {
            $result = mysqli_query($link,
                        "SELECT
                            twitterhandle FROM accounts");


            while ($row = mysqli_fetch_assoc($result)) //build an array of the twitter handles in the "accounts" table in the database
                    {
                            $handles[]= $row['twitterhandle'];

                    }

            foreach ($handles as $handle) //loop each handle as a twitter api Query grabbing the following JSON array
            {
                $url =  file_get_contents('http://api.twitter.com/1/users/show.json?screen_name='.$handle.'&include_entities=true');
                $json_a = json_decode($url, true);
                $owners[$handle]= array('screen_name'=>$json_a['screen_name'],'name'=>$json_a['name'],'image'=>$json_a['profile_image_url'],
                                        'following'=>$json_a['friends_count'],'listed'=>$json_a['listed_count'],'followers'=>$json_a['followers_count'],
                                        'tweets'=>$json_a['statuses_count']); //try to echo to see if it works, should go and grab field 'owner' from JSON file

            }



                                         $sql = "UPDATE accounts SET
                                                   account_owner='it works' //just using this as to focus on getting the WHERE clause to work

                                                   WHERE twitterhandle='$?'"; //not sure what to put here. i have tried several things but nothing works. The best I can do is get the last set of results in my array.

                                           if (!mysqli_query($link,$sql))
                                			{
                                				$error = 'Error adding twitter data. ' . mysqli_error($link);
                                				include  '/includes/error.html.php';
                                                                exit();
                                			}

        }

        ?>

Any help would be mucho appreciated.

thanks

2

You could just save the JSON string you have stored in $url, then json_decode it when you get it back out of the database. It really depends on what you’re planning to do with the data, personally, I’d probably look to store the data a little more structured than you are; horses-for-courses.

:slight_smile:

3

storing an encoded string consisting of multiple values in a single database column works just fine… up until the point where you need to search on the existence of a particular value within that string, when you will learn a new concept called table scan

:slight_smile:

4

Good idea Anthony, thanks!

r937 - I am about 1/2 through your SQL book. Is there information on a table scan as I get towards the end? Otherwise, what is a good resource for learning?

Thanks

5

sorry, my book doesn’t really cover query optimization

table scan refers to the action of a query which must read every row in the table in order to evaluate the search predicate (i.e. WHERE clause condition)

this is due to the ineffectiveness of an index on that column, because the value being searched for is inside the column value

you’re familiar with printed phone books, right? the entries are in sequence by last name, then first name, then address

if i asked you to get me the phone number of everybody whose first name is todd, you’d have to go through the entire phone book, right?

for more information, search for optimizing mysql queries (there are pages on the mysql.com site as well as many others on the web)

6

Thanks for the info. Much appreciated.

7

Just to add, if you have a php array you can also use serialize() to turn it into a string and store that. unserialize() will convert it back to its array when pulled back out.