php/mysql Rows not updating

[wez87]

Hi,

 

I have a form that posts a variable called 'mycat' to it shelf.

It also carries a variable in the URL called 'merchant'. I am able to echo these variables so they are set okay.

 

I am attempting to use these variable to run a mysql update script but its not working!

 

Here is the mysql bit:

 

if(isset($_POST["mycat"]))
{
$mycat = $_POST["mycat"];
$merchcat =  $_GET['merchant'];

$sql2 = "update products
set `my_cat` = \"$mycat\"
where `merchant_category` = \"$merchcat\" ";
$result2= @mysql_query($sql2, $conn )or die(mysql_error());
}

 

Heres the form that supplies the variables

 

<form action="setcat.php?merchant=<? echo $row['merchant_category']?> " method="post"><?
 echo "<tr>";
    	echo "<td>set products with merchant category of: <B> " .$row['merchant_category']. "</B></td>" ;
	 echo "</tr>";
	  echo "<tr>";
echo "<td>to my category:<input name=\"mycat\" type=\"text\" size=\"3\" maxlength=\"3\" /></td>" ;
 echo "</tr>";
//echo "<input name=\"merchant_cat\" size=\"100\" type=\"hidden\" value=".$row['merchant_category']." />";
 ?> <input type="submit" value="submit" name="submit"> </form> <?
 echo "</table>";

 

The sql script works in phpmyadmin have and its not throwing any errors.... any ideas?

Thanks

[garethhall]

Please change you code to this and post the result

 

if(isset($_POST["mycat"]))
{
$mycat = $_POST["mycat"];
$merchcat =  $_GET['merchant'];

$sql2 = "update products
set `my_cat` = \"$mycat\"
where `merchant_category` = \"$merchcat\" ";
$result2= mysql_query($sql2, $conn )or die(mysql_error());
if($result2){
echo "DB updated";
}else{
echo "Update failed SQL is = ".$sql2;
}
}

[wez87]

Thanks for your help,

Some times I get "DB updated" other times i get "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Double''  at line 3"

 

Its more commonly "DB Updated" but even when it is the database isn't updated.

 

Could this be to do with whats inside the variable?

For the example above the variable contained    Bed Manufacturer/Silentnight Beds/Silentnight Mattresses/4ft 6' Double.

 

Something to do with the / or ' ? Also seems strange that it prints 'Double''  with ' at the start and '' at the end..

[iScript]

First, it's recommended to use UPPERCASE letters for saved words like SELECT, UPDATE, DELETE and more.

Second, do you know to put a dot (.) between variables and strings?

(I don't know how to call it in English, in Hebrew its : לשרשר)

[wez87]

Thanks your right I should have it in uppercase, the . is either called 'full stop' in common usage or in the sense that were using it here its an 'operater' or you might be after the word  'concatenate'

 

The problem looks to be the '  in some of the variables.

tried using addslashes but no joy yet

 

$sql2 = "UPDATE products
SET `my_cat` = \"$mycat\"
WHERE `merchant_category` = ".addslashes($merchcat)." ";
$result2= @mysql_query($sql2, $conn )or die(mysql_error());

[wez87]

ok this returns DB updated for everything now but the database still isnt updated!!

 

$mycat = $_POST["mycat"];	
$merchcat = mysql_real_escape_string ($_GET['merchant']);

$sql2 = "update products
set `my_cat` = \"$mycat\"
where `merchant_category` = \"$merchcat\" ";
$result2= mysql_query($sql2, $conn )or die(mysql_error());
if($result2){
echo "DB updated";
}else{
echo "Update failed SQL is = ".$sql2;
}
}

Heres everything

 

<?php require('includes/header.php'); 


?>


<div id="content_shadow">
    <div id="content">
    
    <?php
if(isset($_POST["mycat"]))
{

$mycat = $_POST["mycat"];	
$merchcat = mysql_real_escape_string ($_GET['merchant']);

$sql2 = "update products
set `my_cat` = \"$mycat\"
where `merchant_category` = \"$merchcat\" ";
$result2= mysql_query($sql2, $conn )or die(mysql_error());
if($result2){
echo "DB updated";
}else{
echo "Update failed SQL is = ".$sql2;
}
}


   $page=$_GET['page'];

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
}
  
$sql = "SELECT COUNT( * ) AS `Rows` , `merchant_category`
FROM `products`
GROUP BY `merchant_category`
ORDER BY `Rows`";  
     $result= @mysql_query($sql, $conn )or die("Could not pull info");
      $num = mysql_numrows($result);

$rows_per_page =1;	 $lastpage   = ceil($num/1);   
 		if ($pageno > $lastpage) {	$pageno = $lastpage;}
 		if ($pageno < 1) {$pageno = 1;} 
    			 $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page; 

			 $sql = $sql . ' ' .$limit;
			 $result= @mysql_query($sql, $conn )or die("Could not pull products");
             $num = mysql_numrows($result);

echo "<table width=\"250\">";
echo "<tr><td align=\"center\" >$title</td></tr>";
echo "<tr><td align=\"center\" >";

if ($pageno == 1) {
   echo " First Prev ";
} else {
   echo " <a class=\"product\" href='{$_SERVER['PHP_SELF']}?pageno=1'> First </a> ";
   $prevpage = $pageno-1;
   echo " <a class=\"product\" href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'> prev </a> ";
}
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
   echo " Next Last ";
} else {
   $nextpage = $pageno+1;
   echo " <a class=\"product\" href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'> Next </a> ";
   echo " <a class=\"product\" href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'> Last </a> ";
}
echo "</td></tr>";
echo "</table>";

