SQL errors

[kcmajor36]

Alright I hate to do this but I have tried everything I can remember but thats apparently not good enough. I am having issues with v2 clean install of he script never had problems in the past, but I cant create items, or send messages with it popping up an SQL error saying something like this... "QUERY ERROR: Incorrect integer value: '' for column 'mail_id' at row 1" what is going on?!

[Magictallguy]

Chances are you're trying to insert a string into your INT field.

Please provide the line of code generating this error, along with 5 lines above and 5 lines below (11 lines in total).

[kcmajor36]

I wanna say for this one this is the code that is generating it: Sending mail -

function mail_send()
{
global $db,$ir,$c,$userid,$h;
$subj=str_replace(array("\n"),array("<br />"),strip_tags($_POST['subject']));
$msg=str_replace(array("\n"),array("<br />"),strip_tags($_POST['message']));
if($_POST['user1'] && $_POST['user2'])
{
 die("Please do not select a contact AND enter a username, only do one.<br />
<a href='mailbox.php'>> Back</a>");
}
if(!$_POST['user1'] && !$_POST['user2'])
{
 die("You must select a contact or enter a username.<br />
<a href='mailbox.php'>> Back</a>");
}
$sendto=($_POST['user1']) ? $_POST['user1'] : $_POST['user2'];
$q=$db->query("SELECT userid FROM users WHERE username='{$sendto}'");
if($db->num_rows($q)==0)
{
 die("You cannot send mail to nonexistant users.<br />
<a href='mailbox.php'>> Back</a>");
}
$to=$db->fetch_single($q);
$db->query("INSERT INTO mail VALUES ('',0,$userid,$to,unix_timestamp(),'$subj','$msg')");
$db->query("UPDATE users SET new_mail=new_mail+1 WHERE userid={$to}");
print "Message sent.<br />
<a href='mailbox.php'>> Back</a>";
}

 

and creating items :

function new_item_submit()
{
global $db,$ir,$c,$h;
if($ir['user_level'] > 2)
{
die("403");
}
if(!isset($_POST['itmname']) || !isset($_POST['itmdesc']) || !isset($_POST['itmtype'])  || !isset($_POST['itmbuyprice']) || !isset($_POST['itmsellprice']))
{
print "You missed one or more of the fields. Please go back and try again.<br />
<a href='staff_items.php?action=newitem'>> Back</a>";
$h->endpage();
exit;
}
$itmname=$db->escape($_POST['itmname']);
$itmdesc=$db->escape($_POST['itmdesc']);
$weapon=abs((int) $_POST['weapon']);
$armor=abs((int) $_POST['armor']);
if($_POST['itmbuyable'] == 'on') { $itmbuy=1; } else { $itmbuy=0; }
$efx1=$db->escape(serialize(array("stat" => $_POST['effect1stat'], "dir" => $_POST['effect1dir'], "inc_type" => $_POST['effect1type'], "inc_amount" => abs((int) $_POST['effect1amount']))));
$efx2=$db->escape(serialize(array("stat" => $_POST['effect2stat'], "dir" => $_POST['effect2dir'], "inc_type" => $_POST['effect2type'], "inc_amount" => abs((int) $_POST['effect2amount']))));
$efx3=$db->escape(serialize(array("stat" => $_POST['effect3stat'], "dir" => $_POST['effect3dir'], "inc_type" => $_POST['effect3type'], "inc_amount" => abs((int) $_POST['effect3amount']))));
$m=$db->query("INSERT INTO items VALUES('',{$_POST['itmtype']},'$itmname','$itmdesc',{$_POST['itmbuyprice']},{$_POST['itmsellprice']},$itmbuy, '{$_POST['effect1on']}', '$efx1', '{$_POST['effect2on']}', '$efx2', '{$_POST['effect3on']}', '$efx3', $weapon, $armor)");
print "The {$_POST['itmname']} Item was added to the game.";
stafflog_add("Created item {$_POST['itmname']}");

}

[Magictallguy]

Right, you'll need to restructure your query to include column name declarations.

This is based on stock MC Craps

$db->query('INSERT INTO `items` (`itmtype`, `itmbuyprice`, `itmsellprice`, `itmbuyable`, `weapon`, `armor`, `effect1_on`, `effect2_on`, `effect3_on`, `effect1`, `effect2`, `effect3`, `itmname`, `itmdesc`) VALUES ('.$_POST['itmtype'].', '.$_POST['itmbuyprice'].', '.$_POST['itmsellprice'].', '.$itmbuy.', '.$weapon.', '.$armor.', '.$_POST['effect1on'].', '.$_POST['effect2on'].', '.$_POST['effect3on'].', "'.$efx1.'", "'.$efx2.'", "'.$efx3.'", "'.$itmname.'", "'.$itmdesc.'")');

Edited July 3, 2016 by Magictallguy
Fixed query

[kcmajor36]

Well see thats were im lost because this is the stock v2 mccode and its giving me problems that I have never encountered before. Now with the updated code its saying :QUERY ERROR: Unknown column 'effect1on' in 'field list'

[Magictallguy]

Whoops, sorry, that's my fault.

Previous post updated

[kcmajor36]

Awesome it works now so what will I have to do to get the mail to send?

[Magictallguy]

Do the same - restructure the query to have column name declarations

$db->query('INSERT INTO `mail` (`mail_from`, `mail_to`, `mail_time`, `mail_subject`, `mail_text`) VALUES ('.$userid.', '.$to.', '.time().', "'.$subj.'", "'.$msg.'")');