From the relationship tables with php, not work ing pull data in multiple select box!


#1

I’m using php and Mysqli,

relationship tables, I want to get the data in multiple selectbox, but! previously selected recordings must be selected in the box. I’ve been looking at the web for hours, looked at the examples, but still failed.

How should the mysql query be?

How should I loop with while?

Table structure in this way:

+----------------------+
|       tb_haber       |
+----------------------+
| haber_id | haber_tit |
+----------+-----------+
|     1    |    text   |
+----------+-----------+

+------------------+
|    tb_kategori   |
+------------------+
| kat_id | kat_tit |
+--------+---------+
|    1   |   PHP   |
+--------+---------+
|    2   |   CSS   |
+--------+---------+
|    3   |   ASP   |
+--------+---------+
|    4   |  MYSQL  |
+--------+---------+

+-------------------+
|    tb_relation    |
+-------------------+
| haber_id | kat_id |
+----------+--------+
|     1    |    1   |
+----------+--------+
|     1    |    2   |
+----------+--------+

this code I tried, only selected categories appear.



<?php 
$con= mysqli_connect("localhost","root","","db");
mysqli_set_charset($con, 'utf8');

if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    mysqli_close($con);
}

$id=$_GET['id'];
$query1=mysqli_query($con,"SELECT * FROM tb_haber JOIN tb_relation ON tb_haber.id = tb_relation.haber_id  WHERE haber_id=$id ");
$row1=mysqli_fetch_array($query1);                        
$haber=$row1['haber_id'];

$query2=mysqli_query($con,"SELECT * FROM tb_kategori JOIN tb_relation ON tb_kategori.kat_id = tb_relation.kat_id  WHERE haber_id=$haber ");
?>

<select name="haber_kategori[]" class="select-chosen " data-placeholder="Kategori Seç..." multiple="multiple">

    <?php 
    while($row2=mysqli_fetch_array($query2)){
        $kategori=$row2['kat_tit'];
        $k_id=$row2['kat_id'];

        $sql_id=mysqli_query($con, "SELECT * FROM tb_kategori");
        while ($bul_id=mysqli_fetch_array($sql_id)) {
            $k_ids=$bul_id['kat_id'];
            $k_tit=$bul_id['kat_tit'];
            if ($k_ids==$k_id) {
                ?>
                <option value="<?php echo $k_ids; ?>" selected>
                    <?php echo $k_tit; ?>                                      
                </option>
            <?php } } }?>
        </select>

#2

Well what have you tried? What query are you currently using?


#4

Thank you for your answer. I updated the question again. Waiting for suggestions.


#5

Here it is in PDO.

define('DB_TYPE', 'mysql'); // Database Type
define('DB_NAME', 'sitepoint'); // Database Name
define('DB_USER', 'root'); // Database Username
define('DB_PASSWORD', ''); // Database Password
define('DB_HOST', 'localhost'); // Database Hostname
define('DB_CHARSET', 'utf8'); // Database Charset
$dsn = DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
$opt = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

$pdo = new PDO($dsn, DB_USER, DB_PASSWORD, $opt);
$_GET['id'] = 1;// FOR Testing ONLY  
?>
<select multiple name="haber_kategori[]">
<?php
$sql = "SELECT * FROM tb_kategori";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

foreach ($result as $row) {
    $selected = '';

    $sql = "SELECT
	* 
FROM
	tb_haber AS h
	INNER JOIN tb_relation AS pr ON pr.haber_id = h.haber_id
	INNER JOIN tb_kategori AS k ON pr.kat_id = k.kat_id 
WHERE
	h.haber_id = {$_GET['id']}";

    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result2 = $stmt->fetchAll();

    foreach ($result2 as $row2) {
        if ($row2['kat_id'] == $row['kat_id']) {
            $selected = "selected='selected'";
        }
    }

    echo "<option value='{$row['kat_id']}' $selected>{$row['kat_tit']}</option>\n";
}
?>
</select>

#6

I’ll try with mysqli, thank you


#8

Thank you, worked.


#9

Mysqli also worked this way
mysqli_fetch_all ($ argument,MYSQLI_ASSOC);