MySQL issues

evil-monkey · 19-05-2012 03:21PM #1

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.

carveone · 19-05-2012 05:33PM

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

evil-monkey · 19-05-2012 05:40PM

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.

carveone · 19-05-2012 06:20PM

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...

fcrossen · 20-05-2012 10:26AM

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

MySQL issues

Comments