Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts

    pdo dynamic query based upon form values

    I have this form, containing a group of 9 checkboxes with the name check[], a select option field, a single textfield and 2 pair of textfields (one pair is to check between zipcodes if the second zipcode field is filled, otherwise it will just use the first zipcode field and not make a between. And the second pair of textfields is check between birthday years if the second birthday year is filled else the first birthday year textfield will be used and not make a between).

    That's a lot, hope it make sense.

    So here is my problem.

    I use pdo with classes, on my frontpage I use a code to call function from a class to execute the selection I made in the form.
    This is that code:
    PHP Code:
    if($export->exportSelection2CSV(//here should come some parameters)){
        
    $message "Export of selection was successful.";
    }else{
        
    $message "Export of selection to excel has failed, please try again or contact the administrator!";

    The function in the class looks like the following:
    PHP Code:
    public function exportSelection2CSV(// some parameters need to be put here)
    {
        try 
        {
            
    $this->pdo $this->connectMySql();
            
    $query "SELECT * FROM nbs_contacts";
            
    $stmt $this->pdo->prepare($query);
            if(!
    $stmt->execute()){
                return 
    false;
            }
            
    $nart $stmt->rowCount();
            echo 
    "returned count: " $nart;
            if (
    $nart == 0) {
                return 
    "No records returned!";
            }
            
    $filename "db_export_selection.csv";
            
            
    $handle fopen($filename'w+');
                
            
    $headers = array();
                
            while (
    $row $stmt->fetch(PDO::FETCH_ASSOC)){
                if(empty(
    $headers)){
                    
    $headers array_keys($row);
                    
    fputcsv($handle$headers);
                }
                
    fputcsv($handle$row);
            }
            
    fclose($handle);
            
    $this->pdo null;
            return 
    true;
        } 
        catch (
    PDOException $e
        {
            if(
    $curuser === 'super-admin') {
                
    $message '';
                
    $message .= '<pre>';
                
    $message .= 'Regelnummer: '.$e->getLine().'<br>';
                
    $message .= 'Bestand: '.$e->getFile().'<br>';
                
    $message .= 'Foutmelding: '.$e->getMessage().'<br>';
                
    $message .= '</pre>';
                echo 
    $message;
            }
            return 
    false;
        }

    Beside this I don't exactly how to proceed. I hope anyone can help me with this.

    If I need to give more info, please let me know.

    Thanks.

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    I don’t get what the problem is.

    besides that, there is no need for $this->pdo. from the looks of it, the used PDO (statement) instance is not used outside this method. additionally, in the code right now all the $header part can be omitted (an empty array is always empty).
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    I don’t get what the problem is.

    besides that, there is no need for $this->pdo. from the looks of it, the used PDO (statement) instance is not used outside this method.
    As the matter a fact, I use a database class, so the pdo (statement) is being used outside this method, therefor needs to be $this->pdo.

    Quote Originally Posted by Dormilich View Post
    additionally, in the code right now all the $header part can be omitted (an empty array is always empty).
    Just forget the part of the code between $filename and fclose (this is for the export to excel, and I know how this works.

    The only problem I have is to make a dynamic query from the form values when submitted, if there are any checked or filled, those need to be in the query.

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    Quote Originally Posted by UD2006 View Post
    As the matter a fact, I use a database class, so the pdo (statement) is being used outside this method, therefor needs to be $this->pdo.
    even when you create and delete the instance in this method?

    Quote Originally Posted by UD2006 View Post
    The only problem I have is to make a dynamic query from the form values when submitted, if there are any checked or filled, those need to be in the query.
    dynamic in what sense? your shown query does not have anything related to parameters (because of the wildcard).
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #5
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    even when you create and delete the instance in this method?
    I created it that way, so that if I have more classes that need the connection to the database, I only need to use that line of code to make that happen.


    Quote Originally Posted by Dormilich View Post
    dynamic in what sense? your shown query does not have anything related to parameters (because of the wildcard).
    Dynamically in the way that, for example on the front I enter a birthday year and a zipcode, I then want to make a selection on only those those items. Or when I enter 2 zipcodes (to make a between selection).

    The query I wrote was not finished, it was just a standard select. It still needs to be extended and adapted.

    Do I need to put all the fields from the form into the function (where I have the comment of the parameters?). What I think is that I need some if isset statements to check if a parameter has a value, if so add it to the query, if not, leave it out.

  • #6
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    Quote Originally Posted by UD2006 View Post
    I created it that way, so that if I have more classes that need the connection to the database, I only need to use that line of code to make that happen.
    if you call $this->connectMySQL() each time and delete the connection after use, why making a property for that? when the method starts $this->pdo is null, when the method finishes, $this->pdo is null. I don’t see the use for $this->pdo here.


    Quote Originally Posted by UD2006 View Post
    Do I need to put all the fields from the form into the function (where I have the comment of the parameters?). What I think is that I need some if isset statements to check if a parameter has a value, if so add it to the query, if not, leave it out.
    what fields to call mainly depends on what fields you have in the DB. this may or may not conform to the form’s fields.

    So if I understand you right, you want the WHERE clause of the query dependent on what is passed to the method?
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #7
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    what fields to call mainly depends on what fields you have in the DB. this may or may not conform to the form’s fields.
    The fields that are in the form (to make a selection from) are existing fields in the DB. That has to be, because those fields are filled in when a new contact is being add to the database.


    Quote Originally Posted by Dormilich View Post
    So if I understand you right, you want the WHERE clause of the query dependent on what is passed to the method?
    You nailed it.

  • #8
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    the single fields and checkboxes are quite straightforward. you have a key and a value in the $_POST/$_GET array.
    PHP Code:
    $source array_filter($_POST/* filter function */);
    $conditions = array();
    foreach (
    $source as $field => $value)
    {
        
    // for queries
        
    $conditions[] = $field " = " sql_escape($value);
        
    // for (PDO) prepared statements
        
    $conditions[] = $field " = :" $field;
    }
    $where " WHERE " implode(" AND "$conditions); 
    for the between statements you need to do a conditional test, whether they are to be used like the others or if you need to code them as BETWEEN statement.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #9
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dormilich View Post
    the single fields and checkboxes are quite straightforward. you have a key and a value in the $_POST/$_GET array.
    PHP Code:
    $source array_filter($_POST/* filter function */);
    $conditions = array();
    foreach (
    $source as $field => $value)
    {
        
    // for queries
        
    $conditions[] = $field " = " sql_escape($value);
        
    // for (PDO) prepared statements
        
    $conditions[] = $field " = :" $field;
    }
    $where " WHERE " implode(" AND "$conditions); 
    Thanks for your help this far. With this sample code, do I need to make a new prepare and execute each time or just a single one at the end?

  • #10
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    in this case, once.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #11
    Regular Coder
    Join Date
    Jul 2007
    Location
    Velsen Noord, Netherlands
    Posts
    218
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi again, the only part I am not getting is the /* filter function */ part, I've searched in google for this, but haven't figured that part out.

    What I think should be (but doesn't work so is not the right thing):
    PHP Code:
    $source array_filter($_POST$eventlist); 
    $conditions = array(); 
    foreach (
    $source as $field => $value

        
    // for queries 
        
    $conditions[] = $field " = " sql_escape($value); 
        
    // for (PDO) prepared statements 
        
    $conditions[] = $field " = :" $field

    $where " WHERE " implode(" AND "$conditions); 
    $eventlist is a value set at the front end.

    Because if I use the code like above here, I get the following error message:
    Warning: array_filter() expects parameter 2 to be a valid callback, array must have exactly two members in C:\xampp\htdocs\contactdatabase\contact_nbs\classes\export.class.php on line 77

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\contactdatabase\contact_nbs\classes\export.class.php on line 79

  • #12
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,472
    Thanks
    13
    Thanked 361 Times in 357 Posts
    as the error tells you, $eventlist must be the name of an existing PHP function. cf. http://php.net/array-filter

    the second message is just a follow-up error.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer


  •  

    Posting Permissions

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