Page 1 of 1

[SOLVED]Insert into MySQL DB

Posted: Mon Apr 04, 2005 9:26 am
by KevinCB
I'm creating a php script that adds data into a MYSQL DB from data entered into a form. So far the script works fine, but theres just two parts I haven't got to work yet.

This is my code:

Code: Select all

<?php
if ($_POST[op] != "add") {
   //haven't seen the form, so show it
	 $display_block = "<h1>Add a Book</h1>
	 <form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
	 <P><strong>Artist:</strong><br>
	 <input type=\"text\" name=\"book_artist\" size=30 maxlength=100>
	 
	 <P><strong>Book Title:</strong><br>
	 <input type=\"text\" name=\"item_name\" size=30 maxlength=75>
	 
	 <P><strong>Book Author:</strong><br>
	 <input type=\"text\" name=\"book_author\" size=30 maxlength=50>
	 
	 <P><strong>Description:</strong><br>
	 <textarea name=\"desc\" cols=35 rows=7 wrap=virtual></textarea>
	 
	 <P><strong>Price:</strong><br>
	 <input type=\"text\" name=\"item_price\" size=10 maxlength=10>
	 
	 <P><strong>Image:</strong><br>
	 <input type=\"text\" name=\"item_image\" size=30 maxlength=100>
	  <input type=\"hidden\" name=\"op\" value=\"add\">
		
	 <p><input type=\"submit\" name=\"submit\" value=\"Add Book\"></p>
	 </FORM>";
	 
} else if ($_POST[op] == "add") {
   //time to add to tables, so check for required fields
	  if ($_POST[book_artist] == "") {
		   header("Location: addbook.php");
			 exit;
		}
		
	 //connect to database
	 $conn = mysql_connect("localhost", "username", "password")
	     or die(mysql_error());
	 mysql_select_db("shoot_the_moon",$conn)  or die(mysql_error());
	 
	 //add to store_items table
   $add_items = sprintf('
	 INSERT INTO store_items
   (item_name, item_price, item_image)
   VALUES("%s", "%s", "%s")
   ',
   mysql_real_escape_string($_POST['item_name']),
   mysql_real_escape_string($_POST['item_price']),
   mysql_real_escape_string($_POST['item_image'])
	 );
   mysql_query($add_items) or die(mysql_error());
	 
	 //get item id for use with other tables
	 $item_id = mysql_insert_id();
	 
	 if (($_POST[book_artist]) || ($_POST[book_author])) {
	    //something relevant so add to the book table
			$add_book = sprintf('
			INSERT INTO store_books
			(book_artist, book_author)
			VALUES("%s", "%s")
			',
			mysql_real_escape_string($_POST['book_artist']),
			mysql_real_escape_string($_POST['book_artist'])
			);
			mysql_query($add_book) or die(mysql_error());
	 }

   $display_block = "<h1>Record Added</h1>
	 <P>Your record has been added.  Would you like to
	  <a href=\"addbook.php\">add another</a>?</p>
		<P>Go back to the
		<a href=\"adminmenu.php\">main menu</a></p>";
}
?>
<HTML>
<HEAD>
<TITLE>Add a Book</TITLE>
</HEAD>
<BODY>
<?php echo $display_block; ?>
</BODY>
</HTML>
Ok, I suppose what I'm asking is how can I insert data for the description which is in a textarea box on the form, as the mysql_real_string doesn't work for that input.

And also can anyone suggest the best way to insert some sort of image that is on the server and retrieve it from the database e.g. would saving the URL of the image and retrieving it be the best solution?

And one final thing, since I'm new to PHP, if anyone can see any vulnerable parts of the code security wise, then please post a link or solution to get round this. It's something I no doubt will need to learn.

Cheers

Posted: Mon Apr 04, 2005 9:46 am
by feyd
  1. $_POST['desc'] isn't used anywhere.
  2. quote your named array indices (lines 30 & 55)

Posted: Mon Apr 04, 2005 10:54 am
by KevinCB
Ok thanks for telling me about the quotes, totally missed them.

As for the first part I already know that $_POST['desc'] isn't used anywhere, I was asking what function to use for the input as I did try it with the mysql_real_escape_string function and it didn't input the data I entered, as it's not set to VARCHAR in the DB, but as TEXT instead.

Posted: Mon Apr 04, 2005 10:55 am
by feyd
mysql_real_escape_string() is one function to use. What code did you try with it that "didn't work?"

Posted: Mon Apr 04, 2005 11:31 am
by KevinCB
The code is the same, but I just added $_POST['item_desc'] to the rest of the code (just a note that in the db the name is item_desc, not just desc).

Anyway this is the code:

Code: Select all

//add to store_items table
   $add_items = sprintf('
	 INSERT INTO store_items
   (item_name, item_price, item_desc, item_image)
   VALUES("%s", "%s", "%s", "%s")
   ',
   mysql_real_escape_string($_POST['item_name']),
   mysql_real_escape_string($_POST['item_price']),
   mysql_real_escape_string($_POST['item_desc']),
   mysql_real_escape_string($_POST['item_image'])
	 );
   mysql_query($add_items) or die(mysql_error());
The code adds the data for the other fields, and displays the message asking if you want to add another record, but when I go and have a look at the table in phpmyadmin the description field 'item_desc' has no value.

Posted: Mon Apr 04, 2005 11:35 am
by feyd
You don't have a field 'item_desc', just 'desc'. Could this be the problem?

If not, echo out $add_items after calling sprintf(). Also var_dump($_POST) to check what is being sent to your script.

Posted: Mon Apr 04, 2005 12:58 pm
by KevinCB
I've done that to my script and this is the following output I got:
array( 8 ) { ["book_artist"]=> string(11) "The Beatles" ["item_name"]=> string(34) "The Encyclopedia of Beatles People" ["book_author"]=> string(10) "Bill Henry" ["desc"]=> string(34) "An encyclopedia of Beatles people." ["item_price"]=> string(4) "4.99" ["item_image"]=> string(0) "" ["op"]=> string(3) "add" ["submit"]=> string( 8 ) "Add Book" } INSERT INTO store_items (cat_id, item_name, item_price, item_desc, item_image) VALUES("1", "", "The Encyclopedia of Beatles People", "4.99", "")
The above is the var_dump($_POST), and the echo of the $add_items.

The var_dump() is recognising that I have inputted a value for $_POST['item_desc'], but the echo is returning a null value.

I'm completely lost with this now :?

Posted: Mon Apr 04, 2005 2:51 pm
by feyd
no.. var_dump() shows that $_POST['desc'] is your description, not $_POST['item_desc'] which is what your code is "expecting"