Results 1 to 5 of 5

Thread: Alter a MySQL table to add a column using PHP

  1. #1
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cool Alter a MySQL table to add a column using PHP

    I'm having big problems with this one.

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

    PHP Code:
        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.

  2. #2
    Join Date
    Oct 2006
    Posts
    94
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    Ok, this I have now fixed.

    PHP Code:
    @mysql_query("ALTER TABLE `gallery` ADD `$alias` VARCHAR( 100 )") or die(mysql_error()); 
    My silly mistakes!! But all working perfect now.

  3. #3
    Join Date
    Mar 2009
    Location
    Chennai, India
    Posts
    77
    Thanks
    16
    Thanked 7 Times in 6 Posts

    Default

    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:

    Code:
        $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:


    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>";
                             }
                            
                            
                            
                            
                         }
                         
                         
                            
                            
                        }
                        
                                        
                        ?>

  4. #4
    Join Date
    Mar 2009
    Location
    Chennai, India
    Posts
    77
    Thanks
    16
    Thanked 7 Times in 6 Posts

    Default

    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?

  5. #5
    Join Date
    Jun 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking Solution to using variable name in sql statement to add a column to table!!!

    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);

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •