arrays-to-sql-statements.php

<?php
  $social_media_sites = [
    [
    "name" => "facebook",
    "url" => "https://www.facebook.com",
    "title_prefix" => "like"
    ],
    [
    "name" => "twitter",
    "url" => "https://twitter.com",
    "title_prefix" => "follow"
    ],
    [
    "name" => "linkedin",
    "url" => "https://linkedin.com",
    "title_prefix" => "find"
    ],
    [
    "name" => "instagram",
    "url" => "https://instagram.com",
    "title_prefix" => "follow"
    ],
    [
    "name" => "youtube",
    "url" => "https://youtube.com",
    "title_prefix" => "find"
    ]
  ];

  $sharing_links = [
    [
    "name" => "facebook",
    "base_url" => "http://www.facebook.com/sharer.php?u=",
    "title_prefix" => "share"
    ],
    [
    "name" => "twitter",
    "base_url" => "https://twitter.com/share?url=",
    "title_prefix" => "tweet"
    ],
    [
    "name" => "google plus",
    "base_url" => "https://plus.google.com/share?url=",
    "title_prefix" => "share"
    ],
    [
    "name" => "email",
    "base_url" => "mailto:?",
    "title_prefix" => "send"
    ]
  ];

  $tablenames = [
    "social_media_sites",
    "sharing_links"
  ];

  $tablecontents = [
    $social_media_sites,
    $sharing_links
  ];

  // Helper function to check for last element in an array for proper SQL statement termination.
  function last($element, $array) {
    return $element == array_values(array_slice($array, -1))[0];
  }

  // SQL generation function
  function construct_sql(array $tablenames, array $items) {
    $final_sql = "";
    for ($i = 0; $i < count($items); $i++) {
      // table fields
      $fields = $items[$i][0];

      // creating the table
      $csql = "DROP TABLE IF EXISTS `". $tablenames[$i] . "`;" . "<br>";
      $csql .= "CREATE TABLE IF NOT EXISTS `". $tablenames[$i] . "` (" . "<br>";
      $csql .= "&nbsp;&nbsp;";
      $csql .= "`id` int(11) NOT NULL AUTO_INCREMENT," . "<br>";
      foreach ($fields as $k => $v) {
        $csql .= "&nbsp;&nbsp;`" . $k . "` ";
        $csql .= "VARCHAR(255) NOT NULL," . "<br>";
      }
      $csql .= "&nbsp;&nbsp;";
      $csql .= "PRIMARY KEY (`id`)" . "<br>";
      $csql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" . "<br><br>";

      // inserting data
      $csql .= "INSERT INTO `$tablenames[$i]` (";
      foreach ($fields as $k => $v) {
        $csql .= "`" . $k . "`";
        $csql .= !last($v, $fields) ? ", " : "";
      }
      $csql .= ") VALUES " . "<br>";

      // table values
      foreach ($items[$i] as $p) {
        $csql .= '("' . join('", "', $p);
        $csql .= '")';
        $csql .= last($p, $items[$i]) ? ";" : ",";
        $csql .= '<br>';
      }

      $final_sql .= $csql . '<br>';
    }
    return $final_sql;
  }

  echo construct_sql($tablenames, $tablecontents); // Testing SQL generation.

?>