Need help inserting array info into a table

[EdLentz]

I have a page that uses several queries and puts the results in a table. The first query gets a list of extensions and the secret, then the next two columns are dropdowns with the options from a query allowing the user to select different options. I have a submit to send the info to a page to insert the info into a DB. Right now the info is printed to the page so I can see what is going on. So, right now the insert page is showing One array record for the dropdown boxes, not the extension and secret. I need all the columns and all the rows in the array to insert into the DB. I am using $post method.

$sql2 = "SELECT extension, secret from extensions;";
$result2 = mysql_query($sql2) or die(mysql_error());
echo "<table border='3'>
    <tr>
        <th>Extension #</th>
        <th>Secret</th>
        <th>MAC Address</th>
        <th>Template</th>
    </tr>";

while($row = mysql_fetch_assoc($result2))
{
    $sql = "SELECT id , mac FROM phones order by mac;";
    $result = mysql_query($sql) or die(mysql_error());
    $sql1 = "SELECT id , templatename FROM templates order by templatename;";
    $result1 = mysql_query($sql1) or die(mysql_error());
    echo "<tr>";
    echo "<td>" . $row['extension'] . "</td>";
    echo "<td>" . $row['secret'] . "</td>";
    echo "<td> <select name='phone'>";
    while($rowA = mysql_fetch_assoc($result)) {
        echo '<option value="' . $rowA['id'] . '">' . $rowA['mac'] . '</option>';
    }
    echo "</select></td>";
    echo "<td><select name='template'>";
    while($rowB = mysql_fetch_assoc($result1)) {
        echo '<option value="' . $rowB['id'] . '">' . $rowB['templatename'] . '</option>';
    }
    echo "</select></td>";
    echo "</tr>";
}
echo "</table>";
?>


<input type="submit" value="Submit your selections">
</body>
</html>

Insert page

<?php
echo "You got here";
//***********Get the Assignment information *************
$values = array_values($_POST);
print_r($values);
?>

I know I need to change to mysqli, I just need this to work soon and after I get it working I can work on cleaning it up. Any help is much appreciated!

 

[Geates]

Quick and Dirty:
Inside the while loop, assign the values of 'extension' and 'secret' to [red]hidden[/red] inputs where the name is an [blue]array[/blue].

<input type=[red]"hidden"[/red] name="extensions[blue][][/blue]" value="<? echo $row['extension']; ?>">
<input type=[red]"hidden"[/red] name="secrets[blue][][/blue]" value="<? echo $row['secret']; ?>">

array_values() won't do what you intend because the above code stores and array (extension and secretes) inside an array ($_POST). Just print_r($_POST).

-Geates

 

[EdLentz]

Using this got me a "Page isn't working"

while($row = mysql_fetch_assoc($result2))
{
    $sql = "SELECT id , mac FROM phones order by mac;";
    $result = mysql_query($sql) or die(mysql_error());
    $sql1 = "SELECT id , templatename FROM templates order by templatename;";
    $result1 = mysql_query($sql1) or die(mysql_error());
    echo "<tr>";
    echo "<td>" . $row['extension'] . "</td>";
    echo "<td>" . $row['secret'] . "</td>";
    echo "<td> <select name='phone'>";
    <input type="hidden" name="extensions[]" value="<? echo $row['extension']; ?>">
    <input type="hidden" name="secrets[]" value="<? echo $row['secret']; ?>"> 
    while($rowA = mysql_fetch_assoc($result)) {
        echo '<option value="' . $rowA['id'] . '">' . $rowA['mac'] . '</option>';
    }
    echo "</select></td>";
    echo "<td><select name='template'>";
    while($rowB = mysql_fetch_assoc($result1)) {
        echo '<option value="' . $rowB['id'] . '">' . $rowB['templatename'] . '</option>';
    }
    echo "</select></td>";
    echo "</tr>";
}

 

[Geates]

what is the output of $_POST?

 

[EdLentz]

Sorry to be dense, but if the page isn't loading How can I get the output of $_Post?

 

[ChrisHirst]

Because what is in $_POST is NOT likely to be the cause of the page not loading.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[EdLentz]

Removing the hidden imputs and adding print_r $post to the insert file I get this

You got hereArray ( [0] => 594 [1] => 1 ) Array ( [phone] => 594 [template] => 1 )

from the insert page

 

[EdLentz]

I realized that I was getting the id of the items in the array, so I changed the
echo '<option value="' . $rowA['id'] . '">' . $rowA['mac'] . '</option>';}
to
echo '<option value="' . $rowA['mac'] . '">' . $rowA['mac'] . '</option>';}

I did the template name as well.

