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

Question

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

Why are you doing multiple queries in the first place? Just do one, and name all the columns whose content you want in the column list of the statement. — C3roe
– C3roe, Commented Oct 12, 2017 at 17:44
I am not able to understand your problem and what u are trying to do — Giacomo M
– Giacomo M, Commented Oct 12, 2017 at 17:45
Hi everyone, sorry for being vague here. Essentially I'm trying to read every item from an SQL table onto an HTML page to display different pages. Using 'plink' as the reference link and 'ptitle' as the display title. — RWN
– RWN, Commented Oct 12, 2017 at 17:47
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…”) The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Oct 12, 2017 at 17:52
@tadman thanks for your advice. Other people have suggested the same thing. I'm fairly inexperienced with SQL so I apologise if my mistakes are based off of very fundamental stuff :) — RWN
– RWN, Commented Oct 12, 2017 at 22:22

AbraCadaver · Accepted Answer · 2017-10-12 17:56:06Z

3

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');

edited Oct 12, 2017 at 17:56

answered Oct 12, 2017 at 17:45

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related