Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL issues

Options
  • 19-05-2012 4:21pm
    #1
    evil-monkey
    Closed Accounts Posts: 1,663 ✭✭✭


    Hi all,

    Built a simple database which I'm now having some issues with. There are two issues: the first is with importing data, the second is with editing it.

    1. Importing data

    My table has has only three fields: ID, INSERT_DATE and INSERT_TEXT

    I want to import data using CSV. I have tried the following:

    "1";"2012-01-01";"This is a test";

    but I am receiving the following message:
    Import has been successfully finished, 0 queries executed.

    Any ideas?

    2. Outputing content

    I have created a page where people can edit the DB data externally. The code is as follows. This connects fine and works ok, but for one issue. Any data in INSERT_TEXT that contains an apostrophe is not outputing correctly, and therefore inputting incorrectly after external users make edits.

    Here is the relevant code:
    <title>You may edit dates and lines of text here</title>
<script type="text/javascript">
    var globalID = null;
    function getResponse(url)
        {
            if (window.ActiveXObject) // for IE
            {
                httpRequest = new ActiveXObject("Microsoft.XMLHTTP");
            }
            else if (window.XMLHttpRequest) // for other browsers
            {
                httpRequest = new XMLHttpRequest();
            }
            httpRequest.open("GET", url, true);
            httpRequest.onreadystatechange = function() {processRequest(); } ;
            httpRequest.send(null);
        }

    function processRequest()
    {
        if (httpRequest.readyState == 4)
        {
            if(httpRequest.status == 200)
            {
                checkResponse(httpRequest.responseText);
                httpRequest=null;
            }
            else
            {
                document.getElementById("response"+globalID).innerHTML="error occured";
            }
        }
    }

    function checkResponse(response)
    {
        var str= response;
                alert(str);
                window.location ='lines_gallery.php';
                var ap = document.getElementById("row"+globalID);
                ap.style.backgroundColor='green';
    }

function checkStatus(val)
{
    deleteBtn.onClick=checkStatus(val);
}

function  DeleteImage(id)
{
    globalID = id;
    var URL = "deleteLine.php?line_id="+globalID;
    getResponse(URL);
}
function  EditImage()
{         
    var dateVal = document.getElementById("date"+globalID).value;
    var lineVal = document.getElementById("line"+globalID).value;
    var URL = "EditLine.php?line_id="+globalID+"&dateVal="+dateVal+"&lineVal="+lineVal;
    getResponse(URL);
}
  function highlight(id)
  {
      globalID = id;
      var ap = document.getElementById("row"+id);
      ap.style.backgroundColor='gray';     
  }
</script>
</head>
<body>
<center><h1>Lines Gallery</h1></center>
<center>

    <table align="center" width="100%">
        <tr style="background-color:#01303F;color:white;">
            <th width="100px;">Date</th>
            <th>Line Of Text</th>
             <th width="100px;">Action</th>
        </tr>
        <?php
        include 'db_connect.php';
$query = "SELECT ID,INSERT_DATE,LINE_TEXT FROM DATA_TAB ORDER BY INSERT_DATE DESC";
$result = mysql_query($query);
        while($row = mysql_fetch_array($result))
        {
             $id = $row['ID'];
            $insert_date = $row['INSERT_DATE'];
            $line_text = $row['LINE_TEXT'];
           echo  "<tr  id='row".$id."' ><td><input type='text'  name='date".$id."' id='date".$id."' value='".$insert_date."' onclick='highlight(".$id.");'/></td>";
               echo  "<td align='left' width='nowrap'><input type='text' style='width:100%;'  name='line".$id."' id='line".$id."' value='".$line_text."' onclick='highlight(".$id.");'/></td>";
               echo  "<td><input type='image' src='images/edit.png' style='width:25px;height:25px;border:none;' name='editBtn' id='editBtn' onclick='EditImage();'/> &nbsp; &nbsp; <input type='image' src='images/delete.png' style='width:25px;height:25px;border:none;' name='deleteBtn' id='deleteBtn' onclick='DeleteImage(".$id.");' /></td></tr>";
        }
        mysql_close($con);
        ?>
    </table>
</center>
</body>

    And here's my db_connect.php:

    [PHP]<?php
    $con = mysql_connect("*****","*****","*****");
    if (!$con)
    {
    echo "error occured while connecting to database";
    die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("*****",$con);
    ?>
    [/PHP]

    Thanks in advance for any help with this.




Comments

  • Options
    #2
    carveone
    Registered Users Posts: 1,216 ✭✭✭carveone


    evil-monkey wrote: »
    Hi all,

    Built a simple database which I'm now having some issues with. There are two issues: the first is with importing data, the second is with editing it.

    1. Importing data

    My table has has only three fields: ID, INSERT_DATE and INSERT_TEXT

    I want to import data using CSV. I have tried the following:

    "1";"2012-01-01";"This is a test";

    but I am receiving the following message:
    Import has been successfully finished, 0 queries executed.

    Any ideas?

    I'll try on your first problem. What command are you using to import the data. Given that you've separated the fields with semicolon, I think it's supposed to be:
    load data local infile 'mydata.csv' into table tablename fields terminated by ';' enclosed by '"' lines terminated by '\n';

    See: http://dev.mysql.com/doc/refman/5.5/en/load-data.html


  • Options
    #3
    evil-monkey
    Closed Accounts Posts: 1,663 ✭✭✭evil-monkey


    carveone wrote: »
    I'll try on your first problem. What command are you using to import the data. Given that you've separated the fields with semicolon, I think it's supposed to be:
    load data local infile 'mydata.csv' into table tablename fields terminated by ';' enclosed by '"' lines terminated by '\n';
    See: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

    Sorry - I should have specified - I'm using phpMyAdmin.

    Screenshot attached.


  • Options
    #4
    carveone
    Registered Users Posts: 1,216 ✭✭✭carveone


    Ummm. Scratching my head a bit - don't know phpadmin enough I'm afraid. All I can think of is there's supposed to be a header to indicate which field is which:

    ID; INSERT_DATE; INSERT_TEXT

    "1";"2012-01-01";"This is a test";

    or something. I'm totally guessing I'm afraid :(

    Edit: And that's only if the number of fields in the table differs from the number of fields (and order) provided in the CSV file. I'm stumped...


  • Options
    #5
    fcrossen
    Registered Users Posts: 241 ✭✭fcrossen


    evil-monkey wrote: »
    I want to import data using CSV.
    .......
    but I am receiving the following message:
    Import has been successfully finished, 0 queries executed.
    This is normal. Did you check in phpMyAdmin that the data was imported?
    evil-monkey wrote: »
    follows. This connects fine and works ok, but for one issue. Any data in INSERT_TEXT that contains an apostrophe is not outputing correctly, and therefore inputting incorrectly after external users make edits.
    Single quotes must be escaped in MySQL. See:
    http://forums.devarticles.com/php-development-48/escaping-special-characters-for-storage-in-database-mysql-9255.html


Advertisement