Duplicate entry in SQL Database

[ultraspoon]

Hi everyone, I need a little bit of help finishing off my code. Ive managed to get this far.

 

<?php
mysql_connect("") or die ("Not Connected to MYSQL");
echo "</br>";
mysql_select_db("") or die ("Not Connected to DB");
// Database Connection stuff

$partialNumber = $_POST['partialNumber']; // Post the Partial number
$partialNumber = strtoupper($partialNumber);
$numberSearch = mysql_query("SELECT * FROM product_option_value_description WHERE name LIKE '%$partialNumber%'") or die (mysql_error()); // Query to select the key number

//Query to get product ID //

$productId = "SELECT product_id FROM product_option_value_description";

//Query to get product ID //

while ($keyNumber = mysql_fetch_array($numberSearch)) {
$id = $keyNumber['product_id'];
// Query for the images // 
$query = "SELECT image FROM product WHERE product_id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result) or die(mysql_error());

$query2 = "SELECT product_option_id FROM product_option_value WHERE product_id = '$id'";
$result2 = mysql_query($query2);
$row2 = mysql_fetch_array($result2) or die(mysql_error());

$query3 = "SELECT product_option_value_id FROM product_option_value WHERE product_id = '$id'";
$result3 = mysql_query($query3);
$row3 = mysql_fetch_array($result3) or die(mysql_error());
?>
    <div>
    
    <br /><br />
    Key Number: <? echo $keyNumber['name']; ?></a>
    
    <form action="http://www.co.uk/teststore/index.php?route=checkout/cart" method="post" enctype="multipart/form-data" id="product">
<br />
<table style="width: 100%;">
<tr>
<td>
Colour:
<select name="option[<? echo $row2['product_option_id']; ?>]">
<option value="<? echo $row3['product_option_value_id']; ?>"></option>
</select></td>
</tr>
</table>
<div class="content"> Qty: <input type="text" name="quantity" size="3" value="1" />
    <input type="hidden" name="product_id" value="<? echo $id; ?>" />
    <input name="submit" type="submit" value="Add to Cart" />

</div>
</form>
<? echo $row3['product_option_value_id']; ?>
</div>
<br />
<img height="150" width="150" src='http://www.co.uk/teststore/image/<? echo $row['0']; ?>'/>
<? } 
?>

 

And here is my SQL Table code.

 

product_option_value_id		product_option_id	product_id	
599				               302		               49		
598				               302		               49		
589				               297		               42		
588				               297		               42

 

So as you can probably tell, it is a search program that looks for products on a shopping cart. The products will have different option values, and the php script will grab the option values and echo them in a form to post back to the cart to add the product to the basket.

 

The problem is that the "product_option_value_id" can have lots of different values, but my code echos only the first one it finds. So when I click the add to cart button, it will only add the first option value for the product it finds.

 

For some reason I am having a hard time explaining this, so I hope someone can help me.

 

Thanks for looking.

[Muddy_Funster]

your getting all the info back, your just not looping through the result set when your using the info, your oly using the first entry returned, not all of them, the info is there, you just need some loops to access it properly.

[ultraspoon]

Thanks Muddy_Funster,

 

It has been a long while since I was last coding, im surprised I have managed to throw that together. From what I remember there are a few different kind of loops. Could you point me in the direction of which kind and then I can research into it.

 

Thank you

[Muddy_Funster]

now that I actualy have a close look at the code I see you have one loop in there already, a while loop.  Unfortunately, you have chosen to perform 3 additional queries within this loop - very bad idea!  This will hammer you db server as the table grows. fist up we need to condense all those queries into one:

$sql = <<<SQL_BLOCK
SELECT product_option_value_description.product_id, product_option_value_description.name, product.image, product_option_value.product_option_id, product_option_value.product_option_value_id 
FROM product_option_value_description LEFT JOIN product ON (product_option_value_description.product_id = product.product_id)
LEFT JOIN product_option_value ON (product_option_value_description.product_id = product_order_value.product_id)
WHERE product_option_value_description.name LIKE '%$partialNumber%'
SQL_BLOCK;

should do the trick.

 

next you want to move your output inside the while loop that you have for the data retreval:

