mySql table not updating

s_ainley87 · March 5, 2008

Hello there,

I have a problem with a php and the sql that goes with, what is meant to happen is that a person with search for a category and if it is not currently exsisting a form will be displayed to add the category to the database, the problem is that the data gets sent away but on the auto id gets put in the table the fields that are sent dont, what i am doing wrong?

<!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>Jetstore | Category Administration</title>



<link href="CSS/sytle.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="wrapper">

  <div id="header"><img src="Images/Jetstore_Banner.gif" width="850" height="116" /></div>
  
  	<div id="navigation">
	<ul class="jetnav"> 
	<li><a href="index.php"><b>Store Front</b></a></li> 
	<li class="current"><a href="categories.php"><b>Category Admin</b></a></li>
	<li><a href="products.php"><b>Product Admin</b></a></li>
	<li><a href="orders.php"><b>Order Admin</b></a></li>
	<li><a href="special.php"><b>Offers Admin</b></a></li>
	<li><a href="user.php"><b>User Admin</b></a></li>
	<li><a href="staff.php"><b>Staff Admin</b></a></li>
	</ul>
</div>

  		<div id="quicklink">
  		 
  		</div>

  <div id="main">
  	<h3>Jetstore Staff Hub</h3> 
<img src="Images/divider.gif" width="500" height="20" /><br/>
<h4>Category Search</h4>

<?php
require_once ('include/mysql_connect.php'); // Connect to the database.
//show list if record if not selected
if ($_POST[op] != "yes")
{ 
echo "<br/>";
$display = "
<form method=\"POST\" action=\"$SERVER[php_SELF]\">
 <strong>Category Name <input type=\"text\" name=\"searchfield\"/></strong><br/>
<input type=\"hidden\" name=\"hiddenname\" value=\"categoryName\"/>
 <input type=\"hidden\" name=\"op\" value=\"yes\" />
 <input type=\"submit\" name=\"submit\" value=\"Search\" />
</form>	";

echo $display;
}
	else
		{
			//  contruct the SQL
			if ($_POST[hiddenname] == "categoryName")
			{
			$sql = "SELECT * FROM category WHERE category_name LIKE '$_POST[searchfield]'";
			}
		}
//  Execute the SQL
$result = mysql_query($sql,$dbc) or die();
while ($newArray = mysql_fetch_array($result))
{
$category_id = $newArray['category_id'];
$category_name = $newArray['category_name'];
$category_desc = $newArray['category_desc'];
}

$record = mysql_num_rows($result);
if ($record == 0)
{
echo "Sorry no matches found";
echo " please add the category to the database <br/><form method=\"POST\" action=\"$SERVER[php_SELF]\">
<div align=\"left\">
<strong>Category Name:</strong><input type=\"text\" name=\"Catname\"/><br/><br/>
<strong>Category Description:</strong><textarea name=\"Catdesc\"/></textarea><br/>
<input type=\"submit\" name=\"submit\" value=\"Add To Database\" />
</div>";

$sql="INSERT INTO category (category_name, category_desc) VALUES ('$_POST[Catname]','$_POST[Catdesc]')"or die();
if (!mysql_query($sql,$dbc))
  	{
  	echo "not added";
  	}
echo "1 record added";
}
	else
	{
	echo "
	<table width=\"303\" border=\"0\">
	 <tr>
		<td width=\"128\"><strong>Category ID</strong></td>
		<td width=\"157\">$category_id</td>
	 </tr>
	 <tr>
		<td><strong>Category Name</strong> </td>
		<td>$category_name</td>
	 </tr>
	 <tr>
		<td colspan=\"2\"><strong>Category Description</strong></td>
	 </tr>
	 <tr>
		<td colspan=\"2\">$category_desc</td>
	</tr>
	</table>";
	}
	?>
	<img src="Images/divider.gif" />
</div>


				<div id="rightside">

			</div>
</div>
</body>
</html>

I hope someone can help as i am really new to PHP.

uniflare · March 5, 2008

i can tell you are very new to php and are just grasping the logical structure of php syntax.

Instead of rewriting your code and showing you stuff you dont understand im going to tell you a few things you should/could remember whilst coding in php.

Procedural Structure

Think about Clear Procedural Structure in PHP, ie; if you have a form that submits in the same file that records the submitted data (like yours), you need to seperate the file into "Form" and "Record", usually using something like:

<?php
if(isset($_POST['input_name'])){ 
// deal with user data 
}else{
// display Form
}
?>

PHP works like this:

1. Browser calls Script (test.php)

2. Then PHP Executes Script

3. Then Apache Sends Result to Browser

----

Therefor stuff like the code below wont function as intended

<?php

echo " please add the category to the database <br/><form method=\"POST\" action=\"$SERVER[php_SELF]\">
<div align=\"left\">
<strong>Category Name:</strong><input type=\"text\" name=\"Catname\"/><br/><br/>
<strong>Category Description:</strong><textarea name=\"Catdesc\"/></textarea><br/>
<input type=\"submit\" name=\"submit\" value=\"Add To Database\" />
</div>";

$sql="INSERT INTO category (category_name, category_desc) VALUES ('$_POST[Catname]','$_POST[Catdesc]')"or die();
if (!mysql_query($sql,$dbc))
  	{
  	echo "not added";
  	}
?>

Catname and Catdesc will always be null as the form cannot be submitted during an execution of a script, as php gets POST variables from the Browser, you need to return the page, then the user fills the form and submits, then the submittion page can handle the submitted data.

