• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1829
  • Last Modified:

PHP excecuting SQL commands

How can PHP execute SQL commands?
such as reading from a text file and inserting the data into MySQL database?
0
cofeeextravaganza
Asked:
cofeeextravaganza
  • 7
  • 5
1 Solution
 
DoppyNLCommented:
Take a look at the manual pages on php.net, it will explain all you need to know.
http://www.php.net/mysql

Most helpfull will probably be the example on that page, since that gives you an example on how to do it.

Allthough it's not really neccesary to release results and close the database connection in your script (php does that automaticly at the end of the script).
0
 
cofeeextravaganzaAuthor Commented:
okay, i did this ...


+++++++++++++++++++++++++++++++

<?php
    /* Connecting, selecting database */
    $link = mysql_connect("localhost", "root", "blahblah")
        or die("Could not connect : " . mysql_error());
    print "Connected successfully";
    mysql_select_db("newdb") or die("Could not select database");

    /* Performing SQL query */
    $query = "LOAD DATA LOCAL INFILE '/var/www/html/PHPs/010403.txt' INTO TABLE `taff` FIELDS TERMINATED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n'";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);
?>

+++++++++++++++++++++++++++


then i get this error ..


Connected successfullyQuery failed : 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 ''' at line 2



what's the problem?
0
 
DoppyNLCommented:
there is an error in your query.
use this line to check if it is really ok:

print($query);

also, there is no need to put \n in your query.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cofeeextravaganzaAuthor Commented:
but when i use the above query in mysql itself, it works fine..

and when i use other queries on the page, it works, such as a SELECT * FROM table; query

hmm....
0
 
DoppyNLCommented:
there could be a problem with the query because it might be changed a little by PHP (escape characters and stuff like that are involved).
if you print it out to the page, you can see exactly what is actually executed.
0
 
cofeeextravaganzaAuthor Commented:
It still gives this statement:

Connected successfullyQuery failed : 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 ''' at line 2


and does not print the query out..
i tried printing the query out with a different query and it works fine.
0
 
DoppyNLCommented:
try this:

$query = "LOAD DATA LOCAL INFILE '/var/www/html/PHPs/010403.txt' INTO TABLE `taff` FIELDS TERMINATED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\\n'";

(note the extra \ at the end)
0
 
cofeeextravaganzaAuthor Commented:
it looks like it cannot insert data from a textfile with that code..
it can only get a display..
0
 
cofeeextravaganzaAuthor Commented:
now it says ..

Connected successfullyQuery failed : 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 '\n'' at line 1

0
 
cofeeextravaganzaAuthor Commented:
okay, i sorta got it now.. but there's one problem..
how do i specify a null value in PHP?
0
 
DoppyNLCommented:
I never use null values in PHP itself.
I use isset() to check if a variable is set
http://www.php.net/isset
and unset() to "delete" a variable
http://www.php.net/unset

If you need to set a field in the database to the "value" "null", simply put that in as a value

i.e.: "set `field` = null"
0
 
cofeeextravaganzaAuthor Commented:
yesss!!!
done..

$query = "LOAD DATA LOCAL INFILE '/var/www/html/PHPs/010403.txt' INTO TABLE `taff` FIELDS TERMINATED BY '' ESCAPED BY '\'' LINES TERMINATED BY '\n'";

what a stupid thing! just one little character missplaced! urrrgh..

thanks hey.. :D
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now