Alter a MySQL table to add a column using PHP

[smithster]

I'm having big problems with this one.

Ive tried alsorts. Here's my coding...

    foreach( $idlist as $key => $value){ 
mkdir("../img/profilepics/$alias", 0700);
      $copy = mysql_fetch_array(mysql_query("SELECT * FROM `apps` WHERE `id`='" . $value . "'")) or die(mysql_error()); 
$result = mysql_query("ALTER TABLE `gallery` ADD $alias VARCHAR( 100 )") or die(mysql_error()); 

And here's the error...

Warning: mkdir(../img/profilepics/) [function.mkdir]: File exists in /home/psmith83/public_html/clan/admin/apps.php on line 59
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 'VARCHAR( 100 )' at line 1

Basically an application is received and if I approve it, then it moves the data to an approved table and it creates a new column in another table which is named as $alias being a variable. It also creates a directory also named as $alias.

Thing is, it doesn't work!! And I cannot find the solution as everywhere I look, it seams I am using the correct script to do what I want but still the same error everytime.

Please, if anyone can see the stupid, most likely small error that I've made, let me know!!

Thanks in advance.

Smithster.

[smithster]

Ok, this I have now fixed.

@mysql_query("ALTER TABLE `gallery` ADD `$alias` VARCHAR( 100 )") or die(mysql_error()); 

My silly mistakes!! But all working perfect now.

[borris83]

Please anyone help.. I have the exact same error and I am trying to search google for past half an hour because If I just got this one line to be working then I would have finished some important feature in a project that I am working on:

The line in which i am getting the error is:

    $query_string = "ALTER TABLE inventory ADD '" .$product_field . "' TINYINT NULL ";
                     
                     $query = mysql_query($query_string);

The error:

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 ''anyword_or_any_phrase' TINYINT NULL' at line 1

Full code:

   <form action = "newproduct.php" method = "POST">
                        <div><b>Enter the name of the product: </b><input type = "text" name = "product_name"></div>
                         <div> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                               &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                         <input type = "submit" name = "submit" value = " Add " ></div>
                        
                    </form>
                    
                    
                    <?php
                    
                    if(isset($_POST['submit']))
                    
                    {
                     $product_name =  $_POST['product_name'];
                     $product_field = mb_strtolower($product_name );
                     $product_field = preg_replace("/\s+/", "_", $product_field);
                     
                     $query_string = "ALTER TABLE inventory ADD '" .$product_field . "' TINYINT NULL ";
                     
                     $query = mysql_query($query_string);
                     
                     if(!$query){
                        die(mysql_error());
                     }else
                     {
                        $query2 = mysql_query("INSERT INTO products (product_field, product_name)VALUES ('$product_field', '$product_name' ) ");
                         
                         if(!$query) {die(mysql_error());}
                         else
                         {
                            echo " <p style = \" color:CC0033; font-weight:bold  \"> Product has been added successfully</p>";
                         }
                        
                        
                        
                        
                     }
                     
                     
                        
                        
                    }
                    
                                    
                    ?>

[borris83]

Just got it fixed, but I just came to know something after 1 month of programming...

the single quote didn't work in my above SQL query but this ` character which is on the tilde key works...

But I have been using single quote in my sql queries all the time..

WHY DIDN'T IT WORK THIS TIME?

[polyglot]

Hallelujah! I have found a solution!!!!

I have been struggling with this problem for literally days and finally found a solution, with borris83's help (thanks, Dude!). I thought I would post the solution I found so that others don't have to go through the same headaches I did...

I am a total newbie when it comes to php and mysql, having done some amateur programming with HTML, javascript, css, and actionscript. What I wanted to do was create a php app that would allow the user to select a date from drop-down boxes (month, date, and year) in an HTML form. This would result in a variable representing the date that would then be used as the name of a column to be added to a table in a database. For example, the value of the variable might be "2011-06-23" (representing the date June 23rd, 2011). This would be the name that the database would assign to a new column in the table. I found in my earlier attempts that my syntax was wrong where I had the variable name ($mydatevar):

$sql = "ALTER TABLE attendance ADD $mydatevar DATE UNIQUE;";


When I had this line, sql was not adding the new column to the table. After an obscene number of attempts at tweaking the variable in the statement to try to get it to work I almost gave up.

Then in a last-ditch Hail Mary attempt I came across this thread and found the answer; as borris83 mentioned, there did seem to be a problem with the particular characters used in the statement. The accent mark character on the tilde key did work - although nowhere in my extensive research on the internet did I find anything about having to use this character in my syntax when setting up my sql statement (until I came to borris83's post). What a pain!!!

Here for your viewing pleasure I have put the code that worked for me - I am now able to add new columns to my database table with the name representing the specific date chosen by the user. I hope that you find the code helpful and informative! (Note the use of the accent mark characters surrounding the variable in the sql statement.)


$mydatevar = $yearvalue . "-" . $monthvalue . "-" . $dayvalue;

$sql = "ALTER TABLE attendance ADD `$mydatevar` DATE UNIQUE;";

$result = @mysql_query($sql, $conn);