SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Question Deciphering if Database Writing was Successful- I Do Not See Any New Rows in phpMyAdm

    Hello all,

    I have used PHP code to incorporate PDO (PHP Data Objects) for statements made to my web host server's address. Upon looking on the phpMyAdmin client application provided from web host, I didn't see any new rows after a while of executing this databasewriter.php file with new data each time (new people subscribing to the e-mail list). I'm most sure that I have the server address, the username, database name, and password correct, but I'll double-check to make sure it's right. Also, there are no errors or warnings appearing in the console when I submit the submission form on my website. Do I have to access the data in a different way because I used PDO? What's going on here?

    Here is my code:
    PHP Code:
    <?php
        
    class DatabaseWriter
        
    {
            public function 
    writeUserToDatabase($email $name $age $country $category)
            {
                
    $host '*************';
                
    $dbname 'emailcollection';
                
    $user '**********';
                
    $password '*********';
                
                try {
                    
    $connection = new PDO("mysql:host=".$host.";dbname=".$dbname$user$pass);
                    
    $statement $connection -> prepare("INSERT INTO emailcollection (email, name, age, country, category) VALUES (:email, :name, :age, :country, :category)");
                    
    $statement -> bindParam(':email'$email);
                    
    $statement -> bindParam(':name'$name);
                    
    $statement -> bindParam(':age'$age);
                    
    $statement -> bindParam(':country'$country);
                    
    $statement -> bindParam(':category'$category);
                    
    $statement -> execute();
                
                    
    $connection NULL;
                }catch (
    Exception $e){
                    
    error_log($e->getMessage());
                }
            }
        }
    ?>

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Hi, try things in this order


    1. Have you tested this work when the "happy path" is taken? (ie everything is filled in as expected)

    2. Your method args have no default value, so unless you are filtering/checking elsewhere, you will be getting `var not set` PHP errors

    PHP Code:
     public function writeUserToDatabase($email ""$name="" $age=$country="" $category=""
    I mean, does this work?

    PHP Code:
    $email "this is set";
    $name="this is set"
    // age, country, category are not set

    $db = new DbWriter ;
    $db->writeUserToDatabase($email $name $age $country $category
    3. Turn on error reporting for a while

    4. Turn on PDO error reporting, read the docs to find the correct switches ... have PDO show errors

    5. Check you own logging, then check your Mysql update log too

  3. #3
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi, Cups, and thanks for your reply.

    Yes, the database writer script does receive actual values from the main confirmform.php script that validates form submissions. I have a try-catch in there, too, to try to catch a PDOException, but I'm not seeing any data reported back.

    confirmform.php
    PHP Code:
    <?php
        $instance 
    = new CheckForm;
        
    $instance -> checkSubmission();
        
        class 
    CheckForm
        
    {
            public function 
    checkSubmission()
            {    
                
    $origEmail $_POST['origEmail'];
                
    $confirmEmail htmlspecialchars($_POST['confirmEmail']);
                
    $name htmlspecialchars($_POST['name']);
                
    $ageRange $_POST['age'];
                
    $gender $_POST['gender'];
                
    $country $_POST['country'];
                
    $catcher htmlspecialchars($_POST['catcher']);
                
    $mathAnswer htmlspecialchars($_POST['addition']);
                
    $rightAnswer $_POST['mathAnswer'];
                
    $submissionTime $_POST['submissionTime'];
                
    $status 0;
            
                
    $response = array("validation" => " ""message" => " ""database" => " ");
                
                if (
    $submissionTime >= 10000){
                    if(
    $mathAnswer === $rightAnswer){
                        if (empty(
    $confirmEmail) && empty($name) && $country === "Select Country") {
                            
    $response = array("validation" => "fail""message" => "That's not a valid submission.");
                        } elseif (empty(
    $confirmEmail) && $country === "Select Country"){
                            
    $response = array("validation" => "fail""message" => "Please confirm your e-mail and select a location.");
                        } elseif (empty(
    $name) && $country === "Select Country"){
                            
    $response = array("validation" => "fail""message" => "Please enter a name and select a location.");
                        } elseif (empty(
    $name)) {
                            
    $response = array("validation" => "fail""message" => "Please enter a name.");
                        } elseif (empty(
    $confirmEmail)) {
                            
    $response = array("validation" => "fail""message" => "No confirmation e-mail was entered.");
                        } elseif (
    $origEmail != $confirmEmail) {
                            
    $response = array("validation" => "fail""message" => "E-mail addresses don't match.");
                        } elseif (
    $country === "Select Country") { 
                            
    $response = array("validation" => "fail""message" => "Please select a location.");
                        } elseif (!empty(
    $catcher)) {
                            
    $response = array("validation" => "fail""message" => "Bot submission.");
                        } else
                            
    $status 1;
                    } else
                        
    $response = array("validation" => "fail""message" => "Math answer is incorrect.");
                } else
                    
    $response = array("validation" => "fail""message" => "Woah! Slow down and fill out the form.");
                
                if (
    $status === 1) {
                            require_once(
    "categoryfinder.php");
                            
    $categoryFinder = new CategoryFinder;
                            
    $category $categoryFinder -> getCategory();
                            
    $response = array("validation" => "pass""message" => "Thanks for joining the e-mail list, <b>" $name "</b>, under the e-mail address, <b>" $confirmEmail "</b>.""database" => "pass");
                            try{
                                require_once(
    'databasewriter.php');
                                
    $dbWriter = new DatabaseWriter;
                                
    $dbWriter -> writeUserToDatabase($confirmEmail$name$ageRange$gender$country$category);
                            } catch (
    PDOException $e) {
                                
    $response = array("validation" => "pass""message" => "Thanks for joining the e-mail list, <b>" $name "</b>, under the e-mail address, <b>" $confirmEmail "</b>.""database" => "fail");
                            }
                }

                echo 
    json_encode($response);
            }
        }
    ?>
    However, I am confused as to step #3 and #4.

    #3 Pertaining to turning on error reporting- do you mean to turn XAMPP (local server) error reporting on? I have no local database model set up... so I'm sure that won't work.

    #4 I amended my database writer script to turn on PDO error reporting, but I don't see anything reported in Firebug.

    PHP Code:
    <?php
        
    class DatabaseWriter
        
    {
            public function 
    writeUserToDatabase($email $name $age $gender $country $category)
            {
                
    $host '*********';
                
    $dbname 'emailcollection';
                
    $user '**********';
                
    $password '********';
                
                try {
                    
    $connection = new PDO("mysql:host=".$host.";dbname=".$dbname$user$pass);
                    
    $connection -> setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION); 
                    
    $statement $connection -> prepare("INSERT INTO emailcollection (email, name, age, gender, country, category) VALUES (:email, :name, :age, :gender, :country, :category)");
                    
    $statement -> bindParam(':email'$email);
                    
    $statement -> bindParam(':name'$name);
                    
    $statement -> bindParam(':age'$age);
                    
    $statement -> bindParam(':gender'$gender);
                    
    $statement -> bindParam(':country'$country);
                    
    $statement -> bindParam(':category'$category);
                    
    $statement -> execute();
                    
                    echo 
    "\nPDOStatement::errorInfo():\n";
                    
    $arr $statement -> errorInfo();
                    
    print_r($arr);
                
                    
    $connection NULL;
                }catch (
    Exception $e){
                    
    error_log($e->getMessage());
                }
            }
        }
    ?>

  4. #4
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    UPDATE:

    So instead of catching a general Exception, I changed the bottom to catch a PDOException, and I did obtain an error message. It looks as though it may be as simple as my login credentials being incorrect. I will talk it over with my web host.

    SQLSTATE[28000] [1045] Access denied for user 'worldreviewgroup'@'cgi2203.int.bizland.net' (using password: NO)

  5. #5
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    According to support with the web host, I was given a link to a page that only says "Successfully Connected to the Database". I was told that the problem exists in my databasewriter.php script. All privileges appear to be available for my user on phpMyAdmin.

    Edit: this is the php code for that page
    PHP Code:
    <?php
     
    $dbhost 
    "**********";
    $dbusername "*********";
    $dbpassword "**********";
    $database "emailcollection";
    $connection = new mysqli($dbhost,$dbusername,$dbpassword,$database) or die("Could not connect to DB");
    if(
    mysqli_connect_errno())
    {
     
    printf("Connect failed: %s\n"mysqli_connect_error());
         exit();
    }
    else
    {
      echo 
    "Successfully Connected to the Database<br><br>";
    }
     
    ?>
    They are connecting with mysqli and not PDO.

    Hm... this is confusing!!

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    #3 Pertaining to turning on error reporting- do you mean to turn XAMPP (local server) error reporting on? I have no local database model set up... so I'm sure that won't work.
    Turn on error_reporting on the webpage which is calling your class.
    PHP Code:
    <?php
    //top of the page
     
    error_reporting(E_ALL);
     
    ini_set("display_errors"1);
     include(
    "file_with_errors.php");
    ?>
    Maybe that will turn something up.

  7. #7
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Okay, I put that code into my home page file. Now, for the time being, it is worldreviewgroup.com/index.php instead of index.html. I see nothing new beside the error I posted earlier that says

    SQLSTATE[28000] [1045] Access denied for user 'worldreviewgroup'@'cgi2203.int.bizland.net' (using password: NO)

  8. #8
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Okay, so I discovered that the code has an obvious issue.

    I defined a variable, $password, at the beginning, but in the PDO object, I called an undefined variable, $pass.

    Now, I've worked down to see the error:

    SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

  9. #9
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Well, it looks like I'm on my own with this one.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
        class DatabaseWriter
        
    {
            public function 
    writeUserToDatabase($email $name $age $gender $country $category)
            {
                
    $host '*********';
                
    $dbname 'emailcollection';
                
    $user '**********';
                
    $password '********';
                
                try {
                    
    $connection = new PDO("mysql:host=".$host.";dbname=".$dbname$user$pass); // Change this line from this 
    $connection = new PDO("mysql:host=$host;dbname=$dbname$user$pass); // to this 
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi, Space Phoenix, and thanks for your reply.

    I tried changing that line, although what you posted appears to be missing an end quote, which I got a syntax error from. So, I just added an end quote at the end right before the closing parentheses,

    PHP Code:
    $connection = new PDO("mysql:host=$host;dbname=$dbname$user$pass"); 
    Upon uploading and trying it, I was given the following error:
    SQLSTATE[28000] [1045] Access denied for user 'moo.worldreviewg'@'cgi2203.int.bizland.net' (using password: NO)
    I'm guessing the Using password: NO means that I have the wrong password, which I know I have the correct one (I reset it and changed it just so I knew I had the right password).

    -Tyler

  12. #12
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Success!

    The solution is that the SQL query was using :emailaddress, while bindValue() was using :email. Hooray!


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
  •