RopeADope

Hi all. I have the following function which auto-generates a form based on a database table. What I want to do is "cancel" the function if the table doesn't exist. The function as is... function build_form($table_name){ $sql="SELECT * FROM $table_name"; $result=mysql_query($sql); $num=mysql_num_rows($result); $i=0; echo "<form method=\"post\" action=\"/php/process_data.php\">"; echo "<input type=\"hidden\" name=\"selected_table\" value=\"" . $table_name . "\"/>"; echo "<table>"; echo "<tr><td colspan=\"2\" style=\"font:1em arial;font-weight:bold;text-align:center;\">Input Form: " . $table_name ."</td></tr>"; $field_names=array(); while ($i < mysql_num_fields($result)){ $fields=mysql_fetch_field($result,$i); echo "<tr><td>" . $fields->name . "</td><td><input type=\"text\" size=\"30\" name=\"" . $fields->name . "\" /></td></tr>"; $i++; }; echo "<tr><td colspan=\"2\" style=\"text-align:center;\"><input type=\"submit\" value=\"Submit Data\" style=\"width:75%\" /></td></tr>"; echo "</table>"; echo "</form>"; }; Would something like this work? (note lines 3-5) function build_form($table_name){ $sql="SELECT * FROM $table_name"; if(!$sql){ return; }; $result=mysql_query($sql); $num=mysql_num_rows($result); $i=0; echo "<form method=\"post\" action=\"/php/process_data.php\">"; echo "<input type=\"hidden\" name=\"selected_table\" value=\"" . $table_name . "\"/>"; echo "<table>"; echo "<tr><td colspan=\"2\" style=\"font:1em arial;font-weight:bold;text-align:center;\">Input Form: " . $table_name ."</td></tr>"; $field_names=array(); while ($i < mysql_num_fields($result)){ $fields=mysql_fetch_field($result,$i); echo "<tr><td>" . $fields->name . "</td><td><input type=\"text\" size=\"30\" name=\"" . $fields->name . "\" /></td></tr>"; $i++; }; echo "<tr><td colspan=\"2\" style=\"text-align:center;\"><input type=\"submit\" value=\"Submit Data\" style=\"width:75%\" /></td></tr>"; echo "</table>"; echo "</form>"; };

Hi all. The title pretty much says it all. I really have no idea if this is even possible or where to start. A recent dilemma caused me to have to change a field name with some spaces in it. I have a database with ~65 tables, a majority of which were converted from Access. So I'm curious as to how many others have bad field names. Is there a way to use PHP to go through all the field names in a database and remove spaces?

Changed the field name to %ITServicesTested and everything works fine now. Thanks to all for the help!

Ah, backticks worked. But I've come into another interesting problem. After adding the backticks, the statement made it past the "%" characters in the field names but stopped on a field name of "%IT_services_tested". Unknown column '%IT_services_tested' in 'field list' IN INSERT INTO kpiscmmin (`KPISCMMinID`,`KPISCMMinDate`,`%P1AppsCovered`,`%TestFailures`,`%IT_services_tested`,`MTBStratDays`,`MTBBiADays`,`MTBRADays`,`MTBTests-Days`,`SCMViabIndex`) VALUES ("","","","","","","","","","") In the MySQL table and in the input form, this field is "%IT services tested". I have mysql_real_escape_string(trim($fieldname)) earlier in my code. Would either of those be adding the underscores to the field name? If so...how should I approach this problem? My initial thought is to just change the field name...

Ok, so its definitely the bad field name characters causing a problem. I added the line that you suggested so my code looks like this... $sql="INSERT INTO {$selected_table} ({$key_string}) VALUES ({$value_string})"; $result=mysql_query($sql) or die(mysql_error() . " IN $sql"); And the output looks like... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%P1AppsCovered,%TestFailures,%IT_services_tested,MTBStratDays,MTBBiADays,MTBRADa' at line 1 IN INSERT INTO kpiscmmin (KPISCMMinID,KPISCMMinDate,%P1AppsCovered,%TestFailures,%IT_services_tested,MTBStratDays,MTBBiADays,MTBRADays,MTBTests-Days,SCMViabIndex) VALUES ("","","","","","","","","","") I'm not sure how I go about fixing this. I tried adding single quotes around the field names but that didn't help at all. Any ideas?

Ah, thanks. I'll give that a test and post anything I find out. On a side note, is there a way to just change every field name and remove/replace the problematic characters? Preferably something I could write with PHP?

Hi all. I'm not sure if this post belongs here or in the MySQL forum because I'm not entirely sure where the problem is. I have a script that takes data from a form and puts it into a database. There are several tables that were hand built and the script works fine with those. There are several tables that were converted from M$ Access to MySQL and the script does not work with those. All tables are InnoDB (if that matters). My inkling is that the converted tables that have weird field names, some containing "%","#", and "-" characters, are interfering with the MySQL statement to do the insert. Any thoughts on this? Would those odd characters for field names in fact affect the MySQL statement to enter data? P.S. I don't have access to the code at the moment. Can post it later unless the solution to this problem is simple (i.e. weird characters are the problem). Thanks in advance!

