更新不同表中的数据sql数据库
首先,我的数据库中有3个表,即: -更新不同表中的数据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 = "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";
“”错过了在查询 –
所有列缺少逗号分隔符,一两件事。 –
'$ sqlUpdateEvent =“更新te_event SET eventTitle ='$ title',eventDescription ='$ desc',catDesc ='$ category',venueName ='$ venue',location ='$ location',eventStartDate ='$ eStart' ,eventEndDate ='$ eEnd',eventPrice ='$ ePrice'WHERE eventID = $ id“;' –