phpmysql inserts blank instead of value

0
votes

I have the following function:

function insert($database, $table, $data_array)
{
# Connect to MySQL server and select database
$mysql_connect = connect_to_database();
mysql_select_db ($database, $mysql_connect);

# Create column and data values for SQL command
foreach ($data_array as $key => $value) 
    {
    $tmp_col[] = $key;
    $tmp_dat[] = "'$value'";
    }
 $columns = join(",", $tmp_col);
 $data = join(",", $tmp_dat);

# Create and execute SQL command
$sql = "INSERT INTO ".$table."(".$columns.")VALUES(". $data.");";
$result = mysql_query($sql, $mysql_connect);

# Report SQL error, if one occured, otherwise return result
if(mysql_error($mysql_connect))
    {
    echo "MySQL Update Error: ".mysql_error($mysql_connect);
    $result = "";
    }
else
    {
    return $result;
    }
}

The values in php are the following:

$content_table = "p_content";
$insert_array['title'] = $title;
    $insert_array['content'] = $content;
$insert_array['url'] = $get_source;
$insert_array['video'] = $video;
$insert_array['date'] = $date;
insert(DATABASE, $content_table, $insert_array);

The result of all this adds a row with id (key, autoimcrement), url, and date. Title, content and video are blank. If I echo title I get the correct result, if i var_dump the title I get string(15)"blablablabla", again correct.

Now if I hand set the $title = "asdf"; it is getting inserted correctly. Same goes for content and video.

table structure

id int(8) unsigned NO PRI NULL auto_increment

title varchar(1000) YES NULL

content longtext YES NULL

video varchar(3000) YES NULL

url varchar(300) YES NULL

date date YES NULL

2
phpmysqlinsert
What is the output of $insert_array? Try using var_dump or print_r to see the format and ensure proper SQL format. - djthoms
Array ( [title] => "asjgalkjhgfdkjas". [content] => lqejrhgojiehrgakehlgkj [url] => xxxxxxxxxxxxxxxxx.html [video] => [date] => 2013-02-17 00:45:46 ) This is how the print_r looks like and I have tried putting the insert query directly without the function but same result - Alexk
this is part of a library, anyway, the insert works great if I hand code the values of every variable - Alexk
Oke, at quotes $title = ´(strip_tags($title))´; - Mr. Radical
Ok, as @Mr.Radical says, we need to exact examples of what you're trying to insert. Are there NULL values? Your fields allow NULL values but you're wrapping your values in quotes ''. - Gerard Roche

2 Answers

0
votes

Try adding quotes to your variables. :-) The reason is that you MYSQL column types are set as VARCHAR. And inserting data requires that you surround your inserts with quotes.

B.T.W. if this is new code I would recommend to switch to the MYSQLI or PDO library.

0
votes

Try:

function insert($database, $table, $data_array)
{
    # Connect to MySQL server and select database
    $mysql_connect = connect_to_database();
    mysql_select_db($database, $mysql_connect);

    $cols = array();
    $vals = array();
    foreach ($data_array as $key => $value) {
        $cols[] = "`" . $key . "`";
        if (is_int($value) || is_float($value)) {
            $vals[] = $value;
        } else {
            $vals[] = "'" . mysql_real_escape_string($value) . "'";
        }
    }

    $sql = "INSERT INTO " . $table
         . ' (' . implode(', ', $cols) . ') '
         . 'VALUES (' . implode(', ', $vals) . ')';

    $result = mysql_query($sql, $mysql_connect);

    # Report SQL error, if one occured, otherwise return result
    if(mysql_error($mysql_connect)) {
        echo "MySQL Update Error: " . mysql_error($mysql_connect);
        $result = ""; // FIXME should probably return false here
    } else {
        return $result;
    }
}

IMPORTANT

Your code and the above is potentially vulnerable to SQL Injections. Go read about them.