Form not adding data MySQL Database:more than a couple of characters or using spaces

PHP
1

Hi,

I cannot understand why data is not being correctly added to my database from a form in my admin pages. The problem is if I enter something like:
1
2
y
8

this works fine.

If I enter in the textarea (the last box in the form) something like:
1 3
o p
7 8
988786

(ie. there is a space in between the data entered, or there is more than a couple of characters or numbers.) So it is very restrictive.

this does not work. The error I get is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘10\r
)’ at line 1

It basically seems to say ‘because you have a space in between the entries, or because you are using more than 2 characters in a row when entering data, there is a problem’

WHY!!!???

I am using this form


<form method="post" action="CMS-home.php">

<input type="hidden" name="add" value="true" />

<div>
<label for="productid">ProductID</label>
<input type="text" name="productid" id="productid" />
</div>

<div>
<label for="categoryid">CategoryID</label>
<input type="text" name="categoryid" id="categoryid" />
</div>


<div>
<label for="name">Name</label>
<input type="text" name="name" id="name" />
</div>

<div>
<label for="order">Order</label>
<input type="text" name="order" id="order" />
</div>

<div>
<label for="price">Price</label>
<input type="text" name="price" id="price" />
</div>

<div>
<label for="shortname">Short name</label>
<input type="text" name="shortname" id="shortname" />
</div>

<div>
<label for="extendedname">Extended name</label>
<input type="text" name="extendedname" id="extendedname" />
</div>

<div>
<label for="code">Code</label>
<input type="text" name="code" id="code" />
</div>

<div>
<label for="manucode">Manufacturers code</label>
<input type="text" name="manucode" id="manucode" />
</div>

<div>
<label for="text">Text</label>
<textarea name="text" id="text" rows="20" cols="100"></textarea>
</div>

<input type="submit" name="submit" value="Add Content" />
</form>

This is the code that enters the code into the database


function add_content($p) {
		$productid = mysql_real_escape_string($p['productid']);
		$categoryid = mysql_real_escape_string($p['categoryid']);
		$name = mysql_real_escape_string($p['name']);
		$order = mysql_real_escape_string($p['order']);
		$price = mysql_real_escape_string($p['price']);
		$shortname = mysql_real_escape_string($p['shortname']);
		$extendedname = mysql_real_escape_string($p['extendedname']);
		$code = mysql_real_escape_string($p['code']);
		$manucode = mysql_real_escape_string($p['manucode']);
		$text = mysql_real_escape_string($p['text']);
	
	
	
	
	$sql = "INSERT INTO products (productid, categoryid, name, `order`, price, shortname, extendedname, code, manucode, `text`) VALUES ('$productid', '$categoryid', '$name', '$order', $price, $shortname, $extendedname, $code, $manucode, $text)";
	$res = mysql_query($sql) or die(mysql_error());
	
	}
2

It’s because you haven’t got quotes around all your values in the INSERT, you have quoted some but for some reason then have stopped after ‘$order’.

3

To easily debug a query, just echo it out before running it so you can see the actual query being run.


$sql = 'insert blah blah';

echo $sql;

die();

When you echo out the actual query, the errors are usually obvious.

And you don’t need the back-ticks so you can remove them.

4

that’s right - I have added them and it is working now. Cheers,

Matt.

5 
$p = array('name'=&gt;'this','email'=&gt;'that');  // go on, go on...

$keys = array();
$values = array();
foreach( $p as $k=&gt;$v ){
$keys[]   = "`{$k}`";
$values[] = "'". mysql_real_escape_string($v) ."'"; //**
}
$sql ='insert into mytable ( '. join(',', $keys) . ') values ('. join(',', $values) .');'; 

// echo $sql
// insert into mytable ( `name`,`email`) values ('this','that');

** had to guess that bit, I don’t have mysql_* installed

Better that having to type any new field you may need to add 4 times, imagine the chance of an error…

Code is arrays too :wink: read loops are good!.