PHP MySQL SQL Parser (Insert and Update)

PHP MySQL SQL parser (INSERT and UPDATE)

http://pear.php.net/package/SQL_Parser

http://sourceforge.net/projects/txtsql

http://code.google.com/p/php-sql-parser

For Perl there's more variety

Parsing MySQL prepare statement

remove '' in update and insert

$update = $wpdb->prepare("UPDATE '. $dbTable. ' SET Distance = %d WHERE Date = %s AND UserID = %d, '. $distance. ', '. $today. ', '. $userID. '");
$insert =$wpdb->prepare("INSERT INTO '. $dbTable. ' (Distance, Date, UserID) VALUES (%d,%s,%d)", '. $distance. ', '. $today. ', '. $userID.'");

and execute in funtion like this $update->execute()

you received syntx error because $update contains string not query
in your code here

$wpdb->query($update);

$update contains'$wpdb->prepare("UPDATE '. $dbTable. ' SET Distance = %d WHERE Date = %s AND UserID = %d", '. $distance. ', '. $today. ', '. $userID. ')'; \

echen you print $update it will print the value of update which your prepare statement.

for e.g $R='priot()'; do you think this will execute, it won't $R contain value i.e priot() not tha return value of priot()

learn here

Parsing sql query PHP

Use an SQL parser. http://code.google.com/p/php-sql-parser/

Copy paste from this example:

<?php
  require_once('php-sql-parser.php');
  $parser=new PHPSQLParser('SELECT a FROM some_table an_alias WHERE d > 5;', true);

  print_r($parser->parsed);  

Example output:

Array
(
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [alias] => 
                    [base_expr] => a
                    [sub_tree] => 
                    [position] => 8
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [expr_type] => table
                    [table] => some_table
                    [alias] => Array
                        (
                            [as] => 
                            [name] => an_alias
                            [base_expr] => an_alias
                            [position] => 29
                        )

                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => some_table an_alias
                    [sub_tree] => 
                    [position] => 18
                )

        )

    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => d
                    [sub_tree] => 
                    [position] => 45
                )

            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => >
                    [sub_tree] => 
                    [position] => 47
                )

            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => 5
                    [sub_tree] => 
                    [position] => 49
                )

        )

)

PHP MySQL INSERT INTO parsing error

fulltext is a reserved word in MySQL. Either use backticks to escape it or use another name.

INSERT INTO nlqov_content (..., `fulltext`, ... 

PHP MYSQL UPDATE if Exist or INSERT if not?

I believe you are looking for the following syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement

How to parse a string to insert a row into a mysql table?

You should always prepare your variables in a good manner like below that is a good practice:

    $userId = $_SESSION['user_id'];
    $productId = $row['product_id'];
    $company = $row['saving'];
    $categoryId = 1;
    $savingAmount = $saving;

    $sql = "INSERT INTO user_saving_plans (user_id, catergory_id, product_id, 
    savings_amount, company) 
    VALUES ('$userId', 'categoryId', '$productId', '$savingAmount', '$company')";

Parsing sql query PHP

Use an SQL parser. http://code.google.com/p/php-sql-parser/

Copy paste from this example:

<?php
  require_once('php-sql-parser.php');
  $parser=new PHPSQLParser('SELECT a FROM some_table an_alias WHERE d > 5;', true);

  print_r($parser->parsed);  

Example output:

Array
(
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [alias] => 
                    [base_expr] => a
                    [sub_tree] => 
                    [position] => 8
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [expr_type] => table
                    [table] => some_table
                    [alias] => Array
                        (
                            [as] => 
                            [name] => an_alias
                            [base_expr] => an_alias
                            [position] => 29
                        )

                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => some_table an_alias
                    [sub_tree] => 
                    [position] => 18
                )

        )

    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => d
                    [sub_tree] => 
                    [position] => 45
                )

            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => >
                    [sub_tree] => 
                    [position] => 47
                )

            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => 5
                    [sub_tree] => 
                    [position] => 49
                )

        )

)

---

Related Topics