Product Database

iNFERiON

Hi,

I am building a website for my company. I want to make a php page with all products we offer on it. I want to make a form where employees can add new products to the mySQL database. Then I want the product page to list that new product and give it a number to get more info (like products.php?product_id=1) I would like PHP to automatically assign a new product_id to a new product inserted to the database. Now I now this can be done (it is going to be something like adding news in a portal system such as phpNuke, which also gives news their own id number to access them), I just need some help with this, as I am kind of new to SQL/PHP.

Summarized I need help with:

The SQL code to add new products to the database (in table 'products', and into a field which is the right category (I have 1 field for each category)) and the price for it.
The PHP Code to give each added product it's own product_id
The PHP Code to fetch the products from my database and echo them on products.php when no product_id is given. So that would be

if (!$_GET['product_id']){ // Product Overview Product Name, Category (Name of SQL field) and Price
}

Thanks already 🙂

nemonoman

Try this:

http://www.gurulance.com

Somebody in Bangladesh or Bosnia will do all this for about $5 US.

This is a forum for stuck programmers.

helz

when you set up your database table for the products add another field called: id or product_id make it auto-incrementing and make it primary
for adding:

$query = mysql_query("INSERT INTO [table name goes here] ([field1], [field2], [field3]) VALUES ('[field1value]', '[field2value]', '[field3value]') ") or die(mysql_error());

for deleting:

$delete = mysql_query("DELETE FROM [table name goes here] WHERE id='[id goes here]' ") or die(mysql_error());

for editing:

$update = mysql_query("UPDATE [table name goes here] SET field1='[field1value]', field2='[field2value]', field3='[field3value]' WHERE id='[id goes here]' ") or die(mysql_error());

Just replace the [textinhere] with a variable like: $field1 or somthing...

iNFERiON

Thanks, will try that 😃

But when I try to add the id field to my database, I get this and I don't understand what's wrong.

SQL-query :  

ALTER TABLE `producten` ADD `pid` DECIMAL NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST 

MySQL said: 


#1063 - Incorrect column specifier for column 'pid'

iNFERiON

Fixed the above problem. Next problem is in the source code, when I try to add something, it doesn't. My source code so far:

<?
if(!session_id()){
  session_start(); }
if (isset ($_POST['password'])){
$_SESSION['password'] = $_POST['password']; }
if (isset ($_SESSION['password'])){
$password = $_SESSION['password'];
$link = mysql_connect("localhost", "tuningcenter", $password)
   or die("" . session_unset() . "<META HTTP-EQUIV=\"refresh\" content=\"3;URL=admin.php\">");
if (isset ($_GET['action']))
	{
$action = $_GET['action'];
if ($action == 'add'){ echo "<form action=\"admin.php?action=do_add\" method=\"post\" name=\"add\" id=\"add\">
  <p align=\"center\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Type Product<br>
      <select name=\"typeprod\" id=\"typeprod\">
        <option value=\"Velgen\">Velgen</option>
        <option value=\"Spoilers\">Spoiler</option>
        <option value=\"Grills\">Grill</option>
        <option value=\"Blaat\" selected>Nog aanvullen</option>
      </select>
    <br>
    <br>
  Fabrikant<br>
    <input name=\"fab\" type=\"text\" id=\"fab\">
    <br>
    <br>
    Model<br>
    <input name=\"model\" type=\"text\" id=\"model\">
    <br>
    <br>
    Korte omschrijving:<br>
    <textarea name=\"desc\" id=\"desc\"></textarea>
    <br>
    <br>
    <input name=\"Add\" type=\"submit\" id=\"Add\" value=\"Toevoegen\">
</font></p>
</form>";
exit; }
elseif ($action == 'do_add'){ $sqladd = mysql_query("INSERT INTO producten (" . $_POST['typeprod'] . ") VALUES ('" . $_POST['fab'] . "', '" . $_POST['model'] . "', '" . $_POST['desc'] . "')");
echo "<META HTTP-EQUIV=\"refresh\" content=\"3;URL=admin.php\">";
exit; }
elseif ($action == 'edit'){  }
elseif ($action == 'del'){  }
	}
echo "<p><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><strong>TuningCenter Database Administratie</strong></font></p>
<p><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Kies een optie:</font></p>
<ol>
  <li><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><a href=\"admin.php?action=add\">Artikel toevoegen</a></font></li>
  <li><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><a href=\"admin.php?action=edit\">Artikel wijzigen</a></font></li>
  <li><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><a href=\"admin.php?action=del\">Artikel verwijderen</a></font></li>
</ol><br><br><b><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Klaar met de administratie ? <form action=\"admin.php\" method=\"post\" name=\"logout\" id=\"logout\">
  <input name=\"logoff\" type=\"submit\" id=\"logoff\" value=\"Afmelden\"></form> <font color=\"red\">(Echt Afmelden ! Venster sluiten is niet voldoende !)</font></font></b>"; }
