PDO: how to populate html table with rows from database table?

That is a fairly vague error. Is there an error log file that has more details?
You could add try/catch error handling to your code to find out what is going wrong. There is a bit about PDO error handling here, see the section called “Exceptions and PDO”.

In the earlier thread, I gave some example code with the line:

$sql = $dbh->prepare("The Query String");

In that example, the variable $dbh is your database connection, which in youe mysql script you called $link, so make sure you use your connection variable there.

500 happens only when you do your .htaccess wrong or your httpd.conf file. This is neither a PHP error nor mysql error. Usually happens when you don’t know the syntax and put random configurations together.

i think that is wrong. when i delete this line, don`t run error.

$sql->execute([’:predmet’ => $predmet1]);

Ah, yes. I only ever see that on one site that has an iis server rather than Apache, so it has a web.config file instead of .htaccess, meaning I don’t have a clue, and get it wrong when I try setting things up.

That’s odd, how a pdo execute causes a server error :confused:

What happens if do a straight query, instead of prepare then execute?

$sql = $dbh->query(Your Query String Here);

Try this. I cleaned up your snippet. You don’t need $sql->setFetchMode(PDO::FETCH_ASSOC);, I’m not sure what you are trying to do, but that’s not how you are able to fetch arrays.

Furthermore, if($sql->rowCount() != 0) { is a noobie way of checking to see if there are records. If rowCount doesn’t equal to 0, display records? That doesn’t sound right. Either way, you can reduce this line to just if($sql->rowCount()) { because rowCount and num_rows already returns a 1 or a 0. 1 meaning true and 0 meaning false. When you throw rowCount and num_rows into an if statement while having rowCount and num_rows in the first if statement, if there are records, rowCount and num_rows will display the records accordingly and if there isn’t, it’ll display whatever is in the else statement. So there really is no need for !=0 or ==1 or <1 or >0 when rowCount and num_rows already return what is needed. When you do !=0 or ==1 or <1 or >0 to rowCount and num_rows, you are being redundant.

<?php
require('includes/config.php'); // CONNECTION to Database

$sql = $dbh->prepare("SELECT id, name, age FROM info WHERE name = :name");
$sql->execute([':name' => $name1]);

if($sql->rowCount()) {
?>
<table border="0">
    <tr COLSPAN=2 BGCOLOR="#6D8FFF">
        <td>ID</td>
        <td>Name</td>
        <td>Age</td>
    </tr>
<?php
while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
?>
    <tr>
        <td><?php print($row['id']); ?></td>
        <td><?php print($row['name']); ?></td>
        <td><?php print($row['age']); ?></td>
    </tr>
<?php
}
?>
</table>
<?php
} else {

    print('There are no records at this moment.');

}

I usually get this a lot when I have typos in my .htaccess file. 500 usually relates to your Apache or IIS configuration file, 404 usually means the file doesn’t exist, 403 usually means you don’t have folder or file permissions, 401 usually means the username or password is wrong when trying to access an authentication protected folder either with Apache or IIS, and many other neat and fun errors that usually are done through configuration files.

Any idea how would a pdo query affect that?
@spaceshiptrooper to give the back-story on this one, the OP was previously struggling with a mysql script. We seem to have talked him round to using pdo instead as learning mysql would be a waste of time.
Could it be the server does not have pdo enabled? Would that cause a server error? Perhaps try mysqli?

I don’t know Valentin_Gjorgoski’s set-up but I’m wondering about two things.

First, is the name field unique? it doesn’t feel like the best choice to use in a WHERE to me.

Because there are no try{} catch {} there may be a PDO error / exception and, if the error handling has a problem (eg. trying to write to a file it doesn’t have permission to write to) it might cause a 500, no?

Both the PDO dll and the MySQL driver would need to be enabled

Anything done with PHP has nothing to do with the 500 error. I believe the OP was attempting to do something in the .htaccess file. We would have to see the .htaccess file before conducting any thorough inspection first though. We can’t just assume PHP is doing the problem because 500 errors usually happen in Apache files. Unless you are messing around with the .htaccess via PHP, then I would say that you shouldn’t do that if you don’t know what you’re doing.

No, usually if there are no permissions to the file, it would throw an open stream error I believe. I’ve had this done with my /var/log/apache2.log before when I was trying to demonstrate that I had no errors in the error file to an earlier thread I posted in.

Good point, since the original mysql script failed too, there could be something like that.

OK, I just took the OP’s word for it when he says:

I’m not really sure what he’s implying though with that statement. Does it mean that the error stops when he deletes those lines? Or does it mean that the error continues, but it’s a different error?

Its Ok, I solve with this:

<?php
require('includes/config.php');

function get_info($dbh, $name)
{
    $sql = $dbh->prepare("SELECT * FROM info WHERE name = :name");
    $sql->setFetchMode(PDO::FETCH_ASSOC);
    $sql->execute([':name' => $name]);
    if ($row = $sql->fetch()) {
        return $row;
    }
    return false;
}

?>


<table border="0">
    <tr COLSPAN=2 BGCOLOR="#6D8FFF">
        <td>ID</td>
        <td>Name</td>
        <td>Age</td>
    </tr>
    <?php
    if (isset($_POST['name1'])) {
        if ($row = get_info($dbh, $_POST['name1'])) {
            echo "<tr>" .
                "<td>" . $row["id"] . "</td>" .
                "<td>" . $row["name"] . "</td>" .
                "<td>" . $row["age"] . "</td>" .
                "</tr>";
        } else {
            echo "don't exist records for list on the table";
        }
    }
    ?>
</table>

It’s working now?

I have never used setFetchMode before, but looking at the PHP manual, I don’t see where PDO::FETCH_ASSOC is being used. Although I do see this description.

The fetch mode must be one of the PDO::FETCH_* constants.

It could be the cause of the fatal error.

The PDOStatement fetch method requires the fetch mode as an argument. This is the likely cause of a fatal error which results in a 500 response from the server. Though there could be other errors as well but that is the one thing that sticks out to me.

It shouldn’t, if not defined it defaults to PDO::FETCH_BOTH
Plus the fetch mode is already stated in the line:

$sql->setFetchMode(PDO::FETCH_ASSOC);

I’ve tried it and it works that way.
Though it may be better to put it in the fetch, I don’t think that was the problem.

Anyway, I think it’s solved.
He’s not confirmed, but I guess he logged off now he has it working.

Yes, however it’s best to define a default fetch mode in the options like so.

$options = [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC];
$db = new PDO('mysql:host=127.0.0.1;dbname=test', USERNAME, PASSWORD, $options);

That’s the proper way.

1 Like

Thanks, that’s useful. I didn’t know how to change the default fetch mode. I’m new to pdo myself, I just finished converting all my scripts to it this past weekend. I wish I’d known this before.

Only i need is “while()” to print rows for all selectbox. How to make “while” to do this.

<?php
require('includes/config.php');
require('layout/header.php'); 
function get_info($db, $predmet)
{
    $sql = $db->prepare("SELECT * FROM raspored WHERE predmet = :predmet");
    $sql->setFetchMode(PDO::FETCH_ASSOC);
    $sql->execute([':predmet' => $predmet]);
    if ($row = $sql->fetch()) {
        return $row;
    }
    return false;
}

?>


<table border="0" class="table table-hover table-striped">
    <tr COLSPAN=2 BGCOLOR="#6D8FFF">
        <th>ИД</th>
        <th>Предмет</th>
        <th>Професор</th>
		<th>Ден</th>
		<th>Час</th>
		<th>Просторија</th>
		<th>Тип</th>
    </tr>
	
    <?php
    if (isset($_POST['predmet1'], $_POST['predmet2'], $_POST['predmet3'], $_POST['predmet4'])) {
        if ($row = get_info($db, $_POST['predmet1'], $_POST['predmet2'], $_POST['predmet3'], $_POST['predmet4'])) {
			
            echo "<tr>" .
                "<td>" . $row["ID"] . "</td>" .
                "<td>" . $row["predmet"] . "</td>" .
                "<td>" . $row["profesor"] . "</td>" .
				"<td>" . $row["den"] . "</td>" .
				"<td>" . $row["chas"] . "</td>" .
				"<td>" . $row["prostorija"] . "</td>" .
				"<td>" . $row["tip"] . "</td>" .
                "</tr>";
			
        } else {
            echo "don't exist records for list on the table";
        }
    }
    ?>
	
</table>
</div>

<?php 
//футер
require('layout/footer.php'); 
?>

that’s the same as writing return $sql->fetch().

second, your get_info() function only supports a single value, not multiple values (for which you would need to repeatedly execute the prepared statement).

$stmt = $db->prepare("SELECT * FROM raspored WHERE predmet = :predmet");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->bindParam('predmet', $predmet, PDO::PARAM_STR);

// enumerated variables are a sign that you should use arrays instead
// i.e. use <input name="predmet[]"> instead of <input name="predmet1">/<input name="predmet2">/etc.
foreach ($_POST['predmet'] as $predmet) {
    $stmt->execute();
    echo result_to_html($stmt); // needs to be written
}