phpmysqlsqlarraysvariables

How can I store MySQL results in different individual variables with one connection to the database?


I am currently working on a personal project and I need your help. After a lot of research I can't seem to find a proper solution to my problem (probably because I am not very good PHP developer - I am still learning).

I need to get 3 post titles from my database and store each one of them in individual variables. I need them to be individual because I want to use them in different parts of my website. I have managed to do this but only by doing three different queries to the database which I suppose is bad. Is there a way to send one query to the database and store them at once inside different variables? I tried to do this with an array and although I was close enough I didn't seem to get this working.

Here is my code:

try {
    $stmt = $db->query('SELECT postTitle FROM blog_posts ORDER BY postID DESC limit 0,1');
    $sslider_title1='';
    while($row = $stmt->fetch()){
        $sslider_title1 = $row['postTitle'];
    }
}   
catch(PDOException $e) {
    echo $e->getMessage();
}
try {
    $stmt = $db->query('SELECT postTitle FROM blog_posts ORDER BY postID DESC limit 1,2');
    $sslider_title2='';
    while($row = $stmt->fetch()){
        $sslider_title2 = $row['postTitle'];
    }
}
catch(PDOException $e) {
    echo $e->getMessage();
}
try {
    $stmt = $db->query('SELECT postTitle FROM blog_posts ORDER BY postID DESC limit 2,3');
    $sslider_title3='';
    while($row = $stmt->fetch()){
        $sslider_title3 = $row['postTitle'];
    }
}
catch(PDOException $e) {
    echo $e->getMessage();
}

So in order to get this running with one query I must do

try {
    $stmt = $db->query('SELECT postTitle FROM blog_posts ORDER BY postID DESC limit 0,3');
    $sslider_title1='';
    $sslider_title2='';
    $sslider_title3='';
    while($row = $stmt->fetch()){
        \\ This is the part that I can't seem to solve
    }
} 

Solution

  • Please, do not use variables with such names. What you need is array:

    $titles = array();
    try {
        $stmt = $db->query('SELECT postTitle FROM blog_posts ORDER BY postID DESC limit 0,3');
        while($row = $stmt->fetch()){
            $titles[] = $row['postTitle'];
        }
    } 
    

    Then in your code you can use

    echo $titles[0];
    echo $titles[1];
    echo $titles[2];
    

    for each of you titles.