Saya telah membuat pernyataan pilih menggunakan php untuk mengembalikan hasil ke HTML berdasarkan input pengguna. Umpan balik dikembalikan berdasarkan bidang input yang cocok dengan bidang dalam tabel yang diisi secara manual. Pernyataan pilih harus mengembalikan ID tertinggi. Formulir memasukkan data ke dalam tabel 'adopsi'. Hasilnya kemudian mengembalikan baris dari tabel 'anjing' berdasarkan bidang di bawah yang cocok dengan tabel 'adopsi'.

<?php
$connect = mysqli_connect("localhost", "root", "", "ncdr_first", "3308");

$sql = "SELECT dogs.name, dogs.breed, dogs.age, dogs.sex, dogs.image, adoption.adoption_id 
        FROM dogs 
            INNER JOIN adoption ON dogs.adultRadio = adoption.adultRadio 
                AND dogs.child = adoption.child 
                AND dogs.petRadio = adoption.petRadio 
                AND dogs.pet = adoption.pet 
                AND dogs.activity = adoption.activity 
                AND dogs.home = adoption.home 
                AND dogs.environment = adoption.environment
        ORDER BY adoption.adoption_id DESC 
        LIMIT 0, 1";

$result = mysqli_query($connect, $sql);

?>

Hasilnya kemudian ditampilkan dalam tabel di bagian HTML halaman.

<div class="table">
    <center><table id="feedback">
            <tr>
                <th>Name</th>
                <th>Breed</th>
                <th>Age</th>
                <th>Sex</th>
                <th>Image</th>
            </tr>
    <?php
    if($result->num_rows > 0){
        while($row = $result->fetch_assoc()){
    ?>
        <tr>
            <td><?php echo $row["name"];?></td>
            <td><?php echo $row["breed"];?></td>
            <td><?php echo $row["age"];?></td>
            <td><?php echo $row["sex"];?></td>  
            <td><img src="<?php echo $row["image"];?>"/></td>
        </tr>
    <?php
        }

    } else {
        echo "Unfortunately you have not been a match for any of our furry friends! Please try again soon.";
    }   
    ?>
    </center></table>


CREATE TABLE dogs (dog_id int, name varchar, breed varchar, age varchar, sex 
varchar, adultRadio varchar, child varchar, petRadio varchar, pet varchar, 
activity varchar, home varchar, environment varchar, image varchar ); 

CREATE TABLE adoption (adoption_id int, name varchar, address varchar, 
adultRadio varchar, child varchar, petRadio varchar, pet varchar, activity 
varchar, home varchar, environment varchar, image varchar );

Namun, jika saya memasukkan data ke tabel 'adopsi' yang tidak cocok dengan salah satu baris di tabel 'anjing', itu akan mengembalikan baris yang terakhir dicocokkan alih-alih pernyataan di yang lain. Adakah yang punya saran tentang cara memperbaikinya?

<?php
session_start();

//Check if user is logged in, if not redirect to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
header("location: login.php");
exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="eng">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta name="view" content="width=device-width"/>
    <title>North Coast Dog Rescue Contact Page</title>
    <meta name="description" content="Responsive website for North Coast Dog 
 Rescue"/>
    <meta name="author" content="Chloe Leighton B00710338"/>
    <link rel="stylesheet" href="style.css"/>
<style type="text/css">
    .formContainer #adultNo:checked ~ .conditional, #petYes:checked ~ .conditional {
    clip: auto;
    height: auto;
    margin: 0;
    overflow: visible;
    position: static;
    width: auto;
    }

    .formContainer #adultNo:not(:checked) ~ .conditional, #petYes:not(:checked) ~ .conditional {
    border: 0;
    clip: rect(0 0 0 0);
    height: 1px;
    margin: -1px;
    overflow: hidden;
    padding: 0;
    position: absolute;
    width: 1px;
    }
</style>
</head>
<body>
    <div class="header">
        <div class="container">
            <div id="logo">
                <img src="images/logo.png" alt="logo"/>
            </div>
        <div class="nav">
            <ul>
                <li class="home"><a href="index.html">Home</a></li>
                <li class="about"><a href="aboutUs.html">About Us</a></li>
                <li class="dogs"><a href="dogs.html">Dogs for Adoption</a></li>
                <li class="current"><a href="adoptionForm.php">Online Adoption Form</a></li>
                <li class="contact"><a href="contactUs.html">Contact Us</a></li>
        <li class="login"><a href="login.php">Login</a></li>
            </ul>
            </div>
        </div>
    </div>
<form action="adoptionForm.php" method="post">

        <div class="formContainer">
    <h1>Adoption Form</h1>
    <p>Please fill in this form to be matched to a suitable dog.</p>
    <hr/>

            <label><b>Name</b></label>
            <input type="text" name="name"/>

            <label><b>Address</b></label>
            <input type="text" name="address"/>

            <label><b>Do you have an adult only home?</b></label><br />
            <input type="radio" name="adultRadio" id="adultYes" value="Yes" onclick="radio_check()"; /> Yes <br />
    <input type="radio" name="adultRadio" id="adultNo" value="No" onclick="radio_check()"; /> No <br />

    <div class="conditional">
        <label><b>If you don't have an adult only home, please select the age of any children: </b></label><br />
        <select id="child" size="0" name="child">
            <option value="" name=""></option>
            <option value="baby/toddler" name="baby/toddler">Baby/Toddler (0-4 years)</option>
            <option value="youngChild" name="youngChild">Small Child (5-12 years)</option>
            <option value="olderChild" name="olderChild">Older Child (13-18 years)</option>
        </select><br /> 
    </div>

    <label><b>Do you have any pets?</b></label><br/>
    <input type="radio" name="petRadio" id="petYes" value="Yes" onclick="radio_check()";/> Yes <br />
    <input type="radio" name="petRadio" id="petNo" value="No" onclick="radio_check()";/> No <br />

    <div class="conditional"> 
        <label><b>If you have a pet, please select the description that fits them best: </b></label><br />
        <select id="pet" name="pet"> 
            <option value="" name=""></option>
            <option value="youngDog" name="youngDog">Young, playful dog</option>
            <option value="oldDog" name="oldDog">Older, calm dog</option>
            <option value="cat" name="cat">Cat</option>
        </select>
    </div>

    <label><b>Please select which activity level fits you best: </b></label><br/>
    <select name="activity">
        <option value="notActive" name="notActive">Not very active</option>
        <option value="moderatelyActive" name="moderatelyActive">Moderately active</option>
        <option value="veryActive" name="veryActive">Very active</option>
    </select>

    <label><b>Please select how often you are at home: </b></label><br/>
    <select name="home">
        <option value="little" name="littleHome">At home very little</option>
        <option value="most" name="mostHome">At home most of the time</option>
        <option value="all" name="allHome">There is always someone at home</option>
    </select>



    <label><b>Please select which best describes your home environment: </b></label>
    <select name="environment">
        <option value="city" name="city">Busy city</option>
        <option value="suburbs" name="suburbs">Suburbs</option>
        <option value="rural" name="rural">Quiet and rural</option>
    </select>

            <button class="submit" type="submit">Submit Form</button>

        </div>

        <div class="loginContainer" style="background-color:#f1f1f1">
            <a href="logout.php"><button type="button" class="cancelbtn">Logout</button></a>
        </div>
    </form>

    <div class="footer">
        <p>North Coast Dog Rescue, Copyright &copy; 2020</p>
    </div>
</body>
    </html>

    <?php
    if($_SERVER["REQUEST_METHOD"] == "POST"){
    $name = $_POST['name'];
    $address = $_POST['address'];
    $adultRadio = $_POST['adultRadio'];
    $child = $_POST['child'];
    $petRadio = $_POST['petRadio'];
    $pet = $_POST['pet'];
    $activity = $_POST['activity'];
    $home = $_POST['home'];
    $environment = $_POST['environment'];
    $bool = true;

    //Connect to server and database
    $conn = mysqli_connect("localhost", "root", "", "ncdr_first", "3308");
    if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    echo "Success: A proper connection to MySQL was made! <br>" ;
    echo "Host information: " . mysqli_get_host_info($conn) ;

    $query_ins = "INSERT INTO adoption (name, address, adultRadio, child, 
    petRadio, pet, activity, home, environment)
    VALUES ('$name', '$address', '$adultRadio', '$child', '$petRadio', 
    '$pet', '$activity', '$home', '$environment')";

    if($bool) {
    mysqli_query($conn, $query_ins);
    echo "<script>alert('Adoption form submitted successfully!');</script>";
    echo "<script>window.location.assign('feedback.php');</script>";
    }
    mysqli_free_result($result);
    mysqli_close($conn);
    }

    ?>

hasil kueri SQL

-1
Chloe17 17 April 2020, 23:02

1 menjawab

Jawaban Terbaik

Melihat kode SQL itu akan mengembalikan ID terakhir yang cocok bukan ID terakhir. Anda hanya dapat memilih sisipan terakhir dengan yang berikut ini,

$sql = "SELECT dogs.name, dogs.breed, dogs.age, dogs.sex, dogs.image, adoption.adoption_id 
        FROM dogs 
            INNER JOIN adoption ON dogs.adultRadio = adoption.adultRadio 
                AND dogs.child = adoption.child 
                AND dogs.petRadio = adoption.petRadio 
                AND dogs.pet = adoption.pet 
                AND dogs.activity = adoption.activity 
                AND dogs.home = adoption.home 
                AND dogs.environment = adoption.environment
        WHERE adoption_id = (SELECT MAX(adoption_id) FROM adoption)";
0
User1010 17 April 2020, 21:03