Avatar of JohnLourdu
JohnLourduFlag for Afghanistan asked on

how to create table in mysql

Hi experts,

As per below mysql query how to create table in mysql using terminal:
db1.php
<?php
      //database connection
      mysql_connect("localhost", "root", "sam123") or die(mysql_error());
      mysql_select_db("test") or die(mysql_error());
?>

save.php

<?php
      include("db1.php");
      $content = $_POST['content']; //get posted data
      $content = mysql_real_escape_string($content);      //escape string      
      
      $sql = "UPDATE content SET text = '$content' WHERE element_id = '1' ";
      
      if (mysql_query($sql)) {
            echo 1;
      }
?>

ajax in main.html

$("#libary-save-ico").click(function (e) {
$(".savebtn").hide();
var content = $('body').html();
$.ajax({
url: 'save.php',
type: 'POST',
data: {
content: content
},
success:function (data) {
if (data == '1') {
$("#curEditStatus")
.addClass("success")
.html("Data saved successfully")
.fadeIn('slow')
.delay(3000)
.fadeOut('slow');
}
else {
$("#curEditStatus")
.addClass("error")
.html("Error, data could not be saved")
.fadeIn('slow')
.delay(3000)
.fadeOut('slow');
}
}
});
});

            <?php
                  //get data from database.
                  include("db1.php");
                  $sql = mysql_query("select text from content where element_id='1'");
                  $row = mysql_fetch_array($sql);                  
                  echo $row['text'];
            ?>            

Regards,
John.A
PHPWeb Languages and StandardsWeb Servers

Avatar of undefined
Last Comment
JohnLourdu

8/22/2022 - Mon
Meir Rivkin

this is the basic syntax to create mysql table:
CREATE TABLE table_name (column_name column_type);

here's a php example creating mysql table:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorials_tbl( ".
       "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
       "tutorial_title VARCHAR(100) NOT NULL, ".
       "tutorial_author VARCHAR(40) NOT NULL, ".
       "submission_date DATE, ".
       "PRIMARY KEY ( tutorial_id )); ";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table created successfully\n";
mysql_close($conn);
?>

Open in new window


check here for reference:
http://www.tutorialspoint.com/mysql/mysql-create-tables.htm
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ray Paseur

John: Please read the Experts-Exchange grading guidelines.
http://support.experts-exchange.com/customer/portal/articles/481419

Since you marked the grade down to "B" without any comment or dialog we are entitled to receive an explanation of the deficiencies that you found in the answers.  I think your explanation of the bad grade is even more important given the fact that you got a complete, tested and working code example that shows exactly how to create a table!

Please explain, thanks. ~Ray
ASKER
JohnLourdu

Hi Ray,

I have updated your code to my file as below:

save.php

<?php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
//echo '<pre>';



$db_host = "localhost";
$db_name = "test";
$db_user = "root";
$db_word = "sam123";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno) {
$err = "CONNECT FAIL: " . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);

// CREATING A TABLE FOR OUR TEST DATA
$sql="CREATE TEMPORARY TABLE my_table(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
curhtmlcont TEXT NOT NULL DEFAULT '',
curhtmlpath TEXT NOT NULL DEFAULT '')";

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res) {
$err = 'QUERY FAILURE:' . ' ERRNO: ' . $mysqli->errno . ' ERROR: ' . $mysqli->error . ' QUERY: ' . $sql;
trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// GET POSTED DATA FROM PAGE1.PHP LIKE BELOW
/*
$("#libary-save-ico").click(function (e) {
$(".savebtn").hide();
document.body.contentEditable='false';
document.designMode='off';
void 0;

$(".flowing-wrapper").each(function() {
var flowWrapStyle = $(this).attr("style");
var flowWrapStatus = flowWrapStyle.substring(0,14);
if (flowWrapStatus=='display: block') {
var curhtmlpath = $(this).children("iframe").attr("src");
var curhtmlbody = $(this).children("iframe").contents().find("body").html();
//alert();
}
else {
var curhtmlpath = '';
var curhtmlbody = '';
}
});


var content = curhtmlbody;
var element_id = curhtmlpath;
$.ajax({
url: 'save.php',
type: 'POST',
data: {
content: content,
element_id: curhtmlpath
},
success:function (data) {
if (data == '1') {
$("#curEditStatus")
.addClass("success")
.html("Data saved successfully")
.fadeIn('slow')
.delay(3000)
.fadeOut('slow');
}
else {
$("#curEditStatus")
.addClass("error")
.html("Error, data could not be saved")
.fadeIn('slow')
.delay(3000)
.fadeOut('slow');
}
}
});
});
*/

$content = $_POST['content']; 
$element_id = $_POST['element_id'];

// ESCAPE THE DATA FOR SAFE USE IN A QUERY
$content = $mysqli->real_escape_string($content);
$element_id = $mysqli->real_escape_string($element_id);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
$sql = "INSERT INTO my_table (curhtmlcont, curhtmlpath) VALUES ('$content', '$element_id')";

// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res) {
$err = 'QUERY FAILURE:' . ' ERRNO: ' . $mysqli->errno . ' ERROR: ' . $mysqli->error . ' QUERY: ' . $sql;
trigger_error($err, E_USER_ERROR);
}

// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
$id  = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$element_id</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;

echo PHP_EOL;
	
?>

Open in new window


page1.php

<?php
$db_host = "localhost";
$db_name = "test";
$db_user = "root";
$db_word = "sam123";

if (isset($_POST['curhtmlpath'])) {

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno) {
$err = "CONNECT FAIL: " . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);

$curhtmlpath=$_POST['curhtmlpath'];
$safe_fn = $mysqli->real_escape_string($curhtmlpath);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT curhtmlcont FROM my_table WHERE element_id='$safe_fn'";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res) {
$err = 'QUERY FAILURE:' . ' ERRNO: ' . $mysqli->errno . ' ERROR: ' . $mysqli->error . ' QUERY: ' . $sql;
trigger_error($err, E_USER_ERROR);
}

// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object()) {
// ROW BY ROW PROCESSING IS DONE HERE
print_r($row);
echo PHP_EOL;
}
echo PHP_EOL;
}
?>

Open in new window


I have not received "Data saved successfully" prompt. Also I have refresh the page, the updates are gone. Please advice how to save content editable in remote server without refresh the current webpage.
Regards,
John.A
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ray Paseur

You need a bit of background information to understand how all of this works.  First, get up to speed on the Client/Server protocols.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

In the case of JavaScript and PHP scripts, the PHP script runs on the server as the result of an HTTP request from a client.  PHP creates the response and sends it to the client before the client can render the web page.  That means that the PHP script is complete and the server is disconnected by the time that the client begins running the JavaScript.  In other words, the client/server communication that you perceive as happening "without refresh the current webpage" is an AJAX request, initiated after the page load, by a JavaScript method.

Here is the hello world example of a simple jQuery/AJAX request.  I think your issues are mostly a matter of sequencing the client and server parts of the application.
https://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Jquery/A_10712-The-Hello-World-Exercise-with-jQuery-and-PHP.html

After you read the articles, if you still have any questions about it please post an example and we will be glad to help.
ASKER
JohnLourdu

Thanks for articles. But I fail to found where is the problem occurs. I have pasted my code below:

page1.php

<?php
session_start();
if ( !isset($_SESSION['login']) || $_SESSION['login'] !== true) {
header("location:index.php");
exit;
}
?>


<!DOCTYPE html>
<html>
    <head>
        <!-- EPUB module dependencies -->
        <script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js" type="text/javascript"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/modernizr/2.6.2/modernizr.min.js" type="text/javascript"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/json2/20121008/json2.js" type="text/javascript"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.4.4/underscore-min.js" type="text/javascript"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/backbone.js/1.0.0/backbone-min.js" type="text/javascript"></script>
        <script src="modules_and_dependencies/URI.js" type="text/javascript"></script>
        <script src="modules_and_dependencies/bootstrap.min.js" type="text/javascript"></script>

        <link rel="stylesheet" type="text/css" href="css/bootstrap.css">

	<script type="text/javascript" src="js/fancybox/source/jquery.fancybox.js?v=2.1.5"></script>
	<link rel="stylesheet" type="text/css" href="js/fancybox/source/jquery.fancybox.css?v=2.1.5" media="screen" />


        <!-- Readium Web Components SimpleRWC.js -->
        <script src="modules_and_dependencies/SimpleReadium.min.js" type="text/javascript"></script>
