Parse Select Clause of SQL Queries into a PHP Array

Parse SELECT clause of SQL queries into a PHP array

You would have to write a parser almost as complex as MySQL's query parser (written in YACC/Bison for C). It's not going to be a regular expression or a little string manipulation. This is a nonregular language, you can't parse them without an actual parser.

You can't just walk through the string finding commas and parentheses either, SQL is much more complex than that. You have expressions within expressions, function calls, conditional logic, etc. all of which can be nested arbitrarily deep with commas and parentheses all over.

http://dev.mysql.com/doc/refman/5.0/en/expressions.html

If you really want to do this with PHP, you have a big job ahead of yourself.

How to use php array with sql IN operator?

Since you have plain integers, you can simply join them with commas:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";

If working with with strings, particularly untrusted input:

$sql = "SELECT * FROM table WHERE comp_id IN ('" 
     . implode("','", array_map('mysql_real_escape_string', $arr)) 
     . "')";

Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.

mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.

However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";

How to parse query row into arrays?

In PHP 5.3 and later, fetch_all will return all rows as an array. If you specify MYSQLI_ASSOC as the result type, each element in the array will be an associative array of fields, indexed by name.

// having done the query above

$rows = $mQuery->fetch_all(MYSQLI_ASSOC);

foreach ($rows as $row)
{
  print "email: " . $row['email'] . "<br />";
  // etc
}

for earlier versions, you'd do it manually:

$rows = array();

while ($row = $mQuery->fetch_array(MYSQLI_ASSOC))
  $rows[] = $row;

foreach ($rows as $row)
{
  print "email: " . $row['email'] . "<br />";
  // etc
}

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
                )

        )

)

How to parse sql queries in php

Very ugly solution :

$string = "select column1,column2,column3 from table where  condition";

$data1 = explode(" ",$string);
$data2 = explode(",",$data1[1]);
$data1[1] = $data2[0];
$string2 = implode($data1, " ");

print $string2;

how to run an sql query against all items in array php

I really had no clue what I was doing. what you gotta do is convert a JSON object to an array and loop thrugy the array.

it should look something like this.(this example comes from w3 schools)

$jsonobj = '{"Peter":35,"Ben":37,"Joe":43}';

$arr = json_decode($jsonobj, true);

foreach($arr as $key => $value) {
  echo $key . " => " . $value . "<br>";
}

https://www.w3schools.com/php/php_json.asp

How to convert array values to MySQL query using PHP SQL Parser?

To build a query from the Array, PHPSQLParser has a creator method,

From the documentation here : Parser manual

There are two ways in which you can create statements from parser output
Use the constructor
The constructor simply calls the create() method on the provided parser tree output for convenience.

 $parser = new PHPSQLParser('select 1');

 $creator = new PHPSQLCreator($parser->parsed);

 echo $creator->created;

Use the create() method

 $parser = new PHPSQLParser('select 2');

 $creator = new PHPSQLCreator();

 echo $creator->create($parser->parsed); 

 /* this is okay, the SQL is saved in the _created_ property. */

 /* get the SQL statement for the last parsed statement */

 $save = $creator->created;

of course since $parser->parsed is an array, you can passe your own Array

echo $creator->create($myArray);

To add a condition to the array, you can add it to the WHERE array of conditions

each condition has 3 arrays defining colref ( the column name ), operator ( well .. operator ) and const ( the value )

the tricky part is the position in the sub array of the WHERE as you need to specify where exactly you want to insert each one of those three, so based on the WHERE in the example you provided , you can see that the position of the operator = is 169 ( starting from 0 )

check this tool to see character position in a string ( this starts from 1 ).

And based on this Complexe example

Your final WHERE Array should look like this (i'm not sure if you need the [no_quotes] key though) :

PS : i used the query with multiple conditions you provided and took off indentation and line breaks to figure out the positions, play around with those values if you don't have the desired string output as this is supposed to be just an example.

i hope this helps or at least gives you an idea and Good Luck.

Formatting a PHP array for an SQL IN clause

There is a better way

You mention in the comments that you are using CodeIgniter. Unless you are making something extraordinarily complicated, there is no practical reason you should be building your own home-baked queries when you have where_in built in.

And if that doesn't work, then there is good ol' fashioned escape.

OK, so, you have most people saying that you need to quote the items and are giving you this:

function createInClause($arr)
{
    return '\'' . implode( '\', \'', $arr ) . '\'';
}

but that really isn't sufficient if you have the possibility for questionable input (such as '); DROP TABLE STUDENTS; --. To protect against that, you need to make sure you check for SQL injection:

function createInClause($arr)
{
    $tmp = array();
    foreach($arr as $item)
    {
        // this line makes sure you don't risk a sql injection attack
        // $connection is your current connection
        $tmp[] = mysqli_escape_string($connection, $item);
    }
    return '\'' . implode( '\', \'', $tmp ) . '\'';
}