Dynamic PHP/SQL query, complete with bug!

[michaelsanford]

I've got a table of 44 columns and I don't particularly feel like hard-coding the INSERT statement. So, instead, I've done this:

	mysql_select_db('science', $dblink) or
		die("Select database error: ". mysql_error($link));

	$result = mysql_query("SELECT * FROM `garden_data`", $dblink) or
		die("Can't select" . mysql_error($dblink));

	$dbdata = NULL;
	$dbfields = NULL;

for ($i = 0; $i < mysql_num_fields($result); $i++) {
	$meta = mysql_fetch_field($result, $i);
	$fieldname = $meta->name;

	// Parse the fields array
	$dbfields .= $fieldname;
		if ( $i < (mysql_num_fields($result)-1)) $dbfields .= ", ";

	// Parse the data array
	$dbdata .= "`" . $_POST[$fieldname];
		if ( $i < (mysql_num_fields($result)-1)) $dbdata .= "`, ";
		else { $dbdata .= "`"; }
	//echo $i . "<br />";
}

$query = "INSERT INTO `garden_data` (" . $dbfields . ") VALUES (" . $dbdata . ")";

My HTML form's id= and name= tags are all identical to the table's columns names, including two type="hidden" fields.

I've done all the text-based tests I can think of, including parsing out the entire query and looking for extra `` or commas at the end or in the middle and I can't find any. I even outputted $i and I'm outputting 44 iterations which is the number of columns.

Where the heck, then, does this error come from ?
There was an error: Unknown column '' in 'field list'

I also get that error if I reformat the query like this:
<font face="Courier New">

$query = "INSERT INTO `garden_data` VALUES (" . $dbdata . ")";

What's going on ?

 

[davidbrit2]

It looks like the back-ticks in your field value quoting are doing something unexpected. Use single quotes instead, like this:

    $dbdata .= "'" . $_POST[$fieldname];
        if ( $i < (mysql_num_fields($result)-1)) $dbdata .= "', ";
        else { $dbdata .= "'"; }

Kinda looks ugly with those single quotes enclosed in double quotes, but you get the idea. ;-) I'm not totally sure what the back-ticks do, but I suspect they somehow dereference an actual database entity. If I do this in my really simple test database:

insert into `res` (id, resdate, tab, size, name, phone, restime) values (`20`, `2005-3-20`, `3`, `2`, `Dave`, `111-1111`, `07:00:00`);

I get this "result":

ERROR 1054 (42S22): Unknown column '20' in 'field list'

So it apparently thinks the `20` is trying to reference a particular column in the table, or something along those lines. You seem to be getting the same result, but are presumably leaving the first data field blank (primary key with auto-increment?), which would explain the error looking like that.

 

[michaelsanford]

G E N I U S that did indeed fix my problem. It also explains why, in another reformulation of the INSERT, I got an error "Check your MySQL syntax near (`id`..." which made no sense...at the time.

All I needed was an addslashes($_POST[$fieldname]) (since PHP not longer ships with magic_quotes enabled) and it worked like a charm.

Thanks !!