Regarding a complex problem

[bintech21]

ok,thanks i found it there,but i was expecting it should have displayed some message like 'table with this name created successfully' any way.

 

i have searched on google and found pdo is better than my sql also for next connecting making process in you tube example they have like it below:-

"<?php //database_connection.php $connect = new PDO("mysql:host=localhost;dbname=testing", "root", ""); session_start(); $_SESSION["user_id"] = "1"; ?>"

 

but i am not sure whether above php script would establish connection with My SQL successfully or not as they have used pdo here my i created table on My SQL database.

 

[bintech21]

also they did not tell what name should we give to this script ?

[bintech21]

please ignore my last posts as upon running these code i was able to add /delete from 'my -to do list' but how does it match with my pain problem :-

 

Is it possible to create an web-page,form,that would accept specific input and generate
an XML file from it.The form has to have the ability to add items in one particular section(where we can
add additional streams) and give us the ability to remove streams as well.
For each stream we must be able to attach a logo for that stream as well.

Xojo or Php for this could be used,

below is the sample XML file and Data Format.

<?xml version="1.0" encoding="UTF-8" ?>
<collectionList>
<grid class="5ColumnGrid">
<section>
<lockup onselect="playMedia('https://player.vimeo.com/external/187143276.m3u8?s...')">
<img src="/resources/images/lockups/shelf1.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 1</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/184669023.m3u8?s...')">
<img src="/resources/images/lockups/shelf2.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 2</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/181758939.m3u8?s...')">
<img src="/resources/images/lockups/shelf3.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 3</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/180890459.m3u8?s...')">
<img src="/resources/images/lockups/shelf4.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 4</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/180014530.m3u8?s...')">
<img src="/resources/images/lockups/shelf5.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 5</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/178583018.m3u8?s...')">
<img src="/resources/images/lockups/shelf6.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 6</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/178457098.m3u8?s...')">
<img src="/resources/images/lockups/shelf7.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 7</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/177558935.m3u8?s...')">
<img src="/resources/images/lockups/shelf8.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 8</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/173738601.m3u8?s...')">
<img src="/resources/images/lockups/shelf9.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 9</title>
</lockup>
<lockup onselect="playMedia('https://player.vimeo.com/external/187143276.m3u8?s...')">
<img src="/resources/images/lockups/shelf2.png" width="308" height="308" />
<title class="showTextOnHighlight">Title 10</title>
</lockup>
</section>
</grid>
</collectionList>
</stackTemplate>
</document>

[Barand]

You have a form for the user to enter data for those sections of xml, so now you need

• a table to store them,
• code to process the form data and store it (including uploading the image files and storing those)
• code to retrieve those items from the and output the xml 

[bintech21]

but how can i create table based on this XML file?

[Barand]

Each section in the XML contains

• media url
• image name
• title

By happy coincidence, the form code that you posted asks the user to input:

• media url
• image name
• title

Given the above, what do you think your table should contain?

[bintech21]

Ok, I think it should have these 3 fields 

1.media url

2.image name

3. title

 

But I am not sure of their length but their types based on this sample XML file i think it should be of char.

also regarding their length I am not sure how much length should i keep let's say if i keep the length 60 then how would we be able to manage in case of excess or shorter length in case based on values (variables) of these fields later on?

 

 

[bintech21]

how does it look here?

CREATE TABLE t1 (
    mediaurl varchar(60),
    imagename varchar(60),
    title varchar(60),
    
);

[Barand]

1 hour ago, bintech21 said:

if i keep the length 60 then how would we be able to manage in case of excess or shorter length in case based on values (variables) of these fields later on?

https://dev.mysql.com/doc/refman/5.7/en/char.html

[bintech21]

how does it look here?

CREATE TABLE t1 (
    mediaurl varchar(60),
    imagename varchar(60),
    title varchar(60),
    
);

[bintech21]

just removing extra comma(,) after title field--

how does it look here?

CREATE TABLE t1 (
    mediaurl varchar(60),
    imagename varchar(60),
    title varchar(60)
    
);

Now I think it's good table based on my problem here and should be able to handle values as i have used varchar here right?

[Barand]

15 minutes ago, bintech21 said:

how does it look here?

Give your tables meaningful names that describe the entity being stored in the table. Looking at the XML then perhaps the name would be "lockup".

