Mysql question

[Niss3]

I'm trying to print data from my SQL table, but there is something wrong, or something I don't get.

Depending on which tutorial I am following certain commands simply just don't work for me.

In this example, I should write;

try{
$opendb = NEW PDO('mysql:host=$dbhost;dbname=$dbname','$dbuser','$dbpass');
}catch(PDOException $e){
die($e->getMessage());
}

$result = $opendb->query("SELECT * FROM quiz");

$all = $result->fetchAll();
echo "<pre>";
print_r($all);

But I couldn't get it working no matter what I tried. I think it might be the mysql -> commands that isn't working for me? But I am not sure. I rewrote the example with code I've been using earlier so it looked like this:

$opendb = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die(mysqli_error($opendb));

$result = mysqli_query($opendb, "SELECT * FROM quiz");


$all = mysqli_fetch_all($result);
echo("<pre>");
print_r($all);

This one works just fine.
I'm in the process of learning and I have no idea why the given example isn't working but it does exactly what it should when I use mysql_command() instead of ->command.

[Niss3]

This is what I am trying to do:

$mysqli = new mysqli("localhost", $username, $password, $database); 
$query = "SELECT * FROM table_name";


echo '<table border="0" cellspacing="2" cellpadding="2"> 
      <tr> 
          <td> <font face="Arial">Value1</font> </td> 
          <td> <font face="Arial">Value2</font> </td> 
          <td> <font face="Arial">Value3</font> </td> 
          <td> <font face="Arial">Value4</font> </td> 
          <td> <font face="Arial">Value5</font> </td> 
      </tr>';

if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $field1name = $row["col1"];
        $field2name = $row["col2"];
        $field3name = $row["col3"];
        $field4name = $row["col4"];
        $field5name = $row["col5"]; 

        echo '<tr> 
                  <td>'.$field1name.'</td> 
                  <td>'.$field2name.'</td> 
                  <td>'.$field3name.'</td> 
                  <td>'.$field4name.'</td> 
                  <td>'.$field5name.'</td> 
              </tr>';
    }
    $result->free();
} 

And this is how I am trying to do it, but it doesn't work for some reason I can't figure out:

<?php
$opendb = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die(mysqli_error($opendb));

$query = ("SELECT * FROM quiz");

echo '<table border="0" cellspacing="2" cellpadding="2"> 
      <tr> 
          <td> <font face="Arial">Value1</font> </td> 
          <td> <font face="Arial">Value2</font> </td> 
          <td> <font face="Arial">Value3</font> </td> 
          <td> <font face="Arial">Value4</font> </td> 
          <td> <font face="Arial">Value5</font> </td> 
      </tr>';

if ($result = mysqli_query($opendb, $query)) {
    while ($row = mysqli_fetch_assoc($result) {
        $field1name = $row["id"];
        $field2name = $row["namn"];
        $field3name = $row["points"];
        $field4name = $row["level"];
        echo '<tr> 
                  <td>'.$field1name.'</td> 
                  <td>'.$field2name.'</td> 
                  <td>'.$field3name.'</td> 
                  <td>'.$field4name.'</td> 
                  <td>'.$field5name.'</td> 
              </tr>';
    }
    mysqli_free($result);
} 
mysqli_close($opendb);
?>

This is what my database "quiz" looks like:

[Niss3]

When I try this:

$result = mysqli_query($opendb, "SELECT * FROM quiz");
$row = mysqli_fetch_assoc($result);
print_r($row['namn']);

I get niss3 printed. So everything seems to be working individually. Something with the if and while logic that isn't working?

 

------------

edit; I got it working. I had forgotten a simple ) after the while function.

Still my original question remains, why do I have to use mysql_query (why doesn't query-> work).

Edited October 18, 2022 by Niss3

[Barand]

35 minutes ago, Niss3 said:

$opendb = NEW PDO('mysql:host=$dbhost;dbname=$dbname','$dbuser','$dbpass');

Variables inside single quotes are treated as string literals.Try

$opendb = NEW PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

 

[mac_gyver]

44 minutes ago, Niss3 said:

$opendb = NEW PDO('mysql:host=$dbhost;dbname=$dbname','$dbuser','$dbpass');

php variables are not replaced with their value unless the string they are inside of uses initial/final double-quotes. also, when you have just a variable, e.g. the $dbuser and $dbpass, don't put quotes around it/them at all.

several of the things you have posted should have been producing php errors. do you have php's error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying ALL the errors it detects?

the PDO extension is much simpler, more consistent, and more modern than the mysqli extension. if you are just starting out, forget about the mysqli extension.

when you make the PDO connection, you should name the variable holding the connection as to what it is, such as $pdo. you should also set the character set to match your database tables, set the error mode to use exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc.

don't bother catching any database exception unless it is for something that the user to your site can recover from, such as when inserting/updating duplicate or out of range data. in all other cases, simply let php catch and handle any database exception, i.e. don't put any try/catch logic in your code.

you should always list out the columns you are SELECTing in a query and build the sql query statement in a php variable.

29 minutes ago, Niss3 said:

border="0"

this is obsolete markup. use css instead. you should validate your resulting web pages at validator.w3.org

29 minutes ago, Niss3 said:

$field1name = $row["col1"];

don't copy variables to other variables for nothing. this is just a waste of typing. just use the original variables.

29 minutes ago, Niss3 said:

mysqli_free($result);
} 
mysqli_close($opendb);

in most cases, there's no need to free result sets, free prepared query handles, or close database connections, since php will destroy these when your script ends.

 

Edited October 18, 2022 by mac_gyver

[Niss3]

Thank you, turning display errors on in the PHP file was incredible helpful!

I will try to get the PDO working again!

[mac_gyver]

26 minutes ago, Niss3 said:

in the PHP file

putting the settings in your php code won't help with php parse errors in that file, which was one of the problems in this thread, since the code never runs to change the settings. putting these settings in the php.ini will cause ALL errors to be reported and displayed. it also allows you to change the settings at a single point, so you don't need to go through and edit your code when you move it to a live/public server, where you DON'T want to display all errors, you want to log them.