MySQL Server error using php code

Question

MySQL query is giving the error but it seems to me that table name in data_new only and columns are same as written

This is the SQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0573','cate-csir','NKN')' at line 1"

<?php
    $fm = fopen('main.txt','r');
    @mysql_connect('localhost','root','');
    @mysql_select_db('username'); 
    $count=0;

    while ($line = fgets($fm)) {
        $new_data=@split(':',$line);
        $query="INSERT INTO `data_new` (`directory`,`machineip`,`description`,`state`,`status`,`instituteid`,`category`,`project`) VALUES ('$new_data[0]','$new_data[1]','$new_data[2]','$new_data[3]','$new_data[4],'$new_data[5]','$new_data[6]','$new_data[7]')";

    }

    $result=@mysql_query($query);

    if(!$result){
        die(mysql_error());
    }

    fclose($fm);
?>

Every time you use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), **a Kitten is strangled somewhere in the world** it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Aug 26 '16 at 09:08
If you were not supressing ALL YOUR ERRORS (`@`) then maybe you would have been told what the error was — RiggsFolly, Aug 26 '16 at 09:11
You are only running the code that actually issues a query to the database `mysql_query($query);` ONCE after looping round the complete file. DUH — RiggsFolly, Aug 26 '16 at 09:15
@ error supression is still just a demonstration of ineptitude or lazyness — RiggsFolly, Aug 26 '16 at 09:18
@RiggsFolly Perhaps OP is only starting to learn PHP and saw it somewhere in a tutorial not knowing what it does. No need to be rude. — Cas, Aug 26 '16 at 09:48
@cascer1 Quite possibly, but now OP knows that it is a bad idea. They also need to be told to find a tutorial that uses `mysqli_` or `PDO` database extensions, which I also did. — RiggsFolly, Aug 26 '16 at 09:50

Shadow · Answer 1 · 2016-08-26T09:12:56.973

2

You have a missing ' after $new_data[4] in the insert command:

...'$new_data[4],'$new_data[5]'...
                ^
                |
               here

Just a note: MySQL extension has been deprecated long time ago and has been removed from php as of v7. Use mysqli or pdo instead.

edited Aug 26 '16 at 09:12

answered Aug 26 '16 at 09:10

Shadow

33,525
10
51
64

Look closer mate there is more – RiggsFolly Aug 26 '16 at 09:12
Like running the `$result=@mysql_query($query);` only once OUTSIDE the loop – RiggsFolly Aug 26 '16 at 09:12
@RiggsFolly I did not debug the whole code. I answered the question. – Shadow Aug 26 '16 at 09:15
Hay just trying to help so you can give a full and complete answer rather than just a _Faster Gun In The West Answer_ – RiggsFolly Aug 26 '16 at 09:16
@RiggsFolly submit your own answer if you believe the others are wrong or incomplete. Commenting those small statements doesn't help OP understand *why* something doesn't work. – Cas Aug 26 '16 at 09:49
@cascer1 I did, however as Shadow had already started I though I might just tell him about the other error so he could cover all the errors in one go – RiggsFolly Aug 26 '16 at 09:52
@cascer1 believe it or not I was trying to be helpful and not trying to point out a problem with this answer. Notice I did not DV or anything unpleasant like that – RiggsFolly Aug 26 '16 at 09:54

score 1 · Answer 2 · edited May 23 '17 at 12:14

You have 2 obvious errors one syntactic error and one logic error

First a missing quote after '$new_data[4] in the VALUES clause

Second you are only issuing the last query you build i.e. after the loop has completed. That will only INSERT the last line of your input file to the database.

<?php
    $fm = fopen('main.txt','r');

    // its also a good idea to check your  connection worked before proceeding with any more code
    $link = mysql_connect('localhost','root','');
    if (!$link) {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('username'); 
    $count=0;

    while ($line = fgets($fm)) {
        $new_data=@split(':',$line);

        // a missing single quote after'$new_data[4]
        $query="INSERT INTO `data_new`  
                     (`directory`,`machineip`,`description`,
                      `state`,`status`,`instituteid`,
                      `category`,`project`) 
              VALUES ('$new_data[0]','$new_data[1]','$new_data[2]',
                      '$new_data[3]','$new_data[4]','$new_data[5]',
                       '$new_data[6]','$new_data[7]')";

        // and the query must be executed inside the loop 
        $result = mysql_query($query);

        if(!$result){
            die(mysql_error());
        }

    }

    //$result=@mysql_query($query);

    //if(!$result){
    //    die(mysql_error());
    //}

    fclose($fm);
?>

I have to mention Every time you use the mysql_ database extension, a Kitten is strangled somewhere in the world it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the PDO database extensions. Start here its really pretty easy

On some days, I wish I had a login error too ... and in case you're wondering, I'm the one who monitors the mysql library usage and has to strangle all those kittens. I fear for my sanity so for all that is holy, stop using the mysql extension! ._. — Jakumi, Aug 26 '16 at 09:32
@Jakumi Oh and I spotted the typo, once I worked out the cryptic clue in your comment. Thanks — RiggsFolly, Aug 26 '16 at 09:55

MySQL Server error using php code

2 Answers2