There is a requirement that each row in a table should have unique identifier otherwise you cannot edit/delete rows. The easiest way is use an auto_incrementing value that guarantees uniqueness.

CREATE TABLE lockup (
    lockup_id int not null auto_increment primary key,
    mediaurl varchar(60),
    imagename varchar(60),
    title varchar(60)
);

 

[bintech21]

ok,thanks.

 

Does my index page look good as per modified code( Just changed the fields name as this new table) based on referred example from that link:-https://www.webslesson.info/2019/08/developed-to-do-list-in-php-using-ajax.html

    <?php

//index.php

include('database_connection.php');

$query = "
 SELECT * FROM task_list 
 WHERE user_id = '".$_SESSION["user_id"]."' 
 ORDER BY task_list_id DESC
";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

?>

<!DOCTYPE html>
<html>
 <head>
  <title>Developed To-update-add-delete items in XML section in PHP using Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
   body
   {
    font-family: 'Comic Sans MS';
   }

   .list-group-item
   {
    font-size: 26px;
   }
  </style>
 </head>
 <body>
  
  <br />
  <br />
  <div class="container">
   <h1 align="center">Developed To-update-add-delete items in XML section in PHP using Ajax</h1>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">
     <div class="row">
      <div class="col-md-9">
       <h3 class="panel-title">My To-Do List</h3>
      </div>
      <div class="col-md-3">
       
      </div>
     </div>
    </div>
      <div class="panel-body">
       <form method="post" id="to_do_form">
        <span id="message"></span>
        <div class="input-group">
         <input type="text" name="task_name" id="task_name" class="form-control input-lg" autocomplete="off" placeholder="Title..." />
         <div class="input-group-btn">
          <button type="submit" name="submit" id="submit" class="btn btn-success btn-lg"><span class="glyphicon glyphicon-plus"></span></button>
         </div>
        </div>
       </form>
       <br />
       <div class="list-group">
       <?php
       foreach($result as $row)
       {
        $style = '';
        if($row["task_status"] == 'yes')
        {
         $style = 'text-decoration: line-through';
        }
        echo '<a href="#" style="'.$style.'" class="list-group-item" id="list-group-item-'.$row["lockup_id"].'" data-id="'.$row["lockup_id"].'">'.$row["mediaurl"].' <span class="badge" data-id="'.$row["lockup_id"].'">X</span></a>';
       }
       ?>
       </div>
      </div>
     </div>
  </div>
 </body>
</html>

<script>
 
 $(document).ready(function(){
  
  $(document).on('submit', '#to_do_form', function(event){
   event.preventDefault();

   if($('#task_name').val() == '')
   {
    $('#message').html('<div class="alert alert-danger">Enter Task Details</div>');
    return false;
   }
   else
   {
    $('#submit').attr('disabled', 'disabled');
    $.ajax({
     url:"add_task -Copy.php",
     method:"POST",
     data:$(this).serialize(),
     success:function(data)
     {
      $('#submit').attr('disabled', false);
      $('#to_do_form')[0].reset();
      $('.list-group').prepend(data);
     }
    })
   }
  });

  $(document).on('click', '.list-group-item', function(){
   var lockup_id = $(this).data('id');
   $.ajax({
    url:"update_task - Copy.php",
    method:"POST",
    data:{lockup_id:lockup_id},
    success:function(data)
    {
     $('#list-group-item-'+lockup_id).css('text-decoration', 'line-through');
    }
   })
  });

  $(document).on('click', '.badge', function(){
   var lockup_id = $(this).data('id');
   $.ajax({
    url:"delete_task - Copy.php",
    method:"POST",
    data:{lockup_id:lockup_id},
    success:function(data)
    {
     $('#list-group-item-'+lockup_id).fadeOut('slow');
    }
   })
  });

 });
</script>

 

[Barand]

The purpose of using prepared statements is so you don't put values directly into the query itself. Instead you use a "placeholder" for the value and pass the value in the execute statement.

$query = "
 SELECT * FROM task_list 
 WHERE user_id = ? 
 ORDER BY task_list_id DESC
";

$statement = $connect->prepare($query);

$statement->execute( [ $_SESSION["user_id"] ] );

 

There is another commonly used convention when using a database: if you want to produce a list of data items from a table then execute a query against that table. Querying the task_list table in order to get a list of lockups ain't going to work too well.

[bintech21]

Ok ,Thanks.

I hope now following code is good with respect to my problem here ,could you please have a look on this , I have made corrections based on my understanding for new table 'lockup' and it's fields for all the important sections here to get the desired results for this new webform.

*************
modified code for database_connection -Copy.php

***************
<?php
//database_connection.php

$connect = new PDO("mysql:host=localhost;dbname=MySQL", "root", "");

session_start();

$_SESSION["user_id"] = "1";

?>

***************************************************************************************************

modified code for index -Copy.php as per new table 'lockup'.

********
<?php

//index.php

include('database_connection -Copy.php');

$query = "
 SELECT * FROM lockup
 WHERE user_id = '".$_SESSION["user_id"]."' 
 ORDER BY user_id DESC
";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

?>

<!DOCTYPE html>
<html>
 <head>
  <title>Developed To-update-add-delete items in XML section in PHP using Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
   body
   {
    font-family: 'Comic Sans MS';
   }

   .list-group-item
   {
    font-size: 26px;
   }
  </style>
 </head>
 <body>
  
  <br />
  <br />
  <div class="container">
   <h1 align="center">Developed To-update-add-delete items in XML section in PHP using Ajax</h1>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">
     <div class="row">
      <div class="col-md-9">
       <h3 class="panel-title">My To-Do List</h3>
      </div>
      <div class="col-md-3">
       
      </div>
     </div>
    </div>
      <div class="panel-body">
       <form method="post" id="to_do_form">
        <span id="message"></span>
        <div class="input-group">
         <input type="text" name="task_name" id="task_name" class="form-control input-lg" autocomplete="off" placeholder="Title..." />
         <div class="input-group-btn">
          <button type="submit" name="submit" id="submit" class="btn btn-success btn-lg"><span class="glyphicon glyphicon-plus"></span></button>
         </div>
        </div>
       </form>
       <br />
       <div class="list-group">
       <?php
       foreach($result as $row)
       {
        $style = '';
        if($row["task_status"] == 'yes')
        {
         $style = 'text-decoration: line-through';
        }
        echo '<a href="#" style="'.$style.'" class="list-group-item" id="list-group-item-'.$row["lockup_id"].'" data-id="'.$row["lockup_id"].'">'.$row["mediaurl"].' <span class="badge" data-id="'.$row["lockup_id"].'">X</span></a>';
       }
       ?>
       </div>
      </div>
     </div>
  </div>
 </body>
</html>

<script>
 
 $(document).ready(function(){
  
  $(document).on('submit', '#to_do_form', function(event){
   event.preventDefault();

   if($('#task_name').val() == '')
   {
    $('#message').html('<div class="alert alert-danger">Enter Task Details</div>');
    return false;
   }
   else
   {
    $('#submit').attr('disabled', 'disabled');
    $.ajax({
     url:"add_task -Copy.php",
     method:"POST",
     data:$(this).serialize(),
     success:function(data)
     {
      $('#submit').attr('disabled', false);
      $('#to_do_form')[0].reset();
      $('.list-group').prepend(data);
     }
    })
   }
  });

  $(document).on('click', '.list-group-item', function(){
   var lockup_id = $(this).data('id');
   $.ajax({
    url:"update_task - Copy.php",
    method:"POST",
    data:{lockup_id:lockup_id},
    success:function(data)
    {
     $('#list-group-item-'+lockup_id).css('text-decoration', 'line-through');
    }
   })
  });

  $(document).on('click', '.badge', function(){
   var lockup_id = $(this).data('id');
   $.ajax({
    url:"delete_task - Copy.php",
    method:"POST",
    data:{lockup_id:lockup_id},
    success:function(data)
    {
     $('#list-group-item-'+lockup_id).fadeOut('slow');
    }
   })
  });

 });
</script>

********
modified code for add_task -Copy.php as per new table 'lockup'.
********

<?php

//add_task.php

include('database_connection -Copy.php');

if($_POST["task_name"])
{
 $data = array(
  ':lockup_id'  => $_SESSION['lockup_id'],
  ':mediaurl' => trim($_POST["mediaurl"]),
  ':imagename' => 'no'
 );

 $query = "
 INSERT INTO lockup 
 (lockup_id, mediaurl,imagename,title) 
 VALUES (:lockup_id, :mediaurl, :imagename,title)
 ";

 $statement = $connect->prepare($query);

 if($statement->execute($data))
 {
  $task_list_id = $connect->lastInsertId();

  echo '<a href="#" class="list-group-item" id="list-group-item-'.$lockup_id.'" data-id="'.$lock_id.'">'.$_POST["mediaurl"].' <span class="badge" data-id="'.$lockup_id.'">X</span></a>';
 }
}


