Create array for PDO from variables passed from jquery

Question

This is the PHP code I have now to get counts from my database:

$hostname = '****';
$username = '****';
$password = '****';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=firstdb", $username, $password);

echo 'Connected to database<br />';
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** some example variables ***/
$firstpara = 'age';
$secondpara = 'marital_status';
$thirdpara = 'gender';
$data = array($firstpara=>55, $secondpara=>'single', $thirdpara=>'male');

/*** prepare the SQL statement ***/
$stmt = $dbh->prepare("SELECT COUNT(guid) FROM full_db2 WHERE {$firstpara} = :{$firstpara} AND {$secondpara} = :{$secondpara} AND {$thirdpara} = :{$thirdpara}");

$stmt->execute($data);

$count =$stmt->fetch();
    echo json_encode($count);

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

This successfully gives me JSON containing the count of users in the database that have the attributes set in my variables above ($firstpara, $secondpara, $thirdpara).

Of course, this is all hard-coded and what I want to do is have the user pick from select boxes containing the different attribute options (e.g., marital status, gender) and based on that give them the possible value options in a second select (e.g., single). I'm generating these select boxes dynamically no problem and can store the selections as variables. I can pass them into AJAX to send to the PHP file, but I have no idea how to parse them all together. Something like this to send the variables. var para1 = "male"; //this captured from select value //any more attributes selected

$.ajax({
        url: 'all_get_2.php',
        type: 'GET',
        dataType: 'JSON',
        data: {firstpara: para1, secondpara: para2 ,thirdpara: para3},
        success: function(data) {
       //do something
      }
      });

And in my PHP I can add lines like this to set variables:

  $firstpara = isset($_GET['firstpara'])? "{$_GET['firstpara']}" : '';
  $firstpara = mysql_real_escape_string($firstpara);

The problem here is that I don't know how many attributes (e.g., marital status) the user might select. They might use two or ten or some other number. So, my questions are this:

How can I send the data to PHP (securely...so there are no injections possible) just that I can dynamically add the right number of $firstpara like variables to account for everything that AJAX passed including adding the right number of where clauses into the SQL statement?

I'm guessing there needs to be some sort of for/each loop or similar, but I'm just not familiar enough with PHP to know how to do it.

Any direction would be appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

I am not sure if I got your problem in correct way but you can try (or comment if I got it wrong).

Replace thise fragment:

$stmt = $dbh->prepare("SELECT COUNT(guid) FROM full_db2 WHERE {$firstpara} = :{$firstpara} AND {$secondpara} = :{$secondpara} AND {$thirdpara} = :{$thirdpara}");

$stmt->execute($data);

with this:

$validKeys = array('gender','maritalstatus', 'age');
    $sql = 'SELECT COUNT(guid) FROM full_db2';
    $any_condition = false;
    foreach($_GET as $key=>$val) {
       if (!empty($val) && in_array($key,$validKeys)) {
         if ($any_condition) {
           $sql .= ' AND '.$key.' = :'.$key;
         } else {
           $sql .= ' WHERE '.$key.' = :'.$key;
           $any_condition = true;
         }
       }
    }

    $stmt = $dbh->prepare($sql);

    foreach($_GET as $key=>$val) {

   if (!empty($val)  && in_array($key,$validKeys)) {
     $stmt ->bindValue(':'.$key, $val, PDO::PARAM_STR);
   }
}

$stmt->execute();

for your ajax request just read here: Submit a form using jQuery

in your ajax request change line format from: data: {firstpara: para1, secondpara: para2 ,thirdpara: para3}

to data: {age: para1, maritalstatus: para2 ,gender: para3},

edited May 23 '17 at 12:20

Community

1
1

answered Jan 14 '15 at 14:33

Alex

16,739
1
28
51

This is probably on the right track. How do I need to pass the variables in my AJAX? – jonmrich Jan 14 '15 at 14:36
As $_GET variables. So with jQuery it would be ```$.get(url, $("#form").serialize());``` for example. – Marcel Burkhard Jan 14 '15 at 14:36
What if I don't know the number of variables in advance? Do I send an array? If so, what format? – jonmrich Jan 14 '15 at 14:38
I'm still not sure how to handle submitting the data and what format it has to be. I know how to use ajax enough to be able to construct just about anything to pass into the PDO, but I don't know what format will work. How do I show which keys and which values go together, etc.? – jonmrich Jan 14 '15 at 15:54
did you try my php code? you are saying that jquery side is not a problem for you. so just send it to php - and receive the result the way I showed. if you can't send it from jquery side, we can figure that out, but it looks like you CAN send 1-2-3 parameters, so you can't send 7? or you don't know how to organize the loop? – Alex Jan 14 '15 at 16:04
I guess I don't know how to create the loop correctly, but I can probably figure that out. The thing I can't figure out is how I need to send data to php...what format. I know the key and value pairs, but how do I bundle them together to create something PHP will understand. Right now, I'm cramming them into an array as people select them, but I don't know how the array should look. Should it be like this `["age , 29", "gender, male"]` or some other format. I just need to know what this format should be and what I should call it in order to get it into the PHP correctly. – jonmrich Jan 14 '15 at 18:59
doesn't your code `data: {firstpara: para1, secondpara: para2 ,thirdpara: para3}` work ?? – Alex Jan 14 '15 at 19:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68831/discussion-between-jonmrich-and-kim-alexander). – jonmrich Jan 14 '15 at 19:27

Create array for PDO from variables passed from jquery

1 Answers1

Linked