[SOLVED] Need some help with Bookstore administration page

sstoveld · March 22, 2009

hey guys, i was getting some help with my bookstore a little earlier and finally finished that part up. thread was here: http://www.phpfreaks.com/forums/index.php/topic,244285.0.html

now im having trouble making my administration page (of course!)

what it's supposed to do is allow the user to insert new titles (books) into the database, along with the price and quantity of that book into the two tables in my database.

also, it is supposed to list all the books in a table and allow the user to update the prices and quantity of books that are already in the database

right now, it doesnt do any of that because im too stupid.

here's a screen shot of how my database looks:

and here's my code:

http://pastebin.com/m233325a3

here's the code not in pastebin if it's easier:

<!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>Administration</title>
<link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" />
</head>

<body>

<div id="wrapper">
<div id="banner">
    	<h1><center><a href="index.php">Bookstore Administration</a></center></h1>
    </div><!-- banner -->
    
  <div id="mainContent">
  	
    
    <?php	
////////// CONNECT TO SERVER //////////

$dbcnx = @mysql_connect('localhost', 'dbase18', '*******') or die ('I cannot connect to the database because: ' .mysql_error());

///////// CONNECT TO DATABASE ////////////
mysql_select_db('dbase18', $dbcnx);
if (!@mysql_select_db('dbase18')) {
	exit ('<p>Error in query.'. mysql_error(). '<p>');
}

/////////// FIRST SQL QUERY ///////////

if (isset($_POST['update'])){
	$books_id=$_POST['books_id'];
	$title = htmlentities($_POST['title']);
	$sql1 = "INSERT INTO books SET title='$title'";
	if (!mysql_query($sql1)){
		echo "<p>Error updating. ".mysql_error()."</p>";
	}
}

/////////// GET PRIMARY KEY FROM PREVIOUS QUERY /////////
$title_id = mysql_insert_id();	
////////// SECOND SQL QUERY //////////
if (isset($_POST['update'])){
	$stock_id=$_POST['stock_id'];
	$quantity = htmlentities($_POST['quantity']);
	$price = htmlentities($_POST['price']);
	$sql2 = "INSERT INTO stock SET quantity='$quantity', price='$price'";
	if (!mysql_query(sql2)){
		echo "<p>Error updating. ".mysql_error()."</php>";
	}else{
		echo "<p>Updated successfully.</p>";
	}
}

////// GET THE RESULT OF THE QUERY /////////
$row = mysql_fetch_array($queryresult);
$title = $row['title'];
$stock = $row['stock'];

///////// MAKE A TABE CONTAINING FORMS FOR USER TO ADD NEW TITLES //////////
?>
    
    <p>
    <h2>Add Titles</h2>
    <table>
    <tr>
    	<td>Title</td>
        <td>Stock</td>
        <td>Price</td>
        <td>Update</td>
    </tr>
    <form name="edit" method="post" action=<?php echo $_SERVER['PHP_SELF'];?>>
    <tr>
    	<td><input name="title" type="text" value="<?php echo $title?>" size="30" />
    <input type="hidden" name="id" value=<?php echo $id;?>>
    	<td><input name="stock" type="text" value="<?php echo $stock?>" size="4" />
    <input type="hidden" name="id" value=<?php echo $id;?>>
    	<td><input name="price" type="text" value="<?php echo $price?>" size="6" />
    <input type="hidden" name="id" value=<?php echo $id;?>>
    	<td><input name="update" type="submit" value="update"></td>
    </tr>
    </form>
    </table>
    </p>
    
    <p>
    <h2>Update quantities and prices</h2>
    <table>
        <tr>
    	<td>Title</td>
        <td>Stock</td>
        <td>Price</td>
        <td>Update</td>
    </tr>
    </table>
    </p>
    
    
  </div><!-- mainContent -->
    
    <div id="footer">
    	<p>Coded and designed by: <a href="mailto:[email protected]">Steve Stoveld</a></p>
    </div><!-- footer -->
</div><!-- wrapper -->

</body>
</html>

and i've already got this warning:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/dbase18/Sites/bookstore/edit.php on line 57

any and all help is appreciated. thanks

trq · March 22, 2009

I don't see any SELECT query at all so don't see why you would be calling mysql_fetch_array.

sstoveld · March 22, 2009

I don't see any SELECT query at all so don't see why you would be calling mysql_fetch_array.

wont i need that for when i try to populate the table full of books allowing the user to update the quantities and prices?

trq · March 22, 2009

Yeah, you'll also need a SELECT query to go with it.

sstoveld · March 22, 2009

Yeah, you'll also need a SELECT query to go with it.

ok i've got my select query in there now for the 2nd table now. for my first table where the user can add new titles, im having a problem.

there are 3 forms for the user to enter info into - Title, Quantity, and Price, then the submit button.

it inserts the title from the first query fine no problems there, but then i get this error:

Error updating. 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 'sql2' at line 1

the 2nd query isnt going through that updates the stock table with quantity and price.

here's my code:

/////////// FIRST SQL QUERY ///////////

if (isset($_POST['update'])){
	$books_id=$_POST['books_id'];
	$title = htmlentities($_POST['title']);
	$sql1 = "INSERT INTO books SET title='$title'";
	if (!mysql_query($sql1)){
		echo "<p>Error updating. ".mysql_error()."</p>";
	}
}

/////////// GET PRIMARY KEY FROM PREVIOUS QUERY /////////
$title_id = mysql_insert_id();	
////////// SECOND SQL QUERY //////////
if (isset($_POST['update'])){
	$title=$_POST['title'];
	$stock_id=$_POST['stock_id'];
	$quantity = htmlentities($_POST['quantity']);
	$price = htmlentities($_POST['price']);
	$sql2 = "INSERT INTO stock SET quantity='$quantity', price='$price'";
	if (!mysql_query(sql2)){
		echo "<p>Error updating. ".mysql_error()."</php>";
	}else{
		echo "<p>Updated successfully.</p>";
	}
}

////////// QUERY DATABASE //////////
$queryresult = @mysql_query('SELECT stock_id, title, quantity, price FROM stock LEFT JOIN books ON stock.title_id = books.books_id ORDER BY title;');
if (!$queryresult) {
	exit ('<p>Error in query.'. mysql_error().'</p>');
}

is there something wrong with my $sql2 query?

trq · March 22, 2009

That error doesn't corrilate with the code you have provided. Its saying you have an error near 'sql2' in your query. The word sql2 does not appear in your query.

Do yourself a favour an echo your query. eg;

echo "<p>Error updating. ".mysql_error()."$sql2</p>";

sstoveld · March 22, 2009

That error doesn't corrilate with the code you have provided. Its saying you have an error near 'sql2' in your query. The word sql2 does not appear in your query.

Do yourself a favour an echo your query. eg;
echo "Error updating. ".mysql_error()."$sql2";

ok i did that and here's what came up when i tried entering a new title:

Error updating. 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 'sql2' at line 1INSERT INTO stock SET quantity='', price='1'

does this mean that there is something wrong with the quantity variable as it is not being passed through the query?

trq · March 22, 2009

That error doesn't corrilate with the code you have provided. Its saying you have an error near 'sql2' in your query. The word sql2 does not appear in your query.

Do yourself a favour an echo your query. eg;
echo "Error updating. ".mysql_error()."$sql2";
ok i did that and here's what came up when i tried entering a new title:
Error updating. 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 'sql2' at line 1INSERT INTO stock SET quantity='', price='1'
does this mean that there is something wrong with the quantity variable as it is not being passed through the query?

It would appear so. You need to validate all user input before using it in any queries. Using $_POST values directly within a query like that is a major security whole. Check the var is set and is what you expect it to be before using it.

