Please help with inserting data into the database (using html form)

[atom_p]

Lets say I have this database:

 

table brand [brandID] [brandName] where brandID -> auto increment and one brand always has the same brandID

table product [brandID] [productID] [price] where productID -> auto increment

 

I want to insert new product into the database using only values [brandName] and [price]

and want brandID and productID to be created automatically

 

 

I use this form:

 

<form id="insertingDataToBrand" action="administratorCode.php" method="post">

  <div>Brand Name: <input type="text" name="brandName"/></div>

  <div>Price: <input type="text" name="price"/></div>

</form>

 

And here is the php code:

 

<?php

//connection to database

include 'connectToDatabase.php';

 

//data retrieveing

$brand = $_POST['brandName'];

$price = $_POST['price'];

 

//As I am inserting to two different tables I use two INSERT statements

$sql = "INSERT INTO brand (brandName) values ('$brand')";

 

mysql_query($sql) or die (mysql_error());

 

//as brandID is created automatically I am going to insert the same value to another table

$last_id = mysql_insert_id ();

 

$sql = "INSERT INTO product (price, brandID) values ('$price', '$last_id')";

 

?>

 

This should work just fine (it doesnt tho) BUT my question is:

 

I have 3 different brands (brand A with brandID 1, brand B with brandID 2, brand C with brandID 3).

When want to insert brand D, automatically created brandID should be automatically set to 4. BUT

when I want to insert product of the same brand, lets say brand A (with different productID) brandID is automatically set to 4(or higher) as well.

 

What do I have to do(use) so it would be able to realise what brandID should be added?

 

Thanks a lot.

 

[jdavidbakr]

So the user is manually typing in the brand and you don't want to insert the new brand if it's there already?  Before you do the insert into brand, do a select brand_id from brand where brand_name = the new name.  If you get a result, use that brand_id for the product insert, otherwise insert into the brand table as you have done.

 

Oh, and don't forget to sanitize your $_POST variables.

[atom_p]

Thanks a lot it gave me an idea but I still cant work it out. You said that:

 

"Before you do the insert into brand, do a select brand_id from brand where brand_name = the new name.  If you get a result, use that brand_id for the product insert, otherwise insert into the brand table as you have done."

 

I have tried and got this so far:

 

$getBrandID = "SELECT brandID FROM brand WHERE brandName = $brand"

$queryresult = mysql_query($getBrandID)

or die(mysql_error());

 

(i dont think thats even right)...

 

Im having problem with that last bit, "use that brand_id for the product insert, otherwise insert into the brand table as you have done." Could you give a hint on how to write the code maybe? Much appreciated. Thanks

[jdavidbakr]

You need to put quotes around $brand but that query looks right.  Now just check the result with mysql_fetch_row() perhaps, and see if you got a brand_id; use it if you did, and insert if you didn't.

[atom_p]

I give up. I was trying to do what you recommended, I have tried to set variable that would get the id that match to selected brand, but it keeps giving me the same ID no matter what brand I select.

 

here is the full code if you want to have a look, maybe you can spot what Im doing wrong, I cant..

 

Form:

 

<form id="insertingDataToBrand" action="administratorCode.php" method="post">

 

<div>

<p>Choose the brand</p>

<select name="selectBrand">

  <option value="Adidas">Adidas</option>

  <option value="Lacoste">Lacoste</option>

  <option value="New Balance">New Balance</option>

          <option value="Nike">Nike</option>

  <option value="Skechers">Skechers</option>

</select>

</div>

 

 

<p>Insert Details</p>

<div>Shoe name: <input type="text" name="shoeName"/></div>

<div>Shoe size: <input type="text" name="shoeSize"/></div>

<div>Colour: <input type="text" name="colour"/></div>

 

<div>

<p>Select gender</p>

        <select name="selectGender">

<option value="Male">Male</option>

<option value="Female">Female</option>

</select>

</div>

 

<div>Description: <input type="text" name="description"/></div>

 

<div><input type="submit" value="Submit"/></div>

</form>

 

PHP:

 

<?php

include 'connectToDatabase.php';

 

$brand = $_POST['selectBrand'];

$sname = $_POST['shoeName'];

$ssize = $_POST['shoeSize'];

$colour = $_POST['colour'];

$gender = $_POST['selectGender'];

$desc = $_POST['description'];

 

echo($brand);

echo($sname);

echo($ssize);

echo($colour);

echo($gender);

echo($desc);

 

 

$getBrandID = "SELECT brandID FROM brand WHERE brandName = 'nike'";

$queryresult = mysql_query($getBrandID)

or die(mysql_error());

 

$id = $queryresult;

echo ($id);

 

?>

[atom_p]

I have just realised the mistake in select statement, it should be like this, however it still doesnt work :

 

$getBrandID = "SELECT brandID FROM brand WHERE brandName = $brand";

  $queryresult = mysql_query($getBrandID)

  or die(mysql_error());

 

  $id = $queryresult;

  echo ($id); 

[jdavidbakr]

$brand needs to be in single quotes

 

$getBrandID = "SELECT brandID FROM brand WHERE brandName = '$brand'";

[atom_p]

thanks a lot!!

 

now I got it working finally:

 

$getBrandID = mysql_query("SELECT brandID FROM brand WHERE brandName = '$brand'");

$row = mysql_fetch_assoc($getBrandID);

 

$detectedID = "$row[brandID]";

echo "$detectedID";