...

View Full Version : id column causing problems...



Rithotyn
02-28-2010, 11:02 PM
Hi, i'm trying to make a simple content management system for my girlfriends jewellery website. I've managed to get the guts of it working via a tutorial off about.com, but i'm running into problems attempting to add a primary key id column to the SQL table.

While I can add the column with the primary key, auto increment and not null as I wish and it auto numbers the entries I already have in the database, it seems to stop any further uploads from working, despite being told they have succesful.

The upload page, which works fine until the id column has been manually added to the MySQL database.

http://www.riccyd.co.uk/sally/index.php


<!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=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<form enctype="multipart/form-data" action="add.php" method="POST">
<table width="600" border="0">
<tr>
<td colspan="2"><div align="center"><strong>Create a new item listing</strong></div></td>
</tr>
<tr>
<td width="180">Item Title</td>
<td width="410"><input name="name" type="text" size="53" /></td>
</tr>
<tr>
<td>Item Description</td>
<td><textarea name="email" id="textarea" cols="50" rows="5"></textarea></td>
</tr>
<tr>
<td>Item Category</td>
<td><select name="phone" id="select">
<option value="Necklace">Necklace</option>
<option value="Ring">Ring</option>
<option value="Earrings">Earrings</option>
<option value="Brooch">Brooch</option>
</select></td>
</tr>
<tr>
<td>Upload Image</td>
<td><input name="photo" type="file" size="41" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>
<div align="left">
<input type="submit" value="Add" />
</div></td>
</tr>
</table></form>
<hr />
<h3>Product Information</h3>
<a href="view.php">View Files</a>
<?php
// Connects to your Database
$db=mysql_connect("localhost", "riccyd_co_uk", "*******"); mysql_select_db("riccyd_co_uk", $db);

if (!$db) {
exit ('Cannot Connect to MySQL Server, please notify webmaster');
} else {
echo '';
}

$result = mysql_query("SELECT * FROM jewellery", $db);
$num_rows = mysql_num_rows($result);

echo '<p>You have a total of <b>' . "$num_rows" . ' </b>items on your website</p>';

?>

<table width="200" border="1">
<tr>
<td><strong>Category</strong></td>
<td><div align="center"><strong>Number of Items</strong></div></td>
</tr>
<tr>
<td>Brooch</td>
<td><?php

$result = mysql_query("SELECT * FROM jewellery WHERE phone='Brooch'", $db);
$num_rows = mysql_num_rows($result);

echo '<center>' . "$num_rows" . '</center>';

?>
<div align="center"></div> </tr>
<tr>
<td>Necklace</td>
<td><?php

$result = mysql_query("SELECT * FROM jewellery WHERE phone='Necklace'", $db);
$num_rows = mysql_num_rows($result);

echo '<center>' . "$num_rows" . '</center>';

?></td>
</tr>
<tr>
<td>Ring</td>
<td><?php

$result = mysql_query("SELECT * FROM jewellery WHERE phone='Ring'", $db);
$num_rows = mysql_num_rows($result);

echo '<center>' . "$num_rows" . '</center>';

?></td>
</tr>
<tr>
<td>Earring</td>
<td><?php

$result = mysql_query("SELECT * FROM jewellery WHERE phone='Earrings'", $db);
$num_rows = mysql_num_rows($result);

echo '<center>' . "$num_rows" . '</center>';

?></td>
</tr>
</table>

</body>
</html>

