Welcome to WebmasterWorld Guest from 54.163.168.15

Forum Moderators: coopster & jatar k

Nothng written to the table

mysql and php not writing to the table

   
9:34 am on Nov 18, 2006 (gmt 0)

5+ Year Member



Hello .....can someone help me with this. I am new to php and just experiementing with few lines of codes. I've created a databse called carousel and there is 2 tables, table bin_number and other table resistor. I have a html form which I want to get data from and write it into table resistor. But so far its not doing that, nothing is entered into the database when the submit button is pressed. I dont understand why, please help. I would appreciate if any advice and tips provided.

<?php

$connection = mysql_connect($_host, $db_user, $db_password) or die("Unable to connect to the database");
mysql_select_db($db_name, $connection);

$link = "<a href='resistor.php'> Click Here to Return To Data Entry Page </a>";
$date = time();
$component_Cat = 1;

If (isset($_POST['submit']) &&!empty($_POST['faiveley_PN']) &&!empty($_POST['uksupplier_PN']) &&!empty($_POST['Supplier_Name']) &&!empty($_POST['component_Value']))
{
$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VLAUE('$date', 1, 'bin_Number', '$component_Value', '$package_type', '$voltage_rating', '$current_rating', '$wattage_rating', '$quantity, '$description')";
mysql_query($query, $connection);

}
else
{
echo "you must fill out every field in the form";
echo $link;

}
?>

Form:

<!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=iso-8859-1" />
<title>resistor</title>
<link href="resistor.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style></head>

<body>
<h1 align="center">Resistor</h1>
<form action="resistor_in.php" method="post">
<p>Faiveley Part Number:
<input name="faiveley_PN" type="text" id="faiveley_PN" maxlength="10" />
</p>
<p>UK Supplier Part Number:
<input name="uksupplier_PN" type="text" id="uksupplier_PN" maxlength="10" />
</p>
<p>Supplier Name:
<select name="Supplier_Name" id="Supplier_Name">
<option value="far">Farnell Electronics</option>
<option value="Rs">RS Electronics</option>
</select>
</p>
<p>Component Value:
<input name="component_Value" type="text" id="component_Value" maxlength="10" />
</p>
<p>Package Type:
<input name="package_type" type="text" id="package_type" />
</p>
<p>Voltage Rating:
<input name="voltage_rating" type="text" id="voltage_rating" maxlength="10" />
</p>
<p>Current Rating:
<input name="current_rating" type="text" id="current_rating" maxlength="10" />
</p>
<p>Wattage Rating:
<input name="wattage_rating" type="text" id="wattage_rating" maxlength="10" />
</p>
<p>Quantity:
<input name="quantity" type="text" id="quantity" maxlength="6" />
</p>
<p>Cost ():
<input name="cost" type="text" id="cost" maxlength="4" />
</p>
<p>Description:
<textarea name="description" cols="50" rows="10" id="description"><Please Enter Component Details Here>
</textarea>
</p>
Bin Number:
<select name ="num">
<?php
require($_SERVER["DOCUMENT_ROOT"]."/Php_Codes/User_details.php");
$connection = mysql_connect($_host, $db_user, $db_password) or die("Unable to connect");
mysql_select_db($db_name, $connection);

$query = "SELECT * FROM bin_Number";
$result = mysql_query($query);
while ($record = mysql_fetch_assoc($result))
{
echo "<OPTION VALUE = '".$record["num"]."'>".$record["num"];
}
?>
</select>
</p>
<p>Submit:
<input type="submit" name="Submit" value="Submit" />
</p>
</form>
</body>

</html>

2:18 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Hi Atomic_Guy,

You are missing an apostrophe here:

'$quantity

Try adding the following to your query, this will generate an error and can help you with debugging.

mysql_query($query, $connection) or die(mysql_error());

dc

2:49 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



hi dreamcatcher....

I have corrected that mistake and run the script again. And still nothing happening. Im not getting any errors, simply the execution jumps to echo "you must fill out every field in the form";
echo $link;

Am I using the validation correct?

3:32 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



One of the items in this if statement is failing so check them individually for what you expect and you'll find the answer:

If (isset($_POST['submit']) &&!empty($_POST['faiveley_PN']) &&!empty($_POST['uksupplier_PN']) &&!empty($_POST['Supplier_Name']) &&!empty($_POST['component_Value']))

JAG

3:45 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



What is the way to check it individually? What do I need to do? Sorry for the inconvenience.
4:35 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



First off, there is a typo in your query:

$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
[b]VALUES[/b] ('$date', 1, 'bin_Number', '$component_Value', '$package_type', '$voltage_rating', '$current_rating', '$wattage_rating', '$quantity, '$description')";

secondly, where are you getting these variables from? They seem like POST variables to me:


$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VLAUE('$date', 1, '".$_POST['bin_Number']."', '".$_POST['component_Value']."', '".$_POST['package_type']."', '".$_POST['voltage_rating']."', '".$_POST['current_rating']."', '".$_POST['wattage_rating']."', '".$_POST['quantity']."', '".$_POST['description']."')";

Thirdly, if this is the case, then you must escape your variables:


$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VLAUE('$date', 1, '".mysql_real_escape_string($_POST['bin_Number'])."', '".mysql_real_escape_string($_POST['component_Value'])."', '".mysql_real_escape_string($_POST['package_type'])."', '".mysql_real_escape_string($_POST['voltage_rating'])."', '".mysql_real_escape_string($_POST['current_rating'])."', '".mysql_real_escape_string($_POST['wattage_rating'])."', '".mysql_real_escape_string($_POST['quantity'])."', '".mysql_real_escape_string($_POST['description'])."')";

Try this. Also, if you want to know what you are getting in your _POST variables, put this at the top of the script:


echo '<pre>';
print_r($_POST);
echo '</pre>';

Good luck! :)

4:35 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You could do a print_r($_POST) to see what is in all the post vars.

JAG

5:50 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



Thanks eelixduppy, I've used your modified codes with lots or escapes. I can now see the value in $_POST. But Still not writing to the database. Also isnt there any other way around to this other then to use lots of scapes?

I found this on someone elses forum and modified it....

$faiveley_PN = addslashes(faiveley_PN);
$uksupplier_PN = addslashes($uksupplier_PN);
$component_Value = addslashes($component_Value);
$package_type = addslashes($package_type);
$voltage_rating = addslashes($voltage_rating);
$current_rating = addslashes($current_rating);

If I just use this just like that before the $query line....will this work?

5:57 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



Yes, you can use it like that before the query but do not use addslashes, use mysql_real_escape_string [us2.php.net]. But make sure you are using $_POST['var_name'] instead of just $var_name unless you have register_globals enabled.

Also, add this to the top of your script:


error_reporting(E_ALL);

And make sure to use Dreamcatcher's advice for adding

die(mysql_error())
after your mysql_query call.
6:12 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



Im getting there buddy! Feeling bit normal now! lol

But I get these to lines of error thrown.

Notice: Use of undefined constant uksupplier_PN - assumed 'uksupplier_PN' in #*$!#*$!#*$! on line 29

Notice: Use of undefined constant faiveley_PN - assumed 'faiveley_PN' in #*$!#*$!#*$!X on line 35

What does it mean?

6:16 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



Those are notices that come from not surrounding the array index with quotes. You can get rid of them by simply do just that: adding quotes.

Other than those notices, is it still not adding values to the database?

Have you tried to manually add it yourself through phpmyadmin or the command prompt? Have you checked the spelling of all the tables, databases, column names, etc...?

Keep us updated :)

