更新不同表中的数据sql数据库

问题描述：

te_event，te_venue，te_category。

te_event表格显示了categoryID，venueID，事件描述，标题，日期和价格。

的具有venueID，venueName和位置

的具有CATID和catDesc te_category te_venue表。

这是管理员为了更新而需要选择事件的第一页。

<!DOCTYPE html> 
<html lang="en"> 
<html> 
<head> 
    <link rel="stylesheet" type="text/css" href="test1.css"> 
    <meta charset = "utf-8"> 
<title> 
</title> 
</head> 
<body> 

<div id="title"> 

     <p><h1>Tyne Events</h1></p> 

    </div> 


<div id="wrapper"> 

    <div id="navbar" > 
      <ul class="nav"> 
       <li><a href="home.html">Home</a></li> 
       <li><a href="findoutmore.php">Find out more</a></li> 
       <li><a href="offer.html">Offer</a></li> 
       <li><a href="credit.html">Credit</a></li> 
       <li><a href="restrictedPage.php">Admin</a></li> 

       <li> 
        <form class="formright"> 
        <input type="text" placeholder="Search"> 
        <button type="submit">Search</button>           
        </form> 
       </li> 
      </ul> 


     </div> 

    <div id= "detailCenter"> 

     <?php 
    include "database_conn.php"; 

    $sqlEvent = "SELECT * FROM te_events 
       INNER JOIN te_venue ON te_events.venueID = te_venue.venueID 
       INNER JOIN te_category ON te_events.catID = te_category.catID 
       ORDER by eventTitle ";//select all event record 



    //query the statement 
    $event= mysqli_query ($conn , $sqlEvent) 
    or die (mysqli_error($conn)); 

?> 


<h1>Select Event To Modify</h1> 

<table border = "1" cellpadding="10" width=100% > 

<thead> 
    <th>Title</th> 
    <th>Category</th> 
    <th>Venue Name</th> 
    <th>Location</th> 
    <th>Start Date</th> 
    <th>End Date</th> 
    <th>Price</th> 

</thead> 

<?php 

    //display all the event record 
    while ($row = mysqli_fetch_assoc ($event)){ 
     //extract the field 
     $id   = $row ["eventID"]; 
     $title  = $row ["eventTitle"]; 
     $desc  = $row ["eventDescription"]; 
     $venue  = $row ["venueName"]; 
     $location = $row ["location"]; 
     $category = $row ["catDesc"]; 
     $eStart  = $row ["eventStartDate"]; 
     $eEnd  = $row ["eventEndDate"]; 
     $ePrice  = $row ["eventPrice"]; 



     //start a row 
     echo"<tr>\n"; 

     //output the URL 
     echo "<td>\n"; 
     echo "<div> <a href = \"allDetails.php?eventID=$id\"> 
        $title</a></div>\n"; 
     echo "</td>\n"; 

     echo "<td>\n"; 
     echo "<div> $category</div>\n"; 
     echo "</td>\n"; 

     echo "<td>\n"; 
     echo "<div> $venue</div>\n"; 
     echo "</td>\n"; 

     echo "<td>\n"; 
     echo "<div> $location</div>\n"; 
     echo "</td>\n"; 

     echo "<td>\n"; 
     echo "<div>$eStart</div> \n"; 
     echo "</td>\n"; 

     echo "<td>\n"; 
     echo "<div>$eEnd</div>\n"; 
     echo "</td>\n"; 

     echo "<td> \n"; 
     echo "<div>$ePrice</div>\n"; 
     echo "</td>\n"; 
    } 

?> 
</table> 
</div> 

</body> 
</html> 

<?php 
    mysqli_close($conn); 
?>

这里是管理员可以编辑事件细节
页面

