Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

So I'm developing a web application in php that's grabbing a .csv file and parsing through it. It then submits it to a mysql database. I'm successfully retrieving the data and I'm able to input all of the numerical values that I'm retrieving. The one problem I'm having is with inserting the date into the sql database.

I'm not getting an error that I'm aware of and the date values are just 0000-00-00. I'm error checking by echoing the data in php to the screen when I run the script. It echoes fine and is in the format of yyyy-mm-dd. Below is php code that will run fine if you want to give it a test run, unfortunately I don't have a public server to let you test it on if you don't have wamp/lamp/equivalent already running on your system.

Am I missing something simple? Why won't this date insert correctly? Is it the wrong format? It looks to be the right format, but who knows?

<?php

    //Connect to Database
    $con=mysqli_connect("localhost","root","","stockmarket");
    // Check connection
    if (mysqli_connect_errno($con))
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }


    // Setup URL to download csv file
    $requestUrl = "http://ichart.yahoo.com/table.csv?s=GOOG";


    // Pull data (download CSV as file)
    $CSV = file_get_contents($requestUrl);
    // Split results, trim way the extra line break at the end
    $quotes = explode("\n",trim($CSV));

    //Explore array with .csv contents
    foreach($quotes as $quoteraw) {
    $quoteraw = str_replace(", I", " I", $quoteraw);
    $quote = explode(",", $quoteraw);
    echo $quote[0];//error check, should print dates in correct format      

        //Insert contents into database 
    mysqli_query($con,"INSERT INTO nasdaq (Symbol,Date,Open,High,Low,Close,Volume,Adjusted Close) VALUES ('goog',$quote[0],$quote[1],$quote[2],$quote[3],$quote[4],$quote[5],$quote[6])");
    }

    mysqli_close($con);
?>
share|improve this question
1  
If you checked the mysql error you might see it. For one thing, date is a reserved word. –  Jessica Jul 8 at 0:15
 
Okay, is it just a simple line to check the error? –  Stephen R Jul 8 at 0:16
3  
@Jessica date is a reserved keyword but is permitted to use without escaping it. The real problem is one of the columns contains space in between. To the OP, you need to escape it using single quote. –  今 草 顿 웃 Jul 8 at 0:16
1  
You would use mysql_error(). –  Jessica Jul 8 at 0:17
1  
mysqli_error($con) would be useful in this case - mysqli_query($con,"INSERT INTO nasdaq ... ) or die(mysqli_error($con)); –  Sean Jul 8 at 0:32
show 2 more comments

2 Answers

up vote 2 down vote accepted

You must remember to quote the values that goes into your MySQL query.

INSERT INTO nasdaq (Symbol,Date,Open,High,Low,Close,Volume,Adjusted Close) 
VALUES ('goog','".$quote[0]."','".$quote[1]."','".$quote[2]."'"); -- and so on

Your query gives you an SQL error. You must always check the result of mysql_query to see if it is correct. As pointed out by others, you should take further steps to ensure that you escape the individual $quote values, and preferably use prepared statements.

share|improve this answer
2  
Please remember. The OP is using MySQli, which is the improved extension to mysql_* functions. Suggesting PDO will do nothing but teach a new Database library, when MySQli is not depreciated. –  Daryl Gill Jul 8 at 0:19
 
Fair comment. I'll edit my answer. –  apartridge Jul 8 at 2:01
 
Thanks so much apartridge. Daryl Gill seems to be in a grumpy mood tonight, downvoting my question and telling you you were wrong. However, your answer fixed my problem completely. I really appreciate your feedback as I was stuck on this problem for a few hours and you were of great help :). Have a great week, +1, best answer, Thank You! :)) –  Stephen R Jul 8 at 2:02
1  
Glad i could help. –  apartridge Jul 8 at 2:03
add comment
up vote 1 down vote

As mentioned, you need to quote your strings. I don't recommend the approach by apartridge, because it will fail if any of your strings contain quote characters. It also opens you up to SQL Injections attacks.

For example: running this query will fail:

<?php
$name = "O'Brien";
$query = "INSERT INTO table (LastName) VALUES ('$name')";
[...]
?>

To help me, I use a simple function to escape and quote each variable in one step, using MySQLi's own escape function:

<?php

function sq($mysql_obj, $value) {
    // this function quotes $value to make it safe in an SQL statement
    return "'" . $mysql_obj->real_escape_string($value) . "'";
}

$mi = new mysqli("hostname", "username", "password", "database");
$name = "O'Brien";
$query = "INSERT INTO table (LastName) VALUES (".sq($mi,$name).")";
[...]
?>

Note that you can also quote numeric data and MySQL will handle it quite happily. So you may as well quote everything this way.

There are more things you can do to sanitise input and protect against attacks but that goes beyond answering the question.

share|improve this answer
 
Cool, thankyou gbe, +1. I'm keeping apartridge as first answer because I could literally copy that line of code into my .php file and have it work (and it did). But I will keep this in mind and implement it before (if) I go live. –  Stephen R Jul 8 at 13:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.