0

I am creating an Inline Edit data by using X-editable with PHP Dropdown Select Box. I am facing an issue in that I am not able to create a dynamic select option in jquery from database data.

Actually, I want to echo the source format of text in PHP so I can add in jquery. The snippet below is in the correct format, but I don't know how to echo that text dynamically using data from PHP.

-- PHP -- 
$stmt = $connect->prepare("SELECT * FROM `profile`");
$stmt->execute();  

-- JQUERY --
$('#employee_data').editable({
  container: 'body',
  selector: 'td.gender',
  title: 'Gender',      dataType: 'json',
  source: [{value: "Male", text: "Male"}, {value: "Female", text: "Female"}]
 });

Please help me to create this json structure [{value: "Male", text: "Male"}, {value: "Female", text: "Female"}] from a server-side query and pass it into the editable() function's argument in jquery.

I tried this, but it didn't work:

$result = $stmt->fetchAll();     

foreach($result as $data => $value) {         
  $data = array('value' => $value["category_id"], 'text' => $value["category"]);     
}     

$category_list = json_encode($data);
mickmackusa
  • 43,625
  • 12
  • 83
  • 136
John Cart
  • 69
  • 8
  • You can use the json_encode function. But first you need to process the results of that query – ADyson Dec 03 '20 at 08:05
  • I used the foreach function but it not working with json encode – John Cart Dec 03 '20 at 08:50
  • Please help me ow to write in your way – John Cart Dec 03 '20 at 08:51
  • You need to show us exactly what _you_ tried. Then we can help you understand why it isn't working and how to fix it. That's how this site works. So please update your question with the code. Thankyou. – ADyson Dec 03 '20 at 09:17
  • I want when I will execute the PHP as above question then it will give the output in this format `[{value: "Male", text: "Male"}, {value: "Female", text: "Female"}]` – John Cart Dec 03 '20 at 09:21
  • Yes we know, you've already said that in the question. Please respond to my previous comment and provide the information requested. – ADyson Dec 03 '20 at 09:37
  • `$result = $stmt->fetchAll(); foreach($result as $data => $value) { $data = array('value' => $value["category_id"], 'text' => $value["category"]); } $category_list = json_encode($data);` I tried but not help full – John Cart Dec 03 '20 at 09:46
  • In future please add code to the question itself, not in comments (where it's harder to read). Your question has an "edit" button which enables you do to this. On this occasion, I handled it for you. Also, you should be telling us what output you get from the code, not making people work it out. "not help full" doesn't tell us anything about what happens - ironically, it's not a very helpful description! As it happens, I can see from the code what is probably going wrong, and I wrote an answer below for it, but it's not possible to do that in every case, so please always give full details. – ADyson Dec 03 '20 at 10:03

2 Answers2

0

There is a simple logic error here - you are overwriting the value of $data every time your loop runs, so you'll only ever end up encoding the value of whatever the last entry in your data was.

Instead you need to declare an array, and then add items to that array each time you loop:

$result = $stmt->fetchAll();     
$list = array();

foreach($result as $data => $value) {         
  $list[] = array('value' => $value["category_id"], 'text' => $value["category"]);     
}     

$category_list = json_encode($list);

And then in the JavaScript, simply write:

source: <?php echo $category_list; ?>
ADyson
  • 57,178
  • 14
  • 51
  • 63
  • not working, error - Cannot use a scalar value as an array – John Cart Dec 03 '20 at 10:11
  • Oh sorry it's because you already defined $data as a loop parameter. Try the updated version, above - just need to use a different variable name – ADyson Dec 03 '20 at 10:25
  • thank You.. I beleive you know well code.. could you please help me here..`https://stackoverflow.com/questions/65120394/append-function-is-not-working-in-jquery-ajax-of-php-response?noredirect=1#comment115133476_65120394` – John Cart Dec 03 '20 at 10:31
0

There are several points to inform you of...

  1. If you are not using any placeholders in your sql, then using a prepared statement is needless overhead. Just use query().

  2. Do not ask for more data from your SELECT clause than you actually intend to use. I don't know how many columns are in your table, but you only need to be fetching category_id and category according to your posted script.

  3. By aliasing your column names in your SELECT clause, you can avoid the extra step of iterating the result set's rows just to adjust the keys.

  4. There seems to be a disconnect in your post between category_id & category versus gender & gender. I don't know exactly what data is being used to create these <option>s, but I would like to tell you that there is never any benefit to repeating identical text in the option's text and the same option's value attribute. In other words, <option value="Male">Male</option> should only ever be written as <option>Male</option>; the former is simply redundant markup bloat. I will assume that category_id and category are not identical values, so there should be no concern for redundancy.

  5. fetchAll() is most appropriately used when you are not iterating the data in the same "layer". Because the server-side (php) data is being passed to the client-side (js), there is no need to manually iterate the result set to create an array; just let fetchAll() do all of the work.

Code: (assuming you are querying in the same file that you are printing to screen)

source = <?php echo json_encode($pdo->query("SELECT `category_id` AS `value`, `category` AS `text` FROM `profile`")->fetchAll()); ?>

There is a bounty of good advice/techniques @ https://phpdelusions.net/pdo_examples/select

Dharman
  • 30,962
  • 25
  • 85
  • 135
mickmackusa
  • 43,625
  • 12
  • 83
  • 136