CODEWITHSUNDEEP
phpmysqljsonpdo
James D.
James D.

Reputation: 141

Get data from mysql database inside for loop with php pdo

I have this php code - for loop and on every step , every increment search for data in mysql table aktivnosti

PHP:

for ($i=1; $i<=30; $i++;){
    $temp = array();
    $temp['ID'] = $i;

// ATTEMP TO GET DATA FROM aktivnosti WHERE id_activity = $i

    $rs1 = $db->prepare('SELECT naziv FROM aktivnosti WHERE id_activity=:idd');
          $rs1->bindParam(':idd', $i); 

          $rs1->execute();
          $naz = $rs1->fetchColumn();

          $temp['activity'] =  '<button>'.$naz.'</button>';



    $output['data'][] = $temp;

}
$jsonTable = json_encode($output);

So as you can see from code above I try to get data on every $i increment and search if id_activity on table aktivnosti = $i

I get just one result so I get just first 'naziv', I need to get all naziv data from table aktivnosti where id_activity = $i and create:

<button>$naz[0]<button>
<button>$naz[1]<button>
<button>$naz[2]<button>
<button>$naz[how many times id_activity = $i]<button>

How I can do that? Some ideas?

sorry for my engish. Thanks

Upvotes: 1

Views: 957

Answers (3)

user3931708
user3931708

Reputation:

try this...

 while($naz=$rs1->fetch(PDO::FETCH_OBJ))

{

echo $naz->column1;
echo $naz->column2;

}

instead of

 $naz = $rs1->fetchColumn();

Upvotes: 1

mpyw
mpyw

Reputation: 5754

If you need both ID and activity:

$sql = <<<EOD
SELECT
    id_activity AS ID,
    CONCAT('<button>', naziv, '</button>') AS activity
FROM aktivnosti
WHERE id_activity BETWEEN 1 AND 30
ORDER BY id_activity
EOD;
$data = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$jsonTable = json_encode(compact('data'));

If you only use activity:

$sql = <<<EOD
SELECT CONCAT('<button>', naziv, '</button>')
FROM aktivnosti
WHERE id_activity BETWEEN 1 AND 30
ORDER BY id_activity
EOD;
$data = $db->query($sql)->fetchAll(PDO::FETCH_COLUMN, 0);
$jsonTable = json_encode(compact('data'));

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

As pointed out in comments above, you are taking a bad approach here. You should be able to get all this data in a single query. You probably also need to take a look at your schema if you want to have the concept of a fixed number of 30 days with each days related to n number of records. I would suggest two tables

day_list

day_id  day_name (or any other day-related data fields)
1       ...
2       ...
...     ...
30      ...

days_records

record_id   day_id   other_data
1           1        ...
2           1        ...
3           3        ...
4           5        ...
...

You would then query this like:

SELECT
    d.day_id AS day_id
    dr.record_id AS record_id
    dr.other_date AS other_data
FROM day_list AS d
LEFT JOIN day_records AS dr
    ON d.day_id = dr.day_id

Sorry for the change in table names, as don't know what your database schema represents in real-world terms.

You then make a single query like:

$query = <<<EOT
SELECT
    d.day_id AS day_id
    dr.record_id AS record_id
    dr.other_date AS other_data
FROM day_list AS d
LEFT JOIN day_records AS dr
    ON d.day_id = dr.day_id
EOT;

$rs1 = $db->execute($query);
if (false === $rs1) {
   // something went wrong. perhaps log an error
} else {
   while($row = $rs1->fetch(PDO::FETCH_ASSOC)) {
        $temp = $row;
        // check to see if this date has a record
        if (empty($temp['record_id'])) {
            // this is a day with no associated record.
            // do something
        }
        // not shown - continue to manipulate your $temp as desired
        // then add to output array
        $output['data'][] = $temp
   }
}

Upvotes: 2

Related Questions