<style type="text/css">
.savebtn {
display: none;
}
</style>


        <!-- Example of initialization -->
        <script type="text/javascript">

            RWCDemoApp = {};

            // This function will retrieve a package document and load an EPUB
            RWCDemoApp.loadAndRenderEpub = function (packageDocumentURL, startSpineItem) {

                $(".show-on-load").show();
                $("#epub-reader-container").html("");

                var that = this;

                // Clear the viewer, if it has been defined -> to load a new epub
                RWCDemoApp.epubViewer = undefined;

                // Get the package document and load the modules
                $.ajax({
                    url : packageDocumentURL,
                    success : function (result) {

                        // Get the HTML element to bind the module reader to
                        var elementToBindReaderTo = $("#epub-reader-container");

                        // Create an object of viewer preferences
                        var viewerPreferences = {
                            fontSize : 12,
                            syntheticLayout : true,
                            currentMargin : 3,
                            tocVisible : false,
                            currentTheme : "default"
                        };
                        var currLayoutIsSynthetic = viewerPreferences.syntheticLayout;

                        if (result.nodeType) {
                            result = RWCDemoApp.parseXMLFromDOM(result);
                        }

                        // THE MOST IMPORTANT PART - INITIALIZING THE SIMPLE READIUM JS MODULE
                        RWCDemoApp.epubViewer = new SimpleReadiumJs(
                            elementToBindReaderTo, viewerPreferences, packageDocumentURL, result, "lazy"
                        );

                        // Set a fixed height for the epub viewer container, as a function of the document height
                        RWCDemoApp.setModuleContainerHeight();

                        // These are application specific handlers that wire the HTML to the SimpleReadiumJs module API

                        // Set callbacks for events triggered by SimpleReadium.js
                        RWCDemoApp.epubViewer.on("atNextPage", function () {
                            console.log("EVENT: atNextPage");
                        });
                        RWCDemoApp.epubViewer.on("atPreviousPage", function () {
                            console.log("EVENT: atPreviousPage");
                        });
                        RWCDemoApp.epubViewer.on("atFirstPage", function () {
                            console.log("EVENT: atFirstPage");
                        });
                        RWCDemoApp.epubViewer.on("atLastPage", function () {
                            console.log("EVENT: atLastPage");
                        });
                        RWCDemoApp.epubViewer.on("displayedContentChanged", function () {
                            console.log("EVENT: displayedContentChanged");
                        });

                        // Set handlers for click events
                        $("#previous-page-btn").unbind("click");
                        $("#previous-page-btn").on("click", function () {
                            RWCDemoApp.epubViewer.previousPage(function () {});
                        });

                        $("#next-page-btn").unbind("click");
                        $("#next-page-btn").on("click", function () {
                            RWCDemoApp.epubViewer.nextPage(function () {});
                        });

                        $("#toggle-synthetic-btn").unbind("click");
                        $("#toggle-synthetic-btn").on("click", function () {

                            if (currLayoutIsSynthetic) {
                                RWCDemoApp.epubViewer.setSyntheticLayout(false);
                                currLayoutIsSynthetic = false;
                                $("#single-page-ico").show();
                                $("#synthetic-page-ico").hide();
                            }
                            else {
                                RWCDemoApp.epubViewer.setSyntheticLayout(true);
                                currLayoutIsSynthetic = true;
                                $("#single-page-ico").hide();
                                $("#synthetic-page-ico").show();
                            }
                        });

                        // Render the reader
                        RWCDemoApp.epubViewer.on("epubLoaded", function () { 

                            if (startSpineItem === undefined) {
                                startSpineItem = 0;
                            }
                            // Show the first content document in the spine
                            RWCDemoApp.epubViewer.showSpineItem(startSpineItem, function () {
                                console.log("showed first spine item"); 
                            });
                            $(window).on("resize", function () {
                                RWCDemoApp.setModuleContainerHeight();
                                RWCDemoApp.epubViewer.resizeContent();
                            });

                            // APPLY A CUSTOM STYLE TO THE VIEWER
                            RWCDemoApp.epubViewer.customize("spine-divider", "box-shadow");
                        }, that);

                        RWCDemoApp.epubViewer.render(0);
                    }
                });
            };


            // When the document is ready, choose an EPUB to show.
            $(document).ready(function () {

            //alert(localStorage.curebpath);

                var that = this;
                var packageDocumentURL;

                $('#wdgt_beforeafter').fancybox({
                'width'        : '50%',
                'height'       : '100%',
                'autoScale'    : false, 
                'type'         : 'iframe',
                'overlayShow'   : false,
                'transitionIn'  : 'elastic',
                'transitionOut' : 'elastic'
                });

                // Load Moby Dick by default
                //RWCDemoApp.loadAndRenderEpub("epub_content/moby_dick/OPS/package.opf", 6);

                // Generate the library
                $.getJSON('epub_content/epub_library.json', function (data) {


                    $(".show-on-load").hide();

                    // Generate the library list in a drop-down
                    RWCDemoApp.addLibraryList($("#library-list"), data);


                    if (localStorage.curebpath) {
                    var liblistlastpath = localStorage.curebpath;
                    }
                    else {
                    var liblistlast = $("#library-list li");
                    var liblistlasttitle = liblistlast.last().children('a').text();
                    var liblistlastpath = liblistlast.last().children('a').attr('id');
                    //alert(liblistlast.last().children('a').attr('id'));
                    //$("#bkttl").text(liblistlasttitle);
                    }
                    RWCDemoApp.loadAndRenderEpub(liblistlastpath, 6);


                }).fail(function (result) {
                    console.log("The library could not be loaded");
                    RWCDemoApp.loadAndRenderEpub("epub_content/moby_dick/OPS/package.opf", 6);
                });

$("#library-edit-btn").click(function (e) {
$(".savebtn").show();
});

$("#libary-save-ico").click(function (e) {
$(".savebtn").hide();
document.body.contentEditable='false';
document.designMode='off';
void 0;
var indata1 = 'John';
var indata2 = 'Lourdu';

$.post("ray_ajax.php", {myArg1:indata1, myArg2:indata2}, function(response){
$("#curEditStatus").html(response);
});

});

});

            
        </script>

        <!-- Demo application code -->
        <script src="modules_and_dependencies/DemoAppCode.js" type="text/javascript"></script>
    </head>

    <!-- This is all application-specific HTML -->
    <body>
       <!-- The navbar -->
        <div class="navbar navbar-fixed-top" id="toolbar-el" contenteditable="false">
            <div class="navbar-inner" id="top-bar" style="">
                <div class="container" style="width: 100%">

                    <!-- Navbar logo -->
                    <a title="Simple RWC Demo" class="brand" href="#">
                        Datamatics&#x00A0;&#x2014;&#x00A0;ePubApp&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;&#x00A0;<span id="bkttl" class="newproj"> </span>
                    </a>

                    <!-- Navbar contents -->
                    <ul class="nav pull-right">

                        <!-- Log out -->
                        <li>
                            <a accesskey="n" href="logout.php" id="btn-logout" role="button" title="Logout">
                                <img src="img/system_log_out.png" class="nav-icon" id="system-logout-ico" alt="" style="">
                            </a>
                        </li>

                        <li class="divider-vertical"></li>

                        <!-- Library page -->
                        <li>
                            <a accesskey="n" href="bkself/shelf.php" id="library-page-btn" role="button" title="Library Page">
                                <img src="img/ico_library.png" class="nav-icon" id="libary-page-ico" alt="" style="">
                            </a>
                        </li>

                        <li class="divider-vertical"></li>

                        <!-- Save page -->
                        <li class="savebtn">
                            <a accesskey="n" href="" id="library-save-btn" role="button" title="Save Page">
                                <img src="img/file_save.gif" class="nav-icon" id="libary-save-ico" alt="" style="">
                            </a>
                        </li>

                        <li class="divider-vertical"></li>


                        <!-- Edit page --> 
                        <li>
                            <a accesskey="n" href="javascript:(function () {if (document.documentElement.contentEditable === false || document.designMode === 'off') {document.body.contentEditable='true';document.designMode='on';void 0;} else if (document.documentElement.contentEditable === true || document.designMode === 'on') {document.body.contentEditable='false';document.designMode='off';void 0;}})();" id="library-edit-btn" role="button" title="Edit Page">
                                <img src="img/file_edit.png" class="nav-icon" id="libary-edit-ico" alt="" style="">
                            </a>
                        </li>

                        <li class="divider-vertical"></li>


                        <!-- Widgets -->
                        <li>
                            <a accesskey="n" href=" " id="btn-widgets" role="button" title="Widgets" class="dropdown-toggle" data-toggle="dropdown">
                                <img src="img/Widgets.png" class="nav-icon" id="system-wdgts-ico" alt="" style="">
                            </a>
                            <ul id="wdgts-list" class="dropdown-menu" role="menu" aria-labelledby="dlabel">
                            <li><a id="wdgt_beforeafter" href="wdgts/beforeafter.php">Before and After</a></li>
                            </ul>
                        </li>

                        <li class="divider-vertical"></li>

                        <!-- Previous page -->
                        <li>
                            <a accesskey="n" href="#" id="previous-page-btn" role="button" title="Previous Page">
                                <img src="img/arrow_previous_medium.png" class="nav-icon" id="arrow-previous-ico" alt="" style="">
                            </a>
                        </li>
                        <!-- Next page -->
                        <li>
                            <a accesskey="n" href="#" id="next-page-btn" role="button" title="Next Page">
                                <img src="img/arrow_next_medium.png" class="nav-icon" id="arrow-next-ico">
                            </a>
                        </li>
                        
                        <li class="divider-vertical"></li>
                        
                        <!-- Toggle single or two pages -->
                        <li>
                            <a accesskey="n" href="#" id="toggle-synthetic-btn" role="button" title="Toggle page layout">
                                <img src="img/1page_view_toggle_medium.png" class="nav-icon" id="single-page-ico" style="display:none;">
                                <img src="img/2page_view_toggle_medium.png" class="nav-icon" id="synthetic-page-ico">
                            </a>
                        </li>

                        <li class="divider-vertical"></li>

                        <!-- The library for the sample reader -->
                        <li>
                            <a accesskey="n" href="#" id="epub-demo-library" role="button" title="Demo Library" class="dropdown-toggle" data-toggle="dropdown">
                                <img src="img/table_of_contents_medium.png" class="nav-icon" id="library-ico">
                            </a>
                            <ul id="library-list" class="dropdown-menu" role="menu" aria-labelledby="dlabel">
                            </ul>
                        </li>
                    </ul>
                </div>
            </div>
        </div>

        <div id="epub-reader-container" style="margin-top:65px;"></div>
<span id="curEditStatus"></span>
</body>
</html>

Open in new window

ray_ajax.php
<?php // RAY_ajax_server.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

// START THE OUTPUT BUFFER TO CAPTURE THE var_dump() DISPLAY
ob_start();

// MAKE THE OUTPUT EASY TO READ
echo '<pre>';

// SEND BACK THE DATE...
echo date('r');
echo  PHP_EOL;

// ... AND THE GET-REQUEST VARIABLES
echo 'GET: ';
var_dump($_GET);

// ... AND THE POST-REQUEST VARIABLES
echo 'POST: ';
var_dump($_POST);

echo '</pre>';
echo  PHP_EOL;

// CAPTURE THE OUTPUT BUFFER
$response = ob_get_clean();

// SEND THE CONTENTS OF THE OUTPUT BUFFER
die($response);

?>

Open in new window

Please advice how to solve this issue.
Regards,
John.A
ASKER
JohnLourdu

Thanks ray, I have figure it out. I have used empty <a href=""> in save icon. So the save.php not fired. I have fixed issue. Thanks for your support.

Regards,
John.A
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
JohnLourdu

Ray:

I unaware of grades in the solutions. Thanks for your suggestion. Please advice how to change Grade B to A.

Regards,
John.A