Variable Arrays

when using variable arrays, eg "$_POST/$_GET/$_REQUEST etc" arrays never use syntax such as "$_POST[Catname]", the correct syntax is "$_POST['Catname']".

the Or Die() Statement

The "Or Die" statement is best used with a predefined function, you really dont need or die() on the end of a string, eg:

mysql_query($query) or die("Error:".mysql_error());

mysql_query()

Note: mysql_query() does not *need* a mysql_connection resource ($dbc), as without it, the function will use the current open connection.

General MySQL

MySQL Manipulation Queries should be on their own, with the only printable result being the content of the query or the success/failure of a query.

Using Mysql LIKE Statements

Mysql LIKE statements need wildcards to function properly,

this:

WHERE category_name LIKE '$_POST[searchfield]'";

should be:

WHERE category_name LIKE '%$_POST['searchfield']%'";

this is all i can find in your script so far good luck,

hope this helps,

s_ainley87 · March 5, 2008

That has helped loads thankyou very much, i hvae read about isset but didnt realise i needed to use it in this case, let me just check i have got this right,

basically before the form can get submitted they two things that will be getting sent to the table need to be set and cant just be pulled straight from the form as that is how i was taught at university.

uniflare · March 5, 2008

oops 1 moment

uniflare · March 5, 2008

lol accidentally clicked post mid-writing;

ill give you an example:

login.php

<form method="post" action="logmein.php">
Username: <input type="text" name="username" value=""><br />
Password: <input type="password" name="password" value=""><br />
<input type="submit" name="login" value="Login">
</form>

logmein.php

<?php

// IF a Form has been submitted
if(isset($_POST['login'])){
    
    // SET Username and Password
    $username = $_POST['username'];
    $password = md5($_POST['password']);
    
    // Connect to Mysql
    mysql_connect(...);
    
    // Query DB
    $result = mysql_query("SELECT * FROM `table` WHERE `username`='".mysql_escape_string($username)."' AND `password`='$password'");
    
    // Get Result Rows
    $rows = mysql_fetch_array($result);
    
    // IF a Specific Result Row Exists
    if($rows['username'] != null){
        // Logged in
    }
    // IF Specific Result Row Does Not Exist
    else{
        // Login Failed
    }
}
// IF No Form has been Submitted
else{
    Header("Location: login.php");
}

?>

^ A Quick & Simple Example Login Script.

These 2 files would be combined like so:

login.php

<?php

// IF a Form has been submitted
if(isset($_POST['login'])){
    
    // SET Username and Password
    $username = $_POST['username'];
    $password = md5($_POST['password']);
    
    // Connect to Mysql
    mysql_connect(...);
    
    // Query DB
    $result = mysql_query("SELECT * FROM `table` WHERE `username`='".mysql_escape_string($username)."' AND `password`='$password'");
    
    // Get Result Rows
    $rows = mysql_fetch_array($result);
    
    // IF a Specific Result Row Exists
    if($rows['username'] != null){
        // Logged in
    }
    // IF Specific Result Row Does Not Exist
    else{
        // Login Failed
    }
}
// IF No Form has been Submitted
else{
    ?><form method="post" action="logmein.php">
    Username: <input type="text" name="username" value=""><br />
    Password: <input type="password" name="password" value=""><br />
    <input type="submit" name="login" value="Login">
    </form><?php
}

?>

Also, in most cases there are multiple ways of doing a specific procedure, the functions and Structure of your code will reflect your understanding of PHP, isset in this case is just what i use in my script to detect if a variable has been set by that name, even if its value is null, isset also does not report an E_NOTICE [undefined variable '' in ...php ...] (i have E_ALL set as my error_reporting setting in php).

the way i see the logic in your code (correct me if im wrong someone), if the way your using the form is almost like a dos prompt, it looks like you assume echo to echo the contents, pause the script and wait for input, and carry on when the user submits. Is this true?

---

Hope this helps,

s_ainley87 · March 6, 2008

The login logic of the form is the user searches for a category if the search comes back null another form is echoed so the category can be added to the database, i now have a problem with a field in another bit of code, i am trying to send a price to a table e.g 389.99 but the table is only showing 99.99 i ahve the data type set to DECIMAL with a length of 3,2. This is not a problem with my code is it?

uniflare · March 6, 2008

echo the query before you insert it, then tell us the query that gets sent to mysql.

could be something to do with mysql table setup.

s_ainley87 · March 6, 2008

INSERT INTO product (product_name, category_name, product_price, product_desc, image_name) VALUES ('asdasd','asdsadd', '389.99','asdasdasd','moi.jpg')

this is the query that is getting sent.

uniflare · March 6, 2008

looks like you configured your table incorrectly... do you have phpmyadmin?

change the field type to float

s_ainley87 · March 6, 2008

Changed the field type to float the same thing is still happening ???

uniflare · March 6, 2008

haveyou tried running the query through phpmyadmin?

the query looks fine, so it must be something to do with your db setup.

Are you limiting the float? if you are then free it.

other than that i'm not sure what could be the problem i have not experienced this before sorry.

hope this helps,

s_ainley87 · March 10, 2008

I solved it i had my decimal set to 3,2 which apparently means the bigest value can be 9.99 so i just increased the decimal and now it works :-)

Sign In

mySql table not updating

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

Join the conversation

Important Information