sstoveld · March 22, 2009

That error doesn't corrilate with the code you have provided. Its saying you have an error near 'sql2' in your query. The word sql2 does not appear in your query.

Do yourself a favour an echo your query. eg;
echo "Error updating. ".mysql_error()."$sql2";
ok i did that and here's what came up when i tried entering a new title:
Error updating. 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 'sql2' at line 1INSERT INTO stock SET quantity='', price='1'
does this mean that there is something wrong with the quantity variable as it is not being passed through the query?
It would appear so. You need to validate all user input before using it in any queries. Using $_POST values directly within a query like that is a major security whole. Check the var is set and is what you expect it to be before using it.

heh, im going on about 2 months of learning php and mysql, so i dont really know what you're talking about or how to do it

as for the security, its not a very pressing issue right now because it's a class project, but i can see how it could be a big deal if it was for something other than a class project

FaT3oYCG · March 22, 2009

hey i have to sleep yano

action=<?php echo $_SERVER['PHP_SELF'];?>

put the quotes in on ur form, looking at it check over ur form u missed a few

sstoveld · March 22, 2009

hey i have to sleep yano
action=<?php echo $_SERVER['PHP_SELF'];?>
put the quotes in on ur form, looking at it check over ur form u missed a few

got them in there now

<form name="edit" method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
    <tr>
    	<td><input name="title" type="text" value="<?php echo $title ?>" size="30" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="stock" type="text" value="<?php echo $quantity ?>" size="4" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="price" type="text" value="<?php echo $price ?>" size="6" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="update" type="submit" value="update"></td>
    </tr>
    </form>

still getting the same error though

FaT3oYCG · March 22, 2009

your missin some semi-colons aswell

trq · March 22, 2009

Your error still doesn't corrilate to the code. There is no sql2 in your query. Place this at the top of your acript and lets see the results.

echo "<pre>",print_r($_POST),"</pre>";

sstoveld · March 22, 2009

Your error still doesn't corrilate to the code. There is no sql2 in your query. Place this at the top of your acript and lets see the results.
echo "<pre>",print_r($_POST),"</pre>";

here's the result:

Array
(
    [title] => Testing
    [id] => 
    [stock] => 123
    [price] => 123
    [update] => update
)
1

FaT3oYCG · March 22, 2009

he has an sql2 variable on the line with the error as he is missin semi-colons and some quotes and now he will report back and we will move onto the next error or find a further solution to his current problem somewhere else

sql2 line

$sql2 = "INSERT INTO stock SET quantity='$quantity', price='$price'";
[/code/]

sstoveld · March 22, 2009

i think i got all the semi colons. you see any missing? ;/

<form name="edit" method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
    <tr>
    	<td><input name="title" type="text" value="<?php echo $title; ?>" size="30" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="stock" type="text" value="<?php echo $quantity; ?>" size="4" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="price" type="text" value="<?php echo $price; ?>" size="6" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="update" type="submit" value="update"></td>
    </tr>
    </form>

still getting that error though

edit:here's my current pastebin: http://pastebin.com/m34bb9704

FaT3oYCG · March 22, 2009

change the input with the name of stock to quantity

sstoveld · March 22, 2009

change the input with the name of stock to quantity

<form name="edit" method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
    <tr>
    	<td><input name="title" type="text" value="<?php echo $title; ?>" size="30" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="quantity" type="text" value="<?php echo $quantity; ?>" size="4" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="price" type="text" value="<?php echo $price; ?>" size="6" />
    <input type="hidden" name="id" value="<?php echo $id;?>">
    	<td><input name="update" type="submit" value="update"></td>
    </tr>
    </form>

you mean like that? same error

FaT3oYCG · March 22, 2009

$sql2 = "INSERT INTO stock VALUES quantity='$quantity', price='$price'";

sstoveld · March 22, 2009

$sql2 = "INSERT INTO stock VALUES quantity='$quantity', price='$price'";

