Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Query database using jQuery WordPress

I want to select some values from a database and insert them into a form using javascript. I understand that it should be done by performing the query in a php file and using the resulting variables in javascript.

I would like to know how the javascript should be done best in a WordPress site and for a particular purpose - pre-selecting values in a form.

If the php variables were in an associative array eg $foo[bar], and the javascript was like:

jQuery('.checkbox[value=*$foo[bar]*]').attr('checked', true);

and

var opt = jQuery("option[value=*$foo[bar]*]")

how can it be done?

Answers (2)

2013-02-26

Francisco Javier Carazo Gil answers:

You can do a foreach in PHP and make an echo with the script:

foreach($array as $value)
echo "<script>jQuery('.checkbox[value=$value]').attr('checked', true);</script>";


houfton comments:

Wow! that was quick.

The trouble is in the context of this form I can add javascript but not php. It needs to be the javascript pulling in data from an external php file.

I have seen lots of examples of this being done but cannot work out the correct, WordPress-friendly way for this context.


Francisco Javier Carazo Gil comments:

You can create a JSON array, pass it to JS and then yes you can manipulate. Result is the JSON array:
$.each(result,function(index, value){
console.log('My array has at position ' + index + ', this value: ' + value);
});


And this the function to pass an array to JSON in PHP:

http://php.net/manual/en/function.json-encode.php


houfton comments:

OK, so I have my array encoded as JSON in my php file, but how does my javascript find that file and extract the values into the script?

So in external.php:
...
json_encode(($foo[bar]);

In javascript:

*get JSON from external.php*
jQuery('.checkbox[value=*$foo[bar]*]').attr('checked', true);
jQuery('.checkbox2[value=*$foo[baz]*]').attr('checked', true);

etc


Francisco Javier Carazo Gil comments:

You can call JavaScript directly from PHP or maybe with AJAX asynchronous is a better option: http://codex.wordpress.org/AJAX


houfton comments:

Thank you Francisco. I have seen several examples of how to do this sort of thing on stackoverflow.com and elsewhere and would probably work out how exactly to do it eventually, but what I am hoping for is some actual code that will do it rather than general pointers!


Francisco Javier Carazo Gil comments:

Ok, you have to call from PHP:
echo "<script>my_function('json_encode(" . $foo[bar] . ")');</script>

In JS:

function my_function()
{
$.each(result,function(index, myvalue){
jQuery('.checkbox[value=myvalue]').attr('checked', true);
jQuery('.checkbox2[value=myvalue]').attr('checked', true);
});
}


houfton comments:

How is the JS finding the PHP?

I have seen this kind of thing:


function getDescription(for_id) {
$.ajax({
type: 'GET',
url: "<?php bloginfo('template_url'); ?>/assets/includes/get-description.php?id=" + for_id,
success: function(data, textStatus, jqXHR){
$('#textdescription').html(data);
}
});
}


It is not exactly what I am looking for but it does get data from an external file and then uses it.

2013-02-27

Christianto answers:

Where the form is located?
is it the same WordPress site where the database value saved?

As stated by Francisco,
you can encode your database value in array to JSON so your javascript code could access it.

The JSON data can be included on your site with [[LINK href="http://codex.wordpress.org/Function_Reference/wp_localize_script"]]wp_localize_script[[/LINK]]
for example you have this kind of array from your database:
$myquery_data = array('my_first_checkbox' => true, 'name' => 'my name is Christianto' );
then we can encode your array and print it as "mydata" object for example;
// include your custom.js (your custom javascript code for form)
wp_enqueue_script( 'my_custom_js', 'http://yoursite.com/wp-content/themes/mytheme/js/custom.js', array('jquery'), '1.0' );
// print your database value as global js var "mydata"
$mydata = array( 'settings' => json_encode($myquery_data) );
// combine your data with your javascript code handle
wp_localize_script( 'my_custom_js', 'mydata', $mydata );

above will print js object "mydata" inside your <head> (wp_head hook needed), it look like this:
<script type="text/javascript">
/* <![CDATA[ */
var mydata = {"settings":"{\"my_first_checkbox\":true,\"name\":\"my name is Christianto\"}"};
/* ]]> */
</script>

so we can access/manipulate it within jQuery code but first we need to parse it, so inside your custom.js:
jQuery(document).ready(function($){
// we can access global object "var mydata"
// first decode json format
var settings = $.parseJSON(mydata.settings);
// now we can manipulate form based on database value
if(settings.my_first_checkbox)
jQuery('.checkbox[value="setting1"]').attr('checked', true);
if(settings.name != '')
jQuery('input.name').val(settings.name);
});


As you can see your array will match each object so you can use it to manipulate certain option.

Hope this help


houfton comments:

The form is actually created by the wp-types.com CRED plugin which gives a box for adding javascript to each form to be called when the form is used. It is all on the same site as the database.

I was hoping to use that to bring in some values from the database based on the user's existing data as and when needed.

Unless I have misunderstood, you are suggesting setting the data as a global which is created whether it is needed or not.

I will have a go tomorrow and see if I can get it to work.


houfton comments:

I have made some progress with this approach:


jQuery(document).ready(function() {
jQuery.getJSON('external.php', function(data) {
alert(data['some-key']);
});


The correct value is being returned as an alert. But I can't work out how to insert the value into my code. I have tried all sorts of things, including creating a var and using that, but it is not working. Eg


jQuery('.checkbox[value=data['some-key']]').attr('checked', true);


I also worry that this is all too simple (!) and does not account for any json errors etc.


Christianto comments:

this won't work because data object ( data['some-key'] ) treat as a string;
jQuery('.checkbox[value=data['some-key']]').attr('checked', true);
it should be
jQuery('.checkbox[value="'+data['some-key']+'"]').attr('checked', true);
or using saving object into variable
var some_key = data['some-key'];
jQuery('.checkbox[value="'+some_key+'"]').attr('checked', true);


As long its well formed json, and the data is correct, it won't be an error, but in case it happen you could bind [[LINK href="http://api.jquery.com/error/"]]event error[[/LINK]] to know about it.


houfton comments:

That is brilliant, thanks, I thought I had tried everything. Using '+data['some-key']+' works in both contexts (so long as I use the right kind of quotes consistently).

I have used a full, relative path to the external php file which I have placed in my theme directory which seems to work. I could not use <?php get_stylesheet_directory() ?>.


Christianto comments:

<blockquote>I could not use
<?php get_stylesheet_directory() ?></blockquote>

Did you mean ajax request url?
it should be:
<?php echo get_stylesheet_directory_uri(); ?>

get_stylesheet_directory() will return server path to theme directory instead of url
( /public_html/wp-content/themes/my_theme )


houfton comments:

Good to know, but the problem is with using any php at all. I guess I will have to stick with a full, relative path (../wp-content/themes/my_theme/).