Simplify php code - connect to a database

Question

Here I have a php code that connects to a database, selects a row by id and creates an associative array from this row using a while loop. Do I have to write this code over and over again to create arrays from other rows by id? Maybe there is a chance to simplify this php code somehow? Please look at my code. BTW I am new in php...

<?php    
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$db = '_erica';
$conn = new mysqli($dbhost, $dbuser, $dbpass,$db);

$sql1 = "SELECT * FROM pics WHERE id = 1;";
$sql2 = "SELECT * FROM pics WHERE id = 2;";
$sql3 = "SELECT * FROM pics WHERE id = 3;";
$sql4 = "SELECT * FROM pics WHERE id = 4;";
$sql5 = "SELECT * FROM pics WHERE id = 5;";
$sql6 = "SELECT * FROM pics WHERE id = 6;";
$result1 = $conn->query($sql1);
$result2 = $conn->query($sql2);
$result3 = $conn->query($sql3);
$result4 = $conn->query($sql4);
$result5 = $conn->query($sql5);
$result6 = $conn->query($sql6);

while($row1 = $result1->fetch_assoc()) {
    $bcgrnd = $row1["link"];
}

while($row2 = $result2->fetch_assoc()) {
    $recipes = $row2["link"];
}

while($row3 = $result3->fetch_assoc()) {
    $header = $row3["link"];
}

while($row4 = $result4->fetch_assoc()) {
    $menu = $row4["link"];
}
while($row5 = $result5->fetch_assoc()) {
    $beauty = $row5["link"];
}

while($row6 = $result6->fetch_assoc()) {
    $kids = $row6["link"];
}

?>

@Kirill Don't forget to accept the answer that helped you the most — simon, Feb 18 '16 at 14:32

simon · Answer 1 · 2016-02-18T13:10:58.933

You can do this in one query:

$sql = "SELECT * FROM pics WHERE id IN (1,2,3,4,5,6);";
$result = $conn->query($sql);

And then you can loop over all results like this:

$data = array();
while ($row = $result->fetch_assoc()) {
    $id = $row["id"];
    $link = $row["link"];
    $data[$id]["link"] = $link;

    // add more fields if you want
}

To access for example the link of ID 1, just do:

$data[1]["link"];

score 1 · Answer 2 · edited May 23 '17 at 10:30

You can write one or two simple functions for this. Moreover, please note that your code is vulnerable to SQL Injection. Here is an example how you can achieve this with some simple functions:

<?php
    function DB() {
        $dbhost = 'localhost';
        $dbuser = 'root';
        $dbpass = '';
        $db = '_erica';
        return new mysqli($dbhost, $dbuser, $dbpass,$db);
    }

    function query($id) {
        $query = "SELECT * FROM `pics` WHERE `id` = $id";
        return DB()->query($query);
    }

    $result = query(1); // will fetch records for ID 1
    while($row = $result->fetch_assoc()) {
        $bcgrnd = $row["link"];
    }

    $result = query(2); // will fetch records for ID 2
    while($row = $result->fetch_assoc()) {
        $bcgrnd = $row["link"];
    }
?>

By adapting this approach, you can fetch data for a specific ID. If you don't like this solution, consider using MySQL IN clause.

Ayaz Ali Shah · Answer 3 · 2016-02-18T12:53:36.813

0

MySQL in() function finds a match in the given arguments, you can use it

select pics where id IN(1,2,3,4,5,6)

edited Feb 18 '16 at 12:53

answered Feb 18 '16 at 12:48

Ayaz Ali Shah

3,453
9
36
68

score 0 · Answer 4 · answered Feb 18 '16 at 12:48

Try this.

 <?php    
    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = '';
    $db = '_erica';
    $conn = new mysqli($dbhost, $dbuser, $dbpass,$db);

    $sql = "SELECT * FROM pics WHERE id IN (1,2,3,4,5,6);";

    $result = $conn->query($sql);

    while($row = $result->fetch_assoc()) {
        $bcgrnd[$row["id"]][] = $row["link"];
    }

    ?>

score 0 · Answer 5 · answered Feb 18 '16 at 12:48

0

Why not try a Query and Limit it to 6 results, it takes up less resources just pulling 6 results:

SELECT * FROM `pics` ORDER BY `[PRIMARY KEY]` LIMIT 6

answered Feb 18 '16 at 12:48

John Hudson

429
1
3
11

Simplify php code - connect to a database

5 Answers5