INSERT INTO MySQL database with PHP :(

Hi guys i have been trying for days now to insert to variables into a table which are “$member_id” and “$product_idx” now for some reason if i echo them out before clicking the add button it displays the correct numbers but when i click the add button it seems to add 2, 0 into the database any help here is my code below:

i will be really grateful if you could help me as i have been struggling for days and its doing my head in i have a deadline on the project :frowning:

[php]
session_start();
$member_id=$_SESSION[‘SESS_MEMBER_ID’];
include_once(‘connections/db101.php’);
$link_id = db_connect();
if(isset($busID)){
$productquery = “SELECT product_idx, business_id, product_name, image, description, price FROM products WHERE business_id=$busID AND active = 1”;
} else {
echo “Sorry This Shop has no products”;
}
$productresult = mysql_query($productquery, $link_id);
//Loop through products
while($productrow = mysql_fetch_array($productresult, MYSQL_ASSOC)){

		$product_idx = $productrow['product_idx'];
		$product_name = $productrow['product_name'];
		$product_image = $productrow['image'];
		$product_desc = $productrow['description'];
		$product_price = $productrow['price'];
		
		//Write Products
		echo "<div class=\"productHold\">";
		echo "<div class=\"productName\">".$product_name."</div>";
		echo "<div class=\"productImage\">".$product_image."</div>";
		echo "<div class=\"productDesc\">".$product_desc."</div>";
		echo "<div class=\"productPrice\">&pound;".$product_price."</div>";
		?>
        <form method="post" name="addtobudget" action="<?php echo $_PHP_SELF; ?>" />
        <input type="submit" name="addtobudget" value="Add" id="<?php echo $product_idx; ?>" />
        </form>
		<?php 
        echo "</div>";

	}
	?>
                            <?php

require_once(‘update.php’);

if(isset($_POST[‘addtobudget’]))
{
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die(‘Could not connect: ’ . mysql_error());
}
$sql = "INSERT INTO clientSelection (client_id,product_id) VALUES (’$member_id’,’$product_idx’)";
mysql_select_db(‘weddingonthego’);
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ’ . mysql_error());
}
echo “Updated data successfully\n”;
mysql_close($conn);
}
?>[/php]

ok so what it seems to me is that you have a few different products going on the page, you are never POSTing that product id in the form!! so you are not telling database which actual productidx to insert, also this is sooo insecure!!! you taking information from a form and shoving it straight to the database without validating it, prepare to be hacked!!! and being that this is a store, that is not too good.

Let me know if this is any good for ya :wink:

[php]

<? session_start(); $member_id=$_SESSION['SESS_MEMBER_ID']; include_once('connections/db101.php'); $link_id = db_connect(); if(isset($busID)){ $productquery = "SELECT product_idx, business_id, product_name, image, description, price FROM products WHERE business_id=$busID AND active = 1"; } else { echo "Sorry This Shop has no products"; } $productresult = mysql_query($productquery, $link_id); //Loop through products while($productrow = mysql_fetch_assoc($productresult)){ $product_idx = $productrow['product_idx']; $product_name = $productrow['product_name']; $product_image = $productrow['image']; $product_desc = $productrow['description']; $product_price = $productrow['price']; //Write Products echo "
"; echo "
".$product_name."
"; echo "
".$product_image."
"; echo "
".$product_desc."
"; echo "
£".$product_price."
"; <?php echo "
"; } ?> <?php require_once('update.php'); if(isset($_POST['addtobudget'])) { $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $prodidx=mysql_real_escape_string($_POST['idx']); $sql = "INSERT INTO clientSelection (client_id,product_id) VALUES ('$member_id','$prodidx')"; mysql_select_db('weddingonthego'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; mysql_close($conn); } ?>

[/php]

Ye it works! :slight_smile: cheers thanks for that i have been looking at it for hours thinking THE FUDGET!? haha, cheers its been a massive help there :slight_smile:

and i took the mysql_real_escape_string out to test if that was the problem, is just adding that make it secure? and its not a shop its likes a virtual budget planner thingy majiggy haha, is there anyway to make it check if the user has already entered the product into the table, it would require to be checking the member and the product because obivously they can have many products but i want them to only have one of one product.

