SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql_query INSERT INTO fails, but no error is returned.

    Hi all

    My function below attempts to insert values into db table. The table is never updated and mysql returns no errors. The table itself remains empty.

    I have found many proposed solutions for similar situations (including in this forum), but none of them have helped. For example, using back ticks instead of single quotes around the values, INSERT might be sending empty values, values might not be reaching the function - along with a few other suggested fixes which didn't work.

    Unfortunately, I don't have direct access to the db. So, I'm taking the clients word that the table is empty, she seems to know what she's doing, I don't have any reason to doubt what she says.

    Can anyone throw some new light on this for me - thanks

    PHP Code:
    function store_details($db_server$db_user$db_pass$db_name$ftp_server$subject$skipfile$skipdir$rename_file$alert$from$message$createLog$cronlogpath){

    $date date ("dMy");
    $time date("H:i");

        
    $site_table 'ssa_'.str_replace('-','$',str_replace('.','_',$ftp_server)).'_site';
        
    $con mysql_connect($db_server,$db_user,$db_pass)or die('Unable to connect to MySQL server: '.$db_server.'<br>Please check that the following details are correct:<br>
            db server name<br>
            db user name<br>
            db password<br>
            <a href="index1.php?check_db_details=Y">Click to reload form</a>'
    );
        
    mysql_select_db($db_name$con)or die('Connected but, unable to select db! '.mysql_error());
        
    $query "TRUNCATE TABLE $site_table";
        
    mysql_query($query)or die('Failed to empty site table<br>'.mysql_error());

        
    $query ="INSERT INTO $site_table (
            email_subj,
            email_alert,        
            skip_files,
            skip_dir,
            from_addr,
            email_header,
            cron_path,
            rename_file,
            SSA_log,
            date,
            time
           )
            VALUES (
            '
    $subject',
            '
    $alert',        
            '
    $skipfile',
            '
    $skipdir',
            '
    $from',        
            '
    $message',
            '
    $cronlogpath',
            '
    $rename_file',
            '
    $createLog',
            '
    $date',
            '
    $time')";
         

        
    mysql_query($query) or die('Failed to update site table. Mysql returned the following:<br><br>'.mysql_error());

        
    mysql_close($con)or die(mysql_error());


    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do you have error checking enabled to see if you just have some simple php error not related to the db actions?

  3. #3
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi ginerjm

    I had error checking enabled. Still nothing in the db and no error revealed.

    I'm about to try a different approach by calling a separate function to do a bit of sanitisation and then do the query. The new function is below.

    PHP Code:
    $inserts //is an array where the keys are the column names and the values are the values to be inserted 
    PHP Code:
    $table //is the name of the table 
    PHP Code:
    function mysql_insert($table$inserts) {
        
    $values array_map('mysql_real_escape_string'array_values($inserts));
        
    $keys array_keys($inserts);        
        return 
    mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`'$keys).'`) VALUES (\''.implode('\',\''$values).'\')');

    It works on all the servers I have access to - just hoping it works on the clients server.
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Echo the qry statement and br sure it looks good

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,812
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Since you need to write all the mysql_ calls to use either mysqli_ or PDO it may be worthwhile doing that rewrite first and see if that makes any difference to whether the code works or not. Doing the rewrite will also mean that you will be looking at the code a different way and so may make it easier to spot the error.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys

    felgall - my entire script will need updating to msqli/PDO, as soon as I get to grips with how to change it that is. I'm not sure what's involved at this time.

    I just want to get this current problem out of the way first.

    Regards
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Haven't seen you post your actual completed query statement. Have you at least looked at it to see if it is syntactically correct?

  8. #8
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    728
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    You might give this a go. Return $sql string for testing query string.
    PHP Code:
    function mysql_insert($table$inserts) {
        
    $values array_map('mysql_real_escape_string'array_values($inserts));
        
    $keys array_keys($inserts);  
        
    $sql "INSERT INTO `$table` (`".implode("`,`"$keys)."`) VALUES ('".implode('\',\''$values)."')";
        
    //return $sql;
        
    return mysql_query($sql);


  9. #9
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys

    ginerjm, my query is in full in my first message. However ...

    I've found the cause of the problem. The failed mysql_query() turned out to be a red herring. The problem is that the function to store the details is never called.

    For some reason on this server, the IF statement that checks for form submission is stopping the function call. I've tried the following checks:
    PHP Code:
    if($_POST['submit']){
      
    store_details( ... );

    and

    PHP Code:
    if(isset($_POST['submit'])){
      
    store_details( ... );

    The Submit button name is 'submit', but the check always fails, which is weird because my script has a 2nd form on a different page with exactly the same check, which works perfectly. And both forms work perfectly on all other servers.

    I'm stuck for ideas - this simply shouldn't be happening

    Can anyone shed any light on this for me?
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Dump out the POST array to see if submit is a value within it. There used to be an issue where if you clicked submit it sent it as normal but if you hit the Enter key - it didnt.
    Dont know if this is still an issue but what it lead me to do (and still do) is use a hidden field with a form_id identifier.

    eg
    Code:
    <input type="hidden" name="form_id" value="insert_user_form">
    then check for that value in the POST array.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  11. #11
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I've already tried the hidden field trick, but still no joy. Also, when I try var_dump or print_r on the $_POST I get a server error
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  12. #12
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorted out the server error (my fault), but now I get an empty array for var_dump($_POST);

    My form action: <form name="myform" method="POST" action="index.php"> is very straightforward.
    index.php is the same page. I've tried $PHP_SELF with the same results GRRRRRRRR!
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My request for the query meant the full, translated query. but never....

    As for your submission - are you sure that your bad form has a closing form tag?

  14. #14
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, the form is closed correctly.

    I've just been speaking with the site owner and he now tells me that he is moving his site from that server due to other problems (which he didn't tell me about), So, as this problem only occurs on that server, I'm not going to investigate any more. I can now concentrate on mysql to mysqli conversion in my script.

    Thanks to all - sorry to back out like this.
    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  15. #15
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    728
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    method="POST" should be lower-case method="post" but I doubt that's the problem.

  16. #16
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Actually most tutorials/examples of method= use UPPER case for POST. Just as it HAS to be in the PHP code processing the array.

    And I really dont' see how an html form would have submission problems from one server to the next. Perhaps you'd like to post the complete form logic that is not working.

  17. #17
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    728
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ginerjm View Post
    Actually most tutorials/examples of method= use UPPER case for POST.
    http://www.w3.org/TR/html4/interact/...ml#adef-method Strictly speaking that is. If you were running the page through a good validater, it would probably throw a warning on the upper-case.

    If he's getting an empty array for var_dump($_POST); then it is a form issue.

  18. #18
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Drummin View Post
    http://www.w3.org/TR/html4/interact/...ml#adef-method Strictly speaking that is. If you were running the page through a good validater, it would probably throw a warning on the upper-case.

    If he's getting an empty array for var_dump($_POST); then it is a form issue.
    or a server issue in that the server is for some reason not processing the posted form.
    If it works on one server but not the other then it's more likely to be the server config.

    As for capitals or not, its a case of personal preference. My preference is lowercase
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


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
  •