Now my result is:
Array ( [phone] => Array ( [0] => 80828703D858 [1] => 80828703658A ) [template] => Array ( [0] => CQ400Template [1] => CQ600Template ) )

I still need to get the extension and secret info.

Any other ideas?

 

[EdLentz]

I found an example of taking the info from two arrays and inserting it into a DB. I updated it to use my code, but I am not getting any errors and also not getting any data to the db. Here is what I am getting back from my form in the $Post data.

Array ( [phone] => Array ( [0] => 80828703658A [1] => 80828703D858 [2] => ) [template] => Array ( [0] => Another 600 Template [1] => CQ400Template [2] => Another 600 Template ) )

<?php
echo "You got here";
//***********Get the Assignment information *************
print_r($_POST);
require('/var/[URL unfurl="true"]www/html/cqadmin/utils/connect.php');[/URL]
$ext = explode('|',$_POST['phone']);
$tmplate = explode ('|',$_POST['template']);
$sqlQuery = "INSERT INTO assignments (macaddress,template) VALUE (?,?)";
$stmt = $link->prepare($sqlQuery);

$stmt->bind_param($phone, $TName);

$rowBuffer = array_map($ext, $tmplate); // combine the arrays so that they end up like array(array(1, 'User1'), array(2, 'User2')), similar to other languages and libraries "zip" function

$link->query('START TRANSACTION'); // optional, but if you're inserting more than a handful of rows this makes it much faster

foreach ($rowBuffer as $row) {
    list($phone, $TName) = $row;

    $stmt->execute();
}

?>

Once this is working is it possible to add an array to also insert the extension and secret info from a query?

I am in over my head here so far.

 

[Geates]

Where is $phone and $TName defined? It appears they are blank. Binding a blank values may produce a valid query but not one that you intend to execute. Also, bind_param() is not a PDOStatement method and results in a fatal error upon execution. That being said, I would venture the bet that you don't have error-reporting enabled or you are building this code in a production environment where error reporting is typically turned off.

$sqlQuery = "INSERT INTO assignments (macaddress,template) VALUE (?,?)";
$stmt = $link->prepare($sqlQuery);

//$stmt->bindParam(parameter number, value);
$stmt->bindParam(1, $phone); //binds the value of $phone - BY REFERENCE - to the first ?
$stmt->bindParam(2, %TName); //binds the value of $TName - BY REFERENCE - to the second ?

-Geates

P.S. Error, Warnings and Notices are extremely valuable. Make sure they are being reported when they need to be.

 

[EdLentz]

That code was something I found and tried to adapt to my situation. Obviously, I am to green at this to know when I should quit.

 

[EdLentz]

I finally got to the point where I get this as an output from my form page


Array ( [extension] => Array ( [0] => 100 [1] => 101 [2] => 102 ) [secret] => Array ( [0] => a467ca4044f298eff15a26e59f39fe21 [1] => 0c4275de171ef363b77aa6aae27afff1 [2] => c1951bfb07ed6a833d6d785ff4e19123 ) [phone] => Array ( [0] => 80828703658A [1] => 80828703D858 [2] => 80828703F866 ) [template] => Array ( [0] => Another 600 Template [1] => Another 600 Template [2] => Another 600 Template ) )

So to my eye it appears that I have all the data that I am expecting. I have some code that inserts the extension and the secret data into the database but not the phone or the template data also inserts a blank record.

// Escape user inputs for security
$ext = mysqli_real_escape_string($link, $_POST['extension']);
$secret = mysqli_real_escape_string($link, $_POST['secret']);
$macaddress = mysqli_real_escape_string($link, $_POST['phone']);
$templatename = mysqli_real_escape_string($link, $_POST['template']);

// attempt insert query execution
$sql = "INSERT INTO assignments (id, extension, secret, macaddress, template) VALUES (null,'$ext', '$secret', '$macaddress', '$templatename')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);
?>

The phone and template information were from dropdowns in the original form page, I am under the impression that once I got it to the insert page all would be good No???
What am I missing?
Thanks for any comments

 

[Geates]

Can't say for sure, but I have a negative premonition. Notice that $ext, $secret, $macaddress, and $templatename are now all arrays, containing multiple values, but you are only running one query.
Modify your code to execute three queries, one for each set of values.

for (var $i = 0; i++; i == 3) {
   $exts = $output['extension'];
   $secrets = $output['secret'];
   $macaddresses = $output['phone'];
   $templatenames = $output['template']; 
   $sql = "INSERT INTO assignments (id, extension, secret, macaddress, template) VALUES (null, '".$exts[$i]."', '".$secrets[$i]."', '".$macaddresses[$i]."', '".$templatename[$i]."')";

   if(mysqli_query($link, $sql)){
      echo "Records added successfully.";
   } else{
      echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
   }
}