CODEWITHSUNDEEP
phphtmlmysqldatabase
victormoretti
victormoretti

Reputation: 3

How to echo a dynamic html snippet based on a MySQL databse with PHP?

I'm trying to create an image gallery with a for loop iterated by a counter of database rows. To make it more clear: for each row in the table, get only the id(primary index) number and the image link from the server (not all the info in the row). With that information, echo an HTML image tag with the link inside the 'src=' and the id inside the 'alt='.

Two problems here:
1- the id number of the first row isn't zero.
2- I don't have a clue on how to get the total number of rows and to fetch only those two informations (id and img source).

That way, I could subtract the total number of rows minus the id number of the first row and using it to put an end on the loop.

So how to echo this dynamic html snippet based on my databse with PHP?

My code:

<?php

$link = mysqli_connect('localhost','user','pass','db');
$result = mysqli_query($link, "SELECT * FROM `table`");
$rows = mysqli_num_rows($result);

/* free result set */
mysqli_free_result($result);

$caption = mysqli_query($link, "SELECT ");

for($i=0; $i < $rows; $i++) {
    echo "<img src='$imageURL' alt='$idNumber'>";
}

?>

Upvotes: 0

Views: 344

Answers (3)

Andrew Coder
Andrew Coder

Reputation: 1058

You need to use sql functions to iterate through the dataset results. Replace your for loop... replacing 'image_url_column' and 'id_number_column' with the name of your actual columns in your db:

while ($row = while ($row = mysqli_fetch_assoc($caption)){){
    echo "<img src='".$row['image_url_column']."' alt='".$row['id_number_column']."'>";
}

Upvotes: 1

Ikhlak S.
Ikhlak S.

Reputation: 9034

This is a really easy task.

First we fetch the data from the database using mysqli_query to do the query.

Then we use mysqli_fetch_array to get an array so then we can loop through it and echo each item.

After that, we mysqli_num_rows to get the total number of rows returned and increment it by 1 so it is not zero.

NOTE: Since you are going to increment the id to avoid getting a '0', don't to forget to minus '1' if you intend to use that id for some server-side purpose.

$result = mysqli_query($link, "SELECT * FROM `table`"); //query sql
$result_array=mysqli_fetch_array($result, MYSQLI_ASSOC);//return array from the query
$count = mysqli_num_rows($result);                      //get numbers of rows received 

foreach($result as $row){ //do a foreach loop which is really simple
    echo "<img src='". $row['img_column_name_from_db'] . "'  alt='" .$row['id_column_name_from_db'] + 1 . "'>"; //echo data from the array, + 1 to "$row['id_column_name_from_db']" so that 'alt=' doesn't start from '0'.
    } 
echo $count;

Upvotes: 0

Murad Hasan
Murad Hasan

Reputation: 9583

  1. You can use the count function of MySql to achieve the total number of rows.
  2. also you can do this via mysqli_num_rows() function of mysql.

Code:

<?php

$link = mysqli_connect('localhost','user','pass','db');

$caption = mysqli_query($link, "SELECT id, img, count(id) as total from table");
echo 
//$rows = mysqli_num_rows($result); 
while($rows = mysqli_fetch_assoc($caption)){
    echo "<img src='$rows[img]' alt='$rows[id]'>";
    echo "Total Rows: ".$rows[total];
}

?>

Upvotes: 0

Related Questions