Oh gees. Of course it was a basic mistake, haha. Thanks for triggering that. I just adjusted it to the following... $fields->name=str_replace("_","",$fields->name);

Hi all. I have a query that returns field names. I'm basically just trying to clean these field names up. Removing underscores, capitalizing letters, etc. The following is supposed to replace underscores with spaces, but it doesn't seem to work. Any help is appreciated. str_replace("_"," ",$fields->name);

Ohhhh. I did not know that. Just gave it a go and it works! Thanks!

The code that I'm showing here is from a file in /input and the includes are for files in /php This code: <html> <head> <title>vCTO-Service Portfolio Input</title> <?php include('/php/head.php');?> </head> <body> <?php include('/php/t1.php');?> <?php include('/php/t2_demand_management.php');?> <?php include('/php/t3.php');?> </body> </html> Produces these errors: Warning: include(/php/head.php) [function.include]: failed to open stream: No such file or directory in C:\xampp\htdocs\input\input_service_portfolio.php on line 5 Warning: include() [function.include]: Failed opening '/php/head.php' for inclusion (include_path='.;C:\xampp\php\PEAR') in C:\xampp\htdocs\input\input_service_portfolio.php on line 5 Warning: include(/php/t1.php) [function.include]: failed to open stream: No such file or directory in C:\xampp\htdocs\input\input_service_portfolio.php on line 8 Warning: include() [function.include]: Failed opening '/php/t1.php' for inclusion (include_path='.;C:\xampp\php\PEAR') in C:\xampp\htdocs\input\input_service_portfolio.php on line 8 Warning: include(/php/t2_demand_management.php) [function.include]: failed to open stream: No such file or directory in C:\xampp\htdocs\input\input_service_portfolio.php on line 9 Warning: include() [function.include]: Failed opening '/php/t2_demand_management.php' for inclusion (include_path='.;C:\xampp\php\PEAR') in C:\xampp\htdocs\input\input_service_portfolio.php on line 9 Warning: include(/php/t3.php) [function.include]: failed to open stream: No such file or directory in C:\xampp\htdocs\input\input_service_portfolio.php on line 10 Warning: include() [function.include]: Failed opening '/php/t3.php' for inclusion (include_path='.;C:\xampp\php\PEAR') in C:\xampp\htdocs\input\input_service_portfolio.php on line 10 This code produces no errors: <html> <head> <title>vCTO-Service Portfolio Input</title> <?php include('../php/head.php');?> </head> <body> <?php include('../php/t1.php');?> <?php include('../php/t2_demand_management.php');?> <?php include('../php/t3.php');?> </body> </html>

So I thought I had this figured out but I've run into another problem. I tried using a leading "/" which worked initially, but I've found that with documents that are in directories, I can only load files in parent folders by using "../". I also tried using $_SERVER['HTTP_HOST'] instead of the leading slash but that hasn't worked.

So how do I go about accomplishing what I'm trying to do? Is there a way to set a document_root-like path to use in URLs?

Hi all. Working on a nav system using the document_root variable. I can't figure out why the links won't execute when I click them. When I mouse over a link, it shows the correct URL, but nothing happens when I click the links. The browser remains on the same page. The page <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>vCTO-Home</title> <link rel="stylesheet" type="text/css" href="css/style.css" media="screen" /> <?php include($_SERVER['DOCUMENT_ROOT'] . '/php/var.php');?> </head> <body> <?php include($_SERVER['DOCUMENT_ROOT'] . "/php/t1.php");?> </body> </html> t1.php <div class="tier"> <a href="<?php echo $docroot;?>/index.php">Home</a> <a href="<?php echo $docroot;?>/demand_management.php">Demand Management</a> <a href="<?php echo $docroot;?>/engineering.php">Engineering</a> <a href="<?php echo $docroot;?>/steady_state.php">Steady State</a> <a href="<?php echo $docroot;?>/communications.php">Communications</a> <a href="<?php echo $docroot;?>/business_management.php">Business Management</a> <a href="<?php echo $docroot;?>/risk_management.php">Risk Management</a> <a href="<?php echo $docroot;?>/other.php">Other</a> </div> var.php <?php $docroot=$_SERVER['DOCUMENT_ROOT']; ?>

Apologies. I don't think I've explained myself well enough. I don't want to explicitly have to write the SQL statement because the processing script will handle upwards of 40 forms, all with unique fields. What I'm trying to do is have the following happen on a form submission... Form->Processing script --Pass a hidden variable to represent the name of the form. This variable is identical to the name of a mysql table, that way I can just put $table_name into an SQL statement and have it work for every form. --Pass the fields in the form to the processing script which will in turn put them into an array so I can use $form_values in an SQL statement and have it work for every form. I'm looking to do something like this... $table_name=$_POST['table']; foreach($_POST as $value) { $sql_values[] = "('" . mysql_real_escape_string($value) . "')"; } $query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values); This is pretty much what you initially posted. Would this work for what I'm trying to do?

Bump

Ok, I have a better understanding now. I've just got one last question for clarification then I'll give it a go. The following is one of the forms... <form name="timesheets" method="post" action="process_data.php"> <input type="hidden" name="timesheets" value="timesheets" /> <table style="background-color:#ccc;border-radius:10px;padding:5px;"> <tr> <th>Timesheets</th> </tr> <tr> <td>Date</td><td>Activity</td><td>Hours</td><td>Description</td> </tr> <tr> <td><input type="text" name="date" /></td> <td> <select name="activity"> <option>Maintenance</option> <option>Break/Fix</option> <option>Admin</option> <option>Service Request</option> <option>Training</option> </select> </td> <td><input type="text" name="hours" /></td> <td><input type="text" name="description" /></td> </tr> <tr> <td colspan="4"><input type="submit" value="Submit" style="width:100%;" /></td> </tr> </table> </form> So what I should do is change the form to look like the following? <form method="post" action="process_data.php"> <table name="table" style="background-color:#ccc;border-radius:10px;padding:5px;"> <tr> <th>Timesheets</th> </tr> <tr> <td>Date</td><td>Activity</td><td>Hours</td><td>Description</td> </tr> <tr> <td><input type="text" name="values[]" /></td> <td> <select name="values[]"> <option>Maintenance</option> <option>Break/Fix</option> <option>Admin</option> <option>Service Request</option> <option>Training</option> </select> </td> <td><input type="text" name="values[]" /></td> <td><input type="text" name="values[]" /></td> </tr> <tr> <td colspan="4"><input type="submit" value="Submit" style="width:100%;" /></td> </tr> </table> </form>

Hmmm...I'm still fairly novice to PHP...I think that's obvious, haha. I just have a few questions on your code to clarify my understanding. What does name="values[]" do? (I've only ever seen the basic <input type="text" name="fname"> etc...) Table: <input type="text" name="table" /> Value 1: <input type="text" name="values[]" /> Value 2: <input type="text" name="values[]" /> etc... What's the purpose of the { } around $table in this statement? $query = "INSERT INTO {$table} VALUES " . implode(', ', $sql_values);

Hi all. I'm working on a script to handle processing of several forms. The issue I've run into is I cannot figure out how to execute a loop and get all of those results into a variable(query). I can't quite seem to wrap my head around the idea. Here's what I have thus far... $new_post_array = array(); foreach ($_POST as $param) { $new_post_array[] = $param; }; print_r($new_post_array); The following is what I think i may need to do... $new_post_array = array(); foreach ($_POST as $param) { $new_post_array[] = $param; }; $i=1; $sql="INSERT INTO $new_post_array[0]("; //$_new_post_array[0] holds the table name; while($i<count($new_post_array)){ $sql=$sql . $new_post_array[$i]; //concat the first part of the sql with the field names } $sql=$sql . ")values("; while($i<count($new_post_array)){ $sql=$sql . $new_post_array[$i]; //concat the second part of the sql with the values } $sql=$sql . ")"; //close the sql I realize this is a huge mess...but I can't figure out an easier way to do it as of yet. Any help would be MUCH appreciated. (Side note: I just realized that the first portion of the sql statement where I give the field names is incorrect in that it will put the values instead of the field names I need. That's simple enough to fix so please ignore that error. My primary goal is to get the $sql statement constructed as efficiently as possible.)

Ah, excellent idea. So with the code you've posted, for each loop through it will append a value to $array from $_POST, correct?

Is there any way to reference a key with an index number? I'm trying to create a data processing script to handle a bunch of forms so I need to be able to reference $_POST keys without actually naming them.

How can you print an individual key in the $_POST array? I've tried... echo $_POST[0]; But with no luck. There is data in the array, not all keys have values.

Every table has different column names. I don't believe any have a column of "name". In terms of output, I'm just looking to list all the data in a table as ordered rows. Ex: ID client date X Y Z 1 Home 2/2/10 1 2 3 2 Business 1/2/10 4 5 6 etc...

I inserted your code, but nothing gets displayed at all. I just tried... function get_data($table_name){ /* $result=mysql_query("SELECT * FROM $table_name"); $num=mysql_num_rows($result); $i=0; while($i < $num){ $fields=mysql_fetch_field($result,$i); $display_fields=mysql_result($result,$i,"$fields->name"); echo " " . $display_fields . "<br />"; $i++; }; */ $result = mysql_query("SELECT * FROM $table_name"); while($row = mysql_fetch_object($result)) { //or mysql_fetch_assoc() echo $row->name; //or $row['name'] }; };

I can get the field names okay. I can't seem to get the while() to print out all the records of the given argument. function get_data($table_name){ //Query $result=mysql_query("SELECT * FROM $table_name"); //Count records $num=mysql_num_rows($result); $i=0; while($i < $num){ //Get field names $fields=mysql_fetch_field($result,$i); //Trying to do: Put query results recursively into a $var $display_fields=mysql_result($result,$i,$fields->name); //Display data echo $display_fields; $i++; }; }; My Problem: I can't figure out how to add a count to the $display_fields so I get a unique $var at every iteration. Right now, this function only prints out a single record. I tried to do $display_fields.$i=mysql_result($result,$i,$fields->name); but to no avail...