Jump to content

Delete Column in a Table


kamiza

Recommended Posts

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]
Link to comment
Share on other sites

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!)?
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.