bintech21 Posted May 27, 2020 Author Share Posted May 27, 2020 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. Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 27, 2020 Author Share Posted May 27, 2020 also they did not tell what name should we give to this script ? Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 27, 2020 Author Share Posted May 27, 2020 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> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 27, 2020 Share Posted May 27, 2020 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 Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 27, 2020 Author Share Posted May 27, 2020 but how can i create table based on this XML file? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 27, 2020 Share Posted May 27, 2020 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? Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 28, 2020 Author Share Posted May 28, 2020 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? Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 28, 2020 Author Share Posted May 28, 2020 how does it look here? CREATE TABLE t1 ( mediaurl varchar(60), imagename varchar(60), title varchar(60), ); Quote Link to comment Share on other sites More sharing options...
Barand Posted May 28, 2020 Share Posted May 28, 2020 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 Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 28, 2020 Author Share Posted May 28, 2020 how does it look here? CREATE TABLE t1 ( mediaurl varchar(60), imagename varchar(60), title varchar(60), ); Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 28, 2020 Author Share Posted May 28, 2020 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 28, 2020 Share Posted May 28, 2020 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) ); Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 28, 2020 Author Share Posted May 28, 2020 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> Quote Link to comment Share on other sites More sharing options...
Barand Posted May 28, 2020 Share Posted May 28, 2020 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. Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 29, 2020 Author Share Posted May 29, 2020 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'; } } ?> ************************************ Quote Link to comment Share on other sites More sharing options...
Barand Posted May 29, 2020 Share Posted May 29, 2020 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. Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 29, 2020 Author Share Posted May 29, 2020 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') ? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 29, 2020 Share Posted May 29, 2020 I showed you how to use a prepared query, rewriting your code for you here Read the replies. Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 29, 2020 Author Share Posted May 29, 2020 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted May 29, 2020 Share Posted May 29, 2020 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 ":" Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 30, 2020 Author Share Posted May 30, 2020 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? Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 30, 2020 Author Share Posted May 30, 2020 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; } Quote Link to comment Share on other sites More sharing options...
Barand Posted May 30, 2020 Share Posted May 30, 2020 22 hours ago, Barand said: this my included connection file that was a clue. Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 30, 2020 Author Share Posted May 30, 2020 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) Quote Link to comment Share on other sites More sharing options...
bintech21 Posted May 31, 2020 Author Share Posted May 31, 2020 Any updates by experts please? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.