PHP - SQL INSERT function

dragon2309

dragon2309

P.I Dragon
Hey all, i have some upcoming coursework from ICT, i've got most of it ifnalized i just need a way to create a web internface whre the user enteres values into text fields/boxes and submits them, then the PHP script would run an INSERT function and shove it all into a new record in the table specified.

In theory in my mind all that would happen is that the text boxes etc... would be given values, they would be stored using the PHP POST function, then later on in the script when i write in the INSERT function to run, just replace the data values with the values from the text boxes. Sounds fairly easy...

But how do i actually put that all together and how do i get it to actually run the script when i hit SUBMIT and what happens once its done the INSERT function.... i woudl need a splash page saying DONE or somethign....

Cheers, dragon
 
apj101

apj101

VIP Member
here is what your query should look like
Code: 
mysql_select_db($mysql);
$query = "INSERT INTO plop (field1, field2, field3) VALUES ('$val2', '$val2', '$val2')";


mysql_query($query) or die('Error, insert query failed');
you need to at least have a go matey, then we/I will help
 
dragon2309

dragon2309

P.I Dragon
ha ha ha, get in!!! it actually works, blimey, the first thing i made in PHP that actually works first time (well, all bar i missed off a closing semicolon, but hey, cant be perfect.

i would post the link to it, but then people might just be mean and go adding thousands of records to the database, if you want to ahve a look at it apj then let me know and ill PM it to you (i think you can be trusted, and if you want to test it then just make sure you put APJ101 in the description field somewhere)

w00t, dragon

*EDIT* heres the code...

PHP: 
<body>

<form>
    <p align="center">
    <img border="0" src="../../logo%202%20better.jpg" width="640" height="150"><script language="JavaScript" src="simplytrue/menu.js"></script><script language="JavaScript" src="simplytrue/menu_items.js"></script><script language="JavaScript" src="simplytrue/menu_tpl.js"></script><script language="JavaScript">
    
    
        <!--//
        new menu (MENU_ITEMS, MENU_POS, MENU_STYLES);
        //-->
    </script></p>
    <p align="right"><b><font face="Tahoma"><a href="javascript:history.back()">
    &lt;-- Back</a></font></b></p>
</form>
<p align="center"><font face="Tahoma" style="font-size: 10pt; font-weight: 700">
Please select a category from the simplyTrue Collection and then enter your 
search criteria.<br>
Click SEARCH to view the items that meet your criteria...</font></p>
<?php
      echo"<form id='item_insert' action='$PHP_SELF' method='post'>
   <p>
    <font face='Tahoma' style='font-size: 11pt'>
      Select category: <select name='cat'>
               <option value='ad_brac'>Adult Bracelets</option>
               <option value='ch_brac'>Child Bracelets</option>
               <option value='charm_brac'>Charm Bracelets</option>
               <option value='ad_neck'>Adult Necklaces</option>
               <option value='ch_neck'>Child Necklaces</option>
              <option value='ad_set'>Adult Sets</option>
              <option value='ch_set'>Child Sets</option>
               <option value='earring'>Earrings</option>
               <option value='k_pins'>Kilt Pins</option>
               <option value='l_pins'>Lapel Pins</option>
               <option value='wine_ch'>Wine Charms</option>
               <option value='bag_ch'>Bag Charms</option>
               <option value='bookmark'>Bookmarks</option>
     </select> <br>
    <br>
    Enter StockCode: <input type='text' name='stockcode' size='6'>
    <br>
    <br>
    Enter Description:<br> <textarea rows='7' name='description' cols='34'></textarea>
    <br>
    <br>
    Enter Price: <input type='text' name='price' size='6'>
    <!-- StockCount should always be 1 for new items !-->
    <input type='hidden' name='stockcount' value='1' size='1'>
    <br>
    <br>
    Enter URL: <input type='text' name='url' size='75'>
    <br>
    <br>
    Enter IMG Location: <input type='text' name='img' size='75'>

    <font face='Tahoma' size='1'>
    (/pics/adult/pic_1_s.jpg)</font><font face='Tahoma' style='font-size: 11pt'><br>
    <br>
    Enter IMG Location: <input type='text' name='imgurl' size='75'>
</font>
    <font face='Tahoma' size='1'>
    (http://www.simplytrue.co.uk/pics/adult/pic_1_s.jpg</a>)</font>
    <br>
    <br>
    Colour 1: <input type='text' name='colour1' size='15'><br><br>
    Colour 2: <input type='text' name='colour2' size='15'><br><br>
    Colour 3: <input type='text' name='colour3' size='15'><br><br>
</font>
   </p>
   <p><input type='submit' value='Submit' name='B1'></p>

   </form>";
     if( ! empty( $_POST["cat"] ) )
     if( ! empty( $_POST["stockcode"] ) )
     if( ! empty( $_POST["description"] ) )
     if( ! empty( $_POST["price"] ) )
     if( ! empty( $_POST["stockcount"] ) )
     if( ! empty( $_POST["url"] ) )
     if( ! empty( $_POST["img"] ) )
     if( ! empty( $_POST["imgurl"] ) )
     if( ! empty( $_POST["colour1"] ) )
     if( ! empty( $_POST["colour2"] ) )
     if( ! empty( $_POST["colour3"] ) )
     
     {
     echo".<br><br>";
     
     require "dbinfo.php";
 
       // Connect to database
       if (!($connection = @ mysql_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD)))
         showerror();
 
       // Use the simplytrue databasee
       if (!mysql_select_db(DB_DATABASENAME, $connection))
         showerror();
 
       // Create SQL statement
       $cat = $_POST["cat"];
       $stockcode = $_POST["stockcode"];
       $description = $_POST["description"];
       $price = $_POST["price"];
       $stockcount = $_POST["stockcount"];
       $url = $_POST["url"];
       $img = $_POST["img"];
       $imgurl = $_POST["imgurl"];
       $colour1 = $_POST["colour1"];
       $colour2 = $_POST["colour2"];
       $colour3 = $_POST["colour3"];

       $query = "INSERT INTO $cat (StockCode, Description, Price, StockCount, URL, IMG, IMGURL, colour1, colour2, colour3) VALUES ('$stockcode', '$description', '$price', '$stockcount', '$url', '$img', '$imgurl', '$colour1', '$colour2', '$colour3')";
           
    
       // Execute SQL statement
       if (!($result = @ mysql_query ($query, $connection)))
         showerror();
 
       
       }
?>

</body>
 
apj101

apj101

VIP Member
good stuff, its not the nicest code in the world, but the first rule is always to get it working

Good job, be sure to add error catches in there, in case the user doesnt enter the text correctly (you could you javascript for that to save your server load)
 
dragon2309

dragon2309

P.I Dragon
yeh, about error catches... you see how the SQL statement is executed in code above, how can i put an error in there, because it says showerror(), but what does that actually mean?

Also, another thing is that at the moment, you fill it in and hit submit and it loads for like a second and then just shows the same page again, it worked but it doesnt give any recognition of this to the user.... any ideas??

cheers, dragon

*EDIT* - yeh, i know the code isnt the nicest, but as you said, it works, and i dont really know of any better way to code it, so....
 
apj101

apj101

VIP Member
because it says showerror(), but what does that actually mean?
Click to expand...
in your case this doesnt do anything, your supposed to have a subroutine called showerror that is responsible for showing errors to the debugger/user.

Also, another thing is that at the moment, you fill it in and hit submit and it loads for like a second and then just shows the same page again, it worked but it doesnt give any recognition of this to the user.... any ideas??
Click to expand...
thats because you have the page linking to itself, why not have a static pages that shows the user options, when the user clicks submit the form action send the data to a php script of a differnet name which picks up the values enters them into the db and then shows the user a nice message on the status of the action.
something like
if (!($result = @ mysql_query ($query, $connection)))
SHOW ERROR PAGE
else
SHOW SUCCESS PAGE
 
dragon2309

dragon2309

P.I Dragon
apj101 said:
thats because you have the page linking to itself, why not have a static pages that shows the user options, when the user clicks submit the form action send the data to a php script of a differnet name which picks up the values enters them into the db and then shows the user a nice message on the status of the action.
something like
if (!($result = @ mysql_query ($query, $connection)))
SHOW ERROR PAGE
else
SHOW SUCCESS PAGE
Click to expand...
ok, that literally meant nothing to me at all...
 
apj101

apj101

VIP Member
a) 1 web page with NO php on it AT ALL
this web page shows the user the FORM to fill out, this form point to a php script
<form id='item_insert' action='/phplocation/php_script.php' method='post'>
<p>
<font face='Tahoma' style='font-size: 11pt'>
Select category: <select name='cat'>
<option value='ad_brac'>Adult Bracelets</option>
<option value='ch_brac'>Child Bracelets</option>
<option value='charm_brac'>Charm Bracelets</option>
<option value='ad_neck'>Adult Necklaces</option>
<option value='ch_neck'>Child Necklaces</option>
<option value='ad_set'>Adult Sets</option>
<option value='ch_set'>Child Sets</option>
<option value='earring'>Earrings</option>
<option value='k_pins'>Kilt Pins</option>
<option value='l_pins'>Lapel Pins</option>
<option value='wine_ch'>Wine Charms</option>
<option value='bag_ch'>Bag Charms</option>
<option value='bookmark'>Bookmarks</option>
</select> <br>
<br>
Enter StockCode: <input type='text' name='stockcode' size='6'>
<br>
<br>
Enter Description:<br> <textarea rows='7' name='description' cols='34'></textarea>
<br>
<br>
Enter Price: <input type='text' name='price' size='6'>
<!-- StockCount should always be 1 for new items !-->
<input type='hidden' name='stockcount' value='1' size='1'>
<br>
<br>
Enter URL: <input type='text' name='url' size='75'>
<br>
<br>
Enter IMG Location: <input type='text' name='img' size='75'>

