Member Avatar for junaid_5 
  DATE        ads visible
  2014-06-23   3    1
  2014-06-13   1    1
  2014-06-12   4    1
  2014-06-10   1    1
  2014-06-09   7    1
  2014-06-08   12   0
  2014-06-07   12   1

i have these three colums selected in mysql

through php i want to display the result like above on front end how can loop through all these columns after this check

if (!empty($adsList['ads']) and $adsList['visible']==1)
// display data here

Edited by junaid_5
  • 2 Contributors
  • 3 Replies
  • 254 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by junaid_5

Dani AI

Generated

A straightforward, maintainable approach is to let the database return exactly the rows needed already ordered and (optionally) aggregated, then render them with a simple PHP loop. As pointed out, filtering in SQL keeps the PHP loop simple; add an ORDER BY on the date column so rows come back newest-first. If multiple rows share the same date and the intent is a single line per date, aggregate in SQL (SUM/COUNT) rather than trying to sum in PHP.

Example SQL (one-line-per-date summary):

SELECT `date`, SUM(`ads`) AS ads_total
FROM `my_table`
WHERE `visible` = 1
GROUP BY `date`
ORDER BY `date` DESC;

If there is only one row per date, omit the SUM and GROUP BY and just ORDER BY date DESC.

Minimal procedural-style PHP loop to render results and format the date:

$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    $date = (new DateTime($row['date']))->format('Y-m-d');
    $ads  = (int)$row['ads_total'];
    if ($ads > 0) {
        echo $date . "   " . $ads . "\n";
    }
}

Practical cautions and tips: wrap date in backticks or rename it (it can conflict with SQL functions), store dates in DATE/DATETIME columns so ORDER BY works correctly, use STR_TO_DATE only if dates are stored as text, and add an index on the date and visible columns for performance. For dynamic inputs use prepared statements. Since marked the problem solved, these points help if the dataset grows or if grouping/formatting needs change.

Recommended Answers

All 3 Replies

Member Avatar for andrevanzuydam

Just a question, why not filter the results when you do your query with a where statement "select date, ads, visible from table where visible = 1".

Secondly, how are you fetching the data to your PHP ?, most common way to get your results out would be :

$result = $mysqli->query($query)

//start a table here

$icount  = 0;
while ($record = $result->fetch_object()) {
     //make the table row

     if ($record->visible == 1 && $record->ads != 0) {
       echo " $icount {$record->DATE} {$record->ads} {$record->visible} ";   
     }
     //close the table row
     $icount++;

}

//end table here
Member Avatar for junaid_5

please can you convert this code in simple php i want to edit but dont understand this arrow language :p .

Member Avatar for junaid_5

i got it done thanx :p

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.