Fetch separated array values from database in php

Question

I've database with a table 'history'

id             title                subtitle                             file
---------------------------- ----------------------------- -------------------------------------
2    SeriesA*SeriesB*SeriesC  SessionA*SessionB*SessionC   Mircosoft.doc*Windows.docx*Server.doc
4    Objective*Punc*Blank     Shorttype*Paragraph*Match    Mircosoft.doc*Windows.docx*Server.doc

I want to fetch title, subtitle and file of that ID, to html input values.

$id = $_GET['id'];
$get_history = mysqli_query($mysqli, "SELECT * FROM history WHERE id=".$id."");
$rows = array();
while($row = mysqli_fetch_assoc($get_history))
$rows[] = $row;
foreach($rows as $row) {    
    $title = explode("*" , $row['title']);
    $subtitle = explode("*" , $row['subtitle']);
    $file = explode("*" , $row['file']);    
}                   

<html>
    <body>
        Title: <input type="text" name="name[]" value="<?php echo $title; ?> "/><br>
        Subtitle: <input type="text" name="title[]" value="<?php echo $subtitle; ?> "/><br>
        File: <input type="file" name="file[]" value="<?php echo $file; ?> "/><br>
        <input type="button" class="add" value="Add"/><br>
        <input type="submit" value="submit" name="submit">
    </body>
</html>

I need the output like when id=2 :

Title : SeriesA
Subtitle: SessionA
File: Mircosoft.doc
---------------------
Title : SeriesB
Subtitle: SessionB
File: Windows.docx
---------------------
Title : SeriesC
Subtitle: SessionC
File: Server.doc

Yes, DB has separated values. They are inserted as array of values to database. — Corren, Sep 30 '20 at 05:52
FYI: You can not set a default value for a `file` input field, https://stackoverflow.com/questions/1696877/how-to-set-a-value-to-a-file-input-in-html — 04FS, Sep 30 '20 at 06:37
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 30 '20 at 10:15
@Dharman - Thank You! So what makes my code corrupting data. I'm working with mysqli. Could you let me know how should I program for safe data — Corren, Sep 30 '20 at 12:40
This table schema is suffering from a lack of Normalization. You have to explode and transpose your values to achieve the desired result, this is not good from the start. A prepared statement as Dharman has mentioned. You should redesign your `history` table then rewrite your code that processes its data, because you are going down a long road to headache-ville. — mickmackusa, Dec 06 '20 at 21:26

nice_dev · Accepted Answer · 2020-09-30T06:04:26.950

1

Looking at your values, you just have to loop $name and print them simultaneously with $subtitle and $file index values like below:

<?php

foreach($rows as $row) {    
    $title = explode("*" , $row['title']);
    $subtitle = explode("*" , $row['subtitle']);
    $file = explode("*" , $row['file']);    

    foreach($title as $index => $title_value){
        echo 'Title:' ,$title_value,PHP_EOL;
        echo 'Subtitle:',$subtitle[ $index ],PHP_EOL;
        echo 'File:',$file[ $index ],PHP_EOL;
        echo "----------",PHP_EOL;
    }
}

edited Sep 30 '20 at 06:04

answered Sep 30 '20 at 05:52

nice_dev

17,053
2
21
35

1

name is nothing but title. I've edited the question. – Corren Sep 30 '20 at 06:03
@Corren Good, changed my answer accordingly. – nice_dev Sep 30 '20 at 06:05
The echo values are returning only the last array values: SeriesC, SessionC and Server.doc – Corren Sep 30 '20 at 06:11
@Corren Are you sure? See demo https://3v4l.org/h2nL6 – nice_dev Sep 30 '20 at 06:14
Could you tell me what PHP_EOL will do? Instead of PHP_EOL, do we have other option – Corren Sep 30 '20 at 06:19
@Corren It's end of line. You can use `
` if you are seeing this in a browser. – nice_dev Sep 30 '20 at 06:21

score 0 · Answer 2 · answered Sep 30 '20 at 05:56

When you explode your row data you have an array, so you can do this:

    foreach($name as $key => $value){
        echo 'name - '.$value;
        if(isset($subtitle[$key])){
            echo 'subtitle - '.$subtitle[$key];
        }
        if(isset($file [$key])){
            echo 'file - '.$file [$key];
        }
    }

But if you are not sure that number if names is equal to numbers of subtitle and file, you can get the highest count of array elements and do it like that:

for($i = 0; $i < $highestCount; $i++){
    ...
    if(isset($subtitle[$i])){
         echo 'subtitle - '.$subtitle[$i];
    }
    ....
}

score -1 · Answer 3 · answered Sep 30 '20 at 05:53

You can just try:

$id = $_GET['id'];
$get_history = mysqli_query($mysqli, "SELECT * FROM history WHERE id=".$id."");
$row = mysqli_fetch_assoc($get_history);

these three below will get the data that is exploded from the original data.

$title = explode("*" , $row['title']);
$subtitle = explode("*" , $row['subtitle']);
$file= explode("*" , $row['file']);

to display them:

Title : $title[0]
Subtitle: $subtitle[0]
File: $file[0]
---------------------
Title : $title[1]
Subtitle: $subtitle[1]
File: $file[1]
---------------------
Title : $title[2]
Subtitle: $subtitle[2]
File: $file[2]

Let me know if this works :)

$title[0], $subtitle[0], $file[0] - this is not good to give numbering. It should auto detect the array — Corren, Sep 30 '20 at 05:55

Fetch separated array values from database in php

3 Answers3