MySQL / php INSERT Statement Showing Unpredictable Results w/ No Insert

All the data is being passed as I have sufficiently tested over and over to ensure it is complete. The problem I am having is with my INSERT statement:

<?php
$db_name = "db";
$connection = mysql_connect('localhost','root','') or die(mysql_error());
$db = mysql_select_db($db_name,$connection) or die(mysql_error());
$sql = "INSERT INTO badges        (id,company_name,company_address,company_city,company_state,company_zip, badge_name,   badge_title) VALUES ";
for($i = 0; $i < count($_POST['badge_name']); $i++) {
$sql = "(
$_SESSION[company_name],
$_SESSION[company_address],
$_SESSION[company_city],    
$_SESSION[company_state],
$_SESSION[company_zip],
".$_POST['badge_name'][$i].",
".$_POST['badge_title'][$i].")";}
$result = mysql_query($sql) or die(mysql_error());
echo print_r($sql);
?>

And my $sql print_r() is:

You have an error in your SQL syntax; check the manual t开发者_StackOverflow社区hat corresponds to your MySQL server version for the right syntax to use near 'Test Company Name, 1224 Adams Ave, Portland,  Oregon, 97128, Bill Smith, W' at line 2

No data is being inserted in my table. I have verified and tested user/pass etc. All working OK.

For good measure, here is my array output showing ALL data is passed as expected.

Mike Jones

Owner

Test Company Name

1224 Adams Ave

Portland

Oregon

97128

Bill Smith

Worker

Test Company Name

1224 Adams Ave

Portland

Oregon

97128

I think I am missing something stupid simple here, but just been on this for 4-5 hours starting to go nuts! Thanks for any insight as to my ignorance!

Here is my table dump for any reference:

CREATE TABLE IF NOT EXISTS `badges` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_name` varchar(255) NOT NULL,
`company_address` varchar(255) NOT NULL,
`company_city` varchar(100) NOT NULL,
`company_state` varchar(25) NOT NULL,
`company_zip` varchar(12) NOT NULL,
`badge_name` varchar(100) NOT NULL,
`badge_title` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

this will work, note the sql concat , for multiple inserts

    <?php 
function cleanit($input){
    return mysql_real_escape_string(preg_replace('/[^a-zA-Z0-9\?.,_ -]/s', '', $input));
}
        $db_name = "db";
        $connection = mysql_connect('localhost','root','') or die(mysql_error());
        $db = mysql_select_db($db_name,$connection) or die(mysql_error());
        $sql = "INSERT INTO badges (id,company_name,company_address,company_city,company_state,company_zip, badge_name, badge_title) VALUES ";
        for($i = 0; $i <= count($_POST['badge_name']); $i++) {


        $sql .= '("","'.cleanit($_SESSION['company_name']).'",
        "'.cleanit($_SESSION['company_address']).'",
        "'.cleanit($_SESSION['company_city']).'",    
        "'.cleanit($_SESSION['company_state']).'",
        "'.cleanit($_SESSION['company_zip']).'",
        "'.cleanit($_POST['badge_name'][$i]).'",
        "'.cleanit($_POST['badge_title'][$i]).'")';
        if($i<count($_POST['badge_name'])){$sql .=',';}
     }
        $result = mysql_query($sql) or die(mysql_error());
        echo print_r($sql);
        ?>

I think your problem is not enclosing your strings upon insert

$_SESSION[company_name] 

to

'$_SESSION[company_name]'

same goes for others, your string is not treated as a string but an sql sort of token

your error was that you do not concatenate sql query properly and try to use single quotation around value that you are going to insert in database. it may also raise error.

enter code here`

<?
$db_name = "test";
$connection = mysql_connect('localhost','root','') or die(mysql_error());
$db = mysql_select_db($db_name,$connection) or die(mysql_error());
$sql = "INSERT INTO badges        (company_name,company_address,company_city,company_state,company_zip, badge_name,   badge_title) VALUES ";

for($i = 0; $i < count($_POST['badge_name']); $i++) 
{
  $sql .= "('". $_SESSION['company_name'] . "','"
            . $_SESSION['company_address'] . "','"
            . $_SESSION['company_city'] . "','"
            . $_SESSION['company_state'] . "','"
            . $_SESSION['company_zip'] . "','"
            . $_POST['badge_name'][$i] . "','"
            . $_POST['badge_title'][$i] ."')";

  if($i<count($_POST['badge_name'])-1){$sql .=',';} // if your array count is more than one than add , to make query compatible with mysql sytax  
}
echo $sql;
$result = mysql_query($sql) or die(mysql_error());

?>`

Working code:

for($i = 0; $i < count($_POST['badge_name']); $i++) 
{
$sql .= "('"
        . $_SESSION['company_name'] . "','"
        . $_SESSION['company_address'] . "','"
        . $_SESSION['company_city'] . "','"
        . $_SESSION['company_state'] . "','"
        . $_SESSION['company_zip'] . "','"
        . $_POST['badge_name'][$i] . "','"
        . $_POST['badge_title'][$i] ."')";

if($i<count($_POST['badge_name'])-1){$sql .=',';}  
}

First off, since this is an insertion, we'll remove id, also, you are overwriting $sql in the for loop ($sql = instead of $sql .=) and the session keys are malformed and non-integer inputs should be quoted. I assumed that zip is stored as an integer, if not add change it to '$_SESSION['company_zip']':

$sql = "INSERT INTO badges (company_name,company_address,company_city,company_state,company_zip,badge_name,badge_title) VALUES ";
for($i = 0; $i < count($_POST['badge_name']); $i++) 
{
     $sql .= "('$_SESSION['company_name']','$_SESSION['company_address']','$_SESSION['company_city']','$_SESSION['company_state']',$_SESSION['company_zip'],'$_POST['badge_name'][$i]','$_POST['badge_title'][$i]')";
}
$result = mysql_query($sql) or die(mysql_error());
echo print_r($sql);
?>