PHP vs SQL query to split row

Question

Hello I just need to point to right direction. I went a long way to make a [for most of you simple] code.

But now I am on crossroad if I should dig deeper in php or sql.

I manage to parse some web page and put the data need into sql database. The data looks like

<a>name of the street</a> of course I need a name of the street without html

now

Can I put any mysql command through php? [that means I dig deeper into sql and just pass that command]

Or does php vs mysql commands have some limitation [wich means dig deeper into regex to get better result]

Yeah I found examples how to split rows in sql I am not just sure if I can pass them.

Answer 1

There a three types of Database connections, or API's, in PHP; to keep this to the point, I am going to cover PDO.

Your question: Can I use complex SQL in PHP, meaning put anything into the query?
The Answer: Yes, you can - in fact, its just like running it inside the Database.

PHP is a weakly typed language meaning you do not need to use Ragex to do complex sequences.

An example here is joining rows for a deletion:

// Connecting to the database via PDO
$dsn = 'mysql: host=localhost; dbname=example';
$user = "user";
$password = "pass";
try {
   $db = new PDO($dsn, $user, $password);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// The query
$stmt = $db->prepare("DELETE t2.* FROM table t1 JOIN table1 t2 ON t1.column= t2.column");
if($stmt->execute()): echo 'Successfully deleted!'; endif; // Conditional statements

// note this returns a bool datatype so we can check if it was successful or not

To learn more about PDO you can use this manual.

Note: You may want to read up on SQL Injections if you're taking this approach - PDO is secure but injections come from user-code sides. (See integration of Classes and Scopes for security when using Databases).

Mysqli example (as requested):

$db = mysqli_connect("localhost","user","pass","name");
if($db->query("DELETE t2.* FROM table t1 JOIN table1 t2 ON t1.column= t2.column")):
    echo 'Deleted Successfully!';
endif;