How to show all DB result with PDO

Question

So, I just started with PDO and the connection and stuff work great, but now I have a little problem. I'm stuck on a part where I want to show 6 results per table. My code is as following:

<?php
$sql = "SELECT * FROM db WHERE id BETWEEN 1 AND 6";
$stmt->bindParam(':userName', $userName);
$stmt->bindParam(':hours', $hours);
try {
   $stmt = $conn->prepare($sql);
   $result = $stmt->execute($parameters);
} while($row = $result->fetch_assoc()){ ?>
   <tr>
       <td><b><?php echo $row['hours'] ?></b></td>
       <td><a href="#"></a></td>
       <td id="dayhour-1">
           <input placeholder="Name" type="text" class="form-control" id="1" value="<?php echo $row['userName'] ?>">
       </td>
   </tr>
<?php  } $stmt->close(); ?>

DB connecting:

<?php
$db_host = "localhost";
$db_name = "xxx";
$db_user = "xxx";
$db_pass = "xxx";
$db_opts = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);
$conn    = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, $db_opts);
?>

When I go to the webpage, it is showing the well-known 500 error. I have no idea what I am doing wrong because I am a starter.
Please let me know what I am doing wrong and how I can solve this problem.

UPDATED CODE

<?php 
$sql = "SELECT * FROM db WHERE id BETWEEN 1 AND 6"; 

$stmt->bindParam(':userName', $userName); 
$stmt->bindParam(':hours', $hours); 

try { 
    $stmt = $conn->prepare($sql); 
    $result = $stmt->execute($stmt); 
} catch ($row = $result->fetch() { 

?> 

<tr>
    <td><b><?php echo $row['hours'] ?></b></td>
    <td><a href="#"></a></td>
    <td id="dayhour-1">
        <input type="text" value="<?php echo $row['userName'] ?>">
    </td>
</tr> 

<?php } $stmt->close(); ?>

you're also mixing different mysql apis and Lord only knows which one is used to connect with — Funk Forty Niner, Mar 09 '17 at 16:41
Looking at your server's error logs always helps in debugging. The error message is there. — aynber, Mar 09 '17 at 16:42
stick with the one api, PDO. You used `fetch_assoc()` which is mysqli_, not PDO and what I said earlier about the not using the named placeholders in values — Funk Forty Niner, Mar 09 '17 at 16:47
5XX errors are caused by server. even though you have some mistakes in your code & it won't work even in a well configured server — behkod, Mar 09 '17 at 16:47

Willem-Jelle · Accepted Answer · 2017-03-13T20:21:24.827

The answer is as follows:

<?php
                            $sql = "SELECT * FROM database WHERE id BETWEEN 1 AND 5";
                            $stmt = $conn->query($sql);
                            $stmt->execute();
                            while($row = $stmt->fetch(PDO::FETCH_OBJ)){
                            ?>
                                <tr>
                                    <td><b><?php echo $row->hours ?></b></td>
                                    <td><a href="#"></a></td>
                                    <td id="dayhour-1">
                                        <input type="text" class="form-control" id="1" value="<?php echo $row->username ?>">
                                    </td>
                                </tr>
                            <?php } ?>

This code will do it's work. Its completely perfect PDO and it works 100%. A friend helped my out.

Good to hear you got a solution! – Spholt Mar 14 '17 at 14:46 — Spholt, Mar 14 '17 at 14:46

score -1 · Answer 2 · edited Mar 11 '17 at 00:13

-1

Ok, so i can see an error here to start with:

try() {} while() {} is not going to work.

This is combining syntax from do {} while() and try {} catch() {}. This will be throwing a 500 internal server error.

Secondly, as per the comments, you will want to standardize on one database connector. I would probably go with PDO.

http://php.net/manual/en/intro.pdo.php

EDIT

Here is an example of a prepared statement using PDO

<?php

// Connect to your database
$pdo = new PDO("mysql:host=localhost;dbname=my_database", "username", "password")
        ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare your statement
$stmt = $pdo->prepare("SELECT * FROM db WHERE id BETWEEN ? AND ?");

// Execute the statement
$results = $stmt->execute([1,6])->fetchAll(PDO::FETCH_ASSOC);

// Loop through your resuls
foreach ($results as $row) {
    ?>
        <tr>
            <td><b><?php echo $row['hours'] ?></b></td>
            <td><a href="#"></a></td>
            <td id="dayhour-1">
                <input type="text" value="<?php echo $row['userName'] ?>">
            </td>
        </tr>
    <?php
}

edited Mar 11 '17 at 00:13

halfer

19,824
17
99
186

answered Mar 09 '17 at 16:52

Spholt

3,724
1
18
29

Well, @Spholt , I tried the following but wont work: `bindParam(':userName', $userName); $stmt->bindParam(':hours', $hours); try { $stmt = $conn->prepare($sql); $result = $stmt->execute($stmt); } catch ($row = $result->fetch(){ ?> close(); ?>` What I am doing wrong – Willem-Jelle Mar 09 '17 at 17:38
Please don't just say "It won't work".... please give the EXACT error message that you get. Imagine yourself in the position of a support person trying to help someone who only says "It doesn't work...." :-) – Ray O'Donnell Mar 09 '17 at 18:52
1

@Willem-Jelle I have taken you code and it still looks like you have basic syntax errors. I would recommend using a text editor or IDE with a linter to highlight these problems. – Spholt Mar 10 '17 at 16:41
I did not give your reaction a dislike :o. BTW if I put in your code, I dont get a page error (5** error), but the information won't show up too. Thanks for the efford, really appreciate it bro @Spholt – Willem-Jelle Mar 10 '17 at 20:40
hmm, have you tried turning on error reporting at the top of your script? `ini_set('display_errors', 1);` `ini_set('display_startup_errors', 1);` `error_reporting(E_ALL);`. It will give us a better idea of what is going on. – Spholt Mar 10 '17 at 21:18
@Spholt: please don't add voting commentary to posts. It's fine to add it into comments, though I don't think it has any effect! – halfer Mar 11 '17 at 00:14
@halfer Fair point. I'm just getting a little fed up with answering questions and either getting no credit or getting random down votes :( – Spholt Mar 11 '17 at 00:22
Hmm, it is not clear why people have voted that way. Sometimes if a question quality is very poor (e.g. unresearched) then people downvote answers on the basis that lazy question askers should not be helped. However that does not seem to be the case here. Your answer seems OK to me; it has an XSS vulnerability, but that comes from the question. – halfer Mar 11 '17 at 00:25
@Spholt I already tried to do do it with the php errors, but it gave me the same reaction. Error 500.. – Willem-Jelle Mar 13 '17 at 18:16
@Spholt OMG! I have the solution. Will Place it myself. – Willem-Jelle Mar 13 '17 at 19:43

How to show all DB result with PDO

2 Answers2

Linked