Asked  7 Months ago    Answers:  5   Viewed   31 times

I have a special case with a PHP project where I am working with a database without a 3rd normal form structure. The database consists of only 1 table with loads of columns. Some data that is supposed to be in a separate table, is clamped in 1 column, separated by a certain sign (in this case, semicolon ";").

There is also other columns where clamped data should be moved to the same separated table as mentioned. This must be confusing, so let me elaborate:

**HugeTable**
id |  Column1      |  Column2     |  Column3

123 | Data1;Data2 Data3;Data4 Data5;Data6

I need to put the data above in a separate table that looks like this:

**NewTable**
id   |  idHugeTable  |  Column1  |  Column2  |  Column3
 1   |  123          |  Data1    |  Data3    |  Data5
 2   |  123          |  Data2    |  Data4    |  Data6

So for each clamped data in the huge table, I need to make a new row in the new table. This process would help me to normalize the database so that it is at least workable. Right now it's a nightmare. This needs to be done either through PHP or MySQL, preferably PHP since looping is easier for one-shot queries per loop through the scripting language.

Edit: Example code of what I have tried in PHP:

$delimiter = ";";
$query = "SELECT * FROM HugeTable";

$result = mysqli_query($connection_var, $query);

while ($row = mysqli_fetch_assoc()){
    $column1_data = explode($delimiter, $row['Column1']);
    $column2_data = explode($delimiter, $row['Column2']);
    $column3_data = explode($delimiter, $row['Column3']);

    foreach ($column1_data as $key => $value){
        //skip if empty value
        if ($value == ""){
            continue;
        }
        else{
            $query_ins = "INSERT INTO NewTable (idHugeTable, Column1, Column2, Column3) VALUES (".$row['id'].", ".$column1_data[$key].", ".$column2_data[$key].", ".$column3_data[$key].");";
            mysqli_query($connection_var, $query_ins);
        }//end if
    }//end foreach
}//end while

mysqli_close($connection_var);

 Answers

91

No PHP is needed. You can do it with pure MySQL code only.

Create table/insert table

CREATE TABLE HugeTable
    (`Column1` VARCHAR(11), `Column2` VARCHAR(11), `Column3` VARCHAR(11))
;

INSERT INTO HugeTable
    (`Column1`, `Column2`, `Column3`)
VALUES
    ('Data1;Data2', 'Data3;Data4', 'Data5;Data6')
; 

CREATE TABLE NewTable
   (`Column1` VARCHAR(11), `Column2` VARCHAR(11), `Column3` VARCHAR(11))
;

First we need MySQL to generate numbers. This MySQL code generates 1 to 100. So the final query will support up to 100 separated values.

Query

SELECT 
 @row := @row + 1 AS ROW
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
  SELECT @row := 0 
) init_user_params 

Result

  row  
--------
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
     ...
     ...
      90
      91
      92
      93
      94
      95
      96
      97
      98
      99
     100

Now we can look at a method to separate on the ; delimiter. We can use nested SUBSTRING_INDEX functions for that

Query

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Data1;Data2', ';', 1), ';', -1) AS DATA

Result

data    
--------
Data1   

You can see only the first word is returned if we want the second word we can use

Query

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Data1;Data2', ';', 2), ';', -1) AS DATA

Result

data    
--------
Data2  

Now we combine the number generator and the SUBSTRING_INDEX to generate the data

Query

SELECT 
  DISTINCT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Column1, ';', rows.row), ';', -1) Column1
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column2, ';', rows.row), ';', -1) Column2
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column3, ';', rows.row), ';', -1) Column3
FROM (
  SELECT 
   @row := @row + 1 AS ROW
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params
)
 ROWS
CROSS JOIN 
 HugeTable 

Result

Column1  Column2  Column3  
-------  -------  ---------
Data1    Data3    Data5    
Data2    Data4    Data6    

Query NewTable

INSERT INTO 
  NewTable
SELECT 
  DISTINCT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Column1, ';', rows.row), ';', -1) Column1
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column2, ';', rows.row), ';', -1) Column2
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column3, ';', rows.row), ';', -1) Column3
FROM (
  SELECT 
   @row := @row + 1 AS ROW
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params
)
 ROWS
CROSS JOIN 
 HugeTable 

Query

SELECT * FROM NewTable

Result

Column1  Column2  Column3  
-------  -------  ---------
Data1    Data3    Data5    
Data2    Data4    Data6   
Wednesday, March 31, 2021
 
Revent
answered 7 Months ago
76

Go for bash loop, because shell_exec function is called only once. It will be faster than calling shell_exec multiple times. Enabling functions like exec, shell_exec itself a huge security issue. If someone managed to upload a PHP shell in your server then he can hack your server.

Saturday, May 29, 2021
 
ericstumper
answered 5 Months ago
88

Delete your other question, ok?

The problem is you loop through $_SESSION and use the same name value each time. You need to create an array of your inputs. Here is an example:

