Forum Moderators: coopster
The question that begs, is there a better way of putting together a query that now looks like this:
"INSERT INTO ".$EVENTS_TB." (id,... 27 more fields) VALUES ('$id", ... 27 more fields)' ;
I guess I still have some way to go in figuring this stuff out. Any further references would help. TIA
"INSERT INTO ".$EVENTS_TB." VALUES ('$id','$next','[i]...more fields, all separated by commas[/i]')";
It also makes it clear what the values refer to.
It is better to make it explicit as he had originally.
..reasons beyond your control (ie, if you recreate the table from a script...
"You" means this would be within my control.
Furthermore, the column order from the database better not be changing or I'd say we have bigger problems. I can understand your point of view, and it will come down to personal preference, but you'll have to provide substantiating evidence (standards) before it can be labeled "bad form".
Regards -- coopster
This can be quite a pain. Get in the habit of listing your columns, it'll save you time in the long run.
The original question was, is there a better way. Not listing the columns in definitely not better.
The answer is actually simpler than that. Let's assume that you are using a webform to submit your data.
Write a loop to build the query. This way you could leave columns out of the form if you didn't need them, and not have to change your submission engine.
The loop would look something like this
$columns="(id,created"; //assuming some stuff here
$values="(0,now()";
while (list($column,$value)=each($HTTP_POST_VARS))
{
$tcol=$column; // a little error checking
$table=strtok($tcol,".");
if ($table==$EVENTS_TB)
{
$columns .= ",".$column;
$values .= ",'".mysql_real_escape_string($value)."'";
}
}
$query="insert into $EVENTS_TB $columns) values $values);"
Then you'd have a form with an input fields with a names that were "table.field". You'd want to add some "required field" processing and stuff.
-Chris
That aside, thanks people. Coopster, yes I've done a fair amount of manual reading but ... there's a lot more to the "real world" than the "hello world" examples that seem to pervade most references. Hence arriving here.
At this stage I may be more inclined to write out the query string in full. In my limited experience have since found out that missing out an item by one tick throws up all number of issues and errors - which usually means to'ing and fro'ing between the referenced table and the query in question to do a fix. One on one referencing might be the way to go.
christodd, I'll take a closer look at your solution.
I was half thinking about setting up an array to handle this sort of thing but, don't know enough about these things ... yet.
In taking a closer look at the code you posted ... I'm sort of stuck between taking it as read or having to read more into it. I know about KISS but then, I don't have enough experience to know when to leave things be or end up making them more complicated.
On inspection, the first thing that came to mind were the "unclosed" brackets. Then looking a little closer I see the append ".=" being used.
Am I to assume that the $column variable started on line 1 will be completed and closed in the $query? If so, this could be a blast.
Also, if I can get this to work, am I right to assume that I could call the variables $columns and $values in the rest of the queries on the given page - some half dozen or so?
I'll probable discover the answers for myself over the next period. Still, would appreciate any further comments.
Not listing the columns is definitely not better.
However, if it we were to say...
"Hardcoding the VALUES list is definitely not better"
...I would agree. There is nothing wrong with the form, but hardcoding the list, whether it be the optional column list and/or the VALUES list, will make for difficult code maintenance. As I said before, the optional column list argument will come down to a matter of personal preference.
That being said, jackson expresses that we are dealing with variables in the statement which means we should be using a dynamic approach to statement-building. christodd has graciously offered a good example in post #6. I use a very similar method but I also create a key field list for updates and deletes. The following code can be stored in an include file that can be called upon when table (file) and column (field) details are needed. This small chunk of code will create two arrays; a column list as well as a key field list (which will come in handy when you start doing UPDATE and DELETE statements):
table_info.php
<?php
# Get column list and key fields for table;
# Let's say $table was an INPUT type hidden value if it
# hasn't been set somewhere else in your code:
if (!$table) $table = $_REQUEST['table'];
unset ($fields, $key_fields);
$rh_column_details = mysql_query("DESCRIBE $table");
while ($row = mysql_fetch_assoc($rh_column_details)) {
if ($row['Key'] == 'PRI') $key_fields[] = $row['Field'];
$fields[array_shift($row)] = $row;
}
# Reset pointer for future uses of query
mysql_data_seek($rh_column_details, 0);
}
?>
<?php
# Begin building the SQL statement. The options are either INSERT, UPDATE or DELETE.
if (!$action) $action = $_REQUEST['action'];
if (!$table) $table = $_REQUEST['table'];
if ($action == 'Add') {
$sql = "INSERT INTO $table VALUES(";
} elseif ($action == 'Update') {
$sql = "UPDATE $table SET ";
} elseif ($action == 'Delete') {
$sql = "DELETE FROM $table";
} else {
exit('Error - undefined action.');
}
# Now finish the SQL statement by describing the file listed and loop
# through the array returned to get the field names and key values.
unset ($where_statement);
if ($table) {
include("table_info.php");
while ($row = mysql_fetch_array($rh_column_details)) {
$fieldname = $row['Field'];
if (!isset($$fieldname)) $$fieldname = $_REQUEST[$fieldname];
if ($action == 'Add') {
# INSERT operations:
# Set field value to NULL for time_stamp field to auto-update with current date and time.
# Set field value to NULL for auto_increment fields to assign next number in sequence.
#
# Note: I name my timestamp column 'time_stamp'
# This could probably be updated to check for something like:
#if (substr($row['Type'], 0, 9) == 'timestamp' or $row['Extra'] == 'auto_increment') {
#
if ($row['Field'] == 'time_stamp' or $row['Extra'] == 'auto_increment') {
$sql .= 'NULL, ';
} else {
$sql .= "'" .$$row['Field']. "', ";
}
} elseif ($action == 'Update' and $row['Field']!= 'time_stamp' and $row['Extra']!= 'auto_increment' and $row['Key']!= 'PRI') {
$sql .= $row['Field']. "='" .$$row['Field']. "', ";
}
# If this is part of the primary key, then build the where statement for update/delete operations.
if ($action!= 'Add' and $row['Key'] == 'PRI') {
if (empty($where_statement)) {
$where_statement = ' WHERE ' .$row['Field']. "='" .$$row['Field']. "'";
} else {
$where_statement = $where_statement .= ' AND ' .$row['Field']. "='" .$$row['Field']. "'";
}
}
}
}
$sql = rtrim($sql, ", ");
if ($action == 'Update' ¦¦ $action == 'Delete') {
$sql .= $where_statement;
} elseif ($action == 'Add') {
$sql .= ')';
} else {
exit('<p>Error - action could not be determined. For security purposes, this query has been disabled. Please try again.</p>');
$sql = '';
}
# Print the sql to the screen for debugging purposes.
// exit("$sql<br />");
if (!mysql_query("$sql")) exit('<p>Error - sql query failed</p>');
?>
And jackson, for what it's worth, I highly recommend a dynamic approach using the theory introduced in this thread by christodd. Dynamic generation becomes very important if you ever add a field or change a field name -- I actually generate my XHTML form INPUT fields from the file as well, thereby eliminating ALL column name hardcoding from my code. You have two examples to feed from and should be able to come up with a solution that you'll use over and over again.
The original question was, is there a better way.
regards -- coopster
Need to add here, what started this debate off was something I posted elsewhere and that was using variables to name tables, eg $EVENTS_TB for the 'events' table. Just looked plain "ugly" and seemed an unnecessary way of doing things - even though it was pointed out this would necessitate making only one change should the table name be changed. This is what prompted the question that started off this thread.
My feeling was, if a table could be called as a variable then what about the rest - as in a better way of doing things.
All this has come about while "hacking" an existing events manager/calendar app that I'm hoping to turn into a plugin for a CMS app.
The piece being hacked is a second generation to the original. The original was quite a nice piece of work even if most of the functions were grouped into single files.
In the second generation, the author spilt the original into a number of files relative to the app's various functions and added in a number of useful features along the way. However, in doing so, seems to have turned this thing into something of a dog's breakfast - me speaking as a something of a rookie on this score.
Got christodd's solution to work quite nicely - this after a few tweaks - thanks there Chris.
Fully concur on the
highly recommend a dynamic approachto this whole thing. This is what had me "blown off the beach", so to speak. The original app had something like 8 or so fields in the events form - date, time, title, etc. I need to up this to include something like another 20 fields. Sitting here fiddling with and matching up columns and values through a dozen or so INSERTS, UPDATES, DELETES and functions doesn't turn me on. Hence this posting.
The response has been really surprising and need to extended my appreciation and thanks to all concerned.
Coopster, let me get my head around this number. I can see where you're coming from and where all this might be going.
BTW I'm quite excited by all this. A bit like moving from a house with an out-house, hole-in-the-ground toilet to one with one of those grand scale bathrooms with a separate toilet and bidet. Just another way of saying, "looks like I'm moving up in the world" - php-wise that is.
Coopster, from where I'm standing that appears to be a serious piece of work.
You'll pick up quite a bit just by digesting the code here as well as other work listed in this PHP forum -- there are a lot of unselfish coders here and that's my main draw to WW -- well, that and the folks are well-controlled -- you'll see some differences, but no name-calling or personal attacks.
Best of luck with this project and future projects, jackson
-- coopster
