使用多个外键的简单 PHP 代码 [英] Simple PHP code for using multiple foreign keys

问题描述

我正在尝试编写订单流程.我在一个数据库 (dbphesemaas) 中有 3 个不同的表(订单、产品、用户).

I'm trying to code an order process. I have 3 different tables (orders, product, users) in a single database (dbphesemaas).

到目前为止我尝试过的方法不起作用:

What I've tried so far doesn't work:

<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('dbphesemaas');

$username=$_POST["username"];
$area=$_POST["area"];
$product=$_POST["product"];
$address=$_POST["address"];
$dol_quantity=$_POST["quantity"];



$query="INSERT INTO orders (id, product_id, address, quantity) VALUES ('$id', '$id2', '$address', '$dol_quantity')";

mysql_close();
?> 

有人可以让这段代码工作吗，id是用户的外键，而product_id是产品的外键?

Can someone make this code work, the id is a foreign key from users, while the product_id is a foreign key of product?

推荐答案

1. 错误处理

您只需连接并执行查询.

1. Error handling

You just connect and execute the query.

好吧，不是 - 你如何确保一切正常?

Well yeah nope - how are you making sure that everything worked?

让我们从错误处理开始.

Let's start off with error handling.

<?php
    $link = mysql_connect('localhost', 'root', '');

    if (!$link) {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('dbphesemaas');
?> 

连接正常吗?数据库选择成功了吗?

Is the connection working? Did the database get selected successfully?

您可以使用 if 模块来检查它是否有效.

You can use the if module to check if it worked.

<?php
    // IF $link = mysql_connect('localhost', 'root', '') did not work (note the ! in front of it)
    if(!$link = mysql_connect('localhost', 'root', '')){
        die('Could not connect to localhost'); // The message displayed. die() will prevent the rest of the script from executing.
    }

    // IF database "dbphesemaas" did not get selected succesfully (note the ! in front of it)
    if(!mysql_select_db('dbphesemaas', $link)){
        die('Could not select the database &quot;dbphesemaas&quot;'); // The message displayed. die() will prevent the rest of the script from executing.
    }
?> 

现在我们有连接工作.如果出现问题，脚本将停止执行并抛出自定义错误.

Now we have the connection working. If something goes wrong, the script will stop being executed and throw a custom error.

$username=$_POST["username"];
$area=$_POST["area"];
$product=$_POST["product"];
$address=$_POST["address"];
$dol_quantity=$_POST["quantity"];

现在是我的问题，为什么?仅在查询中使用它们没有任何问题.你只创建变量的唯一原因是旧变量很长(所以拼写错误的可能性更大)和/或你认为代码太混乱.由于这段代码中使用$_POST变量没有问题，所以我们将这段代码从头开始.

Now is my question, why? There is nothing wrong with just using them inside the query. The only reason why you only would make variables is if the old variable is very long (so the chance of typo's are bigger) and/or if the code is too messy in your opinion. Since there is no problem in this code to use the $_POST variable, we're going to scratch this piece of code.

$query="INSERT INTO orders (id, product_id, address, quantity) VALUES ('$id', '$id2', '$address', '$dol_quantity')";

这里有几个问题:

1. 您编写了查询，但并未执行它.
2. 您在引号内使用了变量($id、$id2 等).在错误的情况下，它会在数据库中插入 $id 而不是实际值.
3. 再一次，没有错误处理.
4. 完全没有污点.用户可以添加到您的查询中并更改查询，从而使可能的泄漏和被黑客攻击的机会更大.我们将使用 mysql_real_escape_string 来防止这种情况:http://php.net/manual/en/function.mysql-real-escape-string.php
5. 看起来有点乱，但这只是视觉问题.

1. You wrote the query, but you aren't executing it.
2. You are using variables ($id, $id2 etc) inside quotes. In the wrong scenario, it's gonna insert $id in the database instead of the actual value.
3. Once again, no error handling.
4. No untainting at all. The user can add on into your query and alter the query, making a possible leak and the chance of being hacked bigger. We're going to prevent this with mysql_real_escape_string: http://php.net/manual/en/function.mysql-real-escape-string.php
5. Looks kinda messy, but that's just a visual problem.

让我们解决这些问题:

$query="
    INSERT INTO 
        orders 
    (
        id, 
        product_id, 
        address, 
        quantity
    ) 
    VALUES 
    (
        '". mysql_real_escape_string($_POST['id']) ."', 
        '". mysql_real_escape_string($_POST['id2']) ."', 
        '". mysql_real_escape_string($_POST['adress']) ."', 
        '". mysql_real_escape_string($_POST['quantity']) ."'
    )
";

if(mysql_query($query)){
    echo 'Succesfully executed the query.';
}
else
{
    echo 'Query not executed - MySQL error. <br>';
    echo '<pre>'. mysql_error() .'</pre>';
}

使用 '". (random php code) ."' 允许在字符串中执行 php 代码.例如:

Using '". (random php code) ."' allows php code to be executed within a string. For example:

$variable = 'This is text '. strtoupper('this is capitalized since strtoupper makes this capital. note that this is inside the string.') .' and this is once again lowercase.';

4.保留这个以备将来使用

我编写这些代码的方式对未来很有用.每次打开/添加新括号时保留使用选项卡 ({).

更多信息 - 从 PHP 5.5 开始，默认的 mysql_* 函数将被弃用 - 将来使用 MySQLi，它是改进的版本.信息:http://www.php.net/manual/en/book.mysqli.php

Further info - the default mysql_* functions are going to be deprecated as of PHP 5.5 - Use MySQLi in the future, it's the improved version. Info: http://www.php.net/manual/en/book.mysqli.php

一个mysql_query只能执行一个查询.你可以这样做:

One mysql_query can only execute one query. You can do this:

$queries = array();
$errors = array();

$queries[] = 'INSERT INTO ... '; // using $variable[] will add another entry to the $variable array.
$queries[] = 'INSERT INTO ... ';
$queries[] = 'UPDATE bla SET ...';

foreach($queries as $query){

    // Foreach will seperate the entries in an array

    // IF mysql query failed
    if(!mysql_query($query)){
        $errors[] = mysql_error(); // We'll add the errors to an array aswell.
    }
}

// Check if there are entries in the $failures array.
if(count($errors) > 0){
    echo 'We had some MySQL errors.';

    echo '<ul>';
    foreach($errors as $failure){
        echo '<li>'. $failure .'</li>';
    }
    echo '</ul>';

}
else
{
    echo 'No errors - MySQL queries executed succesfully.';
}

希望这对您有所帮助.

这篇关于使用多个外键的简单 PHP 代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！