<?php 
    include "database_conn.php"; 

    if(isset($_GET['eventID'])){ 
     $id = $_GET["eventID"]; //get event id 
    } 
    else { 
     header ("Refresh : 3; url=admin.php");//redirect to choose title 
     die ("Please use the Choose Event Title List"); 
    } 

    //get event id 
    $id = $_GET["eventID"];//get event id 

    //select event 
    $sqlEvent =" SELECT * FROM te_events 
       INNER JOIN te_venue ON te_events.venueID = te_venue.venueID 
       INNER JOIN te_category ON te_events.catID = te_category.catID 
       WHERE te_events.eventID =".$id; 



    //excute sql statemente 
    $event = mysqli_query($conn , $sqlEvent) or die (mysqli_error($conn)); 

    $_GET = mysqli_fetch_assoc($event) or die (mysqli_error($conn)); 

    //extract each field$ 

     $title  = $_GET ["eventTitle"]; 
     $desc  = $_GET ["eventDescription"]; 
     $venueId = $_GET ["venueID"]; 
     $venue  = $_GET ["venueName"]; 
     $location = $_GET ["location"]; 
     $categoryId = $_GET ["catID"]; 
     $category = $_GET ["catDesc"]; 
     $eStart  = $_GET ["eventStartDate"]; 
     $eEnd  = $_GET ["eventEndDate"]; 
     $ePrice  = $_GET ["eventPrice"]; 

?> 

<!DOCTYPE html> 
<html lang="en"> 
<html> 
<head> 
    <link rel="stylesheet" type="text/css" href="test1.css"> 
    <meta charset = "utf-8"> 
<title> 
</title> 
</head> 
<body> 

<div id="title"> 

     <p><h1>Tyne Events</h1></p> 

    </div> 


<div id="wrapper"> 

    <div id="navbar" > 
      <ul class="nav"> 
       <li><a href="home.html">Home</a></li> 
       <li><a href="findoutmore.php">Find out more</a></li> 
       <li><a href="offer.html">Offer</a></li> 
       <li><a href="credit.html">Credit</a></li> 
       <li><a href="restrictedPage.php">Admin</a></li> 

       <li> 
        <form class="formright"> 
        <input type="text" placeholder="Search"> 
        <button type="submit">Search</button>           
        </form> 
       </li> 
      </ul> 


     </div> 

    <div id= "detailCenter"> 
<form id="updateEventDetail" method ="get" action ="updateEventDetail.php"> 
<fieldset> 
    <legend>Event details</legend> 


      <div><p> 
      <input type ="hidden" name ="eventID" value="<?=$eventID?>"/> 
      </p></div> 

      <div><p> 
      <label class="field" for="eTitle">Event title</label> 
      <input type ="text" name ="eventTitle" value="<?=$title?>"/> 
      </p></div> 

      <div><p> 
      <label class="field" for="cat">Category</label> 
      <?php 

      $sqlCategory ="SELECT DISTINCT catDesc FROM te_category ORDER BY 1"; 

      //query sqlVenue 
      $rsCategory = mysqli_query ($conn ,$sqlCategory) 
         or die ("SQL ERROR :".mysqli_error($conn)); 

      //create select item 
      echo"<select name=\"catDesc\">\n"; 

      //iterate venue record 
      while ($_GET = mysqli_fetch_assoc($rsCategory)){ 

       //populate select item 
       $category = $_GET[catDesc]; //get each venueName record 

       if($category==$category) 
        echo "<option value =\"$category\" selected> 
         $category</option>\n"; 
       else{ 
        echo "<option value =\"$venue\"> 
         $category</option>\n"; 
       } 

      } 
      echo"</select>\n"; 
      ?> 
      </p></div> 


      <div><p> 
      <label class="field" for="desc">Description</label> 
      <textarea style="resize:none" name="eventDescription" rows="10" cols="40" value="<?=$desc?>"><?=$desc?> </textarea> 
      </p></div> 

      <div> 
      <p> 
      <label class ="field" for="venue">Venue</label> 
      <?php 

      $sqlVenue ="SELECT DISTINCT venueName FROM te_venue ORDER BY 1"; 

      //query sqlVenue 
      $rsVenue = mysqli_query ($conn ,$sqlVenue) 
         or die ("SQL ERROR :".mysqli_error($conn)); 

      //create select item 
      echo"<select name=\"venueName\">\n"; 

      //iterate venue record 
      while ($_GET = mysqli_fetch_assoc($rsVenue)){ 

       //populate select item 
       $eVenue = $_GET[venueName]; //get each venueName record 

       if($eVenue==$venueName) 
        echo "<option value =\"$eVenue\" selected> 
         $eVenue</option>\n"; 
       else{ 
        echo "<option value =\"$venue\"> 
         $eVenue</option>\n"; 
       } 

      } 
      echo"</select>\n"; 
      ?> 
      </p> 
      </div> 

      <div><p> 
      <label class="field" for="location">Location</label> 
      <?php 

      $sqlLocation ="SELECT DISTINCT location FROM te_venue ORDER BY 1"; 

      //query sqlLocation 
      $rsLocation = mysqli_query ($conn ,$sqlLocation) 
         or die ("SQL ERROR :".mysqli_error($conn)); 

      //create select item 
      echo"<select name=\"location\">\n"; 

      //iterate venue record 
      while ($_GET = mysqli_fetch_assoc($rsLocation)){ 

       //populate select item 
       $eLocation = $_GET[location]; //get each location record 

       if($eLocation==$location) 
        echo "<option value =\"$eLocation\" selected> 
         $eLocation</option>\n"; 
       else{ 
        echo "<option value =\"$location\"> 
         $eLocation</option>\n"; 
       } 

      } 
      echo"</select>\n"; 
      ?> 
      </p></div> 

      <div><p> 
      <label class="field" for="sDate">Event Start Date</label> 
      <input name="eventStartDate" type="date" value="<?=$eStart?>"/> 
      </p></div> 

      <div><p> 
      <label class="field" for="eDate">Event End Date</label> 
      <input name="eventEndDate" type="date" value="<?=$eEnd?>"/> 
      </p></div> 

      <div><p> 
      <label class="field" for="ePrice">Event Price</label> 
      <input type="text" name="eventPrice" value="<?=$ePrice?>"readonly/> 
      </p></div> 

      <div><p> 
      <input type="submit" id="floatright" value="Update Event"/> 
      </p></div> 

</fieldset> 
</form> 
</div> 
</body> 
</html> 
<?php 
    mysqli_close($conn); 
?>

下面的代码显示updatedetail.php

<?php 
    include "database_conn.php"; 

    if(isset($_GET['eventID'])){ 
     $id = $_GET["eventID"]; //get event id 
    } 
    //get all data submited 
     $id   = $_GET ["eventID"]; 
     $title  = $_GET ["eventTitle"]; 
     $desc  = $_GET ["eventDescription"]; 
     $venue  = $_GET ["venueName"]; 
     $location = $_GET ["location"]; 
     $category = $_GET ["catDesc"]; 
     $eStart  = $_GET ["eventStartDate"]; 
     $eEnd  = $_GET ["eventEndDate"]; 
     $ePrice  = $_GET["eventPrice"]; 

     $sqlUpdateEvent    = " UPDATE te_event SET 
       eventTitle   = '$title' 
       eventDescription = '$desc' 
       catDesc   = '$category' 
       venueName   = '$venue' 
       location   = '$location' 
       eventStartDate  = '$eStart' 
       eventEndDate  = '$eEnd' 
       eventPrice   = '$ePrice' 
       WHERE eventID  = $id"; 

     mysqli_query ($conn , $sqlUpdateEvent) 
      or die ("SQL ERROR :".mysqli_error($conn)); 
?> 

<!DOCTYPE html> 
<html lang="en"> 
<head> 
<title>update mvie comfirmmation</title> 
<meta charset="utf-8"> 
</head> 
<body> 
<h1>update mvoie detials</h1> 

<?php 

    echo "Title : $eventTitle \n"; 
    echo "Category : $catDesc \n"; 
    echo "Description : $eventDescription \n"; 
    echo "Venue : $venueName \n"; 
    echo "Location : $location \n"; 
    echo "Start Date : $eventStartDate \n"; 
    echo "End Date : $eventEndDate \n"; 
    echo "Price : $eventPrice \n"; 

    if(mysqli_affected_rows($conn)>0) 
     echo "<p> Event update successfully </p>\n"; 
    else 
     echo "<p> Update Fail</p>\n"; 

