CODEWITHSUNDEEP
phpmysqlinsert
Masayuki Tonoki
Masayuki Tonoki

Reputation: 167

Putting several values into one column but different rows in PHP

I have three variables: $Title, $Pubdate and $Link

and in those variables, there are several values like this:

$Title = aa, bb, cc, dd, ee, ff
$Pubdate = aa, bb, cc, dd, ee, ff
$Link = aa, bb, cc, dd, ee, ff

Like this. Then I have a table with the columns (Title, Pubdate and Link). What I have tried:

$sql = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ('$Title', '$Pubdate', '$Link')";
mysql_query($sql,$db_con);

Although no error pops up, it does not perform what I want it to do. It puts all of the values(aa, bb, cc, dd, ee, ff) into one column(Title) and one row(ID = 1), meaning that all of the values are squished into one box, and that is not what I want :(
I want to put it into one column, but in different rows (1,2,3,4,5,6).
I've searched the web for the answer, but everything seems to be answering if they want all the values in one box which is the opposite to what I want.
Perhaps one by one, therefore INSERT INTO ytable (Title)VALUES($Title);, and it puts all of the values in different rows in the one column.

Upvotes: 1

Views: 205

Answers (5)

MaggsWeb
MaggsWeb

Reputation: 3027

You are going to have to split your strings up into an array, so that you can 'loop' through them.

$Title   = "aa, bb, cc, dd, ee, ff";
$Pubdate = "aa, bb, cc, dd, ee, ff";
$Link    = "aa, bb, cc, dd, ee, ff";

$TitleArray   = explode(', ',$Title);
$PubdateArray = explode(', ',$Pubdate);
$LinkArray    = explode(', ',$Link);

//Either: loop through the array and build individual INSERTS
for($i=0;$i<count($TitleArray);$i++){

    // generate an INSERT for each row
    $sql = "INSERT INTO ytable (Title, Pubdate, Link) 
            VALUES ('$TitleArray[$i]', '$PubdateArray[$i]', '$LinkArray[$i]')";

}

//Or you could build a single query string inside the loop, and run it at the end.

Upvotes: 3

Happy Coding
Happy Coding

Reputation: 2525

You can try something like this :

$Title = "aa, bb, cc, dd, ee, ff";
$Pubdate = "aa, bb, cc, dd, ee, ff";
$Link = "aa, bb, cc, dd, ee, ff";
$titleArr = explode(",",$Title);
$pubdateeArr = explode(",",$Pubdate);
$linkArr = explode(",",$Link);

foreach($titleArr as $key => $title){
if(!empty($title) && !empty($pubdateeArr[$key]) && !empty($linkArr[$key]))
    $insertSQL = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ('$title', '$pubdateeArr[$key]', '$linkArr[$key]')";
}

I have tried if not empty because variables cannot have same number of values.

Upvotes: 0

Meenesh Jain
Meenesh Jain

Reputation: 2528

You need something like this,

$Title = "aa, bb, cc, dd, ee, ff";
$Pubdate = "aa, bb, cc, dd, ee, ff";
$Link = "aa, bb, cc, dd, ee, ff";
$titleArr = explode(",",$Title);
$pubdateeArr = explode(",",$Pubdate);
$linkArr = explode(",",$Link);

foreach($titleArr as $key => $title){
    $insertSQL = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ('$title', '$pubdateeArr[$key]', '$linkArr[$key]')";
}

or you can iterate only values 

$a = array();
$insertSQL = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ";

foreach($titleArr as $key => $title){
    $a[] = "('$title', '$pubdateeArr[$key]', '$linkArr[$key]')";
}
$insertSQL .= implode(",",$a);

Upvotes: 0

Hassaan
Hassaan

Reputation: 7662

You need to use explode, implode and foreach loop for it.

$Title   = "aa, bb, cc, dd, ee, ff";
$Pubdate = "aa, bb, cc, dd, ee, ff";
$Link    = "aa, bb, cc, dd, ee, ff";

$Title = explode(', ', $Title);
$Pubdate = explode(', ', $Pubdate);
$Link = explode(', ', $Link);
$sql = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ";
$values= array();
foreach($Title as $key => $val)
{
   $values[] = "('" . $val . "','" . $Pubdate[$key] . "','" . $link[$key] . "')";
}
$sql .= implode(',', $values);

Warning: MYSQL extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

Upvotes: 0

Sougata Bose
Sougata Bose

Reputation: 31739

You can use a loop to generate the query to do so - 

$Title = explode(', ', $Title);
$Pubdate = explode(', ', $Pubdate);
$Link = explode(', ', $Link);
$sql = "INSERT INTO ytable (Title, Pubdate, Link) VALUES ";
$values= array();
foreach($Title as $key => $val) {
   $values[] = "('" . $val . "','" . (isset($Pubdate[$key]) ? $Pubdate[$key] : '')  . "','" . (isset($link[$key]) ? $link[$key] : '') . "')";
}
$sql .= implode(',', $values);

Upvotes: 1

Related Questions