CODEWITHSUNDEEP
phpmysqlmysqli
RWN
RWN

Reputation: 99

Select every item in a SQL column into an array, PHP

I've hit a little wall in something I'm trying to do.

I have a table, entitled 'projects', in a database in SQL.

My issue is that I can't seem to select every row in a specific column.

This is the code that I've got so far relevant to this question (keep in mind that '$conn' is the connection to the SQL database, and the include command on the second line is just to include that connection):

            //CONNECTION FILE   
        include "../include/dbh-inc.php";
        //ASSIGNING QUERY VALUES TO VARIABLES
        $q1 = mysqli_query($conn,"SELECT pid FROM projects;");
        $q2 = mysqli_query($conn,"SELECT ptitle FROM projects;");
        $q3 = mysqli_query($conn,"SELECT plink FROM projects;");
        //SETTING ARRAYS FROM VARIABLES
        $ids = mysqli_fetch_array($q1);
        $titles = mysqli_fetch_array($q2);
        $links = mysqli_fetch_array($q3);

        mysqli_close($conn);
        file_put_contents("newtest.txt","$titles\n");
        foreach($titles as $title) {
            file_put_contents("newtest.txt","$title\n", FILE_APPEND); //Testing
        }
        if(count($ids) > 0) {
            file_put_contents("TEST.txt","");
            for($i = 0; $i < count($ids)-1; $i+=1) {            
                $count = count($ids);
                $title = $titles[$i];
                $id = $ids[$i];
                $link = $links[$i];
                file_put_contents("TEST.txt","$i: $title, $id ($count), $link\n",FILE_APPEND);
                echo("
                <div class=\"projlink\">
                <a href=\"$link\" style=\"width:190px;height:90px\">
                <h2>$title</h2>
                </a>
                </div>
                ");

            }
        }
        else {
            echo("
                <h2>Nothing here currently!<br>Be patient!</h2>
            ");
        }

Any thoughts?

Thanks! - Raphael

Upvotes: 1

Views: 3425

Answers (1)

AbraCadaver
AbraCadaver

Reputation: 78994

You are only fetching one row with mysqli_fetch_array. Also, you can get all columns in one query:

$q1 = mysqli_query($conn,"SELECT pid, ptitle, plink FROM projects;");
$rows = mysqli_fetch_all($q1, MYSQLI_ASSOC);

If you don't have mysqli_fetch_all then:

while($rows[] = mysqli_fetch_assoc($q1));

Then just loop $rows and use the columns:

foreach($rows as $row) {
    echo $row['pid'] . ' ' . $row['ptitle'] . ' ' . $row['plink'];
}

If you really need separate arrays (this is rarely necessary):

$ids = array_column($rows, 'pid');
$titles = array_column($rows, 'ptitle');
$links = array_column($rows, 'plink');

Upvotes: 3

Related Questions