www.webdeveloper.com
Results 1 to 7 of 7

Thread: Fetch all rows from mysql to an array

  1. #1
    Join Date
    Jun 2013
    Posts
    20

    Fetch all rows from mysql to an array

    hi.

    i'm pretty new to web programming and programming in general.

    what i want is to fetch all the rows from a selected query in an array in wich every element ([0], [1], [2],...) is an associative array representing a row.
    how can i do that?
    is that a better method to extract rows from mysql.

    here is what i have written yet but i't not working.

    PHP Code:
        try
        {
            
    $sql "SELECT Data, Readed, Title AS Title FROM news, teams, users, prepared_news WHERE news.Template_news = prepared_news.id AND news.Id_team = teams.id AND teams.Id_user = users.id AND users.id = :Id ORDER BY Data DESC LIMIT(1,10)";
            
    $s $connection->prepare($sql);
            

            
    $s->bindValue(':Id'$_SESSION['Currentuserid']);
            

            
    $s->execute();
            
                foreach (
    $s AS $row)
                {    
                    
    $news_title_array[] = array(
                        
    'Data_notizia' => $row['Data'],
                        
    'Readed' => $row['Readed'],
                        
    'Title' => $row['Title']
                    );

                }    

        } 

  2. #2
    Join Date
    Apr 2010
    Posts
    88
    You could use the PDO::FETCH_ASSOC fetch style.

    Try putting the following after the $s->execute();

    PHP Code:
    $result $s->fetchAll(PDO::FETCH_ASSOC); 

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,321
    Quote Originally Posted by gvre View Post
    You could use the PDO::FETCH_ASSOC fetch style.

    Try putting the following after the $s->execute();

    PHP Code:
    $result $s->fetchAll(PDO::FETCH_ASSOC); 
    This would generally be my recommendation, too, with the caveat that if a possibility exists that the query could return a very large number of rows (for some undefined value of "very large"), you could end up using a lot of memory in your PHP app, made worse by the fact that PHP arrays are not very memory-efficient -- but that's more a question of whether or not you want/need to create one result array, versus whether or not to use the above technique to do so.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Jun 2013
    Posts
    20
    Quote Originally Posted by NogDog View Post
    This would generally be my recommendation, too, with the caveat that if a possibility exists that the query could return a very large number of rows (for some undefined value of "very large"), you could end up using a lot of memory in your PHP app, made worse by the fact that PHP arrays are not very memory-efficient -- but that's more a question of whether or not you want/need to create one result array, versus whether or not to use the above technique to do so.
    mysql will never return more than 10 rows because of the limit clause i think.
    by the way:
    my $sql is changed now and it is:

    $sql = "SELECT Data_notizia, Readed, :wich_title AS Title FROM system_news, teams, users, prepared_news WHERE system_news.Template_news = prepared_news.id AND system_news.Id_team = teams.id AND teams.Id_user = users.id AND users.id = :Id ORDER BY Data_notizia DESC LIMIT :start_with, 10";
    $s = $connection->prepare($sql);

    $s->bindValue(':wich_title', $_POST['type_news']);
    $s->bindValue(':Id', $_SESSION['Currentuserid']);
    $s->bindValue(':start_with', $_POST['from'], PDO::PARAM_INT);

    $s->execute();
    foreach ($s AS $row)
    {
    $news_title_array[] = array(
    'Data_notizia' => $row['Data_notizia'],
    'Readed' => $row['Readed'],
    'Title' => $row['Title']
    );


    the mysql send to me this error: Error1exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', 10' at line 1' in C:\xampp\htdocs\volleyballmanager\login\extract_news_title.php:91


    i think this is because the bindvalue function puts quotes araund :start_with.
    how can i avoid that problem?

    THX GUYS!

  5. #5
    Join Date
    Apr 2010
    Posts
    88
    You must cast the 'from' value to int.
    PHP Code:
    $s->bindValue(':start_with', (int)$_POST['from'], PDO::PARAM_INT); 
    ps. You could replace the foreach with the more efficient fetchAll(PDO::FETCH_ASSOC)

  6. #6
    Join Date
    Jun 2013
    Posts
    20
    Quote Originally Posted by gvre View Post
    You must cast the 'from' value to int.
    PHP Code:
    $s->bindValue(':start_with', (int)$_POST['from'], PDO::PARAM_INT); 
    ps. You could replace the foreach with the more efficient fetchAll(PDO::FETCH_ASSOC)

    fantastic, now it works, i only have to discover how to "echo" the results back to my jquery post function and this is it.

    how would the syntax be and what's PDO::FETCH_ASSOC do, why it's better than foreach?

    sorry for so much questions!

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,321
    What you return depends on what your jQuery expects, but typically JSON objects are used, so you could do:
    PHP Code:
    header('Content-Type: application/json');
    echo 
    json_encode($yourArrayHere);
    exit; 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles