|
PHP - Delete From - A Statement With A Join
The following query selects the rows that I want to delete-
select bad_rows.*
from products as bad_rows
inner join (
select pid, MAX(last_updated_date) as maxdate
from products
group by pid
having count(*) > 1
) as good_rows on good_rows.pid= bad_rows.pid
and good_rows.maxdate <> bad_rows.last_updated_date;
Now to actually delete them I tried changing the first word - 'select' to 'delete', but in this case the query seems to run forever and no rows are deleted. Why ?
How else can these rows be deleted?
btw what I am trying to do is get rid of duplicates. the selelct query works just fine in selecting the records, all I have to do is figure out a way of deleteting them
Thanks
Edited by kutchbhi, 14 May 2014 - 01:24 PM. Similar TutorialsI have two tables, one (projection) with a description of each item in a projected financial statement and the second (projection_detail) with detail for each row. The detail file can have 1 (annual), 4 (quarterly), or 12 (monthly) rows for each row in the projection file. Table structures are projection:
+----------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-----------------------+------+-----+---------+----------------+ | projection_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | company_valuation_id | mediumint(8) unsigned | NO | MUL | NULL | | | year | smallint(5) unsigned | NO | | NULL | | | category_code | char(4) | NO | | NULL | | | description | varchar(60) | NO | | NULL | | | amount | decimal(12,2) | NO | | NULL | | +----------------------+-----------------------+------+-----+---------+----------------+and projection_detail mysql> describe projection_detail; +---------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+------+-----+---------+-------+ | projection_id | int(10) unsigned | NO | PRI | NULL | | | period | smallint(5) unsigned | NO | PRI | NULL | | | amount | decimal(12,2) | NO | | NULL | | +---------------+----------------------+------+-----+---------+-------+I can select all the data for a year using something like this: SELECT * FROM projection JOIN projection_detail ON projection.projection_id = projection_detail.projection_id WHERE company_valuation_id = '1' AND year = '1'but I want to DELETE those rows from both tables and this gives me an error. DELETE FROM projection JOIN projection_detail ON projection.projection_id=projection_detail.projection_id WHERE company_valuation_id='75' and year='1' You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN projection_detail ON projection.projection_id=projection_detail.projection_' at line 1How do I delete a year's data from both tables without having to loop in php? Thanks, David Edit: The amount column in projection is left over from when all projections were annual and will be deleted. I'm open to other suggestions on a better way to structure the tables. Edited by davidannis, 02 June 2014 - 08:28 PM. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=306275.0 Hi, I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense. However, I need to know how the join should be implemented with MySQLi prepared statement. I need some one to review the following statement for me and advise:
if ($stmt = $mysqli->prepare("Select t_persons.PersonID
,t_persons.FamilyName
,t_persons.FirstName
,t_persons.OtherNames
,t_persons.Gender
,t_persons.CountryID
,t_persons.ImagePath
,t_incidents.IncidentID
,t_incidents.Incident
,t_incidents.IncidentDate
,t_incidents.PersonID
,t_incidents.CountryID
,t_incidents.OffenceKeywordID
,t_incidents.StatusID
,t_incidents.AgencyID
,t_status.StatusID
,t_status.Status
,t_offencekeyword.KeywordID
,t_offencekeyword.Keyword
,t_countries.CountryID
,t_countries.Country
,t_agencies.AgencyID
,t_agencies.Agency
From t_persons
Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID
Where t_persons.PersonID = '$PersonID'")) {
Regards.josephbupe Having trouble with my DELETE statement in my code. I'm not sure why it's not working, I've tried pretty much everything but I'm obviously missing someone. If someone could please take a look at my code and help me out, would appreciate it.... Here is my code.... I posted a little bit extra code because my DELETE statement is in the first set of PHP tags and my checkboxes and delete button that use the delete statement are lower down in the code. Code: [Select] <?php require_once('database.php'); session_start(); if (isset($_POST['add_grade'])) { $query = "INSERT INTO grades (student_id, grade_type, grade_name, grade_points) "; $query .= "VALUES (:student_id, :grade_type, :grade_name, :grade_points) "; $statement = $db->prepare($query); $statement->bindValue (':student_id', $_SESSION['student_id']); $statement->bindValue (':grade_type', $_POST['grade_type']); $statement->bindValue (':grade_name', $_POST['grade_name']); $statement->bindValue (':grade_points', $_POST['grade_point']); $statement->execute(); $statement->closeCursor(); if (isset($_POST['remove'])) { foreach($_POST['delete'] as $delete_id) { $query = "DELETE FROM grades WHERE grade_id = $grade_id"; mysqli_query($dbc, $query) or die ('can\'t delete user'); } } } ?> <!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"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>View Course Grades</title> </head> <body> <table> <table border="1"> <th>Grade Type</th> <th>Grade Name</th> <th>Grade Points</th> <th>Delete</th> <?php $student_name = $_SESSION['student_name']; $student_id = $_SESSION['student_id']; $query = "SELECT * FROM grades WHERE student_id = :student_id "; $statement = $db->prepare($query); $statement->bindValue (':student_id', $student_id); $statement->execute(); $grades = $statement->fetchAll(); $statement->closeCursor(); echo "<h1>Show Grades for $student_name </h1>"; foreach ($grades as $grade) { echo $grade['grade_type'] . " " . $grade['grade_name']. " " . $grade['grade_points']; echo '<input type="checkbox" value="' .$grade['grade_id'] . '" name="delete[]" />'; echo ' ' .$grade['grade_type'] .' '. $grade['grade_name']; echo '<br />'; } ?> <form name="grades" method="post" action="grades.php"> <p>Grade Type<SELECT NAME="grade_type"> <OPTION VALUE="Mid-Term">Mid-Term <OPTION VALUE="Final">Final <OPTION VALUE="Lab">Lab </SELECT> <br> <form name="grades" method="post" action="grades.php"> <input type="checkbox" name="delete[]" value="delete" /> <label for="delete"></label> Grade Name:<input type="text" name="grade_name" value=""><br /> Grade Points:<input type="text" name="grade_point" value=""> <input type="submit" name="add_grade" value="Add Grade"> </form> </table> </body> </html> Can't figure out why my form is not deleting a user from database anymore. It was working a hour ago. Now it's not working, and I have no idea why. I've tried everything. Am I missing something? Here is the delete page... Code: [Select] <html> <head> <title>Update User</title> </head> <body> <?php $dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') or die(mysql_error()); //delete users echo '<b>Delete or Update User</b>.<br />'; if (isset($_POST['remove'])) { foreach($_POST['delete'] as $delete_id) { $query = "DELETE FROM users WHERE course_id = $delete_id"; mysqli_query($dbc, $query) or die ('can\'t delete user'); } echo 'user has been deleted.<br />'; } if (isset($_POST['update'])) { foreach($_POST['update'] as $update_id) { $course_id = $_POST['course_id']; $course_name = $_POST['course_name']; $student_id = $_POST['student_id']; $query = "UPDATE `users` SET `course_name` = '$course_name' WHERE `course_id` = '$course_id' AND 'student_id' = '$student_id'"; mysqli_query($dbc, $query) or die ('can\'t update course'); $update_count = $db->exec($query); } echo 'course has been updated.<br />'; } //display users info with checkbox to delete $query = "SELECT * FROM users"; $result = mysqli_query($dbc, $query); while($row = mysqli_fetch_array($result)) { echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; echo ' ' .$row['course_name'] .' '. $row['student_id']; echo '<br />'; } mysqli_close($dbc); ?> <form method="POST" action="update_user2.php"> <label for="course_id">Course ID:</label> <input type="text" id="course_id" name="course_id" /><br /> <label for="course_name">Course Name:</label> <input type="text" id="course_name" name="course_name" /><br /> <label for="course_name">Student ID:</label> <input type="text" id="student_id" name="student_id" /><br /> </form> <form method="post" action="update_user2.php"> <input type="submit" name="remove" value="Remove" /> <input type="submit" name="update" value="Update" /> <br> <br> </body> </html> Hello there! I've got this code:
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
if (isset($_POST["id"]) && !empty($_POST["id"])) {
require_once "login.php";
$stmt = $conn->prepare("DELETE FROM teamlid WHERE lidnummer = ?");
$stmt->bind_param("i", $param_id);
$param_id = $_POST["id"];
$stmt->execute();
if ($stmt) {
header("location: teamsTabel.php");
exit();
} else {
echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
}
mysqli_close($conn);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Gegevens inzien</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper {
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>Gegevens verwijderen</h1>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger fade in">
<input type="hidden" name="id" value="<?php echo trim($_POST["id"]); ?>" />
<p>Weet je zeker dat je deze gegevens wilt verwijderen?</p><br>
<p>
<input type="submit" name="submitDeleteBtn" value="Ja" class="btn btn-danger">
<a href="teamsTabel.php" class="btn btn-default">Nee</a>
</p>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Another PHP script I tried didn't work either:
<?php
if (isset($_POST["id"]) && !empty($_POST["id"])) {
require_once "login.php";
// DELETE query om op basis van lid.lidnummer en postcode.postcode de tabellen te legen.
$sql = "DELETE lid, postcode FROM lid INNER JOIN postcode WHERE lid.postcode = postcode.postcode AND lid.lidnummer = ?";
if ($stmt = mysqli_prepare($conn, $sql)) {
mysqli_stmt_bind_param($stmt, "s", $param_id);
$param_id = trim($_POST["id"]);
if (mysqli_stmt_execute($stmt)) {
header("location: read.php");
exit();
} else {
echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
}
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
} else {
if (empty(trim($_GET["id"]))) {
header("location: error.php");
exit();
}
}
?>
But they're both not doing what I want: deleting the rows in the "teamlid" table where the id matches the column "lidnummer". I'm not getting any errors. When using the delete statement directly in MySQL using a hardcoded "lidnummer" it works. But it's not using the lidnummer here to delete the corresponding columns in the table. I can't get my head around it. Do you guys have any ideas? Thanks! Edited May 28, 2020 by DeckDekki have a table of say 100 entries. i want to delete all but the 10 newest of those rows with a prepared statement. im fairly new with prepared statements so the syntax with a subquery is throwing me a bit.
$stmt = $db_connect->prepare('DELETE FROM table WHERE owner=? AND owner NOT IN (SELECT owner FROM table ORDER BY dob DESC LIMIT 10)');
$stmt->bind_param('i', 0);
$stmt->execute();
$stmt->close();
In the same $-POST , i wanted to perform update and delete. With the updated and deleted database, I need to select the updated data from the database. However, it tells me: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in... .I am new to sql in php, is it true that i cannot perform sql in this way? Or is there any suggestion to perform select straight after deleting and updating?
CANCEL BOOK RESERVATION
if(isset($_POST['cancelres']))
{
$query = "DELETE FROM reserved_books WHERE id='$resid';"; //delete reservation
$query .="UPDATE reserved_books SET queue = queue - 1 WHERE bookid = '$bookid' AND queue > '$bookqueue';";//update queue number where reservation that queue behind/after/ the current reservation
$query_run = mysqli_multi_query($connection, $query);
if($query_run)
{
$_SESSION['success'] = "Reservation Cancelled";
}
else
{
$_SESSION['status'] = " Reservation Not Cancelled";
}
$query= "SELECT title FROM books WHERE id = '$bookid';"; //get book title from db
$query_run = mysqli_query($connection,$query);
if(mysqli_num_rows($query_run)>0)
{
foreach ($query_run as $row) {
$title = $row['title'];
}
}
$bookres = "SELECT * FROM reserved_books WHERE bookid = '$bookid' AND queue = 1";
$bookres_run = mysqli_query($connection,$bookres);
foreach($bookres_run as $row)
{
$res_username = $row['username'];
$res_id = $row['id'];
}
$query= "SELECT option_value FROM settings WHERE option_name = 'email_temp_rescollect'"; //get email template from db
$query_run = mysqli_query($connection,$query);
if(mysqli_num_rows($query_run)>0)
{
$row = mysqli_fetch_array($query_run);
$rescollect_template = $row[0];
}}
This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308855.0 Hi all. Here is my scripts which allow user to check multiple rows of data and delete it , but it require select data and click for twice to delete the rows , what should be the error? Code: [Select] <form name="frmSearch" method="post" action="insert-add.php"> <table width="600" border="1"> <tr> <th width="50"> <div align="center">#</div></th> <th width="91"> <div align="center">ID </div></th> <th width="198"> <div align="center">First Name </div></th> <th width="198"> <div align="center">Last Name </div></th> <th width="250"> <div align="center">Mobile Company </div></th> <th width="100"> <div align="center">Cell </div></th> <th width="100"> <div align="center">Workphone </div></th> <th width="100"> <div align="center">Group </div></th> </tr> </form> <? echo "<form name='form1' method='post' action=''>"; while($objResult = mysql_fetch_array($objQuery)) { echo "<tr>"; echo "<td align='center'><input name=\"checkbox[]\" type=\"checkbox\" id=\"checkbox[]\" value=\"$objResult[addedrec_ID]\"></td>"; echo "<td>$objResult[addedrec_ID] </td>"; echo "<td>$objResult[FirstName]</td>"; echo "<td>$objResult[LastName] </td>"; echo "<td>$objResult[MobileCompany] </td>"; echo "<td>$objResult[Cell] </td>"; echo "<td>$objResult[WorkPhone] </td>"; echo "<td>$objResult[Custgroup] </td>"; echo "</tr>"; } echo "<td colspan='7' align='center'><input name=\"delete\" type=\"submit\" id=\"delete\" value=\"Delete\">"; if (isset($_POST['delete']) && isset($_POST['checkbox'])) // from button name="delete" { $checkbox = ($_POST['checkbox']); //from name="checkbox[]" $countCheck = count($_POST['checkbox']); for($d=0;$d<$countCheck;$d++) { $del_id = $checkbox[$d]; $sql = "DELETE from UserAddedRecord where addedrec_ID = $del_id"; $result2=mysql_query($sql) or trigger_error(mysql_error());;; } if($result2) { $fgmembersite->GetSelfScript(); } else { echo "Error: ".mysql_error(); } } echo "</form>"; Thanks for every reply. <body> <?php
include 'sql.php';
$query = "SELECT * FROM validation";
$result = mysqli_query($con , $query);
$rows = mysqli_fetch_assoc($result) ;
$totals = mysqli_num_rows($result) ;
?>
<div id="css">
<form >
<table width="80%" border="0" cellpadding="2" cellspacing="2" >
<caption><h2>Personal Details of Customers</h2></caption>
<tr class="white">
<td bgcolor="#330033"> </td>
<td bgcolor="#330033"> Id Number </td>
<td bgcolor="#330033"> Full Name </td>
<td bgcolor="#330033"> Email Address </td>
<td bgcolor="#330033"> Website </td>
<td bgcolor="#330033"> Comment </td>
<td bgcolor="#330033"> Time </td>
</tr>
<?php while($rows=mysqli_fetch_assoc($result)
{
<tr>
<input type="raido" name="ID" value="<?php echo $rows['ID']; ?>" />
<td bgcolor="#FFFFCC"><?php echo $rows['ID'];?></td>
<td bgcolor="#FFFFCC"><?php echo $rows['Name'];?> </td>
<td bgcolor="#FFFFCC"><?php echo $rows['Email'];?></td>
<td bgcolor="#FFFFCC"><?php echo $rows['Website'];?></td>
<td bgcolor="#FFFFCC"><?php echo $rows['Comment'];?></td>
<td bgcolor="#FFFFCC"><?php echo $rows['Time'];?></td>
<td> </td>
<td> <a href="delete.php? ID= "$rows[ID]" /"> <input type="submit" name="del" value="Delete" /> </a> <input type="button" name= "edit" value="Edit" /> </td>
</tr>
}
?></table> </form> </div> </body> Hi,
I wish to find out is there any possible that I can delete some data inside my php website but inside my sql database, the record will still at there?
Thank you.
I'm trying to set it so that it will delete an entire populated directory based upon a value in the database then after finishing that to go back and delete that row in the database. my current code is Code: [Select] <?php $page_title = "Central Valley LLC | Photo Addition" ?> <?php include("header.php"); ?> <?php include("nav.html"); ?> <div id="content"> <form action="delprod.php" method="post" enctype="multipart/form-data"> <label for="which">Choose A Product To Remove:</label> <?php $con = mysql_connect("localhost","phoenixi_cv","centraladmin"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("phoenixi_cvproducts", $con); $result = mysql_query("SELECT * FROM Products"); echo "<select name=\"which\">"; while($row = mysql_fetch_array($result)) { echo "<option "; echo "value=\"" . $row['id'] . "\">"; echo $row['Name'] . "</option>"; } echo "</select>"; mysql_close($con); ?> <br /> <input type="submit" name="submit" value="Submit" /> </form> </div><!--#content--> <?php include("footer.html") ?> and the delete script Code: [Select] <?php $con = mysql_connect("localhost","phoenixi_cv","centraladmin"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("phoenixi_cvproducts", $con); $result = mysql_query("SELECT id FROM Products WHERE id=$_POST['which']"); $row = mysql_fetch_array($result) chdir('assets'); chdir('images'); $mydir = $row . '/'; $d = dir($mydir); while($entry = $d->read()) { if($entry!="." && $entry!="..") { unlink($_POST['which'] . '/' . $entry); } } rmdir($mydir); $result = mysql_query("DELETE * FROM Producs WHERE id=$_POST['which']"); ?> Thank you in advance for all your help. any easier ways of approaching this will be welcome as well I'm having issues with the following: Code: [Select] <?php session_start(); $_SESSION['username']=$_POST['username']; $_SESSION['password']=$_POST['password']; if($_SESSION['username']=="username" && $_SESSION['password']=="password"){ if($_GET['product']=="add"){ $content.=' <p><label>Product Name:</label> <input type="text" name="product_name" size="30" /> <label>Product Price:</label> <input type="text" name="product_price" size="5" /> </p> <p><label>Product Category:</label> <input type="text" name="product_category" size="30" /></p> <p><label>Product Link:</label> <input type="text" name="product_link" size="30" /></p> <p><label>Product Image:</label> <input type="text" name="product_image" size="30" /></p> <p><label>Product Tag:</label> <input type="text" name="product_tag" size="30" /></p> <p><label>Product Keywords:</label> <input type="text" name="keyword" size="30" /></p> <p><label>Product Features:</label><br /> <textarea name="product_features" rows="10" cols="60"></textarea> </p> <p><label>Product Pros:</label><br /> <textarea name="product_pros" rows="5" cols="30"></textarea> </p> <p><label>Product Cons:</label><br /> <textarea name="product_cons" rows="5" cols="30"></textarea> </p> <p><label>Product Description:</label><br /> <textarea name="product_description" rows="10" cols="60"></textarea> </p> <p><label>Product Notes:</label><br /> <textarea name="product_notes" rows="5" cols="30"></textarea> </p> '; $logout='<div><a href="./acp_admincp.php?log-out">Log-Out</a></div>'; } elseif($_GET['product']=="view"){ } else{ $content.=' <a href="./admincp.php?product=add">Add New Product</a> <br /> <a href="./admincp.php?product=view">View Products</a> '; } } elseif(isset($_GET['log-out'])){ session_start(); session_unset(); session_destroy(); header("Location: ./admincp.php"); } else{ $content=' <form action="./admincp.php" method="post"> <p><label>Username:</label> <input type="text" name="username" size="30" />'; $content.='</p> <p><label>Password:</label> <input type="password" name="password" /></p>'; $content.='<p><input type="submit" value="Submit" name="Submit" /></p> </form>'; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <base href="http://ghosthuntersportal.com/" /> <title>Ghost Hunter's Portal - Admin Control Panel</title> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <meta name="verify-v1" content="" /> <meta name="keywords" content="ghost, hunters, hunter, ghosts, spirit, spirits, paranormal, investigation, investigator, investigators, k2, emf, meter, kii" /> <meta name="description" content="Ghost Hunters Potal. Parnormal research equipment store." /> <meta name="author" content="Andrew McCarrick" /> <meta name="robots" content="index, follow" <link rel="stylesheet" type="text/css" href="style.css" /> </head> <body> <img src="./logo.png" alt="Ghost Hunter's Portal Admin Control Panel" /> <br /> <div style="color: #AA44AA; font-size: 26px; margin-top: -30px; margin-left: 125px;">Admin Control Panel</div> <?php echo $logout; echo $content; ?> </body> </html> I can log-in, and get to the page with the two links on it. However, once I click one of the links it falls back to the log-in page, and it ends up being a never ending loop. It's doing this: Log-In --> Page with links ---> Log-In page again Should be doing this: Log-In --> Page with links --> Add Product page or View Products page I can never get into the the actual sub page. Just to be clear, the address bar actually shows product=add or product=view, but it still shows the log-in page. I'm trying to figure out the best way to do this if I'm doing it right with my for loop. With how many numAnswers there are for the selected poll its going to put the div with the label and text box for each of those but its giong to go and tie in each of those pollAnswers with what ID of the pollAnswer to the pollAnswer table. Code: [Select] $pollsQuery = " SELECT polls.question, polls.statusID, polls.numAnswers, DATE_FORMAT(polls.dateExpires, '%m/%d/%Y') AS dateExpires FROM polls WHERE polls.ID = '" . $pollID . "'"; $pollsResult = mysqli_query ( $dbc, $pollsQuery ); // Run The Query $row = mysqli_fetch_array ( $pollsResult, MYSQL_ASSOC ); $pollAnswers = " SELECT pollAnswers.ID, pollAnswers.answer FROM pollAnswers WHERE pollAnswers.pollID = '" . $pollID . "'"; $pollAnswersResult = mysqli_query ( $dbc, $pollAnswers ); // Run The Query Code: [Select] <fieldset class="answerLeg"> <legend>Edit Poll Answers</legend> <?php for ( $j = 0; $j <= $numAnswers; $j++) { ?> <div class="field required answers"> <label for="answer<?php $j?>">Answer <?php $j?></label><input type="text" class="text" name="answer<?php $j?>" id="answer<?php $j?>" title="Answer <?php $j?>"/> <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span> </div> <?php } ?> </fieldset> I'm an amateur PHP/mySQL person, trying to learn some more things, and I've hit a snag trying to do something I haven't done before. Okay, here's what I'm trying to do. I have 3 tables: Items (which includes `id` as the primary key) and a TON of information about each item. Shop (which includes `id` as the primary key) and shop name and location. Sale which has shop_id and item_id. So, what I'm trying to do is have a form where someone can input in an item name and a shop name and have that item then added to the shop. This is where the Sale table comes into play too, as that's going to house each of the items and what shop it's in. I currently have the following queries: $query5 = $db->execute("SELECT id FROM items LEFT JOIN sale ON items.id=sale.item_id"); $query6 = $db->execute("SELECT id FROM shop LEFT JOIN sale ON shop.id=sale.shop_id"); I have the following form: if ($name == "") { $errors++; $errorlist .= "Name is required.<br />"; } if ($location == "" ) { $errors++; $errorlist .= "Location is required.<br />"; } if (!isitem($itemname)) die("That item does not exist."); } if ($errors == 0) { $query = doquery("INSERT INTO `sale` SET `item_id`=`$item`"); admindisplay("New Item Added.","Add Items"); $page = <<<END <b><u>Add Item</u></b><br /><br /> <form action="create_shop.php?do=additem" method="post"> <table width="90%"> <tr><td width="20%">Name:</td><td><input type="text" name="itemname" size="30" maxlength="255" value="" />**Be sure the item is already in the database and matches it IDENTICALLY.</td></tr> <tr><td width="20%">Location:</td><td><input type="text" name="location" size="30" maxlength="55" value="" /><br /><span class="small">Where is the item obtained (unique shop name, please!)</span></td></tr> </table> <input type="submit" name="submit" value="Submit" /> <input type="reset" name="reset" value="Reset" /> </form> What next steps do I need to take? Am I even on the right path? Thanks for any and all help anyone provides!! I really appreciate it! Im currently working on a new feature called 'Request Player'. Basically what this does is allows managers to request players within their club for a upcoming fixture. There a multiple teams within a club (Senior As, Senior Bs, Colts U21s). Each team is controlled by a manager. And then they can add players as they need. Anyway, I'm having problems with the sql JOIN. I have currently got this: $check = "SELECT u.* FROM users AS u LEFT JOIN requests AS r ON (u.id = r.player_id) WHERE (u.id = r.player_id)"; I have got two tables. users - all the managers and players. requests - all the requests the managers make to other teams within their club. In the request table i have got this: id player_id - the players that has been requested. fixture_id - the fixture the manager wants to have that player for. accepted - if he has been accepted or not. This comes later on. At the moment i have got it working so that it inserts this data, although the code is working but i want to have a mysql query that checks if a manager has any incoming requests from other teams within the club. BUT only request that has been made to his team.... Could someone please help me out? Hello everyone,
I am hopping someone can help me sort out a query.
I have two tables, one table is a players table, and a second table is a player_vitals data that stores heights and weight changes. I want to list all the players showing the latest height and weight change.
this query will not fetch the latest height and weight
SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight I'm trying to pull from my database every spell or attack that is equal to `All`, but it doesn't seem to be working. All it is doing is pulling all the spells, regardless of class = `All`, and also 0 of the attacks. It should be producing a single attack that is = to `All` as no spells are = `All`. Any help would be greatly appreciated! Thanks! (This is my first attempt at a JOIN statement...) $query = "SELECT attacks.id, attacks.name, attacks.price, attacks.class, attacks.descript, spells.id, spells.name, spells.price, spells.class, spells.descript ". "FROM attacks, spells ". "WHERE attacks.class = 'All' || spells.class = 'All' order by attacks.name, spells.name asc"; Not sure if it is because it is too early in the morning but I am having a problem. Code: [Select] $mpid = # $query = "SELECT table1.title, table2.* ". "FROM table1, table2 ". "WHERE table1.mpid = table2.mpid"; How do I only show the info from the 2 tables where mpid=# |
|||||||