$result= mysql_query($sql) or die(mysql_error());
$display='';
while ($row = mysql_fetch_array($result){
$display .= <<<HTML_BLOCK
    <div>
    
    <br /><br />
    Key Number: {$row['name']}</a>
    
    <form action="http://www.co.uk/teststore/index.php?route=checkout/cart" method="post" enctype="multipart/form-data" id="product">
<br />
<table style="width: 100%;">
<tr>
<td>
Colour:
<select name="option {$row['product_option_id']}">
<option value="{$row['product_option_value_id']}"></option>
</select></td>
</tr>
</table>
<div class="content"> Qty: <input type="text" name="quantity" size="3" value="1" />
    <input type="hidden" name="product_id" value="$row['product_id'];" />
    <input name="submit" type="submit" value="Add to Cart" />

</div>
</form>
{$row['product_option_value_id']}
</div>
<br />
<img height="150" width="150" src='http://www.co.uk/teststore/image/{$row['0']}>
HTML_BLOCK;
echo $display

 

that should at least be close enough for you to get the idea.  Another thing, you'll need to get out the habbit of using short open tags (<? ) - they are depreciated.

[ultraspoon]

Ahh brilliant Muddy_Funster, thanks for that, I was acctually looking up how to do a loop inside a loop before you posted you last message, hopefully this will work now, ill post back after I have had a play.

 

Thank you.

[ultraspoon]

Hi Muddy_Funster,

 

I think im close, im trying to figure this out now, and ive got one error,

Parse error: syntax error, unexpected '{' in getKeynumber.php on line 18

 

while ($row = mysql_fetch_array($result){

[dmikester1]

Need a second closing paren after $result.

[ultraspoon]

Thanks for all your help, Muddy_Funster and dmikester1,

 

Ive manged to sort out all the small issues, so the code is now fully working!

 

There is only one small issue now, when you do a search, it shows two of each of the same products, instead of just one. In my test store, I have 2 products each with 2 option values, so it should show 4 products, not 8.

This is the last thing now I promise! Thank you

 

$partialNumber = $_POST['partialNumber']; // Post the Partial number
$partialNumber = strtoupper($partialNumber);
$sql = <<<SQL_BLOCK
SELECT product_option_value_description.product_id, product_option_value_description.name, product.image, product_option_value.product_option_id, product_option_value.product_option_value_id 
FROM product_option_value_description LEFT JOIN product ON (product_option_value_description.product_id = product.product_id)
LEFT JOIN product_option_value ON (product_option_value_description.product_id = product_option_value.product_id)
WHERE product_option_value_description.name LIKE '%$partialNumber%'
SQL_BLOCK;

$result= mysql_query($sql) or die(mysql_error());
$display = '';
while ($row = mysql_fetch_array($result)){
$display .= <<<HTML_BLOCK

<div>
<br /><br />
Key Number: {$row['name']}</a>
<form action="http://www./teststore/index.php?route=checkout/cart" method="post" enctype="multipart/form-data" id="product">
<br />
<table style="width: 100%;">
<tr>
<td>
<select name="option[{$row['product_option_id']}]">
<option value="{$row['product_option_value_id']}"></option>
</select></td>
</tr>
</table>
<div class="content"> 
Qty: <input type="text" name="quantity" size="3" value="1" />
<input type="hidden" name="product_id" value="{$row['product_id']}" />
<input name="submit" type="submit" value="Add to Cart" />
</div>
</form>
</div>
<br />
<img height="150" width="150" src='http://www..co.uk/teststore/image/{$row['image']}'>
HTML_BLOCK;
}
echo $display;
?>

[Muddy_Funster]

try this:

$sql = <<<SQL_BLOCK
SELECT product_option_value_description.product_id, product_option_value_description.name, product.image, product_option_value.product_option_id, product_option_value.product_option_value_id 
FROM product_option_value_description LEFT JOIN product ON (product_option_value_description.product_id = product.product_id)
LEFT JOIN product_option_value ON (product_option_value_description.product_id = product_option_value.product_id)
WHERE product_option_value_description.name LIKE '%$partialNumber%' GROUP BY product_option_value.product_option_id
SQL_BLOCK;

[ultraspoon]

Hmm, that's only displaying 2 products now, not 4.

[ultraspoon]

Fixed it, i put this.

 

$sql = <<<SQL_BLOCK
SELECT product_option_value_description.product_id, product_option_value_description.name, product.image, product_option_value.product_option_id, product_option_value.product_option_value_id 
FROM product_option_value_description LEFT JOIN product ON (product_option_value_description.product_id = product.product_id)
LEFT JOIN product_option_value ON (product_option_value_description.product_id = product_option_value.product_id)
WHERE product_option_value_description.name LIKE '%$partialNumber%' GROUP BY product_option_value_description.name
SQL_BLOCK;


Thanks dude.

[Muddy_Funster]

no worries, glad you got it working