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.

Community · Accepted Answer · 2017-05-23 12:20:41Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Jan 14, 2015 at 14:33

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

jonmrich Over a year ago

This is probably on the right track. How do I need to pass the variables in my AJAX?

Marcel Burkhard Over a year ago

As $_GET variables. So with jQuery it would be $.get(url, $("#form").serialize()); for example.

jonmrich Over a year ago

What if I don't know the number of variables in advance? Do I send an array? If so, what format?

jonmrich Over a year ago

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.?

Alex Over a year ago

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?

|

Collectives™ on Stack Overflow

Create array for PDO from variables passed from jquery

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related