Solved

PHP excecuting SQL commands

Posted on 2003-10-26
12
1,793 Views
Last Modified: 2012-08-13
How can PHP execute SQL commands?
such as reading from a text file and inserting the data into MySQL database?
0
Comment
Question by:cofeeextravaganza
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:DoppyNL
ID: 9625248
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
 

Author Comment

by:cofeeextravaganza
ID: 9625305
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
 
LVL 6

Accepted Solution

by:
DoppyNL earned 45 total points
ID: 9625314
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
 

Author Comment

by:cofeeextravaganza
ID: 9625319
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
 
LVL 6

Expert Comment

by:DoppyNL
ID: 9625332
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
 

Author Comment

by:cofeeextravaganza
ID: 9625340
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:DoppyNL
ID: 9625349
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
 

Author Comment

by:cofeeextravaganza
ID: 9625354
it looks like it cannot insert data from a textfile with that code..
it can only get a display..
0
 

Author Comment

by:cofeeextravaganza
ID: 9625363
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
 

Author Comment

by:cofeeextravaganza
ID: 9625655
okay, i sorta got it now.. but there's one problem..
how do i specify a null value in PHP?
0
 
LVL 6

Expert Comment

by:DoppyNL
ID: 9625668
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
 

Author Comment

by:cofeeextravaganza
ID: 9625710
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
RSS feed not showing item time 3 19
PHP PDO get the error if exists 3 71
I starting with php 12 114
How do the JavaScript &&  !== operators work together? 3 50
Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now