then all i need to do is make it deduct from the budget

well just adding the mysql_real_escape_string doesnt make your script 100% secure. It all depends on what you are trying to validate, in deciding how to validate it. For example, if you have a forum you are scripting, you have messages from users being shown to other users, so where ever the users are inputing that text you are first gonna use mysql_real_escape_string, then you are also gonna want to protect against xss attacks, so you would use something strip_tags, some people will use regular expressions etc.
If you are only going to allow the user to insert numbers then you can use is_numeric, or is_int.
There is more things in php which you can use such as ctype, to validate. But whatever you do never get lazy in programming, and make sure if you have input from another user, VALIDATE it!!!

Here is an idea for you:
[php]
$result = mysql_query(“SELECT product_id FROM clientSelection”, $link_id);
$num_rows = mysql_num_rows($result);
if($num_rows){
die(“Product already exists”);
}
[/php]

so i should replace my result query with that below which will enable just the inputting of numbers.

Erm obviously multiple users will be able to select the same thing but each individual user will have a unique member id so how could i make it that the user gets prompted so it says you already have “Blah Blah Item” would you like to overwrite it,

basically i have a virtual budget and when users add products it deducts from the budget etc

Sorry I was hastily writing this morning :smiley:
Let me know if this looks right for you?

[php]<?
session_start();
$member_id=$_SESSION[‘SESS_MEMBER_ID’];
include_once(‘connections/db101.php’);
$link_id = db_connect();

if(isset($busID)){
$productquery = “SELECT product_idx, business_id, product_name, image, description, price FROM products WHERE business_id=$busID AND active = 1”;
} else {
echo “Sorry This Shop has no products”;
}
$productresult = mysql_query($productquery, $link_id);
//Loop through products

while($productrow = mysql_fetch_assoc($productresult)){
$product_idx = $productrow[‘product_idx’];
$product_name = $productrow[‘product_name’];
$product_image = $productrow[‘image’];
$product_desc = $productrow[‘description’];
$product_price = $productrow[‘price’];
//Write Products

echo “<div class=“productHold”>”;
echo “<div class=“productName”>”.$product_name."";
echo “<div class=“productImage”>”.$product_image."";
echo “<div class=“productDesc”>”.$product_desc."";
echo “<div class=“productPrice”>£”.$product_price."";

<?php echo ""; } ?> <?php require_once('update.php'); if(isset($_POST['addtobudget'])) { $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $result = mysql_query("SELECT product_id FROM clientSelection WHERE client_id=$member_id", $link_id); $num_rows = mysql_num_rows($result); if($num_rows){ die("Product already exists"); } $prodidx=mysql_real_escape_string($_POST['idx']); $sql = "INSERT INTO clientSelection (client_id,product_id) VALUES ('$member_id','$prodidx')"; mysql_select_db('weddingonthego'); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; mysql_close($conn); } ?>[/php]

Cheers! that helped alot :slight_smile: im getting far now :slight_smile:

is there some way of contacting you off this forum possibly?

explain things a little more

where would i make it to deduct the price from the budget

I would prefer to assist you here in the forums, this way you might be able to recieve help from others that are skilled at php also. This way if I am busy at the moment you can still recieve help and what you have posted others will read, and it might help someone else out also!!!

Do you already have some code worked out deducting the price from the budget?
Please explain what your budget is

Ok that is no problem, i have not got the code for the deduction of the budget i couldn’t figure it out. :confused: haha basically i want it to get the price of the product and deduct it from the budget, the table for the members budget is:

members:
member_id
firstname
lastname
login
passwd
email
budget
selections ( Not 100% sure this is needed )

As when a client add’s a product they add it to another database table called clientSelection
clientSelection:
client_id
product_id

then i need to get the selection to display on another page if they click for example my cart :confused:

once ive solved them two problems it should be done :confused:

Any Help :confused:

Sponsor our Newsletter | Privacy Policy | Terms of Service