?> 

<a href = "admin.php">Choose Event</a> 
</body> 
</html> 
<?php 
    mysqli_close($conn); 
?>

我得到的错误是

SQL错误：您在您的SQL语法中有错误;检查对应于您MariaDB的服务器版本正确的语法使用手动附近“eventDescription =‘的剧场惠特利湾是高兴宣布，利特’在3号线

我加入编辑我的问题一些代码
我希望它有帮助
感谢您的帮助。

“”错过了在查询 –

所有列缺少逗号分隔符，一两件事。 –

'$ sqlUpdateEvent =“更新te_event SET eventTitle ='$ title'，eventDescription ='$ desc'，catDesc ='$ category'，venueName ='$ venue'，location ='$ location'，eventStartDate ='$ eStart' ，eventEndDate ='$ eEnd'，eventPrice ='$ ePrice'WHERE eventID = $ id“;' –

答

你每缺少列逗号分隔符（,），所以将它们添加象下面这样： -

$sqlUpdateEvent = "UPDATE te_event SET eventTitle = '$title', eventDescription = '$desc',catDesc = '$category',venueName = '$venue',location = '$location',eventStartDate = '$eStart',eventEndDate = '$eEnd',eventPrice = '$ePrice' WHERE eventID = $id";

建议： - 您的查询开放至SQL Injection，所以请尝试阅读约prepared statements并使用它们。

码增强： -

<?php 
    include "database_conn.php"; 

    if(isset($_GET['eventID']) && isset($_GET["eventTitle"]) && isset($_GET["eventDescription"]) && isset($_GET["venueName"]) && isset($_GET["location"]) && isset($_GET["catDesc"]) && isset($_GET["eventStartDate"]) && isset($_GET["eventEndDate"]) && isset($_GET["eventPrice"])){ 
     $id = $_GET["eventID"]; 
     $title  = $_GET["eventTitle"]; 
     $desc  = $_GET["eventDescription"]; 
     $venue  = $_GET["venueName"]; 
     $location = $_GET["location"]; 
     $category = $_GET["catDesc"]; 
     $eStart  = $_GET["eventStartDate"]; 
     $eEnd  = $_GET["eventEndDate"]; 
     $ePrice  = $_GET["eventPrice"]; 

     $sqlUpdateEvent = " UPDATE te_event SET eventTitle = '$title',eventDescription = '$desc',catDesc = '$category',venueName = '$venue',location = '$location',eventStartDate = '$eStart',eventEndDate = '$eEnd',eventPrice = '$ePrice' WHERE eventID = $id"; 

     mysqli_query ($conn , $sqlUpdateEvent) or die ("SQL ERROR :".mysqli_error($conn)); 
    }else{ 
     echo "all data is required"; 
    } 

?>

现在我得到这个错误后，添加（，） SQL错误：您的SQL语法中有错误;检查对应于你的MariaDB服务器版本的手册，在' 附近使用正确的语法。注意：未定义的变量：在第10行的C：\ xampp \ htdocs \ assign中的eventID –

你的意思是我必须把eventID放在sql声明？或我错过了什么？ –

@ChiShen不知道你所有的'$ _GET'是从哪里填充的。你发布了一个早先的问题http://*.com/q/40450606/，恕我直言，没有意义'$ _GET = mysqli_fetch_assoc（$事件）'。如果Anant的编辑现在无法为您解决这个问题，那么您需要提供更多代码，以确定这些数组来自哪里。你说你有3个表格，但没有代码来支持这个问题。 –

答

您不使用逗号这里是在您的查询的查询

$sqlUpdateEvent = " UPDATE te_event SET 
       eventTitle   = '$title', 
       eventDescription = '$desc', 
       catDesc   = '$category', 
       venueName   = '$venue', 
       location   = '$location', 
       eventStartDate  = '$eStart', 
       eventEndDate  = '$eEnd', 
       eventPrice   = '$ePrice' 
       WHERE eventID  = $id";

更新不同表中的数据sql数据库

相关推荐