Parsing of my own SQL Request in Php

user_7494305 0
Viewed 41

At the moment I'm making a php program that can take a SQL Request with a left join (only two table) the goal is to display them and to edit them. The problem is that if I want to edit them I have to know where the variable comes from (which table...) and i would like to parse my request for know it.

SELECT users.login, users_info.linkedin, users.password, users.region, users.email, users.business, users.firstname, users.name, users.version, users.updatable, users.bodyshop_id, users.mac, users.register_date, users.lastvisite_date , users_info.address1, users_info.address2, users_info.address3, users_info.zip, users_info.cellphone, users_info.phone, users_info.direct_dial_phone, users_info.birthday, users_info.date_in, users_info.date_out, users_info.picture_private, users_info.picture_public, users_info.bodyshop, users_info.enterprise, users_info.department, users_info.job, users_info.facebook, users_info.twitter, users_info.instagram
FROM users
LEFT JOIN users_info ON users.id = users_info.id

So I just want to create a function that take for example "login" as parameter and return me "users" or linkedin would return me users_info. Do you know a proper way for doing it in php ? Thanks !

mysql parsing php
4 Answers
SELECT users.login, users.password, users.region, users.email, users.business, users.firstname, users.name, users.version, users.updatable, users.bodyshop_id, users.mac, users.register_date, users.lastvisite_date , users_info.address1, users_info.address2, users_info.address3, users_info.zip, users_info.cellphone, users_info.phone, users_info.direct_dial_phone, users_info.birthday, users_info.date_in, users_info.date_out, users_info.picture_private, users_info.picture_public, users_info.bodyshop, users_info.enterprise, users_info.department, users_info.job, users_info.facebook, users_info.twitter, users_info.linkedin, users_info.instagram
FROM users as users 
LEFT JOIN users_info as users_info ON users.id = users_info.id
WHERE user.you_field_name = your_condition
user_68085630

The most modern syntax to connect mysql in php today is using of PDO. File index.php, for example:

<?php
        class ConnectPDO {

            private $db_name = 'my_db_name';
            private $db_login = 'root';
            private $db_password = '12345678';
            private $host = 'localhost';
            private $opt = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ];
            public $connection = null;

            function __construct() {
                $this->connection = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->db_name . ';charset=utf8', $this->db_login, $this->db_password, $this->opt);
            }
        }



    $c=new ConnectPDO();

    $params=array();
    $params['login']='Frodo';

    $prepared=$c->prepare("SELECT * FROM users WHERE login=:login");
    $prepared->execute($params);
    // fetching data array
    $data=$prepared->fetchAll();

    // view into browser
    echo '<pre>';
    print_r($data);
    echo '</pre>';

To prevent injections and to use best practice coding well known practice is to use prepare and execute methods. You can install xampp fast on windows and test this code. Working folder is /xampp/htdocs.

This parameters need to by changed by your credentials.

private $db_name = 'my_db_name';
            private $db_login = 'root';
            private $db_password = '12345678';
            private $host = 'localhost';
user_23600720

Use $stmt->fetch(PDO::FETCH_NUM); to get your data in numeric indexed array, then use $stmt->getColumnMeta($col_num); to get info about a column in question.

user_41609000

Finaly I did it like that thanks for helped !

public function getTableWithField($field) {

    $tableName1 = "users";
    $tableName2 = "users_info";

    $pos = strpos($this->SQLCMD, $field);

    $tryOne = substr($this->SQLCMD, $pos - strlen($tableName1) - 1, strlen($tableName1));
    $tryTwo = substr($this->SQLCMD, $pos - strlen($tableName2) - 1, strlen($tableName2));

    if ($tableName1 == $tryOne)
        return $tableName1;

    else if ($tableName2 == $tryTwo)
        return $tableName2;

    return "";
}
user_74943050
Related