[RESOLVED] Turning an array into an sql statement

vertmonkee

in my script I give the user options and let them build up an array, which is set as a session variable.

When they have finished building up an array I want to turn that array into an sql statement to insert into a database.

When I try to extract the array I just get the first options repeated. How do I get all of the options?

Also when trying to build the slq statement I'm not sure how to end it as at the moment it ends with a ,

Here is my code so far.

Thanks for nay help.

<?php
include "../includes/connect.php";
session_start();

if (isset($_POST['submit']) && $_POST['submit'] == "Add to array") {
$option = $_POST['option'];
$number = $_POST['number'];
$_SESSION['obstacle'][]= array("obstacle1" => "$option", "option" => "$number");
echo "You have added to the array<br />";
print_r($_SESSION['obstacle']);
?>

<html>
<head>
<title>Array Form</title>
</head>

<body>

<form action="arrayform.php" method="post">
<select name="option">
<option value="option1">Option1</option>
<option value="option2">Option2</option>
</select>

<select name="number">
<option value="number1">Number1</option>
<option value="number2">Number2</option>
<select>
<input type="submit" name="submit" value="Add to array">
<input type="submit" name="submit" value="Send Form">
</form>

</body>
</html>

<?php
	} else {
if (isset($_POST['submit']) && $_POST['submit'] == "Send Form") {
echo "You have sent the form<br />";
print_r($_SESSION['obstacle']);
?>

<html>
<head>
<title>Array Form</title>
</head>

<body>

<form action="arrayform.php" method="post">
<select name="option">
<option value="option1">Option1</option>
<option value="option2">Option2</option>
</select>

<select name="number">
<option value="number1">Number1</option>
<option value="number2">Number2</option>
<select>
<input type="submit" name="submit" value="Add to array">
<input type="submit" name="submit" value="Send Form">
</form>

</body>
</html>
<?php
$num = count($_SESSION['obstacle']);
echo "Elements in the array: " . $num . "<br />";

for ($i; $i < $num; $i++) {
	extract($_SESSION['obstacle']);
	$values = "('$option, '$number'),";

}

$sql = "INSERT INTO `test` ( `field1` , `field2` ) VALUES $values";

$result = mysql_query($sql) or die(mysql_error());
}
else {
		echo "You haven't done anything<br />";
$_SESSION['obstacle'] = array();
print_r($_SESSION['obstacle']);
?>
<html>
<head>
<title>Array Form</title>
</head>

<body>

<form action="arrayform.php" method="post">
<select name="option">
<option value="option1">Option1</option>
<option value="option2">Option2</option>
</select>

<select name="number">
<option value="number1">Number1</option>
<option value="number2">Number2</option>
<select>
<input type="submit" name="submit" value="Add to array">
<input type="submit" name="submit" value="Send Form">
</form>

</body>
</html>
<?php
}}
?>

bpat1434

$sql = "INSERT INTO `test` (`field1`, `field2`) VALUES ";
for ($i=0; $i < $num; $i++) {
	extract($_SESSION['obstacle']);
	$sql .= "('$option, '$number')";
    if($i<($num-1)
        $sql .= ", ";
}

echo $sql;

What does that give you? A proper SQL statement?

vertmonkee

Yeah thanks for that it builds the sql statement as desired.

Just being very very picky, and in case anyone else uses this. There is missing ) after the if($i<($num-1) part.

But I still have the problem that the sql statement just builds the same valuse, e.g. option1 and number1.

This happens no matter what I put into the array.

bpat1434

Have you checked your session?

vertmonkee

I am using the print_r($_SESSION['obstacle']) function to display the array as I am building it.

It displays that everything is going in fine.

bpat1434

Ah... know what it is? We dont' specify which array to extract...

 $sql = "INSERT INTO `test` (`field1`, `field2`) VALUES ";
for ($i=0; $i < $num; $i++) {
    extract($_SESSION['obstacle'][$i]);
    $sql .= "('$option, '$number')";
    if($i<($num-1)) // Thanks for the coding mistake :)
        $sql .= ", ";
}

echo $sql;

vertmonkee

I tried that but it returns an error message saying,

Warning extract() [function extract]: first argument should be an array

i thought the same as you that we need to specify the array to extract, and thus the $i counter would work.

bradgrafelman

Are you sure it's an array? Can you show us an example structure of $_SESSION['obstacle'] ?

Here's a rewrite of the code used above:

foreach($_SESSION['obstacle'] as $value)
     $value .= '(\'' . $value['option'] . '\', \'' . $value['number'] . '\'), ';
echo rtrim($sql, ', ');

vertmonkee

I'll try the new reworking of the code thanks.

You can see the page at

http://ttphp.open.ac.uk/~idl27/arrayform.php

thanks.

bradgrafelman

Looking at that array... you'd probably do something like this:

if(isset($_POST['option']))
     $_SESSION['obstacle_array'][] = array('option' => $_POST['option'], 'number' => $_POST['number']);

To add something into the session variable, correct? Then, on the send page, you'd use something like this to process the array in the session:

foreach($_SESSION['obstacle_array'] as $array)
     $array .= '(\'' . $array['option'] . '\', \'' . $array['number'] . '\'), ';

Note the typo in my previous, I used $value in the foreach() statement, but accidentally used $values below.

vertmonkee

I have it sorted now.

Basically it is a good example of leaving something having a good nights sleep then looking again properly.

We were on the right lines by addin gthe $i counter to say which array to extract from, I was then asking for the wrong variable names.

Thanks for all the help.

bpat1434

Glad to hear you got it sorted.