Delete Column in a Table

[kamiza]

Having trouble with deleting a column from a table

I can delete the table now (finally) but not the col. now

Heres the code

[code]
// VIEW FORM
} elseif ($_POST['view_form']) {
   include 'connect.php';
   $cur_form = $_POST['current_forms'];

   $query = "SELECT * FROM $cur_form";
   $result = mysql_query($query);
   $numfields = mysql_num_fields($result);

   echo "Viewing form $cur_form";
   ?>
   <table border='2' cellpadding='3'><tr>
   <form method='post' action='database.php'>
   <? for ($i=0; $i < $numfields; $i++)  {  ?> // Header
   <th> <? echo mysql_field_name($result, $i); ?>
   <input type='submit' name='delete_col' value='X' ><input type='hidden' name=<? echo $i; ?> value=<? echo mysql_field_name($result, $i); ?>>
   </th>
   <? } ?>
   <input type='hidden' name='current_forms' value=<? echo $cur_form; ?>>
   <th><input type='submit' name='delete_table' value='X' ></th>
   </form></tr>
   <?
   while ($row = mysql_fetch_row($result)) {  // Data
   echo '<tr><td>'.implode($row,'</td><td>')."</td></tr>";
   }

   echo "</table>";

// DELETE TABLE
} elseif ($_POST['delete_table']) {
   include 'connect.php';
   $cur_form = $_POST['current_forms'];

   $query = "DROP TABLE $cur_form";
   $result = mysql_query($query);

   echo 'Table '.$cur_form.' deleted';

// DELETE COLUMN
} elseif ($_POST['delete_col']) {
   include 'connect.php';
   $cur_form = $_POST['current_forms'];
   $cols = $_POST[$i];

   $query = 'ALTER TABLE $cur_form DROP $cols';
   $result = mysql_query($query);

   echo '('.$cols.') deteled from '.$cur_form;
}
[/code]

view_form = viewing the table
delete_table = deleting the table (Works)
delete_col = not working, shows: () deleted from [table name]

[fenway]

Not sure what to tell you -- I assume you have the ALTER TABLE priviledge for that DB user. What version of MySQL are you running, and what this error you get following the attempt to drop the column (use mysql_error!)?

[kamiza]

[!--quoteo(post=354619:date=Mar 13 2006, 03:37 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 13 2006, 03:37 PM) [snapback]354619[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Not sure what to tell you -- I assume you have the ALTER TABLE priviledge for that DB user. What version of MySQL are you running, and what this error you get following the attempt to drop the column (use mysql_error!)?
[/quote]

Right now using mysql on my pc, not my websites...my pc's mysql is verson 5.0.18 and websites is 4.1.14 (tho havn't tried it on website yet)
So I also have the priviledges

Going to try that mysql error now
EDIT:
Alright I got mysql error to spit out somethings, here are 2 things it said when tried some stuff
When I have this:
[code]$query = 'ALTER TABLE $cur_form DROP $cols';[/code]
It says:
1146: Table 'databases.$cur_form' doesn't exist

and When I have this: (NOTE: use of quotes " instead of ')
[code]$query = "ALTER TABLE $cur_form DROP $cols";[/code]
It says:
1064: 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 '' at line 1

[fenway]

A few things.

First, if you use single quotes, PHP won't interpolate the variables, so that's not going to work -- you need double quotes for this.

Second, it's very dangerous to take POST input and dump it directly into a query -- I don't see any check for special characters, and you're not using backticks around the table or column name, so a malicious user could potentially do a lot of damage to the DB.

Third, what does doubled-quoted query echo as? Are you $cols has a value? Post the query after interpolation here.

[kamiza]

[!--quoteo(post=354664:date=Mar 13 2006, 05:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 13 2006, 05:36 PM) [snapback]354664[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, if you use single quotes, PHP won't interpolate the variables, so that's not going to work -- you need double quotes for this.
[/quote]

Alright thanks for that info - so that means somethings wrong with a " on line? I've checked and they are all closed

[!--quoteo(post=354664:date=Mar 13 2006, 05:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 13 2006, 05:36 PM) [snapback]354664[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Second, it's very dangerous to take POST input and dump it directly into a query -- I don't see any check for special characters,
[/quote]


Oh ok, yea I'll make sure I'll put in checks once it goes on my website

[!--quoteo(post=354664:date=Mar 13 2006, 05:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 13 2006, 05:36 PM) [snapback]354664[/snapback][/div][div class=\'quotemain\'][!--quotec--]
and you're not using backticks around the table or column name
[/quote]

Oh yea, I think I saw inside myphpadmin that it used `, wasn't sure what it was for, so thats important?

[!--quoteo(post=354664:date=Mar 13 2006, 05:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Mar 13 2006, 05:36 PM) [snapback]354664[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Third, what does doubled-quoted query echo as? Are you $cols has a value? Post the query after interpolation here.
[/quote]

Yea I don't think $cols has a value but I don't know why - oh wait OHHH wait..I know something I forgot, hehe silly me, I did it early with some code, forgot to do it here - so yea this what it ouputs
() deteled from movies
$cols is suppose to be between (), so its empty but I know why now

[kamiza]

Yea ok good

So I added the loop I needed

Changed

[code]$cols = $_POST[$i];[/code]

to

[code]for ($i=0; $i < 20; $i++) { if ($i == $_POST[$i]) { $cols = $_POST[$i]; } }[/code]

forgot since using $i, needed a new $i in this function so yea...

So now $cols has a value which is:
(mysql_field_name(Resource id #4, 0))

and now outputs
(mysql_field_name(Resource id #4, 0)) deteled from new

but it still not working - no matter which column I delete it says that
the mysql_error spits out :
1064: 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 '(Resource id #4, 0)' at line 1

Lets try 1 more idea...
EDIT:
Yea ok I can get it to delete a column now, but its either the first or the last one *sigh*
If you have a string of submit buttons IE:
Name [X] | Age [X] | Date [X]
So thats 3 columns in a table, and [X] are submit buttons that will delete the corrosponding column

How would you find out which one you clicked?

[fenway]

Just go through all of the checkboxes, see which ones are checked -- usually, unchecked ones arent submitted anyway. You'll have to check the syntax from dropping multiple columns at once.

[kamiza]

oh hmm...use checkboxes and then have 1 submit button?

that could work then, could drop multiple-columns too then yea...
or use option buttons

i'll try that then thanks


(I appricaite all the help thanks, been on 3 different forums and this only 1 that has replied, and I posted 3 of my own topics and replied to 2 on this other forum and that was 3 weeks ago, not 1 has been answered lol)

[kamiza]

Yea ok I did option buttons but its same as if it was submit button as in, still don't know which one I clicked/selected

It does but it uses the last entry

Maybe an array of buttons, instead of of just x amt of buttons...I'll have to look into how to make arrays

------

Well I finally got it
Using checkboxes and 1 delete button and so now can delete multiple columns at once

Thanks for all the help fenway

[fenway]

Glad you got it working -- could you post your solution here in case anyone else runs into a similar problem?