<font face='Tahoma' size='1'>
(/pics/adult/pic_1_s.jpg)</font><font face='Tahoma' style='font-size: 11pt'><br>
<br>
Enter IMG Location: <input type='text' name='imgurl' size='75'>
</font>
<font face='Tahoma' size='1'>
(http://www.simplytrue.co.uk/pics/adult/pic_1_s.jpg</a>)</font>
<br>
<br>
Colour 1: <input type='text' name='colour1' size='15'><br><br>
Colour 2: <input type='text' name='colour2' size='15'><br><br>
Colour 3: <input type='text' name='colour3' size='15'><br><br>
</font>
</p>
<p><input type='submit' value='Submit' name='B1'></p>

</form>
Click to expand...
user clicks submit and the post data goes to this page /phplocation/php_script.php

2. The /phplocation/php_script.php script itself
This picks up the posted values
stuffs them into a the insert statement
executes the insert statment, and tell the user the result.
Would look like this
<?php
if( ! empty( $_POST["cat"] ) )
if( ! empty( $_POST["stockcode"] ) )
if( ! empty( $_POST["description"] ) )
if( ! empty( $_POST["price"] ) )
if( ! empty( $_POST["stockcount"] ) )
if( ! empty( $_POST["url"] ) )
if( ! empty( $_POST["img"] ) )
if( ! empty( $_POST["imgurl"] ) )
if( ! empty( $_POST["colour1"] ) )
if( ! empty( $_POST["colour2"] ) )
if( ! empty( $_POST["colour3"] ) )

{
echo".<br><br>";

require "dbinfo.php";

// Connect to database
if (!($connection = @ mysql_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD)))
showerror();//You need to write something to catch these errors at some point

// Use the simplytrue databasee
if (!mysql_select_db(DB_DATABASENAME, $connection))
showerror();

// Create SQL statement
$cat = $_POST["cat"];
$stockcode = $_POST["stockcode"];
$description = $_POST["description"];
$price = $_POST["price"];
$stockcount = $_POST["stockcount"];
$url = $_POST["url"];
$img = $_POST["img"];
$imgurl = $_POST["imgurl"];
$colour1 = $_POST["colour1"];
$colour2 = $_POST["colour2"];
$colour3 = $_POST["colour3"];

$query = "INSERT INTO $cat (StockCode, Description, Price, StockCount, URL, IMG, IMGURL, colour1, colour2, colour3) VALUES ('$stockcode', '$description', '$price', '$stockcount', '$url', '$img', '$imgurl', '$colour1', '$colour2', '$colour3')";


// Execute SQL statement
if (!($result = @ mysql_query ($query, $connection)))
{
echo "There was an error when attempting to add the data to the database, mysql report the following";
echo "<p>" . mysql_error() . "</p>";

else
{
echo "Thankyou the item you requested was added to our database successfully, click <a href="www.google.com">here</a> to return home </br> ";
}


}
?>
Click to expand...

I havent check these for errors, and there are better way to do the same task but this way it should be clear in your head.




<---- bonus marks for you teacher--->
as for error checking you need to be useing a level of validation of the web page 1.
you should use javascript for this:


change this line
<form id='item_insert' action='/phplocation/php_script.php' method='post'>

to
<form id='item_insert' action='/phplocation/php_script.php' method='post' onsubmit="return checkrequired(this)">

then add this code in the header of the html

function checkrequired(theForm) {

for (i=0;i<theForm.length;i++) {
var tempobj=theForm.elements;
if (((tempobj.type=="text"||tempobj.type=="textarea")&&
tempobj.value=='')
{
alert("Please make sure the "+tempobj.name+" field was properly completed.");

return false;
}
}
}
 
dragon2309

dragon2309

P.I Dragon
does it change anything that i already had the showerror function defined in my dbinfo.php file.... it looks like this -

PHP: 
<?php
/*
* dbinfo.php
*/
define("DB_USERNAME", "****");
define("DB_PASSWORD", "****");
define("DB_HOSTNAME", "****");
define("DB_DATABASENAME", "****");
// Show an error and stop the script
function showerror()
{
if ( mysql_error() )
{
die( "Error " . mysql_errno() . " : " . mysql_error() );
}
else
{
die( "Could not connect to the DBMS" );
}
}
?>
Edited for obvious reasons....

But wow apj, all that info in that post, im gonna sit here and read it all now, it looks fantastic, especially the error checking on that form, that looks good.

I will go and try your new setup ow, with the two pages and report back in a while, thanks

dragon
 
dragon2309

dragon2309

P.I Dragon
ok, i tried your code, didnt compile the first few times, after looking through it all, found the prblem, you used a " instead of a ' inside an echo, so it thoguht it was ending it prematurely without a ; at the end of it. fixed and it all works now, doesnt looks user friendly as before, so i might spend liek 30mins tarting it all up.

I havent implemented that javascript bit yet, i suppose i could try that now.... yeh, hold on, brb.

dragon
 
dragon2309

dragon2309

P.I Dragon
ok, your javascript thing broke it, now it doesnt submit the entries to the databse, i dont get any echo result on the PHP page and no javascript alert box xomes up, lol.... i dont know javascript at all, so i have no clue what any of the code snippet means, any help??

cheers, dragon
 
apj101

apj101

VIP Member
sorry about the errors, i just hashed it down and didnt bother debugging it, i dont even have php on this rig (or apache for that matter)

show me the html page with the javascript in I'm pretty sure i know what wrong
(forgot the java script tags :mad: )
 
dragon2309

dragon2309

P.I Dragon
well, i kinda gathered it would need tags around it so i put those in when i started.... but it still doesnt work or do anything for that matter...

Code: 
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Select category</title>

<script language='javascript'>
checkrequired(theForm) { for (i=0;i<theForm.length;i++) {
var tempobj=theForm.elements[i];
if (((tempobj.type=="text"||tempobj.type=="textarea") &&
tempobj.value=="")
{
alert("Please make sure the "+tempobj.name+" field was properly completed.");

return false;
}
}
}
</script>

</head>

<body>

<form id="item_insert" action="/stocklist/stock_control/item_insert.php" method="post">
    <p><font face="Tahoma" style="font-size: 11pt">Select category:
    <select name="cat">
    <option value="ad_brac">Adult Bracelets</option>
    <option value="ch_brac">Child Bracelets</option>
    <option value="charm_brac">Charm Bracelets</option>
    <option value="ad_neck">Adult Necklaces</option>
    <option value="ch_neck">Child Necklaces</option>
    <option value="ad_set">Adult Sets</option>
    <option value="ch_set">Child Sets</option>
    <option value="earring">Earrings</option>
    <option value="k_pins">Kilt Pins</option>
    <option value="l_pins">Lapel Pins</option>
    <option value="wine_ch">Wine Charms</option>
    <option value="bag_ch">Bag Charms</option>
    <option value="bookmark">Bookmarks</option>
    </select> <br>
    <br>
    Enter StockCode: <input type="text" name="stockcode" size="6"> <br>
    <br>
    Enter Description:<br>
    <textarea rows="7" name="description" cols="34"></textarea> <br>
    <br>
    Enter Price: <input type="text" name="price" size="6">
    <!-- StockCount should always be 1 for new items !-->
    <input type="hidden" name="stockcount" value="1" size="1"><br>
    <br>
    Enter URL: <input type="text" name="url" size="75"> <br>
    <br>
    Enter IMG Location: <input type="text" name="img" size="75">
    <font face="Tahoma" size="1">(/pics/adult/pic_1_s.jpg)</font><font face="Tahoma" style="font-size: 11pt"><br>
    <br>
    Enter IMG Location: <input type="text" name="imgurl" size="75"> </font>
    <font face="Tahoma" size="1">(http://www.simplytrue.co.uk/pics/adult/pic_1_s.jpg</a>)</font>
    <br>
    <br>
    Colour 1: <input type="text" name="colour1" size="15"><br>
    <br>
    Colour 2: <input type="text" name="colour2" size="15"><br>
    <br>
    Colour 3: <input type="text" name="colour3" size="15"><br>
    <br>
    </font></p>
    <p><input type="submit" value="Submit" name="B1"></p>
</form>

</body>

</html>
</head>

cheers, dragon
 
apj101

apj101

VIP Member
you never did what i said and changed this line
<form id='item_insert' action='/stocklist/stock_control/item_insert.php method='post'>

to
<form id='item_insert' action='/stocklist/stock_control/item_insert.php' method='post' onsubmit="return checkrequired(this)">

also you never copied the code correctly this line
checkrequired(theForm) { for (i=0;i<theForm.length;i++) {

should read
function checkrequired(theForm) { for (i=0;i<theForm.length;i++) {

as in my original post ;)
 
dragon2309

dragon2309

P.I Dragon
ok, first thing i did change the <form> line to what you dsaid, i just posted an old version of the code by mistake, and yes, i did miss off FUNCTION at the beginning, let me go try that....

dragon
 
dragon2309

dragon2309

P.I Dragon
rght, still does nothign, heres the new code....

PHP: 
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Select category</title>

<script language='javascript'>
function checkrequired(theForm) {

for (i=0;i<theForm.length;i++) {
var tempobj=theForm.elements[i];
if (((tempobj.type=="text"||tempobj.type=="textarea") &&
tempobj.value=='')
{
alert("Please make sure the "+tempobj.name+" field was properly completed.");

return false;
}
}
}
</script>

</head>

<body>

<form id="item_insert" action="/stocklist/stock_control/item_insert.php" method="post" onsubmit="return checkrequired(this)">
    <p><font face="Tahoma" style="font-size: 11pt">Select category:
    <select name="cat">
    <option value="ad_brac">Adult Bracelets</option>
    <option value="ch_brac">Child Bracelets</option>
    <option value="charm_brac">Charm Bracelets</option>
    <option value="ad_neck">Adult Necklaces</option>
    <option value="ch_neck">Child Necklaces</option>
    <option value="ad_set">Adult Sets</option>
    <option value="ch_set">Child Sets</option>
    <option value="earring">Earrings</option>
    <option value="k_pins">Kilt Pins</option>
    <option value="l_pins">Lapel Pins</option>
    <option value="wine_ch">Wine Charms</option>
    <option value="bag_ch">Bag Charms</option>
    <option value="bookmark">Bookmarks</option>
    </select> <br>
    <br>
    Enter StockCode: <input type="text" name="stockcode" size="6"> <br>
    <br>
    Enter Description:<br>
    <textarea rows="7" name="description" cols="34"></textarea> <br>
    <br>
    Enter Price: <input type="text" name="price" size="6">
    <!-- StockCount should always be 1 for new items !-->
    <input type="hidden" name="stockcount" value="1" size="1"><br>
    <br>
    Enter URL: <input type="text" name="url" size="75"> <br>
    <br>
    Enter IMG Location: <input type="text" name="img" size="75">
    <font face="Tahoma" size="1">(/pics/adult/pic_1_s.jpg)</font><font face="Tahoma" style="font-size: 11pt"><br>
    <br>
    Enter IMG Location: <input type="text" name="imgurl" size="75"> </font>
    <font face="Tahoma" size="1">(http://www.simplytrue.co.uk/pics/adult/pic_1_s.jpg</a>)</font>
    <br>
    <br>
    Colour 1: <input type="text" name="colour1" size="15"><br>
    <br>
    Colour 2: <input type="text" name="colour2" size="15"><br>
    <br>
    Colour 3: <input type="text" name="colour3" size="15"><br>
    <br>
    </font></p>
    <p><input type="submit" value="Submit" name="B1"></p>
</form>

</body>

</html>
</head>
cheers again, i really appreciate your help on this.... dragon

*EDIT* - i get this debug error, any ideas??

dfsdffgfge8.jpg
 
Last edited:
dragon2309

dragon2309

P.I Dragon
ha haaa, i got it, followed to line 13, and there is one less closing bracket than there is opening brackets in that statement, so i added one and now it works, cheers apj

dragon
 
dragon2309

dragon2309

P.I Dragon
hey apj, i dont suppose thre is a way to not include just one field in that javascript field check... because colour 3 is optional, items either have 2 or 3 colours, atm if it has 2 it wont let me submit the forum, lol.... any ideas...

cheers, dragon
 
You must log in or register to reply here.
Top