查询语句中的数组用法 - MySQL通过PHP

Hey guys I'm new to PHP yet I gave a shot. There is a change in the requirements now for what I do. The values B1 to B11 are not the same any more. It can be from B1 to B6 or B1 to B50. So I can tell that I need an array to pass the value. However what I need to know is how do I include them in the $sql statement. Instead of B1 to B11 do I have to use any variables? Below is the code for reference.Any help would be greatly appreciated.

嘿伙计们我是PHP的新手,但是我试了一下。现在对我的要求有所改变。值B1至B11不再相同。它可以是B1到B6或B1到B50。所以我可以告诉我需要一个数组来传递值。但是我需要知道的是如何将它们包含在$ sql语句中。我必须使用任何变量而不是B1到B11吗?以下是供参考的代码。非常感谢任何帮助。

    <?php

    $DB_hostname = "localhost";
    $DB_Name = "root";
    $DB_pass = "pass123";


    if(isset($_GET["slot"])){
       $tableName = $_GET["slot"];
       $db = $_GET["db"];
    }
     echo $tableName;

   $con = mysql_connect($DB_Hostname,$DB_Name,$DB_pass) or die(mysql_error());

   // Create table
   mysql_select_db($tableName = $_GET["slot"];, $con);
   if(mysql_num_rows(mysql_query("SHOW TABLES LIKE $tableName"))==1) {
      echo "Table exists";
   }else {

       echo "Table does not exist";
    // Create table
    mysql_select_db($db, $con);
    $sql = "CREATE TABLE $tableName
    (
    Slot int NOT NULL,
    PRIMARY KEY(Slot),
    B1 TEXT NOT NULL,
    B2 TEXT NOT NULL,
    B3 TEXT NOT NULL,
    B4 TEXT NOT NULL,
    B5 TEXT NOT NULL,
    B6 TEXT NOT NULL,
    B7 TEXT NOT NULL,
    B8 TEXT NOT NULL,
    B9 TEXT NOT NULL,
    B10 TEXT NOT NULL,
    B11 TEXT NOT NULL
   )";
   mysql_query($sql,$con);

   }


   mysql_close($con);
   ?>

6 个解决方案

#1

I am not too sure why you need to have so many columns inside your table. A better solution might to have $tableName only have two columns, one for the slot, and the other that defines how many slots the table will have.

我不太清楚为什么你需要在表格中包含这么多列。一个更好的解决方案可能是让$ tableName只有两列,一列用于插槽,另一列用于定义表将有多少个插槽。

The slot column will then be set as auto_increment inside MySQL, which means each time a row is added to the database, the number increments.

然后将插槽列设置为MySQL内的auto_increment,这意味着每次将一行添加到数据库时,该数字都会递增。

The next table, will include all of the data that was originally in the the other columns. It would have three columns, the slot column, the position column (which would replace the B$x columns from the original table) and the value column, where the TEXT data is placed.

下一个表格将包含最初位于其他列中的所有数据。它将有三列,槽列,位置列(将替换原始表中的B $ x列)和值列,其中放置TEXT数据。

