SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INSERT INTO troubleshooting

    Hi,

    I had a similar problem last week with such a query except that one was caused through a date field problem. This time the data I want to insert into a database isn't being inserted at all.

    Here is the code and query I am currently using:

    PHP Code:
     if ($_POST['add_vacancy']) {
         
    $loc_id $_POST['vacancy_location'];
         
    $role_id $_POST['vacancy_role'];
         
    $vac_reference $_POST['vac_reference'];
         
    $vac_telephone $_POST['vac_telephone'];
         
    $vac_hour_from $_POST['vac_hour_from'];
         
    $vac_hour_to $_POST['vac_hour_to'];
         
    $vac_pay $_POST['vac_pay'];
         
    $vac_title $_POST['vac_title'];
         
    $vac_description $_POST['vac_description'];
         
         
    /*
             Get POST varaibles, validate them
             and INSERT them INTO the database
         */
         
         
    $sql "
                 INSERT INTO vacancy
                 (loc_id, role_id, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added) 
                 VALUES ( 
                 '" 
    $loc_id "', 
                 '" 
    $role_id "', 
                 '" 
    $vac_title "', 
                 '" 
    $vac_description "', 
                 '" 
    $vac_telephone "' 
                 '" 
    $vac_reference "'
                 '" 
    $vac_hour_from "'
                 '" 
    $vac_hour_to "'
                 '" 
    $vac_pay "'
                 '" 
    date("j/n/Y") . "'
                 ) 
         " 
    or die(mysql_error());
         
         echo 
    $sql;
         
         
    //Inform the customer
         /*if (mysql_query($sql)) {
             echo "Success";
         } else {
             echo "Failed";
         }*/
         
         
     

    I know that the variables are being collected through $_POST because I was echoing them out before.

    No error is returned either and no data goes in the database.

    What am I doing wrong if anyone can help?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't know why no data is being inserted (echo out your SQL statement and let's have a look at it) but i do know that for mysql dates, the format must be year-month-day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can try this

    PHP Code:
    $sql mysql_query("INSERT INTO vacancy
    (loc_id, role_id, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added)"
    .
    "VALUES ('$loc_id', '$role_id','$vac_title','$vac_description','$vac_telephone','$vac_reference','vac_hour_from','$vac_hour_to','$vac_pay','date("j/n/Y")')"); 
    or die(
    mysql_error($sql)); 

  4. #4
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem: It's not being executed
    As maize wrote, you need to run:
    mysql_query($sql) or die();

    and not

    $sql or die();
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  5. #5
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what the SQL query looks when I echo it:


    INSERT INTO vacancy (loc_id, role_id, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added) VALUES ( 'Non Selected', 'Non Selected', 'Vacancy Title', 'Vacancy Description', 'Vacancy Telephone' 'Vacancy Reference' 'Hours (From)' 'Hours (To)' 'Pay' '24/8/2005' )

  6. #6
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now i'm getting this when I run mysql_query:

    Column count doesn't match value count at row 1

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    there be some commas missing, that query should get errors

    also, some of those values don't look like real values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The values that you see are just placeholder values in the text boxes which disappear with an onFocus event.

  9. #9
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what happens when you run the following...use your database connection.

    PHP Code:
    <?php require_once('yourdatabaseconnectionscript.php');

    $conn db_connect();
          if (!
    $conn)
            return 
    'Could not connect to database server - please try later.';
            
    $loc_id $_POST['vacancy_location'];
    $role_id $_POST['vacancy_role'];
    $vac_title $_POST['vac_title'];
    $vac_description $_POST['vac_description'];
    $vac_telephone $_POST['vac_telephone'];
    $vac_reference $_POST['vac_reference'];
    $vac_hour_from $_POST['vac_hour_from'];
    $vac_hour_to $_POST['vac_hour_to'];
    $vac_pay $_POST['vac_pay'];

    $sql mysql_query("INSERT INTO vacancy 
    (vacancy_location, vacancy_role, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added)"

    "VALUES ('$loc_id', '$role_id','$vac_title','$vac_description','$vac_telephone','$vac_reference','**vac_hour_from','$vac_hour_to','$vac_pay','date("j/n/Y")')"); 
    if(
    false === $rs mysql_query($sql))
    {
    debug($sql);
    }
    else
    {
    ?>Record Updates<?
    }

    function 
    debug($sql)
    {
    if(
    $_REQUEST['debug']) print $sql;


    mysql_close();
    ?>

  10. #10
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just get this:

    Fatal error: Call to undefined function: debug() in c:\inetpub\wwwroot\starcorporate\deployment\staff\add_vacancy.php on line 57

  11. #11
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you post your entire code, and the code you are using for your form?

  12. #12
    SitePoint Enthusiast adriancid's Avatar
    Join Date
    Aug 2005
    Location
    Versalles, Santiago de Cuba, Cuba
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try moving the debug function to the top of your page.

  13. #13
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, my mistake,

    try this

    PHP Code:
    <?php require_once('yourdatabaseconnectionscript.php'); 

    $conn db_connect(); 
    if (!
    $conn
    return 
    'Could not connect to database server - please try later.'

    $loc_id $_POST['vacancy_location']; 
    $role_id $_POST['vacancy_role']; 
    $vac_title $_POST['vac_title']; 
    $vac_description $_POST['vac_description']; 
    $vac_telephone $_POST['vac_telephone']; 
    $vac_reference $_POST['vac_reference']; 
    $vac_hour_from $_POST['vac_hour_from']; 
    $vac_hour_to $_POST['vac_hour_to']; 
    $vac_pay $_POST['vac_pay']; 

    $sql mysql_query("INSERT INTO vacancy 
    (loc_id, role_id, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added)"

    "VALUES ('$loc_id','$role_id','$vac_title','$vac_description','$vac_telephone','$vac_reference','vac_hour_from','$vac_hour_to','$vac_pay','date("j/n/Y")')"); 
    (
    mysql_query($sql));
    mysql_close();
    ?>

  14. #14
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still get:


    Column count doesn't match value count at row 1

  15. #15
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by maize
    can you post your entire code, and the code you are using for your form?
    PHP Code:
    <?php
    require("../include/connection.php");
    require(
    "../include/errorFunc.php");
    if (
    $_POST['add_vacancy']) {
    $loc_id $_POST['vacancy_location'];
    $role_id $_POST['vacancy_role'];
    $vac_reference $_POST['vac_reference'];
    $vac_telephone $_POST['vac_telephone'];
    $vac_hour_from $_POST['vac_hour_from'];
    $vac_hour_to $_POST['vac_hour_to'];
    $vac_pay $_POST['vac_pay'];
    $vac_title $_POST['vac_title'];
    $vac_description $_POST['vac_description'];
     
    /*
    Get POST varaibles, validate them
    and INSERT them INTO the database
    */
     
    $sql mysql_query("
    INSERT INTO vacancy
    (loc_id, role_id, vac_title, vac_description, vac_telephone, vac_reference, vac_hour_from, vac_hour_to, vac_pay, vac_date_added) 
    VALUES ( 
    '" 
    $loc_id "', 
    '" 
    $role_id "', 
    '" 
    $vac_title "', 
    '" 
    $vac_description "', 
    '" 
    $vac_telephone "' 
    '" 
    $vac_reference "'
    '" 
    $vac_hour_from "'
    '" 
    $vac_hour_to "'
    '" 
    $vac_pay "'
    '" 
    date("j/n/Y") . "'

    "
    ) or die(mysql_error());
     
    echo 
    $sql;
     
    //Inform the customer
    /*if (mysql_query($sql)) {
    echo "Success";
    } else {
    echo "Failed";
    }*/
     
     
    } else {
    ?>
    <form action="<?php echo $_SERVER['PHP_SELF'?>" method="post" name="add_vacancy">
    <table border="1" align="left" cellpadding="5" cellspacing="0">
    <tr>
    <td>Select the Vacancies Location</td>
    <td>
        <?php
         $sql 
    mysql_query("SELECT loc_name FROM location");    
         echo 
    "<select name=\"vacancy_location\" class=\"list\">";
         echo 
    "<option selected value=\"Non Selected\" name=\"default\">Vacancy Location</option>";
         while (
    $foo mysql_fetch_array($sql)) {
            
    $loc $foo['loc_name'];
            echo (
    "<option");
            if (
    $HTTP_POST_VARS["loc_name"] == $loc) echo (" selected");
             echo (
    ">$loc");
            }
         echo 
    "</select>";
        
    ?> 
    </td>
    </tr>
    <tr>
    <td>Select the Vacancies Role</td>
    <td>
        <?php
         $sql2 
    mysql_query("SELECT role FROM role");    
         echo 
    "<select name=\"vacancy_role\" class=\"list\">";
         echo 
    "<option selected value=\"Non Selected\" name=\"default\">Vacancy Role</option>";
         while (
    $foo2 mysql_fetch_array($sql2)) {
            
    $role $foo2['role'];
            echo (
    "<option");
            if (
    $HTTP_POST_VARS["parent_vacancy"] == $role) echo (" selected");
             echo (
    ">$role");
            }
         echo 
    "</select>";
        
    ?>
    </td>
    </tr>
    <tr>
    <td><label for="vac_reference">Vacancy Reference</label></td>
    <td><input type="text" name="vac_reference" id="vac_reference" value="<?php if (isset($vac_reference)) { echo $vac_reference;} else { echo "Vacancy Reference"; }?>" onfocus="value=''"></td>
    </tr>
    <tr>
    <td><label for="vac_telephone">Vacancy Contact</label></td>
    <td><input type="text" name="vac_telephone" id="vac_telephone" value="<?php if (isset($vac_telephone)) { echo $vac_telephone;} else { echo "Vacancy Telephone"; }?>" onfocus="value=''"></td>
    </tr>
    <tr>
    <td><label for="vac_hour_from">Hours (From)</label></td>
    <td><input type="text" name="vac_hour_from" id="vac_hour_from" value="<?php if (isset($vac_hour_from)) { echo $vac_hour_from;} else { echo "Hours (From)"; }?>" onfocus="value=''"></td>
    </tr>
    <tr>
    <td><label for="vac_hour_to">Hours (To)</label></td>
    <td><input type="text" name="vac_hour_to" id="vac_hour_to" value="<?php if (isset($vac_hour_to)) { echo $vac_hour_to;} else { echo "Hours (To)"; }?>" onfocus="value=''"></td>
    </tr> 
    <tr>
    <td><label for="vac_pay">Pay</label></td>
    <td><input type="text" name="vac_pay" id="vac_pay" value="<?php if (isset($vac_pay)) { echo $vac_pay;} else { echo "Pay"; }?>" onfocus="value=''"></td>
    </tr>
    <tr>
    <td><label for="vac_title">Vacancy Title</label></td>
    <td><input type="text" name="vac_title" id="vac_title" value="<?php if (isset($vac_title)) { echo $vac_title;} else { echo "Vacancy Title"; }?>" onfocus="value=''"></td>
    </tr>
    <tr>
    <td valign="top"><label for="vac_description">Description</label></td>
    <td><textarea cols="50" rows="10" name="vac_description" id="vac_description" onfocus="value=''"><?php if (isset($vac_description)) { echo $vac_description;} else { echo "Vacancy Description"; }?></textarea></td>
    </tr> 
    <tr>
    <td rowspan="2">
        <input type="submit" name="add_vacancy" id="add_vacancy">
    </td>
    </tr>
    </table>
    </form>
    <?php
    }
    ?>

  16. #16
    SitePoint Zealot
    Join Date
    Aug 2005
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe you're missing some commas in your SQL statement in the VALUES section. That's why you're getting the "column count doesn't match" error.

  17. #17
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, thanks a lot. It's now working.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Snortles
    I believe you're missing some commas in your SQL statement in the VALUES section. That's why you're getting the "column count doesn't match" error.
    nicely spotted -- see post #7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •