Jump to content

Regarding a complex problem


bintech21

Recommended Posts

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.

 

Link to comment
Share on other sites

  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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>

Link to comment
Share on other sites

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 
Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)
);

 

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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';
 }
}

?>

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 ":"

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

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; }

Link to comment
Share on other sites

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)

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.