?>

********
modified code for delete_task -Copy.php as per new table 'lockup'.
********
<?php

//delete_task.php

include('database_connection -Copy.php');

if($_POST["lockup_id"])
{
 $data = array(
  ':lockup_id'  => $_POST['lockup_id']
 );

 $query = "
 DELETE FROM lockup
 WHERE lockup_id = :lockup_id
 ";

 $statement = $connect->prepare($query);

 if($statement->execute($data))
 {
  echo 'done';
 }
}



?>


****************
modified code for update_task -Copy.php as per new table 'lockup'.

***********************************
<?php

//update_task.php

include('database_connection -Copy.php');

if($_POST["lockup_id"])
{
 $data = array(
  ':mediaurl'  => 'yes',
  ':lockup_id'  => $_POST["lockup_id"]
 );

 $query = "
 UPDATE lockup
 SET mediaurl = :mediaurl
 WHERE lockup_id = lockup_id :
  ";

 $statement = $connect->prepare($query);

 if($statement->execute($data))
 {
  echo 'done';
 }
}

?>

************************************

 

[Barand]

22 hours ago, Barand said:

The purpose of using prepared statements is so you don't put values directly into the query itself. Instead you use a "placeholder" for the value and pass the value in the execute statement.

 

[bintech21]

so did you mean o say like  i should modify my code to all the places wherever i used it like $query ' ...some sql query'  i should not use this sql query here instead i should use it like this :-execute('Sql Query') ?

[Barand]

I showed you how to use a prepared query, rewriting your code for you here

Read the replies.

[bintech21]

ok,i just read it again so for index.php it seems you have corrected the execute () similarly i think for rest of other parts like add,delete,update i thnk  it's correct only as i have coded

-->execute($data) there?

and apart from this code( for all idex,add,delete,update & DBconnection) looks good for all these transactions with respect to my problem here?

[Barand]

There are some options that you should set when you connect to the database server. For example, this my included connection file

                const HOST = 'localhost';
                const USERNAME = '???';
                const PASSWORD = '???';
                const DATABASE = '???';

                function pdoConnect($dbname=DATABASE) 
                {
                    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
                    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                    return $db;
                }

$conn = pdoConnect() will then connect to my default database, but if I set up a test database "bintech" to put your tables in for testing code, then I can call $conn = pdoConnect('bintech').

 

Your UPDATE statement is wrong as it will update all records for which "lockup_id = lockup_id", which is all of them. You are missing the ":"

[bintech21]

but as given in that referral link database connection.php file does not have all these things it just have followings as stated in last post:-

<?php
//database_connection.php

$connect = new PDO("mysql:host=localhost;dbname=MySQL", "root", "");

session_start();

$_SESSION["user_id"] = "1";

?>

 

also from myphpadmin link i have selected database name as MySQL so is some thing wrong in it?

 

also  can you please show by example what should ideally be my update statement if I am missing  ":" there?

 

 

[bintech21]

also where should i set all these options const HOST = 'localhost'; const USERNAME = '???'; const PASSWORD = '???'; const DATABASE = '???'; function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; }

[Barand]

22 hours ago, Barand said:

this my included connection file

that was a clue.

[bintech21]

ok,but what is missing in my database_connection php file?

..also from myphpadmin link (which was posed earlier in this thread) i have selected database name as 'MySQL'  so is some thing wrong in it?

 

also  can you please show by example what should ideally be my update statement if I am missing  ":" there?

 

were my add,delete statements were correct?

 

also even if i try to connect and add things in this new table so called 'lockup' where is it going to serve my purpose for ' generate
an XML file from it.The form has to have the ability to add items in one particular section(where we can
add additional streams) and give us the ability to remove streams as well.
For each stream we must be able to attach a logo for that stream as well.' this means some where from this web form we must be able to upload logo where is that functionality is getting covered here in our attempts ( as we are referring currently that you tube video)

 

[bintech21]

Any updates by experts please?