else {
?> 
<p><strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Inloggen</font></strong></p>
<form name="form1" method="post" action="admin.php">
  <font size="-1" face="Verdana, Arial, Helvetica, sans-serif">Wachtwoord: </font>  

  <input name="password" type="password" id="password">
  <input type="submit" name="Submit" value="Login">
</form>

<p><font size="2" face="Verdana, Arial, Helvetica, sans-serif"></font></p>
<?
}
if (isset ($_POST['logoff']))
{
if ($_POST['logoff'] == 'Afmelden')
	{
	$password = $_SESSION['password'];
	$link = mysql_connect("localhost", "tuningcenter", $password)
   		or die("" . session_unset() . "<META HTTP-EQUIV=\"refresh\" content=\"3;URL=admin.php\">");
	mysql_close($link);
	session_unset();
	echo "<META HTTP-EQUIV=\"refresh\" content=\"0;URL=admin.php\">";
	}
}
?>

As you can see I havn't implemented edit and del funtions yet, will do that later, first I need some help with adding new stuff to the database. Any ideas here ?

helz

well in your query
you have the typeprod that tells the query which field to add to, but (and this is where the problem is):
You have 1 field and 3 values in the query, they both need to be equal
ie:
(field1, field2, field3) VALUES ('$field1', '$field2', '$field3') == correct

(field1, field2) VALUES ('$field1') == incorrect

at its current state, you add the value of $POST['fab'] into the dbtable field: $POST['typeprod'] but then for the other two values you specified there are no fields for them values to be entered into.

iNFERiON

Thanks once again for the fast and clear answer Helz. Gonna have to figure a way to get it done the way I want to with correct coding this time 😉

iNFERiON

OK, here's the solution I've come up with, though it still refuses to insert data.

I now have 1 table for each product type. Now I want to insert the inputs from the remaining 3 form fields in their own database fields, so I've changed the query to this:

elseif ($action == 'do_add'){ mysql_query("INSERT INTO " . $_POST['typeprod'] . " ('Fabrikant', 'Model', 'Omschrijving') VALUES ('" . $_POST['fab'] . "', '" . $_POST['model'] . "', '" . $_POST['desc'] . "')");
echo "<META HTTP-EQUIV=\"refresh\" content=\"3;URL=admin.php\">";
exit; }

But it still does not insert it. What am I doing wrong here ?

helz

remove the single quotes around the field names in the query
so its just:

"INSERT INTO " . $_POST['typeprod'] . " (Fabrikant, Model, Omschrijving) VALUES (

also make sure that all the variables posted from the form are being sent

iNFERiON

Hmm, still does not insert it. I'll go write a check script inside the file that checks if all the fields are being sent properly.

iNFERiON

OK, I added this piece of code:

echo "Checking inputs...<br><br>";
echo "<b>typeprod: " . $_POST['typeprod'] . "</b><br>";
echo "<b>fab: " . $_POST['fab'] . "</b><br>";
echo "<b>model: " . $_POST['model'] . "</b><br>";
echo "<b>desc: " . $_POST['desc'] . "</b>";

It works all right, it echoes the inserted values from the form, so what can the problem be then .. I'm real confused here 😕

helz

in your elseif statement, use double quotes instead of single ones

elseif ($action == 'do_add'){

becomes:

elseif ($action == "do_add"){

iNFERiON

Still no luck 🙁

helz

Change:

mysql_query("INSERT INTO " . $_POST['typeprod'] . " ('Fabrikant', 'Model', 'Omschrijving') VALUES ('" . $_POST['fab'] . "', '" . $_POST['model'] . "', '" . $_POST['desc'] . "')");

To:

$sql = "INSERT INTO ".$_POST['typeprod']." ('Fabrikant', 'Model', 'Omschrijving') VALUES ('".$_POST['fab']."', '".$_POST['model']."', '".$_POST['desc']."') ");
echo $sql;
mysql_query($sql) or die(mysql_error());

Thats just to check if its an error in the sql
also is typeprod meant to be the table name?

iNFERiON

yes, prodtype is the table name, as chosen in a dropdown menu by the admin. And sad to say it still doesn't add the data. Pretty confusing.

helz

when you echo out $sql after adding the data, does the sql query look ok?
if it redirects too soon, comment out the redirect so you can see if it works or not

iNFERiON

nah, it doesn't echo it, that's the weird thing. So I guess maybe I need to reopen the connection to the database first. I'll try that when I get the time.