Dynamically change MYSQL query within a PHP file using jQuery .post?

Question

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.

Answer 1

There's nothing wrong with:

$result = mysql_query("SELECT * FROM markers WHERE user_name = '$user_name'");

Other then being ripe for SQL injection - it should work. You may want to try your query directly on the database and see if the results are what you expect.

I'd also recommend turning on error reporting during development. Add the following lines to the top of your document:

error_reporting(E_ALL);
ini_set("display_errors", 1);

and it will help you uncover many errors.

Answer 2

At first, you should escape the $_POST you're inserting straight to the query:

'SELECT * FROM markers WHERE user_name = `' . mysql_real_escape_string($user_name) . '`';

As Erik suggests, don't throw out of the window the most useful warnings - most probably the answer will pop right ahead then.

But what I'm not quite sure about is the way your mashup works. It could get a lot easier if you just do all the stuff inside the php itself, omitting javascript at all. You might also want to check OAuth callbacks - should give you twitter id or user name.