generate query and execute within script (help)

Tea_J

Hi guys,

I was coding up a function that will automate my INSERT INTO query in a sense that i will have to type only the columns i want to populate..

here's the function

// INSERT INTO (doesnt quite work yet)
function db_insert($table,$vars){
	$var = explode(",",$vars);
	$totalVars = count($var) - 1;

$sql_a = "INSERT INTO $table (";
$sql_b .= ") \n Values(";

foreach($var as $curVar => $curVal){

	$sql_a .= trim($curVal);
	$sql_b .= "'$" . trim($curVal) . "'";

	if($curVar != $totalVars){  //if this isnt the last array yet, add comma (,)
		$sql_a .=",";
		$sql_b .=",";

	}
}

$sql = $sql_a . $sql_b . ")";


return $sql;
}

the script works fine and when echoed i can see that it produces the correct sql sytanx.

for example:

if a call the function and echo it like this:

echo db_insert("exams","exam_date,examiner,remarks");

the result is the follwoing:

INSERT INTO exams (exam_date,examiner,remarks)
Values('$exam_date','$examiner','$remarks')

exactly what i wanted. however, when i run this directly into a query, it doesnt work.. eg:

$results = mysql_query(echo db_insert("exams","exam_date,examiner,remarks"));

please help..

tea

planetsim

you may need to use eval instead of echo since you basically have a string instead of a string with variables.

Tea_J

umm..

you mean echo?
echo db_insert("exams","exam_date,examiner,remarks");
???

nah, i used that to see how things are formatted.. i didnt really use echo.. u used..

$results = mysql_query(db_insert("exams","exam_date,examiner,remarks"));

(a little typo only..)

anyway, the above didnt work either..
how does eval work..

planetsim

Well eval will convert you string which has variables into it to make it available to PHP. [man]eval[/man]

Roger_Ramjet

Alternatively, expand your function to accept the data array as well. Then it could return what you want, which is

INSERT INTO exams (exam_date,examiner,remarks)
Values('2005-01-12','T Eacher','Blah'),
('2005-01-13','J Doe','blah blah')

etc

Tea_J

hmm.. how do you propose i use eval on this? cause i tried using eval on each of the $variables.. no luck..

umm.. by the way, when i said it didnt work, i didnt mean that the query failed.. the query was successful however instead of storing the variables name, etc.. it stored the string:

"$name" "$age" etc etc..

tea

planetsim

Well eval can be used instead of using echo. However eval is a bad function I probably should have said that. Anyway Roger has a better alternative a safer one, although with that said Id still check the variables for invalid characters etc.

Tea_J

i gave up.. i redid my script..

the flaw with the first attempt is i made a function that would build my sql statement for me for use in a mysql_query function.. the problem is the sql statement contains the "$variable" name, and that this isnt interpretted as a variable (its value is not read) but instead a string "$variable".. <-- this is what's inserted into the database..

I redid a part of it so that my function would create an sql statement using the actual values of the variables i need passed.. had to use _globals in my function though --which i tried to avoid primarily....

thanks for your help guys.. much appreciated..

tea