$count =0;

echo "<table width =\"610\">"; 
echo $mycat;
 while ($count < $rows_per_page && $count < $num) {
 $row = mysql_fetch_array($result);

?><form action="setcat.php?merchant=<? echo $row['merchant_category']?> " method="post"><?
 echo "<tr>";
    	echo "<td>set products with merchant category of: <B> " .$row['merchant_category']. "</B></td>" ;
	 echo "</tr>";
	  echo "<tr>";
echo "<td>to my category:<input name=\"mycat\" type=\"text\" size=\"3\" maxlength=\"3\" /></td>" ;
 echo "</tr>";
//echo "<input name=\"merchant_cat\" size=\"100\" type=\"hidden\" value=".$row['merchant_category']." />";
 ?> <input type="submit" value="submit" name="submit"> </form> <?
 echo "</table>";
 $count++;

}




?>
    
    
    
    </div>
    </div>

[garethhall]

Ok here is what we will do I too think it has something to with the ' in your variable.

 

Well add a function the clean up the variables and this will also protect you from sql injection.

 

Please add and change code to:

/*** Protect Variables from SQL injection ***/
function cv($value){
// Stripslashes
if (get_magic_quotes_gpc()){
	$value = stripslashes($value);
}
// Quote if not a number
if (!is_numeric($value)){
	$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}

if(isset($_POST["mycat"])){
$sql2 = "UPDATE products SET `my_cat` = ".cv($_POST["mycat"])." WHERE `merchant_category` = ".cv($_GET['merchant']);
$result2= mysql_query($sql2, $conn )or die(mysql_error());
if($result2){
	echo "DB updated";
}else{
	echo "Update failed SQL is = ".$sql2;
}
}

[wez87]

Thanks Again its much appreciated!

Now this code must be ok but theres still a problem.......

 

it seems to update the record if theres only one row that meets the where clause

If theres multiple rows it doesnt update them not even the first row it hits.

It still says 'DB updated though.......?......

[garethhall]

Hmm can you show me the sql file? do a sql export in phpmyadmin or program of you choice.

 

Ohh one more thing update cv($_POST["mycat"]) to cv($_POST['mycat'])

 

DB updated will only show if result2 returned true. Meaning an update has occurred. But again show the sql export so I can see the DB

[wez87]

Heres the first few rows

 

merchant_id 	merchant_name 	aw_product_id 	merchant_product_id 	product_name 	description 	category_id 	category_name 	merchant_category 	aw_deep_link 	aw_image_url 	search_price 	delivery_cost 	merchant_deep_link 	merchant_image_url 	my_cat

962  	Bed Star Ltd  	29188192  	25838  	Star Collection Donnington 5ft Kingsize Wooden Bed...  	A traditional looking bedstead made from solid pin...  	451  	Beds  	[b]Bed category/Pine & Wooden Beds/wooden Beds/Ki... [/b] 	http://www.awin1.com/pclick.php?p=29188192&a=10067...  	http://images.productserve.com/preview/962/2918819...  	635.00  	0.00  	http://www.bedstar.co.uk/shop/customer/product.php...  	http://www.bedstar.co.uk/shop/files/images/thumbs/...  	0

962  	Bed Star Ltd  	29188197  	26096  	Dreamworks Beds Paris 3ft Mattress  	The Paris offers an array of divan and storage opt...  	453  	Mattresses  	[b]Bed category/Mattresses/3FT Mattresses/Dreamworks ...  [/b]	http://www.awin1.com/pclick.php?p=29188197&a=10067...  	http://images.productserve.com/preview/962/2918819...  	125.00  	19.99  	http://www.bedstar.co.uk/shop/customer/product.php...  	http://www.bedstar.co.uk/shop/files/images/thumbs/...  	0

 

Theres 20,000 odd rows but they are all much the same i've bolded the column there where clause uses

not usre if that of use to help?

thanks

 

[garethhall]

Ok I can't see anything wrong there but, I have only one more suggestion. You believe that the sql is not updating all the appropriate records.  The thing is the sql only does as instructed therefor the problem is possibly your understanding of the sql. This sql will do the following:

$sql2 = "UPDATE products SET `my_cat` = ".cv($_POST['mycat'])." WHERE `merchant_category` = ".cv($_GET['merchant']);

It's going to the products table and it's going to change the field my_cat of all the records to your variables value $_POST['mycat'] only where the field merchant_category is absolutely equal to the variable value $_GET['merchant']. So the field merchant_category value must exactly match to the letter the value of  $_GET['merchant'] to be updated.

 

The other thing you can do, to try and find out whats wrong is to do a select and get the DB to return the values of whats doing to be updated. Here is the code for that:

if(isset($_POST["mycat"])){
/*** Show the records thats going to be updated ***/
$rs = mysql_query("SELECT * FROM products WHERE merchant_category = ".cv($_GET['merchant']),$conn);
while($rw = mysql_fetch_assoc($rs)){
	echo "Record to update ".$rw['merchant_name']."<br/>";//I used merchant_name but a better one would be a auto key if the table has one(or any field that is unique).  
}
/*** END ***/
$sql2 = "UPDATE products SET `my_cat` = ".cv($_POST['mycat'])." WHERE `merchant_category` = ".cv($_GET['merchant']);
$result2= mysql_query($sql2, $conn )or die(mysql_error());
if($result2){
	echo "DB updated";
}else{
	echo "Update failed SQL is = ".$sql2;
}
}

Let me know what you find.