6:33 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



Im so blind! gerr....I missed out the dollar signs. Now its not throwing any error messages but not writing anything either to the tables. I can manualy write stuff to the table but not from the script.

this is what my table looks like.....

Field type Null

auto_Date int(10)unsigned No
component_Cat int(4) NO
faiveley_PN tinyiny(10) unsigned NO
uksupplier_PNtiny int(10) unsigned NO
bin_Number int(4) unsignedNo PRI
quantity int(4) NO
package_type varchar(10) NO
component_Value varchar(10) NO
volatage_rating varchar(6) NO
current_rating varchar(6) NO
wattage_rating varchar(6) No
cost decimal(3,3) NO
description tinytext NO

Is it possible that some of the type I got wrong? like auto_date field....and faiveley_PN where the user have to input decimal numbers like 123456.807. Do you think I have all the fields correct against the type?

6:37 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



Change this line real quick to this:

if(!empty($_POST['faiveley_PN']) &&!empty($_POST['uksupplier_PN']) &&!empty($_POST['Supplier_Name']) &&!empty($_POST['component_Value']))
6:39 pm on Nov 18, 2006 (gmt 0)

10+ Year Member



This column volatage_rating varchar(6) NO, shouldn't that be voltage_rating?

Mav

6:39 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



Okay
6:52 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



here is the complete code since all the twicking.... lol

<?php

error_reporting(E_ALL);
echo '<pre>';
print_r($_POST);
echo '</pre>';

$connection = mysql_connect($db_host, $db_user, $db_password) or die(mysql_error());
mysql_select_db($db_name, $connection);

$link = "<a href='resistor.php'> Click Here to Return To Data Entry Page </a>";
$date = time();
$component_Cat = 1;

if(!empty($_POST['faiveley_PN']) &&!empty($_POST['uksupplier_PN']) &&!empty($_POST['Supplier_Name']) &&!empty($_POST['component_Value']))
{
$faiveley_PN = mysql_real_escape_string($faiveley_PN);
$uksupplier_PN = mysql_real_escape_string($uksupplier_PN);
$component_Value = mysql_real_escape_string($component_Value);
$package_type = mysql_real_escape_string($package_type);
$voltage_rating = mysql_real_escape_string($voltage_rating);
$current_rating = mysql_real_escape_string($current_rating);

$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VALUE('$date', 1, 'bin_Number', '$component_Value', '$package_type', '$voltage_rating', '$current_rating', '$wattage_rating', '$quantity', '$description')";
mysql_query($query, $connection);

}
else
{
echo "you must fill out every field in the form";
echo $link;
}

