what is wrong with this query??

adamhhh

right im passing an id and $actions variable to another page, then trying to insert them into a database upon submit. the problem is the query, ive checked EVERYTHING else including the database. Ive checked the variables are passing over to the new page properly, by printing them out on the new page and thats fine.

i just cannot for the life of me figure out whats wrong with the query!!

<form name="form1" method="post" action="miscellaneous2.php">
<?php

include("donuts.inc");

$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

$result = mysql_query("select * from Products where productType='Miscellaneous'");

while($r=mysql_fetch_array($result))
{
$productID=$r["productID"];
$productName=$r["productName"];
$productType=$r["productType"];
$description=$r["description"];
$productRRP=$r["productRRP"];
$price=$r["price"];
$extraInformation=$r["extraInformation"];
$officialDescription=$r["officialDescription"];
$image=$r["image"];

$f_price = number_format($row['price'],2);
$f_price = $price;
echo "<input type='hidden' name='productID' value='$productID'>";

echo "<table cellspacing='0' border='0' cellpadding='0' width='95%' align='center'>";

echo "<tr>\n";

echo "<td width=\"80%\"><font face=\"Verdana\" color=\"#E5E5E5\" font

size=\"-1\">$productName - $description <b>£$f_price</b></font></td>\n";

echo "<td><a href='../store/listitem.php?id=$productID'
border='0'>
<img src='../store/images/$image' border='0'
width='100' height='80'></a></td>\n";
echo "</tr>\n";

  [B]addBasket(); //contains $actions[/B]


	echo "<tr>";  
  echo "<td colspan=\"2\" td background=\"../store/images/linebreak.gif\">&nbsp;</td>\n";
   echo "</tr>\n";

echo "</table>\n";

}
?>
<br>
<input name="submit" type="submit" id="submit" value="Save changes">
</form>

2nd page --->

<?php

$actions = $POST['actions'];
$productID = $POST['productID'];
?>

<?php
include("donuts.inc");

$connection = mysql_connect($host, $user, $password)
or die ("Can't find database, grr");
$db = mysql_select_db($database, $connection)
or die ("Can't find database, grr");

$actions=trim($actions);

$actions=ucfirst(strtolower(strip_tags($actions)));

$actions = strip_tags(trim($actions));

$query = "INSERT INTO Wishlist (productID,wishlist)
VALUES ('$productID','$actions')";

$result = mysql_query($query)
or die ("Can't find database!!");

	echo "Item displayed below has been added to our database: <br>

<ul>
<font face=\"Verdana\" color=\"#E5E5E5\" font
size=\"-1\"><b><li>item 1:</b> $productID \n </font>

	   <font face=\"Verdana\" color=\"#E5E5E5\" font
       size=\"-1\"><b><li>item 2:</b> $actions </font>";

echo "</ul>";

can someone please help im just completely clueless as to what is going on now!! thanks

adamhhh

just quick little bit of extra info- the addBasket() function is-

<?php
function addBasket()
{
echo "<tr>";

echo "<td colspan=\"2\"><select name=\"actions\" id=\"actions\" style=\"background-color: #333333; color: #E5E5E5\" onChange=\"MM_jumpMenu('parent',this,0)\" \"<?php echo $actions ?>\">
<option selected>Actions</option>
<option>Save for wishlist</option>
<option>Add to basket</option>
</select></td>";

   echo "</tr>\n";

}

the error message i get is that it cant execute the query. for some reason i got it to work once (without realising), i tried undoing but it wouldnt work again :mad:

Nomaad

you need to move your $POST statements down below the connection but before the insert. Run the $action mods before the $POST of ID but after $actions $_POST. Make sense?

You might even try defining each mod differently instead of defining $action= in all
ex. I always do something like
$actions=trim($actions);

$str_actions=ucfirst(strtolower(strip_tags($actions)));

$strip_actions = strip_tags(trim($str_actions));

then put $strip_actions in your VALUE on the query.

adamhhh

this is what you mean right-

$actions = $_POST['actions'];

$actions=trim($actions);
$actions=ucfirst(strtolower(strip_tags($actions)));
$actions = strip_tags(trim($actions));

$productID = $_POST['productID'];

ive tried that and the other thing u suggested, and about a million other things, yet still the query doesnt work! Ive checkedd my database for the 10th-20th time too, still nothing wrong there. any ideas anybody?

khendar

Debug time: Start at what you believe to be the problem and work backwards.

You think its the query. So print out the query before executing it to see what its really trying to do. Likely one or more of the variables in the query are not being set properly. Try inserting the query directly into phpmyadmin to see it it works that way. If the query is fine then its your execution thats the problem. If the query is wrong then the problem is your variables, or perhaps the database.

adamhhh

right,

ive tried printing out the query and it seems to be doing what it should be doing.

ive tried printing out the variables and thats fine. ive also check the database and thats fine. the problem has to be here somewhere though i still dont know why or where-

$actions = $_POST['actions'];

$actions=trim($actions);
$actions=ucfirst(strtolower(strip_tags($actions)));
$actions = strip_tags(trim($actions));

$productID = $_POST['productID'];

$query = "INSERT INTO Wishlist (productID,actions)
VALUES ('$productID','$actions')";
echo $query;
mysql_query($query)
or die ("Can't find database!!");

esukf

I think your problem is with the location of your form tag.

Your select statement:

$result = mysql_query("select * from Products where productType='Miscellaneous'");

might be returning multiple products and when you submit the form, all the items are passed on to the next page in POST yet your insert statement only allows for one item to be added.

Try echo $query on your 2nd page to see what u are get.

If the problem is the above then u need the form tag in the while loop so there is a form for each of the products.

adamhhh

esukf wrote:
Try echo $query on your 2nd page to see what u are get.

done it, that works fine

esukf wrote:
might be returning multiple products and when you submit the form, all the items are passed on to the next page in POST yet your insert statement only allows for one item to be added.

surely that shouldnt make a difference? thing is though, theres only one item on the database so this clearly isnt the problem 🙁

adamhhh

ive tried putting the form inside the while loop, still no luck!!! argghh

esukf

Are you getting any error messages at all?

adamhhh

just

Can't find database!!

as a result of the query.

esukf

Seems there is an error with your insert query.

Try change

mysql_query($query) or die ("Can't find database!!");

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

and then see what you get. (Have php error and warning reporting turned on).

Can you copy your echoed query here so we can take a look?

khendar

Actually - Change :

mysql_query($query) or die ("Can't find database!!");

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

So that it actually prints out an error message.

esukf

oops Khendar's right

adamhhh

whoa interesting... could be a database problem after all-->

Column count doesn't match value count at row 1.

is the error. hmm

adamhhh

whoa! it works now 😛 not quite sure what ive done!

thanks for everyone thats helped! cheers guys 😛

Houdini

Well when you echoed out the mysql_error() and it stated that the column count did not match the value count evidently you corrected that part, for every column you must have a value to place into that column, if you have more values than columns or vice versa then you will get that error and the sql fails.