CorScorpii CorScorpii - 2 months ago 21
SQL Question

php update the price on the same page of search results

I am new to php and sql. I want to ask a question about how to update the price in the same page of php search result. I need add an

input
and a
submit
button for updating the price of the books among the search results. Upon submission the new price of the book should be updated onto the database and re-displayed onto the same page.

Here is the existing code of search html page and result php page. The search result already can show on the page, but how do I update the price? After I click the button the page will show error You have not entered search details. Please go back and try again. Please help me.

Search page

Search Page:

<html>
<head>
<title>Book-O-Rama Catalog Search</title>
</head>

<body>
<h1>Book-O-Rama Catalog Search</h1>

<form action="results.php" method="post">
Choose Search Type:<br />
<select name="searchtype">
<option value="author">Author
<option value="title">Title
<option value="isbn">ISBN
</select>
<br />
Enter Search Term:<br />
<input name="searchterm" type="text" size="40">
<br />
<input type="submit" name="submit" value="Search">
</form>

</body>
</html>


Result page with submit button

Update page:



<html>
<head>
<title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
// create short variable names
$searchtype=$_POST['searchtype'];
$searchterm=trim($_POST['searchterm']);

if (!$searchtype || !$searchterm) {
echo 'You have not entered search details. Please go back and try again.';
exit;
}

if (!get_magic_quotes_gpc()){
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}

@ $db = new mysqli('localhost', 'pf31ee', 'pf31ee', 'pf31ee');

if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
exit;
}

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
$result = $db->query($query);

$num_results = $result->num_rows;

echo "<p>Number of books found: ".$num_results."</p>";

for ($i=0; $i <$num_results; $i++) {
$row = $result->fetch_assoc();
echo "<p><strong>".($i+1).". Title: ";
echo htmlspecialchars(stripslashes($row['title']));
echo "</strong><br />Author: ";
echo stripslashes($row['author']);
echo "<br />ISBN: ";
echo stripslashes($row['isbn']);
echo "<br />Price: ";
echo stripslashes($row['price']);
echo "</p>";
}

$result->free();
$db->close();

?>

<form action="results.php" method="post">
<input type="submit" name="submit" value="Update price">&nbsp;<input type="text" name="price" maxlength="7" size="7">
<input type="hidden" name="searchtype" value="<?php echo htmlspecialchars($_POST['searchtype']);?>" />
<input type="hidden" name="searchterm" value="<?php echo htmlspecialchars($_POST['searchterm']);?>" />
<?php
$updateprice=$_POST['price'];

@ $db = new mysqli('localhost', 'pf31ee', 'pf31ee', 'pf31ee');
mysql_select_db('books');

$query1 = "UPDATE books SET price = ".$updateprice." WHERE ".$searchtype." like '%".$searchterm."%'";
$result1 = $db->query($query1);
$result->free();
$db->close();

?>

</form>
</body>
</html>







create table customers
( customerid int unsigned not null auto_increment primary key,
name char(50) not null,
address char(100) not null,
city char(30) not null
);

create table orders
( orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2),
date date not null
);

create table books
( isbn char(13) not null primary key,
author char(50),
title char(100),
price float(4,2)
);

create table order_items
( orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,

primary key (orderid, isbn)

);
create table book_reviews
(
isbn char(13) not null primary key,
review text
);







use myuser;

insert into customers values
(3, "Julie Smith", "25 Oak Street", "Airport West"),
(4, "Alan Wong", "1/47 Haines Avenue", "Box Hill"),
(5, "Michelle Arthur", "357 North Road", "Yarraville");

insert into orders values
(NULL, 3, 69.98, "2007-04-02"),
(NULL, 1, 49.99, "2007-04-15"),
(NULL, 2, 74.98, "2007-04-19"),
(NULL, 3, 24.99, "2007-05-01");

insert into books values
("0-672-31697-8", "Michael Morgan", "Java 2 for Professional Developers", 34.99),
("0-672-31745-1", "Thomas Down", "Installing Debian GNU/Linux", 24.99),
("0-672-31509-2", "Pruitt, et al.", "Teach Yourself GIMP in 24 Hours", 24.99),
("0-672-31769-9", "Thomas Schenk", "Caldera OpenLinux System Administration Unleashed", 49.99);

insert into order_items values
(1, "0-672-31697-8", 2),
(2, "0-672-31769-9", 1),
(3, "0-672-31769-9", 1),
(3, "0-672-31509-2", 1),
(4, "0-672-31745-1", 3);

insert into book_reviews values
("0-672-31697-8", "Morgan's book is clearly written and goes well beyond most of the basic Java books out there.");




Answer

You need to pass the search type and value in the update form as well. You can use hidden input fields for that.

<html>
<head>
  <title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
  // create short variable names
  $searchtype=$_POST['searchtype'];
  $searchterm=trim($_POST['searchterm']);

  if (!$searchtype || !$searchterm) {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }

  if (!get_magic_quotes_gpc()){
    $searchtype = addslashes($searchtype);
    $searchterm = addslashes($searchterm);
  }

  @ $db = new mysqli('localhost', 'pf31ee', 'pf31ee', 'pf31ee');

  if (mysqli_connect_errno()) {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = $db->query($query);

  $num_results = $result->num_rows;

  echo "<p>Number of books found: ".$num_results."</p>";

  for ($i=0; $i <$num_results; $i++) {
     $row = $result->fetch_assoc();
     echo "<p><strong>".($i+1).". Title: ";
     echo htmlspecialchars(stripslashes($row['title']));
     echo "</strong><br />Author: ";
     echo stripslashes($row['author']);
     echo "<br />ISBN: ";
     echo stripslashes($row['isbn']);
     echo "<br />Price: ";
     echo stripslashes($row['price']);
     echo "</p>";
  }

  $result->free();
  $db->close();

?>
<form action="" method="post">
<input type="submit" name="submit" value="Update price">&nbsp;<input type="text" name="price" maxlength="7" size="7">
<input type="hidden" name="searchtype" value="<?php echo htmlspecialchars($_POST['searchtype']);?>" />
<input type="hidden" name="searchterm" value="<?php echo htmlspecialchars($_POST['searchterm']);?>" />
</form>
</body>
</html>

This code also is open to SQL injections. You should use parameterized queries, and a whitelist for columns being passed in. The addslashes function is insufficient, per the manual:

Please note that use of addslashes() for database parameter escaping can be cause of security issues on most databases.