?>

It was a typing error mavherick but in the table its correct.

But still not writting to the mysql table, what am I doing wrong? Im about to pull all my remaining hair out!

6:54 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member




$faiveley_PN = mysql_real_escape_string($_POST['faiveley_PN']);
$uksupplier_PN = mysql_real_escape_string($_POST['uksupplier_PN']);
$component_Value = mysql_real_escape_string($_POST['component_Value']);
$package_type = mysql_real_escape_string($_POST['package_type']);
$voltage_rating = mysql_real_escape_string($_POST['voltage_rating']);
$current_rating = mysql_real_escape_string($_POST['current_rating']);
7:00 pm on Nov 18, 2006 (gmt 0)

10+ Year Member



In the last part of your code, try replacing this line mysql_query($query, $connection); with this:

$result = mysql_query($query, $connection);
if (!$result)
{
die("Query failed: " . mysql_error());
}

If your query fails, the error message should give you a hint as to what is going on.

Mav

7:07 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I'm guessing that your $date var will give you trouble. You're assigning the value as a PHP time() which is a unix style value. I'd replace it with now() in the insert statement and let the db assign it.

JAG

7:14 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



Now i get this error.....

Query failed: Column count doesn't match value count at row 1

What does it mean and how do I correct this?

I appreciate for everyones time. If I get this working, I'll take you all to the pub :)

[edited by: Atomic_Guy at 7:22 pm (utc) on Nov. 18, 2006]

7:23 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Is this what you have now:

$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VALUE(now(), 1, 'bin_Number', '$component_Value', '$package_type', '$voltage_rating', '$current_rating', '$wattage_rating', '$quantity', '$description')";
mysql_query($query, $connection);

JAG

You're missing the cost value :-)

7:38 pm on Nov 18, 2006 (gmt 0)

5+ Year Member



awsome! got it! Thank you all for your vast input and helping me out.
justageek ....your final touch to it got the data in! lol So cheers mate.....

fancy going to the pub?

7:40 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



yay...Glad you got it!

See you at the pub in 20 minutes ;)

7:43 pm on Nov 18, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



awsome! got it!

It's those few words that define why we're all here :-)

JAG

8:31 am on Nov 19, 2006 (gmt 0)

5+ Year Member



Thanks to you brilliant people I have got most of the main body of the code is working...

I now have something else in the code html form, a value not getting inserted to mysql table.

Array
(
[faiveley_PN] => 100000
[uksupplier_PN] => 10000
[Supplier_Name] => far
[component_Value] => 34K
[package_type] => Axial
[voltage_rating] => 200V
[current_rating] => 10A
[wattage_rating] => 20W
[quantity] => 120
[cost] => 2
[description] => Resistor

[bin_Number] => 8
[Submit] => Submit
)

Query failed: Duplicate entry '0' for key 1

Its throwing out this error above....I empty the table just to test to see what it inserts. It always trying to insert 0 in the bin_Number field. But 8 is selected or if I select any other numbers it still says Duplicate entry? For some reason php is not passing the correct number to the Mysql table from the form select options. The code bellow fetches these numbers from the table and put the value in the option field in the html form. When a number is selected the value is not inserted to the MySQL table. But clearly above the Array have value 8 in the bin_Number variable. What am I doing wrong?

<select name ="bin_Number">
<?php
$connection = mysql_connect($_host, $db_user, $db_password) or die("Unable to connect");
mysql_select_db($db_name, $connection);

$query = "SELECT * FROM bin_Number";
$result = mysql_query($query);
while ($record = mysql_fetch_assoc($result))
{
echo "<OPTION VALUE = '".$record["num"]."'>".$record["num"];
}
?>

[edited by: Atomic_Guy at 8:32 am (utc) on Nov. 19, 2006]

10:00 pm on Nov 20, 2006 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



Your query seems to still be wrong in the other script for bin_number. Try this.

$query = "INSERT INTO resistor(auto_Date, component_Cat, bin_Number, component_Value, package_type, voltage_rating, current_rating, wattage_rating, quantity, cost, description)
VALUE('$date', 1, '".[b]mysql_real_escape_string($_POST['bin_Number'])[/b]."', '$component_Value', '$package_type', '$voltage_rating', '$current_rating', '$wattage_rating', '$quantity', '$description')";

You never were inserting the value of $_POST['bin_Number'], just the text "bin_number". This would cause you to have duplicate entries.

10:48 am on Nov 22, 2006 (gmt 0)

5+ Year Member



Thanks buddy. Got most it working now. Seems like this place is going to be my home for a while :)
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month