Dynamically change MYSQL query within a PHP file using jQuery .post?
- by John
Hi,
Been trying this for quite a while now and I need help. Basically I have a PHP file that queries database and I want to change the query based on a logged in users name.
What happens on my site is that a user logs on with Twitter Oauth and I can display their details (twitter username etc.). I have a database which the user has added information to and I what I would like to happen is when the user logs in with Twitter Oauth, I could use jQuery to take the users username and update the mysql query to show only the results where the user_name = that particular users name.
At the moment the mysql query is:
"SELECT * FROM markers WHERE user_name = 'dave'"
I've tried something like:
"SELECT * FROM markers WHERE user_name = '$user_name'"
And elsewhere in the PHP file I have $user_name = $_POST['user_name'];. In a separate file (the one in which the user is redirected to after they log in through Twitter) I have some jQuery like this:
$(document).ready(function(){
$.post('phpsqlinfo_resultb.php',{user_name:"<?PHP echo $profile_name?>"})});
$profile_name has been defined earlier on that page.
I know i'm clearly doing something wrong, i'm still learning. Is there a way to achieve what I want using jQuery to post the users username to the PHP file to change the mysql query to display only the results related to the user that is logged in. I've included the PHP file with the query below:
<?php
// create a new XML document
//$doc = domxml_new_doc('1.0');
$doc = new DomDocument('1.0');
//$root = $doc->create_element('markers');
//$root = $doc->append_child($root);
$root = $doc->createElement('markers');
$root = $doc->appendChild($root);
$table_id = 'marker';
$user_name = $_POST['user_name'];
// Make a MySQL Connection
include("phpsqlinfo_addrow.php");
$result = mysql_query("SELECT * FROM markers WHERE user_name = '$user_name'")
or die(mysql_error());
// process one row at a time
//header("Content-type: text/xml");
header('Content-type: text/xml; charset=utf-8');
while($row = mysql_fetch_assoc($result)) {
// add node for each row
$occ = $doc->createElement($table_id);
$occ = $root->appendChild($occ);
$occ->setAttribute('lat', $row['lat']);
$occ->setAttribute('lng', $row['lng']);
$occ->setAttribute('type', $row['type']);
$occ->setAttribute('user_name', utf8_encode($row['user_name']));
$occ->setAttribute('name', utf8_encode($row['name']));
$occ->setAttribute('tweet', utf8_encode($row['tweet']));
$occ->setAttribute('image', utf8_encode($row['image']));
} // while
$xml_string = $doc->saveXML();
$user_name2->response;
echo $xml_string;
?>
This is for use with a google map mashup im trying to do. Many thanks if you can help me. If my question isn't clear enough, please say and i'll try to clarify for you. I'm sure this is a simple fix, i'm just relatively inexperienced to do it. Been at this for two days and i'm running out of time unfortunately.
