How to make an array insert for this table

Question

I need your help, I have this table Fiddle ( the real table has another 4 providers columns, six in total) and I need some array for insert into a DB, I been trying with this:

    $rowb = array();
    $i = 1;
    while (isset($_POST["prov_name$i"])) {
        $rowb[] = array($_POST["prov_name$i"],$_POST["unitval$i"], $_POST["totval$i"]);
        $i++;
    }

    foreach ($rowb as $row) {
        $query = 'INSERT INTO provprices (CA_id, prov_name, unitval , totval)
                            VALUES ("'.$CA_id.'","'.$row[0].'","'.$row[1].'","'.$row[2].'")';
        mysql_query($query) or die(mysql_error());
    }

But i think, need another increment letter next to $i, hope you can understand me. Thanks!

if you think you need another increment letter - use another letter, what is your question? — Iłya Bursov, Jul 11 '14 at 22:31
What's not working? Are you getting an error message? Is it a PHP error or a MySQL error? Is nothing being inserted? Is something incorrect being inserted? Check out [this](http://stackoverflow.com/help/how-to-ask) and [this](http://stackoverflow.com/help/mcve). — Kryten, Jul 11 '14 at 22:35
Don't use `mysql_` functions. See the red (or pink) box in the docs that says its going away in a future release? That means your code will break. — developerwjk, Jul 11 '14 at 22:38
I would suggest naming the fields on your form differently as an array. So like `prov_name[1], unitval[1]...etc`. Then when you loop over, you can just `foreach($_POST['prov_name'] as $k=>$v)...` and get the corresponding keys in the `$_POST['unitval'][$k]` and other arrays. — Jonathan Kuhn, Jul 11 '14 at 22:41
Your code makes your website vulnerable to [SQL-injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Max, Jul 11 '14 at 22:50
You should change the way you name the `unitval` and `totalval` inputs, you need a delimiter between the provider and the row number. Otherwise, if you have `unitval121`, you can't tell if that's provider 1 row 21 or provider 12 row 1. — Barmar, Jul 12 '14 at 00:35

Barmar · Accepted Answer · 2014-07-12T01:59:34.443

1

Your data is 2-dimensional -- for each prov_name$i you have a series of $unitval$i$j and totalval$i$j. So you need nested loops:

$i = 1;
while (isset($_POST["prov_name$i"])) {
    $prov_name = mysql_real_escape_string($_POST["prov_name$i"]);
    $j = 1;
    while (isset($_POST["unitval$i$j"])) {
        $unitval = intval($_POST["unitval$i$j"]);
        $totalval = intval($_POST["totalval$i$j"]);
        $query = "INSERT INTO provprices (CA_id, prov_name, unitval, totalval)
                  VALUES ('$CA_id', '$prov_name', $unitval, $totalval)";
        mysql_query($query) or die(mysql_error());
        $j++;
    }
    $i++;
}

edited Jul 12 '14 at 01:59

answered Jul 11 '14 at 22:54

Barmar

741,623
53
500
612

Thanks for your answer, I'm think too in nested loops. But when i put your code in mine, the web don't load, What do you think is the cause of that? Thanks – user3810795 Jul 11 '14 at 23:49
If you `echo $query` does it look right? – Barmar Jul 12 '14 at 00:34
I checked with dreamweaver and show me that the third and five lines of your code are wrong but i don't see any wrong in your code, what could be? – user3810795 Jul 12 '14 at 01:03
I was missing `]` characters. – Barmar Jul 12 '14 at 01:59
Thanks, first is totval not totalval I fix that but now shows "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2", sorry for that, is a complicated query. – user3810795 Jul 12 '14 at 02:12
What does `echo $query` show? – Barmar Jul 12 '14 at 02:14

James · Answer 2 · 2014-07-12T00:32:48.137

0

This method uses the Joomla 3 version of inserting records

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$columns = array(
    $db->quoteName('CA_id'),
    $db->quoteName('prov_name'),
    $db->quoteName('unitval'),
    $db->quoteName('totval')
    );
$rowb = array();
$i = 1;
if (isset($_POST["prov_name"])) {
    $rowb[] = $jinput->post->get("prov_name$i", '', 'string').",".$jinput->post->get("unitval$i", '', 'string').",".$jinput->post->get("totval$i", '', 'string');
    $i++;
}
foreach ($rowb as $row) {
    $values[] = $db->quote($CA_id).",".$db->quote($row[0]).",".$db->quote($row[1]).",".$db->quote($row[2]);
}
$query
        ->columns($columns)
        ->values(implode(',',$values))
        ->insert($db->quoteName('#__supersite_contact'));
$db->setQuery($query);
if (!$db->execute()) {
    throw new Exception($db->getErrorMsg());
}

edited Jul 12 '14 at 00:32

answered Jul 11 '14 at 22:56

James

834
7
27

Thanks for your answer, I work with joomla then I don't need mysqli_connect i use $db =& JFactory::getDBO(); -- Apart of that when i put your code in mine, the web don't load, i Just replace the $columns var. You know why¡ – user3810795 Jul 11 '14 at 23:47
Your `while` loop is an infinite loop. You're testing the same variable each time. – Barmar Jul 12 '14 at 00:27
Now you're putting each `$jinput->post->get()` in a different element of `$rowb[]`, but the `foreach` loop expects them to be a sub-array. – Barmar Jul 12 '14 at 00:28
@James Shaver I just replace the #__supersite_contact for my table and don't works, not insert anything and the page is in white when i submitted, others inserts apart works fine :( – user3810795 Jul 12 '14 at 01:08
Sorry, that was a copy/paste from one of my own that does work. Have you turned on debug mode? – James Jul 12 '14 at 01:20
You mean.. error_reporting(E_ALL ^ E_NOTICE) ? Is is that, yes and don't show anything. This is a complicated query, sorry. – user3810795 Jul 12 '14 at 01:35
No, I mean in your Joomla admin: System > Global Configuration > System > Debug System (Yes/No) – James Jul 12 '14 at 03:04

How to make an array insert for this table

2 Answers2