ok i tried that and this is what i get:

Error updating. 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 'sql2' at line 1 INSERT INTO stock VALUES quantity='321', price='321'

this time it atleast had something for quantity, before it just said quantity=''

what's the difference between SET and VALUES?

EDIT:

should there be another ; in there?

$sql2 = "INSERT INTO stock VALUES quantity='$quantity', price='$price';";

trq · March 23, 2009

This....

if (!mysql_query(sql2)){

Should be....

if (!mysql_query($sql2)) {

However,

None of those variables need be in your form, there not populated the first time you load the form anyway. The script is inserting new data, not editing existing data.

All the code could be cleaned up and cleaned alot. For instance, the php....

<?php

if (isset($_POST['submit'])) {
  mysql_connect('localhost', 'dbase18', '*******') or die ('I cannot connect to the database because: ' .mysql_error());
  mysql_select_db('dbase18', $dbcnx);

  // you still need to validate this data. 
  $books_id = $_POST['books_id'];
  $title = htmlentities($_POST['title']);
  $stock_id = $_POST['stock_id'];
  $quantity = htmlentities($_POST['quantity']);
  $price = htmlentities($_POST['price']);
  
  $statements = array("INSERT INTO books SET title = '$title'","INSERT INTO stock SET quantity = '$quantity', price='$price'");

  foreach ($stataments as $statement) {
    if (!mysql_query($statement)) {
      echo "<p>Error updating. ".mysql_error()." $sql2</php>";
    } else {
      echo "<p>Updated successfully.</p>";
    }
  }
}

?>

trq · March 23, 2009

$sql2 = "INSERT INTO stock VALUES quantity='$quantity', price='$price'";
ok i tried that and this is what i get:
Error updating. 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 'sql2' at line 1 INSERT INTO stock VALUES quantity='321', price='321'
this time it atleast had something for quantity, before it just said quantity=''

what's the difference between SET and VALUES?

EDIT:

should there be another ; in there?

$sql2 = "INSERT INTO stock VALUES quantity='$quantity', price='$price';";

Nothing and no, its not required.

FaT3oYCG · March 23, 2009

erm try puttin the data in brackets i.e.

$sql2 = "INSERT INTO stock VALUES (quantity='$quantity', price='$price')";

sstoveld · March 23, 2009

This....

if (!mysql_query(sql2)){

Should be....

if (!mysql_query($sql2)) {

However,

None of those variables need be in your form, there not populated the first time you load the form anyway. The script is inserting new data, not editing existing data.

All the code could be cleaned up and cleaned alot. For instance, the php....

<?php

if (isset($_POST['submit'])) {
  mysql_connect('localhost', 'dbase18', '*******') or die ('I cannot connect to the database because: ' .mysql_error());
  mysql_select_db('dbase18', $dbcnx);

  // you still need to validate this data. 
  $books_id = $_POST['books_id'];
  $title = htmlentities($_POST['title']);
  $stock_id = $_POST['stock_id'];
  $quantity = htmlentities($_POST['quantity']);
  $price = htmlentities($_POST['price']);
  
  $statements = array("INSERT INTO books SET title = '$title'","INSERT INTO stock SET quantity = '$quantity', price='$price'");

  foreach ($stataments as $statement) {
    if (!mysql_query($statement)) {
      echo "<p>Error updating. ".mysql_error()." $sql2</php>";
    } else {
      echo "<p>Updated successfully.</p>";
    }
  }
}

?>

thank you so much it actually doesnt give me an error now, but how do i make the 2nd query match the title_id in stock table with books_id from the books table? title_id is NULL

i know the code is very messy, im re-using code from earlier in the semester trying to save time as the assignment is due tonight in 4 hours ill try and clean it up when i get everything working.

trq · March 23, 2009

Your not using title_id in your query so why do you need it?

Sign In

[SOLVED] Need some help with Bookstore administration page

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information