FetchAll(PDO::FETCH_ASSOC) nothing is being returned

Question

i use this function to call my database

function get_prescriptionWith_4($ID){
    $sql = "SELECT prescriptions FROM tblprescriptions WHERE `ID`='$ID'";
    $query = $this->dbh->prepare($sql);
    $query->execute();
    $row = $query->fetchAll(PDO::FETCH_ASSOC));
    return @$row;
}

my database table looks like this..

ID   |       prescription
1    |    Med  (gen) 20 -- #20/1X per day --morning--for  30 days/,
1    |    test (test) 23 -- #343/1X per day --morning and noon-- 120 days23/,   
          samp (rere) 44 -- #34/1X per day --morning-- 7 days44/, 
1    |    asd (asd) 22 -- #222/3X per day --morning noon and evening--30 days/,   
          qw (wq) 44 -- #222/3X per day --morning noon and evening--60 days/, 
          1233 (123) 21 -- #123213/1X per day --morning---  60 days/,

what i wanted to achieve is to load all prescription with the same id and since row prescription have multiple data inside it i use explode to separate the data with , when i tried to use Fetch(PDO::FETCH_ASSOC) i only returns the first prescription i think that using fetchAll might be better to fetch all data from my database..

$pres_array3=$patient->get_prescriptionWith_4($ID);
$new_array3=explode(',',$pres_array3,-1);

but when i use echo $new_array3 to check nothing is being return if it is on fetchAll

Errrm sorry, that is not a properly prepared statement at all to begin with. Debugging is easy though, instead of returning the data back you can run some error checking functions to see if any pdo errors are being returned — Hanky Panky, May 24 '17 at 06:29
@HankyPanky the prepared statement is valid, OP is only vulnerable to SQL injections. Please use parameterized statements instead. — Tom Udding, May 24 '17 at 06:31
that is actually fetch(PDO::FETCH_ASSOC) to begin with but my problem is if i use fetchAll nothing is being returned that is the reason why i posted that since i think that is my problem.. — Lion Smith, May 24 '17 at 06:31

score 3 · Answer 1 · edited May 24 '17 at 06:35

3

1) Your prepared statement is wrong/misused

$sql = "SELECT prescriptions FROM tblprescriptions WHERE `ID` = :id";
$query = $this->dbh->prepare($sql);
$query->execute([':id' => $ID]);

2) $query->fetchAll(PDO::FETCH_ASSOC) returns array, so $row['prescriptions'] does not exist and it would throw warning of undefined index prescriptions. A correct version would be

if($row = $query->fetch(PDO::FETCH_ASSOC)){

edited May 24 '17 at 06:35

Hanky Panky

46,730
8
72
95

answered May 24 '17 at 06:32

Justinas

41,402
5
66
96

`$sql = "SELECT prescriptions FROM tblprescriptions WHERE `ID` = :id"; $query = $this->dbh->prepare($sql); $query->execute([':id' => $ID]); $row = $query->fetchAll(PDO::FETCH_ASSOC)); return @$row;` is this correct to return all row data ?? – Lion Smith May 24 '17 at 06:34
Please don't mind the edit. +1 – Hanky Panky May 24 '17 at 06:36
what i actually wanted to is to fetch all the data with the same id.. that is the reason why i use fetchAll and as you said that the approach i made is incorrect.. – Lion Smith May 24 '17 at 06:44
i updated my function to give you better understanding on what i want to achieve.. – Lion Smith May 24 '17 at 06:45
1

@LionSmith Why even your `id` is non-unique? ID must be auto-incremented or at least unique for faster database work and enabling indexing – Justinas May 24 '17 at 06:56
@Justinas it is unique tho. i just make it simple.. – Lion Smith May 24 '17 at 07:10
i ithink fetchAll is not the best approach to my question thanks by the way for giving me additional information :D really appreciated – Lion Smith May 24 '17 at 07:13
@LionSmith So if ID is unique, than you will always get max 1 match from database, why `fetchAll` then? – Justinas May 24 '17 at 07:29
check the answer.....i only tried fetchAll to think that this is the best way to achieve what i want.. – Lion Smith May 24 '17 at 07:31

score 0 · Answer 2 · answered May 24 '17 at 07:18

so since fetchAll(PDO:ASSOC) gives me nothing...i use this approach instead..

$prescriptions = array();
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
        $prescriptions[] = $row;
    }
return $prescriptions;

and then $pres_array3=$patient->get_prescriptionWith_4($patientID);

<?php 
    foreach ($pres_array3 as $key => $value) {
       $prescription =  $value['prescriptions'];
       $new_array3=explode(',',$prescription,-1);
        foreach($new_array3 as $value) {    

?>
<input type="text" value="<?php echo $value?>"  />

<?php } } ?>

the reason why i use multiple loop is because on the 1st loop i get all the array data and then explode it to separate the data then loop again since the explode return array not the element..

Just for reference this understanding is incorrect *fetchAll(PDO:ASSOC) gives me nothing*. If `fetch()` is giving you something on a dataset then 100% gauranteed `fetchAll()` will also give *something* — Hanky Panky, May 24 '17 at 08:29
One reason you didn't get any result from it is that this statement has a syntax error `$row = $query->fetchAll(PDO::FETCH_ASSOC));` — Hanky Panky, May 24 '17 at 08:30

FetchAll(PDO::FETCH_ASSOC) nothing is being returned

2 Answers2