PHP/MySQL Insert

[John101]

OK, I've been fighting with this for a few days and I finally broke down and I'm begging for help.

Here is the idea, I'm building a site where people will be able to login(not the problem) and create a new table in the db. Once the table is created, users will be able to add data to the table. My problem is the insert I'm using, it adds a new row with for every field (I know why, I just don't know how to fix it). I'll post some code, just let me know if you need more.

$sql="select * from spreadsheets where id =". $_GET['id'];
$result = @mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$name = stripslashes($row['name']);
 $display_block = "$name";
}
$tablename = str_replace(" ","",$name);
$rs = mysql_query("select * from $tablename where 0");
if (!$rs) {
die("WHOOPS! Something went wrong!");
}
$fields_num = mysql_num_fields($rs);
echo "<h1>Adding a Record to $display_block </h1>";
?>
<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>">
<table border='1' cellpadding='3' cellspacing='0' width='100%'><tr>
<?php
//printing table headers
for($i=0; $i<$fields_num; $i++) {
$field = mysql_field_name($rs, $i);
echo "<td><strong>$field</strong></td>";
}
echo "</tr><tr>";
for($i=0; $i<$fields_num; $i++) {
echo "<td><input type=\"text\" name=\"$i\" /></td>";
}
echo "</tr>";
?>
<tr><td colspan="<?php echo $fields_num ?>"><input type="submit" value="Add Record" name="submit" /></td></tr>
</table></form>
<?php
if (isset($_POST['submit'])) {
echo "<br><br>";
echo "You inserted ";
for($i=0; $i<$fields_num; $i++){
$field = mysql_field_name($rs, $i);
echo "<strong>".$_POST[$i] ."</strong>, ";
// HERE IS WHERE MY PROBLEM IS! 
$insertquery = "INSERT INTO $tablename ($field) VALUES ('$_POST[$i]')";
$insertaction = mysql_query($insertquery) or die ("ERROR: sorry John you messed up. " .mysql_error());
}
 // thought i might use this later, not really doing anything yet.
$last_id=mysql_insert_id();
echo "<br><br>The Last ID inserted was <strong>".$last_id."</strong>";
}
?>

The way my DB is set up, I have a DB called TEST and in it are the table that the user creates. Also there is a table called Spreadsheets that collects an ID and the table name of all the tables the user creates. This is for a list on the main page (code not given) and to identify the correct 'spreadsheet' the user is on in the URL.

I hope this made sense, please let me know if you have any questions or better yet, answers.

Thanks!

[rocknbil]

Welcome aboard John101, you're probably going to laugh, which is good. :-) You have the insert inside a loop.

for($i=0; $i<$fields_num; $i++){
///////////
$insertquery = "INSERT INTO $tablename ($field) VALUES ('$_POST[$i]')";
}

I do **something** like this: build a list of $fields and $values. Chop off the last comma ('cause I'm lazy and it works.)

if (isset($_POST['submit'])) { 
 $fields=$vals=NULL; 
 //echo "<br><br>"; // Displaying the field names to your  
 //echo "You inserted "; // users is a very bad idea. 
 for($i=0; $i<$fields_num; $i++){ 
 if (isset($_POST[$i]) and ($_POST[$i] != '')) { 
  $fields .= mysql_field_name($rs, $i) . ','; 
  $values .= "'" .$_POST[$i] . "',"; 
 } 
 } // Note loop end HERE 
 $fields = preg_replace('/,$/','',$fields); // chop last comma 
 $values = preg_replace('/,$/','',$values); 
 $insertquery = "INSERT INTO $tablename ($fields) VALUES ($values)"; 
 $insertaction = mysql_query($insertquery) or  
 die ("ERROR: sorry John you messed up. " .mysql_error()); 
 echo "<p>done.</p>"; 
} 

Much more advice to offer but the first, after you get this working, is to look at cleansing your input variables and prevention of mysql injection and cross site scripting. Don't want to see you back in a week, "my site was hacked . . ."

[John101]

you are amazing! it worked perfectly! and yes, i did laugh at myself.

Yes, sql injection was my next big worry, and I plan to tackle that next (any pointers would be much appreciated). But I'm still trying to learn this stuff, would you mind explaining what you did line by line so I'll know for next time. I guess my big question is:

- what does the $fields=$vals=NULL; do?

Thanks again for you quick response!

[rocknbil]

what does the $fields=$vals=NULL; do?

Remove it and see. :-)

When you concatenate ...

$fields .= "some value";

... to an undefined variable, you'll get an "undefined variable" warning if PHP errors are turned on (which they shouldn't be.) If they are turned off, it will just clog up your error_log with stuff that doesn't need logging.

It will still "work" without it, but defining the variables first before concatenating to them squelches errors, it's just good practice.

Same "sort" of thing goes on here.

if (isset($_POST[$i]) and ($_POST[$i] != '')) { ....

you could just do

if ($_POST[$i] != '') { ....

but if it's not been set, it will issue a warning "undefined index [whatever the value of $i is]"

By checking with isset, it squelches the warnings and just makes the programming "a little more strict."