-1

My table is looks like

Id Topic Sentence
1 Google bla bla..1
2 YouTube bla bla..2
3 Amazon bla bla..3
4 Google bla bla..4
5 Google bla bla..5
6 YouTube bla bla..6

Now I want just like the Udemy course curriculum listing. Under each topic, list all the sentence in a respective order.

Eg. of what I want,

Google   ^
bla bla..1
bla bla..4
bla bla..5


YouTube  ^
bla bla..2
bla bla..6


Amazon  ^
bla bla..3

NB: No Google should be come first, all is ordered according to the id I think. Idea: Just like Udemy in one of their course there is introduction topic and there are some videos under that topic and there is another topic called basic and there are some contents/videos under that topic too and etc at the last there is end course topic and under that topic there are some listed contents/videos.

So now assuming the topic I'm saying are the topic of the above table and and the contents are the sentence of the above table.

I want to display the topic called Google in this case and display the sentence of it under that topic. same goes to YouTube and soon...

What I've tried is: echoing out all the topic and the sentence but the problem here is I've displayed all the contents under each topic which is there are 3 Google, 2 YouTube and one Amazon topics over their respective sentences.

$select = $db_conn->query("SELECT * FROM contents WHERE email = '$email' ORDER BY id");   
foreach($select as $select_data){
    $topic = $select_data['topic'];
    $sentence = $select_data['sentence'];
    $id = $select_data['id'];
    echo "<h2>$topic</h2>"; // this should be echo out once only if the same topic is there
    echo "<p>$sentence</p>";  
}

So How can I solve this problem?

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Anan
  • 33
  • 7
  • You are open for [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) – DarkBee Apr 19 '22 at 06:47
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) 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/32391315) – Dharman Apr 19 '22 at 10:10
  • @DarkBee & Dharman I did it by escaping the data, like $email = mysqli_real_eascape_string($db_conn, $_POST['email']; and I get pen tester to test my other web app if he can hack my site using sql injection with this escaping method but he can't and I already tried to do hack with that but still its not hackable can you give me some info on this please? – Anan Apr 19 '22 at 10:55

2 Answers2

1

One way you can get the results you want is to get a list of topics and their minimum id value and then JOIN that to the original table, sorting by the topic's minimum id value and then the id value of the row. For example:

SELECT c.*
FROM (
  SELECT MIN(id) AS id, topic
  FROM contents
  GROUP BY topic
) t
JOIN contents c ON c.topic = t.topic
ORDER BY t.id, c.id

Query output (for your sample data):

id  topic   sentence
1   Google  bla bla..1
4   Google  bla bla..4
5   Google  bla bla..5
2   YouTube bla bla..2
6   YouTube bla bla..6
3   Amazon  bla bla..3

Now your input data is sorted you can output the title only when it changes e.g.

$last_topic = '';
foreach($select as $select_data){
    $topic = $select_data['topic'];
    $sentence = $select_data['sentence'];
    $id = $select_data['id'];
    if ($topic != $last_topic) echo "<h2>$topic</h2>"; 
    $last_topic = $topic;
    echo "<p>$sentence</p>";  
}
Nick
  • 138,499
  • 22
  • 57
  • 95
  • You've saved my time and gave me the real solution. It works like What I want. Thank you so much. – Anan Apr 19 '22 at 10:52
0

Beacause you put it in loop and it repeated three times and you can do it for avoid duplication

$prev_id = 0;
foreach($select as $select_data){
   $topic = $select_data['topic'];
   $sentence = $select_data['sentence'];
   $id = $select_data['id'];
   if($prev_id != $id)
      echo "<h2>$topic</h2>"; 
   echo "<p>$sentence</p>";  
   $prev_id = $id;
}

in this code for every id echo topic just one time its for that time you get all of the contents; when you get single email

echo "<h2>$select[0]['topic']</h2>";
foreach($select as $select_data){
   $sentence = $select_data['sentence'];
   $id = $select_data['id'];
   echo "<p>$sentence</p>";  
}
mhhabibi
  • 45
  • 7