The page that does the work (note i know the variable names aren't very appropriate, but changing them caused me further headaches)

http://www.riccyd.co.uk/sally/add.php

<!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=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<?php

//This is the directory where images will be saved
$target = "images/";
$target = $target . basename( $_FILES['photo']['name']);

//This gets all the other information from the form
$name=$_POST['name'];
$email=$_POST['email'];
$phone=$_POST['phone'];
$pic=($_FILES['photo']['name']);

// Connects to your Database
$db=mysql_connect("localhost", "riccyd_co_uk", "********"); mysql_select_db("riccyd_co_uk", $db);

if (!$db) {
exit ('Cannot Connect to MySQL Server, please notify webmaster');
} else {
echo 'Connection success<br />';
}

//Writes the information to the database
mysql_query("INSERT INTO `jewellery` VALUES ('$name', '$email', '$phone', '$pic')") ;

//Writes the photo to the server
if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
{

//Tells you if its all ok
echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded, and your information has been added to the directory";
}
else {

//Gives and error if its not
echo "Sorry, there was a problem uploading your file.";
}
?> <br />
View your file <a href="view.php">here</a>. Add another listing <a href="index.php">here</a>.
</body>
</html>


And the page for viewing the results
http://www.riccyd.co.uk/sally/view.php


<!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=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<table>
<?php
// Connects to your Database
$db=mysql_connect("localhost", "riccyd_co_uk", "*******"); mysql_select_db("riccyd_co_uk", $db);

if (!$db) {
exit ('Cannot Connect to MySQL Server, please notify webmaster');
} else {
echo '';
}

//Retrieves data from MySQL
$data = mysql_query("SELECT * FROM jewellery") or die(mysql_error());

//Puts it into an array
while($info = mysql_fetch_array( $data ))
{

//Outputs the image and other data
Echo "<tr><td><img width=300 height=300 src=http://www.riccyd.co.uk/sally/images/".$info['photo'] ."> <br /><br />";
Echo "<td><b>Item Title: </b> ".$info['name'] . "<br /> ";
Echo "<td><b>Item Description: </b> ".$info['email'] . "<br />";
Echo "<td><b>Item Category: </b>".$info['phone'] . "<br />";
}
?>
</tr></table>
</body>
</html>



The MySQL interfaces...
http://www.riccyd.co.uk/sally/images/screenshot1.gif
http://www.riccyd.co.uk/sally/images/screenshot2.gif

I don't know what the problem with adding this column is, as it isnt referenced anywhere within the PHP, I didn't think it would affect anything but obviously im wrong....

Any help would be appreciated, feel free to submit anything in an attempt to work it out.

Thanks
Richard

masterofollies
02-28-2010, 11:32 PM
From a quick look over, seems this is your problem, the insert query, you aren't even inserting an id at all. Try this


mysql_query("INSERT INTO `jewellery` VALUES ('$name', '$email', '$phone', '$pic', '')") ;

Or even better would be this


$query = "INSERT INTO `{{table}}` (`id`, `name`, `email`, `phone`, `pic`) VALUES('', '{$_POST['name']}', '{$_POST['email']}', {$_POST['phone']}, {$_POST['pic']})";

Customize it to how you need it, but also it's a good idea to have ID as the very first field in your table.

ffmast
03-01-2010, 07:07 AM
Skip the "id" field in the insert query, as it is auto-incremented.
But do add the field names in it, like masterofollies suggested:

INSERT INTO `{{table}}` (`name`, `email`, `phone`, `pic`) VALUES(...)
PS. And. err.. do some input validation here:


$name=$_POST['name'];
$email=$_POST['email'];
$phone=$_POST['phone'];

(like, check for
' chars)

rangana
03-01-2010, 07:30 AM
To add ffmast's suggestion, you can use mysql_real_escape_string (http://php.net/manual/en/function.mysql-real-escape-string.php) for this task.

You might also find these links useful:
http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php
http://php.net/manual/en/security.database.sql-injection.php
http://www.metatitan.com/php/16/protecting-your-phpmysql-queries-from-sql-injection.html

Hope this helps.

Rithotyn
03-01-2010, 01:04 PM
I hadnt put anything in about the ID because it was auto incremented so i thought it basiclly dealt with it itself.

Is it because I'm not naming the fields that its having issues when it comes to write the data into the MySQL database?

Thanks for your help folks.

Ric

masterofollies
03-01-2010, 05:31 PM
Yeah I just said it needs to be customized, just giving examples.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum