Get data from mysql database inside for loop with php pdo

Question

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

why not just get all the rows you need and then loop through the results? instead of hitting the database 30 times? — gloomy.penguin
– gloomy.penguin, Commented Nov 4, 2014 at 17:52
You're making 30 database requests instead of one? What if someone deletes an activity? Your code stops working then. — Jack M.
– Jack M., Commented Nov 4, 2014 at 18:09
the for loop is 30 times becouse in database I have stored days in month, so I dont know in which day i have activity so I create loop to go trought mysql table aktivnosti and catch data... — James D.
– James D., Commented Nov 4, 2014 at 18:10

Mike Brant · Accepted Answer · 2014-11-04 18:29:12Z

2

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
   }
}

edited Nov 4, 2014 at 18:29

answered Nov 4, 2014 at 18:22

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

James D. Over a year ago

Mike, I need to create a some kin of vertical calendar... SO the first column is date (days in month 1-30) and second column is activity, so for each day I need to search is there some activity or not, if not I create <button>Add activity</button> ... s its not answer for my problem

Mike Brant Over a year ago

@JamesD. It is actually. In that the fact that you would LEFT JOIN your static list of days to the records themselves. You would get NULL values for the items in date table where there is no record for that day. I will add example to my code.

James D. Over a year ago

ok, I will chech if data == NULL then I create html to show on that day

James D. Over a year ago

but while is the same as for loop... I need to create this: imgur.com/mXr3ax7 so its datatable with column with date and column with activity

user3931708 Over a year ago

check the answer, I update it, the while works like I use it in the above answer...

|

mpyw · Accepted Answer · 2014-11-04 18:34:20Z

1

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'));

edited Nov 4, 2014 at 18:34

answered Nov 4, 2014 at 18:15

mpyw

5,7744 gold badges35 silver badges36 bronze badges

3 Comments

James D. Over a year ago

Can you rewrite my code, becouse is not usefull when I put in my app

mpyw Over a year ago

...Do you really need ID?

mpyw Over a year ago

So, use second snippet. You can get following json: {"data":["<button>$naz[0]</button>","<button>$naz[1]</button>",...,"<button>$naz[29]</button>"]}

score 1 · Accepted Answer · 2014-11-04 18:39:02Z

1

try this...

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

{

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

}

instead of

 $naz = $rs1->fetchColumn();

edited Nov 4, 2014 at 18:39

answered Nov 4, 2014 at 18:19

user3931708

5 Comments

James D. Over a year ago

i get this error: Catchable fatal error: Object of class stdClass could not be converted to string in /home/agroagro/public_html/template/tema/table1.php on line 45

James D. Over a year ago

$rs1 = $db->prepare('SELECT naziv FROM aktivnosti WHERE id_activity=:idd'); $rs1->bindParam(':idd', $i); $rs1->execute(); while($naz=$rs1->fetch(PDO::FETCH_OBJ)) { $temp['vrsta'] = '<button class="btn btn-danger">'.$naz.'</button>'; } $output['data'][] = $temp; $i++; } $jsonTable = json_encode($output);

user3931708 Over a year ago

try this $rs1 = $db->prepare('SELECT naziv FROM aktivnosti WHERE id_activity=:idd'); $rs1->bindParam(':idd', $i); $rs1->execute(); while($naz=$rs1->fetch(PDO::FETCH_OBJ)) { $temp['vrsta'] = '<button class="btn btn-danger">'.$naz.'</button>'; } $output['data'][] = $temp; $i++; $jsonTable = json_encode($output);

James D. Over a year ago

i get: Parse error: syntax error, unexpected 'catch' (T_CATCH) in /home/agroagro/public_html/template/tema/table1.php on line 43

user3931708 Over a year ago

$rs1 = $db->prepare('SELECT naziv FROM aktivnosti WHERE id_activity=:idd'); $rs1->bindParam(':idd', $i); $rs1->execute(); while($naz=$rs1->fetch(PDO::FETCH_OBJ)) { $temp['vrsta'] = '<button class="btn btn-danger">'.$naz.'</button>'; } $output['data'][] = $temp; $i++; $jsonTable = json_encode($output);

Collectives™ on Stack Overflow

Get data from mysql database inside for loop with php pdo

3 Answers 3

17 Comments

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

17 Comments

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related