[SOLVED]Insert into MySQL DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

[SOLVED]Insert into MySQL DB

Post 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
Last edited by KevinCB on Tue Apr 05, 2005 1:20 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. $_POST['desc'] isn't used anywhere.
  2. quote your named array indices (lines 30 & 55)
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

mysql_real_escape_string() is one function to use. What code did you try with it that "didn't work?"
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

Post 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 :?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

no.. var_dump() shows that $_POST['desc'] is your description, not $_POST['item_desc'] which is what your code is "expecting"
Post Reply