SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting an array into MySQL

    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:

    PHP Code:
        {
                
    $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($urltrue);
                    
    $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. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    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.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the info. Much appreciated.

  7. #7
    SitePoint Guru
    Join Date
    Aug 2009
    Posts
    669
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    I'll do anything to avoid working on my own code

    Are you using: if (isset($_POST['submit'])) ?
    IE has a bug and does not always send the value.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •