Help with PHP

[DeagleMaster]

I'm trying to create an overview of the Lord of The Rings movies where you can click on one of the movies and get an overview of witch characters are in that movie. I have managed to get an overview of the races in the movies where you can click on one of the races and see the characters in that race.

 

This is the diagram layout in mysql:

 

 

This is the PHP code that gives me an overview of the races once I press the link in the php document that gives me an overview of the races with links.

  

    <?php
    if (isset($_GET["rase_id"])) {
      $rase_id =$_GET["rase_id"];
      $rase =$_GET["rase"];
    }
    else {
      die("Du har ikke angitt en rase_id.");
    }

    include "kobling_midgard.php";

    $sql = "SELECT * FROM midgard.karakterer
            where karakterer.rase_id=$rase_id";
            $resultat = $kobling->query($sql);

            echo "<table>";

              echo "<tr>";
                echo "<th>Bilde</th>";
                      echo "<th>Navn</th>";
                echo "<th>Beskrivelse</th>";
            echo "</tr>";

                while ($rad = $resultat->fetch_assoc()) {
                  $bilde = $rad["bilde"];
                  $navn = $rad["navn"];
                  $beskrivelse = $rad["beskrivelse"];

                  echo "<tr>";
                    echo "<td><img src='bilder/$bilde' alt='karakterer'></td>";
                    echo "<td>$navn</td>";
                    echo "<td>$beskrivelse</td>";
                  echo "</tr>";
                  }
                  echo "</table>";
     ?>

 

This is the code I have for making the connection between the "film", "deltakelse" and "karakterer" tables.

   

    <?php
    if (isset($_GET["film_id"])) {
      $film_id =$_GET["film_id"];

    }
    else {
      die("Du har ikke angitt en karakter.");
    }

    include "kobling_midgard.php";

    $sql = "SELECT * FROM midgard.deltakelse
            where deltakelse.film_id=$film_id
            ";
            $resultat = $kobling->query($sql);

            echo "<table>";

              echo "<tr>";
                echo "<th>Navn</th>";
                      echo "<th>Beskrivelse</th>";
            echo "</tr>";

            while ($rad = $resultat->fetch_assoc()) {
              $navn = $rad["navn"];
              $beskrivelse = $rad["beskrivelse"];

              echo "<tr>";
                echo "<td>$navn</td>";
                echo "<td>$beskrivelse</td>";
              echo "</tr>";
              }
              echo "</table>";
     ?>

 

 

[mariushm]

In database ... I recommend setting the ID columns as UNSIGNED INT ... and you would want to make it AUTO_INCREMENT , primary key.

 

I personally like to use just the short id for the primary key, and use the longer something_id for something that makes connection to another table.

 

So basically it would be:

 

film

id  unsigned int auto increment primary key

tittel

arstal  - btw try to not use non-ascii characters even though technically it works

deltakase_id

 

deltakase

id unsigned int auto increment etc

blah

 

then you can write in your code

SELECT id,tittel from film,  WHERE film.id =  ....

 

Also ... NEVER just use input from user without sanitizing first. Check if that parameter is received using isset and if so, make sure the input is actually a number or what you want, before just glue-ing it to the sql query. 

Use ctype_digit or similar functions or intval or other ways of restricting.... use https://www.php.net/manual/en/mysqli.real-escape-string.php to escape strings of text before you submit them to database... otherwise if the text has characters like quotes or ; which have special meaning in sql queries may cause the query to be malformed or users may be able to hack your database.

 

 

 

[DeagleMaster]

23 minutes ago, mariushm said:

In database ... I recommend setting the ID columns as UNSIGNED INT ... and you would want to make it AUTO_INCREMENT , primary key.

 

I personally like to use just the short id for the primary key, and use the longer something_id for something that makes connection to another table.

 

So basically it would be:

 

film

id  unsigned int auto increment primary key

tittel

arstal  - btw try to not use non-ascii characters even though technically it works

deltakase_id

 

deltakase

id unsigned int auto increment etc

blah

 

then you can write in your code

SELECT id,tittel from film,  WHERE film.id =  ....

 

Also ... NEVER just use input from user without sanitizing first. Check if that parameter is received using isset and if so, make sure the input is actually a number or what you want, before just glue-ing it to the sql query. 

Use ctype_digit or similar functions or intval or other ways of restricting.... use https://www.php.net/manual/en/mysqli.real-escape-string.php to escape strings of text before you submit them to database... otherwise if the text has characters like quotes or ; which have special meaning in sql queries may cause the query to be malformed or users may be able to hack your database.

 

 

 

I have managed to get it filtered. If I press the movie with id1 it gives me the id's of the people in the "deltakelse"  table that has the film_id1. I would like to display information like names and descriptions of the people that has this id.

I use the "deltakelse" table to link the different id's, 

PS: I know am truly terrible at this. It's for a high school project in IT

 

film.php karakterer_sortert.php

[lal12]

On 4/11/2020 at 9:52 PM, mariushm said:

Use ctype_digit or similar functions or intval or other ways of restricting.... use https://www.php.net/manual/en/mysqli.real-escape-string.php to escape strings of text before you submit them to database... otherwise if the text has characters like quotes or ; which have special meaning in sql queries may cause the query to be malformed or users may be able to hack your database.

Or even better use mysql prepared statements.

[DeagleMaster]

20 hours ago, lal12 said:

Or even better use mysql prepared statements.

How would I do that?

[mariushm]

4 minutes ago, DeagleMaster69 said:

How would I do that?

See the example in the documentation : https://www.php.net/manual/en/mysqli.prepare.php

 

[DeagleMaster]

3 minutes ago, mariushm said:

See the example in the documentation : https://www.php.net/manual/en/mysqli.prepare.php

 

I think I might understand, So if I want it to show me the info of the characters with movie_id1 and character_id matching this in the "deltakelse" table I need to write: if ($film_id = $karakterer_id->prepare("SELECT "the character table" WHERE Name=?")

[lal12]

Well not exactly, you've missed a couple of steps here.

 

$mysqli = new mysqli("localhost", "my_user", "my_password", "lotr");

if ($stmt = $mysqli->prepare("SELECT * FROM `charachters` WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", "Legolas");

    /* execute query */
    $stmt->execute();

    /* fetch value */
    $res = $stmt->fetch_array(MYSQLI_ASSOC);

    var_dump("my character: ", $res[0]);

    /* close statement */
    $stmt->close();
}else{
	echo "ups, error";
}

This utilizes mysqli which is ok to use, but I personally prefer using PDO which is a bit more comfortable. It is technically a bit slower but this won't matter for your application.

$pdo = new PDO('mysql:host=localhost;dbname=lotr', 'username', 'password');
 
$statement = $pdo->prepare("SELECT * FROM `characters` WHERE Name=:character");
$statement->execute(array(':character' => 'Legolas'));   
$res = $statement->fetchAll(PDO::FETCH_ASSOC);
var_dump('my character:', $res[0]);

Btw passing "MYSQLI_ASSOC" or respectively "PDO::FETCH_ASSOC" will give you an column name indexed Array, which usually is easier than using numeric indexes.