This makes your database relational (see http://en.wikipedia.org/wiki/Relational_model)

这使您的数据库成为关系(请参阅http://en.wikipedia.org/wiki/Relational_model)

You can then do a LEFT JOIN on the two tables to retrieve all of the data (http://www.w3schools.com/sql/sql_join.asp)

然后,您可以在两个表上执行LEFT JOIN以检索所有数据(http://www.w3schools.com/sql/sql_join.asp)

That being said, for your current solution, you would simply need to change some of your code.

话虽这么说,对于您当前的解决方案,您只需要更改一些代码。

if(isset($_GET["slot"])){
   $tableName = $_GET["slot"];
   $db = $_GET["db"];
}

//getting the slot count
if (isset($_GET['slots'])) {
   $slots = (int)$_GET['slots'];
}

if (!isset($slots) or !isset($tableName)) {
   echo "Please first provide the right data to this script!";
   exit;
}

// Create the table
$_columns = array();
for ($i = 1; $i <= $slots; $i++) {
    $_columns[] = "B$i TEXT NOT NULL ";
}

$sql = "CREATE TABLE $tableName
(
Slot int NOT NULL,
PRIMARY KEY(Slot),
" . implode( ', ', $_columns) . "
)";

#2

You can put all your B1,B2 values in an array and then create your $sql variable like this. This will work for any number of entries.

您可以将所有B1,B2值放在一个数组中,然后像这样创建$ sql变量。这适用于任意数量的条目。

<?php
$values=array();
$values[]="B1";
$values[]="B2";
$values[]="B3";
$values[]="B4";

$sql = "CREATE TABLE $tableName
    (
    Slot int NOT NULL,
    PRIMARY KEY(Slot)";

    foreach($values as $value)
    {
    $sql.=",
    $value TEXT NOT NULL";
    }
    $sql.=")";


echo $sql;

?>

You can also simplify it further. If those values are always going to start with B and end on a numeric value then you could just simply run a $count variable and loop as many times and add that rather than having to hand type all the B values in an array.

您还可以进一步简化它。如果这些值总是以B开头并以数字值结束,那么您只需运行$ count变量并循环多次并添加,而不必手动键入数组中的所有B值。

#3

<?php
$con = mysql_connect("localhost", "root", "12345678") or die(mysql_error());
$tableName="new_table";
// Create table
mysql_select_db("spend_track", $con);
if (mysql_num_rows(mysql_query("SHOW TABLES LIKE $tableName")) == 1) {
  echo "Table exists";
} else {

  // Create table
  mysql_select_db($db, $con);
  $sql = "CREATE TABLE $tableName
    (
    Slot int NOT NULL,
    PRIMARY KEY(Slot),
    $filed1 TEXT NOT NULL,
    $filed2 TEXT NOT NULL,
    $filed3 TEXT NOT NULL,
    $filed4 TEXT NOT NULL,
    $filed5 TEXT NOT NULL,
    $filed6 TEXT NOT NULL,
    $filed7 TEXT NOT NULL,
    $filed8 TEXT NOT NULL,
    $filed9 TEXT NOT NULL,
    $filed10 TEXT NOT NULL,
    $filed11 TEXT NOT NULL
   )";
 if(mysql_query($sql, $con)){
   echo "success to create";
 }else{
   echo "failed to create";
 }
}


mysql_close($con);
?>

Here using variable for instead of direct value. Please note that filed name should be unique.It can not be repeat.

这里使用变量而不是直接值。请注意,提交的名称应该是唯一的。不能重复。

#4

Try like this

试试这样吧

You can pass the the array as like IP/file.php?db=dbname&my_arr=B1,B2,B3

您可以像IP / file.php一样传递数组?db = dbname&my_arr = B1,B2,B3

$my_cols = explode(',', $_GET['my_arr']);
$sql = "CREATE TABLE $tableName     (
Slot int NOT NULL,
PRIMARY KEY(Slot)";
foreach($my_cols as $col){
    $sql .= " ,`".$col."` TEXT NOT NULL";
}
$sql .= ')';

#5

You are missing the user information in connection string. It should be like

您缺少连接字符串中的用户信息。应该是这样的

mysqli_connect(host,username,password,dbname);

#6

I have to admit that the question is absolutely not clear. What do you want to do? Insert new records? Create the table? And wait, why should we know that some requirements have changed, we don't know original requirements ;)

我不得不承认这个问题绝对不明确。您想做什么?插入新记录?创建表?等等,为什么我们应该知道一些要求已经改变,我们不知道原始要求;)

There still two things to notice here.

这里还有两件事需要注意。

Escape your variables with mysql_real_escape_string function.

使用mysql_real_escape_string函数转义变量。

Better use PDO or MySQLi because MySQL extension you are using is deprecated.

更好地使用PDO或MySQLi,因为您使用的MySQL扩展已弃用。

#1