Professional Web Applications Themes

php insert command failure - MySQL

I have a mySQL database I am accessing via php using the code if(!$connection = mysql_pconnect("localhost",DBUSER,DBUSERPW)) { Fatalerror($connection); } then calling if(!mysql_select_db(DATABASE,$connection)) to select the database. where DBUSER,DBUSERPW and DATABASE are defined variables These functions appear to operate correctly. If I run SELECT queries, using $result=mysql_query($query,$connection); I get the correct data returned from the database. However, running INSERT queries fail. mysql_errno($connection) and mysql_error() both return empty strings, giving no indication of the cause of the error. I have checked the insert query syntax by cutting and pasting into mySQL at the command line. mysql -h returns mysql Ver 14.7 Distrib ...

  1. #1

    Default php insert command failure

    I have a mySQL database I am accessing via php using the code
    if(!$connection = mysql_pconnect("localhost",DBUSER,DBUSERPW))
    {
    Fatalerror($connection);
    }

    then calling
    if(!mysql_select_db(DATABASE,$connection)) to select the database.

    where DBUSER,DBUSERPW and DATABASE are defined variables

    These functions appear to operate correctly.
    If I run SELECT queries, using $result=mysql_query($query,$connection);
    I get the correct data returned from the database.

    However, running INSERT queries fail.
    mysql_errno($connection) and mysql_error() both return empty strings,
    giving no indication of the cause of the error.

    I have checked the insert query syntax by cutting and pasting into mySQL
    at the command line.

    mysql -h returns
    mysql Ver 14.7 Distrib 4.1.12, for mandriva-linux-gnu (i586) using
    readline 5.0
    ..... cut ....
    auto-rehash TRUE
    character-sets-dir (No default value)
    default-character-set latin1
    compress FALSE
    database (No default value)
    delimiter ;
    vertical FALSE
    force FALSE
    named-commands FALSE
    local-infile FALSE
    no-beep FALSE
    host (No default value)
    html FALSE
    xml FALSE
    line-numbers TRUE
    unbuffered FALSE
    column-names TRUE
    sigint-ignore FALSE
    port 3306
    prompt mysql>
    quick FALSE
    raw FALSE
    reconnect TRUE
    socket (No default value)
    ssl FALSE
    ssl-key (No default value)
    ssl-cert (No default value)
    ssl-ca (No default value)
    ssl-capath (No default value)
    ssl-cipher (No default value)
    table FALSE
    debug-info FALSE
    user (No default value)
    safe-updates FALSE
    i-am-a-dummy FALSE
    connect_timeout 0
    max_allowed_packet 16777216
    net_buffer_length 16384
    select_limit 1000
    max_join_size 1000000
    secure-auth FALSE

    Does this indicate that my php or mySQL setup is wrong ?
    I searched google groups, with no luck.
    Thanks in advance
    Carlton.

    Carlton Guest

  2. #2

    Default Re: php insert command failure


    "Carlton" <demon.co.uk> wrote in message
    news:f61a5d$j57$1$demon.co.uk... 

    Perhaps you do not have the proper permissions?


    Jon Guest

  3. #3

    Default Re: php insert command failure

    Carlton wrote: 

    If you will have many users using your script, switch to mysql_connect() or
    you risk to run out of "connection slots".
     

    I suggest you echo out the insert query and then try that query manually.
    I do suspect, as Jon, that you may not have permissions with the selected
    DBUSER to do the inserts you want.

     

    If php had been the problem, then you got an "undefined function" error and if
    mysql had been wrongly setup you most likely wouldn't been able to connect to
    it. The great benefit of running Linux or BSD is that the default settings
    will ensure that things works (no need to hassle with settings like microsoft
    users has to get things to work, of course use the official packages from your
    distribution).
    If you have a low number of "connection slots" and use mysql_pconnect(), it's
    known to give troubles, specially if you use more than one database at the
    same time (not talking about multiple servers), as mysql_pconnect() will
    remember the last database selected and will use that by default.

    You know there is alt.php.sql that is for this kind of questions.


    --

    //Aho
    J.O. Guest

  4. #4

    Default Re: php insert command failure

    Carlton wrote: 

    First of all, don't use mysql_pconnect(). It will cause you more
    problems than it will solve.

    You may not have INSERT privileges. But I would expect mysql_errno to
    return a value (I forget exactly which one offhand).

    But you haven't shown the actual code you're using, so it's impossible
    to tell what the problem might be.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: php insert command failure

    Jon Slaughter wrote: 
    >
    > Perhaps you do not have the proper permissions?
    >
    >[/ref]
    Running mySQL from the command line, using the same user and password
    the INSERT query runs without error. php will then find the inserted
    data using the mysql_query function. I would assume this means the user
    has suitable permissions.
    I will remove the pconnect function as advised.
    Carlton Guest

  6. #6

    Default Re: php insert command failure

    Jon Slaughter wrote: 
    >
    > Perhaps you do not have the proper permissions?
    >
    >[/ref]
    I have checked the privilege data from mysql, all appears OK to me.
    mysql> SHOW PRIVILEGES;
    +-------------------------+--------------------------+-------------------------------------------------------+
    | Privilege | Context | Comment
    |
    +-------------------------+--------------------------+-------------------------------------------------------+
    | Alter | Tables | To alter the
    table |
    | Create temporary tables | Databases | To use CREATE
    TEMPORARY TABLE |
    | Create | Databases,Tables,Indexes | To create new
    databases and tables |
    | Delete | Tables | To delete
    existing rows |
    | Drop | Databases,Tables | To drop databases
    and tables |
    | File | File access on server | To read and write
    files on the server |
    | Grant option | Databases,Tables | To give to other
    users those privileges you possess |
    | Index | Tables | To create or drop
    indexes |
    | Insert | Tables | To insert data
    into tables |
    | Lock tables | Databases | To use LOCK
    TABLES (together with SELECT privilege) |
    | Process | Server Admin | To view the plain
    text of currently executing queries |
    | References | Databases,Tables | To have
    references on tables |
    | Reload | Server Admin | To reload or
    refresh tables, logs and privileges |
    | tion client | Server Admin | To ask where the
    slave or master servers are |
    | tion slave | Server Admin | To read binary
    log events from the master |
    | Select | Tables | To retrieve rows
    from table |
    | Show databases | Server Admin | To see all
    databases with SHOW DATABASES |
    | Shutdown | Server Admin | To shutdown the
    server |
    | Super | Server Admin | To use KILL
    thread, SET GLOBAL, CHANGE MASTER, etc. |
    | Update | Tables | To update
    existing rows |
    | Usage | Server Admin | No privileges -
    allow connect only |
    +-------------------------+--------------------------+-------------------------------------------------------+

    mysql> SHOW GRANTS for carltonlocalhost;
    +------------------------------------------------------------------------------------------------------------------+
    | Grants for carltonlocalhost
    |
    +------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'carlton''localhost' IDENTIFIED BY
    PASSWORD '67016e577800cc48' WITH GRANT OPTION |
    | GRANT ALL PRIVILEGES ON `picwin`.* TO 'carlton''localhost'
    |
    | GRANT ALL PRIVILEGES ON `picturewin`.* TO 'carlton''localhost'
    |
    +------------------------------------------------------------------------------------------------------------------+
    I believe this indicates I have sufficient privileges to access the
    database. I have also added mysql to my user group, but still no access.

    If the problem is with access rights, where is the problem most likely
    to be?
    regards,
    Carlton.
    Carlton Guest

  7. #7

    Default Re: php insert command failure

    Jerry Stuckle wrote: 
    >
    > First of all, don't use mysql_pconnect(). It will cause you more
    > problems than it will solve.
    >
    > You may not have INSERT privileges. But I would expect mysql_errno to
    > return a value (I forget exactly which one offhand).
    >
    > But you haven't shown the actual code you're using, so it's impossible
    > to tell what the problem might be.
    >[/ref]
    SHOW PRIVILEGES indicates I have insert access to the tables, permitting
    me to manually add data at the mysql> prompt.
    Carlton Guest

  8. #8

    Default Re: php insert command failure

    Carlton wrote: 
    >>
    >> First of all, don't use mysql_pconnect(). It will cause you more
    >> problems than it will solve.
    >>
    >> You may not have INSERT privileges. But I would expect mysql_errno to
    >> return a value (I forget exactly which one offhand).
    >>
    >> But you haven't shown the actual code you're using, so it's impossible
    >> to tell what the problem might be.
    >>[/ref]
    > SHOW PRIVILEGES indicates I have insert access to the tables, permitting
    > me to manually add data at the mysql> prompt.[/ref]

    This is the user you are using in your connect? That is do you have in
    your program:

    define('DBUSER', 'carlton');

    How do you know the insert fails? If there were a problem mysql_errno()
    and mysql_error() would return a value.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. php exec command failure with mysqldump... | mysql...
    By Fritz in forum PHP Development
    Replies: 2
    Last Post: February 8th, 01:53 PM
  2. Failure converting doc/ps to pdf - offending command: setcmykoverprint
    By Don_Parda@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 5
    Last Post: June 14th, 03:39 PM
  3. bulk insert command for vb?
    By Trint in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 5th, 03:42 PM
  4. ping command failure
    By Cathy in forum Windows Networking
    Replies: 3
    Last Post: July 27th, 08:01 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139