How to insert array into MYSQL database?

Question

I have an array that I am trying to insert into my MYSQL database. I'm not sure if its my MYSQL database or if its my php code but when I open my php file it does not insert anything into my table. Here is my code I have so far.

    <?php
    $con = mysql_connect("localhost","***","***");
    if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("ms122r_reports", $con); 

    // Filter out zeros
    $words = file('http://www.ndbc.noaa.gov/data/latest_obs/latest_obs.txt',              FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

    $words = array_map('rtrim',$words);
    $string = implode(" ",$words);
    $array = explode(" ",$string);

    $filtered = (array_filter($array));

    $row4 = array_slice($filtered, 704, 22);

    $var1 = array_slice($row4, 0, 1);
    $var2 = array_slice($row4, 1, 1);
    $var3 = array_slice($row4, 2, 1);
    $var4 = array_slice($row4, 3, 1);
    $var5 = array_slice($row4, 4, 1);
    $var6 = array_slice($row4, 5, 1);
    $var7 = array_slice($row4, 6, 1);
    $var8 = array_slice($row4, 7, 1);
    $var9 = array_slice($row4, 8, 1);
    $var10 = array_slice($row4, 9, 1);
    $var11 = array_slice($row4, 10, 1);
    $var12 = array_slice($row4, 11, 1);
    $var13 = array_slice($row4, 12, 1);
    $var14 = array_slice($row4, 13, 1);
    $var15 = array_slice($row4, 14, 1);
    $var16 = array_slice($row4, 15, 1);
    $var17 = array_slice($row4, 16, 1);
    $var18 = array_slice($row4, 17, 1);
    $var19 = array_slice($row4, 18, 1);
    $var20 = array_slice($row4, 19, 1);
    $var21 = array_slice($row4, 20, 1);
    $var22 = array_slice($row4, 21, 1);

    // Insert into database
    mysql_query("INSERT INTO table1(#STN, LAT, LON, YYYY, MM, DD, hh, mm2, WDIR, WSPD,     GST, WVHT, DPD, APD, MWD, PRES, PTDY, ATMP, WTMP, DEWP, VIS, TIDE)
    VALUES ($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8, $var9, $var10,   $var11, $var12, $var13, $var14, $var15, $var16, $var17, $var18, $var19, $var20, $var21,
    $var22)");

    mysql_close($con);
    ?>

Sorry I am new to PHP and MYSQL. My column names in my MYSQL database are the same as what appears in the ()'s of table1(), and they are set to "text" as the type. Any help is greatly appreciated thanks.

fgetcsv() with a space delimiter would make this so much easier. — user557846
– user557846, Commented May 8, 2012 at 2:19
If you ever find yourself using $var1-22, you are doing it wrong. — jordanm
– jordanm, Commented May 8, 2012 at 2:19

mpratt · Accepted Answer · 2012-05-08 02:35:54Z

1

Dont forget to quote your vars As in VALUES ('$var1', '$var2' ....)

By the way, I suggest you clean your code a little bit. Creating $var{1-22} is going to be a nightmare to mantain. You could use the implode function instead of creating 22 variables

I dont know, perhaps in this way?

echo 'VALUES (\'' . implode('\',\'', $row) . '\')';

Also, im not sure if Mysql allows columns with # on its name. Take a look at that your #STDN column and check if that is allowed.

Take into account the following:

Please Consider switching to PDO or mysqli. If you were using PDO this problem would have a very easy solution.
If you want to stick to mysql_* functions, at least sanitize all the data you are going to insert in your database with mysql_real_escape_string.

answered May 8, 2012 at 2:35

mpratt

1,60813 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Agung Gugiaji · Accepted Answer · 2012-05-08 02:19:26Z

0

Try to debug it with echoing your sql syntaxt in browser:

echo "INSERT INTO table1(#STN, LAT, LON, YYYY, MM, DD, hh, mm2, WDIR, WSPD,     GST, WVHT, DPD, APD, MWD, PRES, PTDY, ATMP, WTMP, DEWP, VIS, TIDE)
VALUES ($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8, $var9, $var10,   $var11, $var12, $var13, $var14, $var15, $var16, $var17, $var18, $var19, $var20, $var21,
$var22)";

Copy the echo result and execute it on mysql. See whats happen.

Hope this help

answered May 8, 2012 at 2:19

Agung Gugiaji

1213 bronze badges

Comments

jordanm · Accepted Answer · 2012-05-08 02:25:04Z

0

I am going to answer the title, which may also help you solve your issue. You can slice your array into another array to get only the values you want to insert.

$dbh = new PDO('mysql:host=localhost;dbname=dbname', $user, $pass);
$sth = $dbh->prepare('INSERT INTO table1(STN, LAT, LON, YYYY, MM, DD, hh, mm2, WDIR, WSPD,     GST, WVHT, DPD, APD, MWD, PRES, PTDY, ATMP, WTMP, DEWP, VIS, TIDE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
$sth->execute(array())

answered May 8, 2012 at 2:25

jordanm

35.4k8 gold badges70 silver badges80 bronze badges

Comments

Falcon · Accepted Answer · 2012-05-08 06:18:38Z

0

You need to wrap your varables in code like '$var1' and in end of do die(mysql_error) like:

mysql_query("INSERT INTO table1(#STN, LAT, LON, YYYY, MM, DD, hh, mm2, WDIR, WSPD,     GST, WVHT, DPD, APD, MWD, PRES, PTDY, ATMP, WTMP, DEWP, VIS, TIDE)
    VALUES ('$var1', '$var2', '$var3', '$var4', '$var5', '$var6', '$var7', '$var8', '$var9', '$var10',   '$var11', '$var12', '$var13', '$var14', '$var15', '$var16', '$var17', '$var18', '$var19', '$var20', '$var21',
   '$var22')") or die(mysql_error());

What mysql_error does is check and report errors in your statement. If got any, post them here to let us resolve it. And you sure you are connectign to the database??

answered May 8, 2012 at 6:18

Falcon

2593 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to insert array into MYSQL database?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related