<?php
echo '<h3>Your Order</h3>';
$current_url = base64_encode($url='http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']);
  if(isset($_SESSION['products'])){
     echo '<ol>';
     echo '<form action="checkout_with_us.php" method="POST">';
     $total = 0;
     $cart_items = 0;

        foreach($_SESSION['products'] as $cart_itm){
           $product_code = $cart_itm['code'];
           $results = $mysqli->query("SELECT product_name,product_desc,price FROM products WHERE product_code='$product_code' LIMIT 1");
             $obj = $results->fetch_object();
                echo '<li>';
                echo 'Price: '.$currency.$obj->price;
                echo '<h4>'.$obj->product_name.'(Code: '.$product_code.')</h4>';
                echo 'Qty: '.$cart_itm['qty'];
                echo '</li>';

                   $subtotal = ($cart_itm['price'] * $cart_itm['qty']);
                     $total = ($total + $subtotal);
                     $cart_items++;
                       echo '<input type="hidden" name="product['.$product_code.'][item_name]" value="'.$obj->product_name.'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_desc]" value="'.$obj->product_desc.'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_qty]" value="'.$cart_itm["qty"].'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_code]" value="'.$product_code.'">';     
                }
                    echo '<strong>Sub Total: '.$currency.$total.'</strong>';
                    echo '<input type="hidden" name="product['.$product_code.'][price]" value="'.$total.'">';
                    echo '</ol>';   
                    }

//Here is the information of the customer
echo 'Firstname: <input type="text" name="firstname"><br />';
echo 'Lastname: <input type="text" name="lastname"><br />';
echo 'Email: <input type="text" name="email"><br />';
echo '<input type="submit" value="Send Step">';

echo '</form>';
?>

You can catch this by looping in your product array:

<?php
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];

$conn = mysqli_connect('localhost','root','','sampsix')or die('Could not connect');

foreach($_POST['product'] as $product)
{
    $order_name = $product['item_name'];
    $order_code = $product['item_code'];
    $order_qty = $product['item_qty'];
    $sub_total = $product['price'];

    $query = "INSERT INTO `sampsix`.`orders`(`firstname`,`lastname`,`email`,`OrderName`,`OrderCode`,`OrderQty`,`SubTotal`) VALUES('$firstname','$lastname','$email','$order_name','$order_code','$order_qty','$sub_total')";
    mysqli_query($conn,$query);
}



mysqli_close($conn);

header('Location: checkout.php');
?>

I don't know what the purpose is of the table orders but with my example the products will be added to this table with the same firstname, lastname, etc.

Saturday, May 29, 2021
 
Ula
answered 5 Months ago
Ula
86

PHPUnit normally runs within the CLI - command line interface.

The PHP you've got there is different than with the webserver. Different binary and often a different configuration as well.

$ php -r "new mysqli();"

Should give you the same error. Verify where the binary and configuration is located:

$ which php

And

$ php -i | grep ini

Ensure you've got the extension for the mysqli class installed and enabled. Once configured you should be able to run your unit-tests flawlessly.

Sunday, August 22, 2021
 
David Heffernan
answered 2 Months ago
23

Okay, it looks like the SCHEMATA table in the information_scheme database contains a list of all databases. Thus, in order to get a list of all the databases you want to run the procedure on, you can do:

SELECT schema_name FROM information_schema.schemata
WHERE schema_name LIKE 'application_%';

The next step is to work this into some kind of procedure. Unfortunately, MySQL doesn't do well with executing dynamically generated SQL if it involves creating procedures. Thus, the purely-SQL version I came up with is a bit messy. It comes down to first creating the 'generator' procedure, then calling it, and finally executing the result of the generator:

delimiter //
DROP PROCEDURE IF EXISTS create_procedures//
CREATE PROCEDURE create_procedures()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE db VARCHAR(255);
    DECLARE appDBs CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'application_%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @procName = "simpleproc"; -- Change this to your proc name

    SET @output = "delimiter //";

    OPEN appDBs;
    REPEAT
        FETCH appDBs INTO db;
        IF NOT done THEN
            -- Replace this procedure declaration with your procedure.
            -- Make sure to keep the ',db,' syntax there.
            -- You should really only have to change the parameters
            -- and the stuff between the BEGIN and END clauses.
            SET @output = CONCAT(@output,'
    DROP PROCEDURE IF EXISTS ',db,'.',@procName,'//
    CREATE PROCEDURE ',db,'.',@procName,'()
        BEGIN
            SELECT 1;
        END//');

        END IF;
    UNTIL done END REPEAT;

    CLOSE appDBs;

    SET @output = CONCAT(@output,'ndelimiter ;');

    SELECT @output AS procs;
END//
delimiter ;

After this procedure is generated, call the procedure:

CALL create_procedures();

This will output a single column that contains the SQL necessary to create procedures for all the application_% tables. Select the entire column (it'll be rather long), and execute it as a new SQL query.

I've never used SQLyog, but if that doesn't work properly, then you may need to use MySQL's command line interface. First, generate a file input.sql containing:

CALL create_procedures();

Then execute the following commands:

mysql -u <username> -p --database=<dbname> -N -r -B < input.sql > proc.sql
mysql -u <username> -p --database=<dbname> < proc.sql

changing <username> and <dbname> to appropriate values (<dbname> can be any database you have permission to access). If you don't run into any errors, then you should have stored procedures defined for every database.

Monday, October 4, 2021
 
LukeP
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :