Trying to retrieve data from mySQL to php array

Question

I have been working on a project recently. I have a DB where I have saved various elements such as id, title, imgUrl, intro and title. I want to retrieve those data from the MySQL database to a PHP array as follows:

<?php

    $slides = array(
        array(
            'id' => 1,
            'title' => '',
            'intro' => '',
            'imgUrl' => '',
            'content' => ''
        ),
        array(
            'id' => 2,
            'title' => '',
            'intro' => '',
            'imgUrl' => '',
            'content' => ''
        ),
?>

I will use this code to display it in another PHP/HTML file that I have already created as follows:

<?php foreach ($slides as $slide): ?>
<div class="swiper-slide" id="slider<?= $slide['id']; ?>">
    <div class="card">
      <div class="card_image">
         <img src="<?= $slide['imgUrl']; ?>">
      </div>
      <div class="card_info">
         <h3 class="card_title">
            <?= $slide['title']; ?>
         </h3>
         <p>
           <?= $slide['intro']; ?>
         </p>
         <button data-modal-target="#modal<?= $slide['id']; ?>" class="card_btn">
            <h4>Learn More</h4>
         </button>
      </div>
   </div>   
</div>
<?php endforeach; ?>

I am still new to PHP array and database but as per my project, I have to use those and I was wondering if there is any way for me to retrieve my data element from MySQL and display it in the PHP array I have created above.

Collect data from your DB using the **SELECT** statement. Then loop through the collected array and save those values into the $slides array. That's it. — dspillai, May 30 '21 at 14:03

score 0 · Answer 1 · edited May 31 '21 at 10:26

Depending what you use will look like this:

<?php
$query = "SELECT name, colour FROM fruit";

// using PDO
$sth = $dbh->prepare($query);
$sth->execute();
$results = $sth->fetchAll(PDO::FETCH_ASSOC);
print_r($results);


// using mysqli
$stmt = $mysqli->prepare($query);
$stmt->execute();
$result = $stmt->get_result();
$resultsArray = $result->fetch_all(MYSQLI_ASSOC);
print_r($results);

PDO fetch doc – https://www.php.net/manual/en/pdostatement.fetch.php

Mysqli doc – https://www.php.net/manual/en/mysqli-result.fetch-assoc.php

But I'd strongly recommend to use some sort of ORM to avoid some errors and security issues.

score -1 · Answer 2 · edited May 31 '21 at 10:22

First of all, you need to create a connection to the DB. If you have done then its great if not then follow the below code:

$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Then you can write a query like :

$sql = "Select id,title,imgUrl,content,intro from table_name";
OR
$sql = "Select id,title,imgUrl,content,intro from table_name where *condition*";

For running this query use $conn->query(), like :

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

Then check whether $slides have returned any row or not by using:

if ($data->num_rows > 0) {
   $slides = $data->fetch_assoc();
   // and now use in foreach like:
   foreach($slides as $slide){
    echo $slide['id'];
  }
}

Trying to retrieve data from mySQL to php array

2 Answers2