insert javascript result into mysql table?

[paulmo]

I need to INSERT the javascript result, geoip_city and geoip_region_name into 2 fields in mysql db table (I know how to insert the values from php form into db table). The js is embedded in the php code, and it's called within several different "phrases" that are within cases; I've posted one example below.

 

Hope this is possible...thanks for help!

 

$phrase7= 'Although the landscape is dormant, you are experiencing a season of growth! You have found a renewed sense of focus and direction, afforded by
quiet hours of productivity. Let the clear, cold air continue to inspire your progress in    
<script language="JavaScript" src="http://j.maxmind.com/app/geoip.js"></script>
<script language="JavaScript">document.write(geoip_city());</script>, 
<script language="JavaScript">document.write(geoip_region_name());</script>.';
break;

[rhodesa]

since the JavaScript isn't parsed/executed until AFTER php is done, and the code is sent to the user's browser, you will need to use AJAX to send the data to your server.

 

the easiest way to do an AJAX call is with jQuery (in my opinion):

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript">
  $(function(){ //This syntax will execute when the page is done loading
    $.post("geo.php",{
      geoip_city: geoip_city(),
      geoip_region_name: geoip_region_name()
    });
  });
</script>

 

the above will do a POST to geo.php with $_POST['geoip_city'] and $_POST['geoip_region_name'] set. the you can use PHP to store the data into mysql

[paulmo]

rhodesa, thanks for getting me started...i've wanted to use jquery.

 

have created geo.php page and put your code into the other php page. getting

Parse error: parse error, unexpected '<'  

here:

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>

 

then tried

src=jquery-1.2.6.min.js

and uploaded that file to root. same error message.

 

this is what my geo.php page looks like:

<?php
ini_set("display_errors", 1);
error_reporting(E_ALL); 


mysql_connect("xxx", "xxx", "xxx") or die(mysql_error()); 
mysql_select_db("xxx") or die(mysql_error()); 

$geoip_city = mysql_real_escape_string($_POST['geoip_city']);
$geoip_region_name = mysql_real_escape_string($_POST['geoip_region_name']);

mysql_query("INSERT INTO interactive (geoip_city, geoip_region_name) VALUES ('$geoip_city', '$geoip_region_name') ") or die(mysql_error()); 
?>

 

of course, i've created geoip_city and geoip_region_name fields in db table.

thank you for your help!

[rhodesa]

the PHP page where you put the JS...did you close php before it? or at least wrap it in an echo? what is the code of your main PHP page?

[paulmo]

This is working almost perfectly; thanks! I had not closed off php, so I did (not sure how to do with echo):

('$name', '$theme') ") or die(mysql_error()); 
?>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript">
  $(function(){ //This syntax will execute when the page is done loading
    $.post("geo.php",{
      geoip_city: geoip_city(),
      geoip_region_name: geoip_region_name()
    });
  });
</script>
<?php
switch (intval(date('n')))

 

I thought js could be embedded in php? It works that way for the geoip js. And why go to http jquery instead of use jquery file? (I'm a newbie on this.)

 

Only problem now is—perhaps because table fields (name/theme & geoip_city/geoip_region_name) are being processed by different pages—the db table rows are not matching up; the name/theme fields are in same row but geoip info is appearing unpredicatably: sometimes above, then below all the other rows. Of course, I need to see name/theme/geo city and name all in same row.

 

Advice on this? thanks

 

[rhodesa]

i think you misunderstand the integration of PHP/HTML/JavaScript...PHP is a server-side language and HTML/JavaScript are client-side languages. So, when you are looking at your script, all the PHP that is in it is run first by the server, to generate the HTML/JavaScript that is then sent to the client's browser. Only AFTER all this is generated, the client's browser runs the HTML/JavaScript.

 

can you post the entire code for the PHP page? i can explain it better with using your code as an example

[paulmo]

I understand the server side/browser side concept. Just seemed odd that js does work embedded in php in one instance but not in the present example.

 

I appreciate your help. As I can't post the entire page, I'll try to re-state my question: how to get values from different pages inserted into same row in db table?

 

thanks

[rhodesa]

ok.....i think i see what you mean...on the main page, do the INSERT and get the ID for the row created using mysql_insert_id(). Then, pass that ID with the AJAX request, and have geo.php run an UPDATE on the row with that ID. does that make sense?

[paulmo]

how close is this for getting all in same db row? from main page:

mysql_query("INSERT INTO interactive (name, theme) VALUES ('$name', '$theme') ") or die(mysql_error()); 
mysql_insert_id();

from geo.php page:

mysql_query("INSERT INTO interactive (geoip_city, geoip_region_name) VALUES ('$geoip_city', '$geoip_region_name') ") or die(mysql_error()); 
mysql_insert_id(UPDATE);

[rhodesa]

assuming your Auto-incrementing Primary Key on the table is called 'id', like this:

<?php
mysql_query("INSERT INTO interactive (name, theme) VALUES ('$name', '$theme') ") or die(mysql_error());
$row_id = mysql_insert_id(); //Put it in a variable for later
?>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript">
  $(function(){ //This syntax will execute when the page is done loading
    $.post("geo.php",{
      geoip_city: geoip_city(),
      geoip_region_name: geoip_region_name(),
      row_id: '<?php echo $row_id; />' //Pass it with the JavaScript
    });
  });
</script>

 

<?php
ini_set("display_errors", 1);
error_reporting(E_ALL); 


mysql_connect("xxx", "xxx", "xxx") or die(mysql_error()); 
mysql_select_db("xxx") or die(mysql_error()); 

$geoip_city = mysql_real_escape_string($_POST['geoip_city']);
$geoip_region_name = mysql_real_escape_string($_POST['geoip_region_name']);
$row_id = mysql_real_escape_string($_POST['row_id']);

mysql_query("UPDATE interactive SET geoip_city = '$geoip_city', geoip_region_name = '$geoip_region_name' WHERE id = '$row_id'") or die(mysql_error()); 
?>

[paulmo]

rhodesa, have done your edits, thanks. did not have primary key, so created "id" field (medint), value 0, and made it primary. the geoip city and region are not posting at all now in the db ("name/theme" did post), and after the 2nd form submit, get error message:

#1062 - Duplicate entry '0' for key 1 

which was same as previous error message in mysql (phpadmin) before deleting all previous db field entries.

 

curiously, "id" row is "0" upon 1st form/field entry ("name/theme"). 

[paulmo]

i've ALTER TABLE AUTO INCREMENT "id" field in mysql query, and selected field as primary, and now "id" field is auto incrementing with numbers, so that looks good (i'm guessing sole purpose is to define row_id). but the geoip city and region are still not posting to the db.

 

i did replace a closing "/>" with "?>" from here

row_id: '<?php echo $row_id; /> //Pass it with the JavaScript

because reported error on that line. the code all seems to work now just this problem above.

 

Thanks for help!!

[rhodesa]

glad you got it working!

[paulmo]

the id field is working

but the geoip city and region are still not posting to the db.

.

 

idea on why this is not working? i've checked and double check all the code you've provided above.

[rhodesa]

ok...time for some debugging...let's add a function that alerts the ajax response:

<?php
mysql_query("INSERT INTO interactive (name, theme) VALUES ('$name', '$theme') ") or die(mysql_error());
$row_id = mysql_insert_id(); //Put it in a variable for later
?>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript">
  $(function(){ //This syntax will execute when the page is done loading
    $.post("geo.php",{
      geoip_city: geoip_city(),
      geoip_region_name: geoip_region_name(),
      row_id: '<?php echo $row_id; /> //Pass it with the JavaScript
    },function(message){
      alert(message);
    });
  });
</script>

 

and print some stuff for it to alert:

<?php
ini_set("display_errors", 1);
error_reporting(E_ALL); 


mysql_connect("xxx", "xxx", "xxx") or die(mysql_error()); 
mysql_select_db("xxx") or die(mysql_error()); 

$geoip_city = mysql_real_escape_string($_POST['geoip_city']);
$geoip_region_name = mysql_real_escape_string($_POST['geoip_region_name']);
$row_id = mysql_real_escape_string($_POST['row_id']);

$sql = "UPDATE interactive SET geoip_city = '$geoip_city', geoip_region_name = '$geoip_region_name' WHERE id = '$row_id'";
print $sql;
mysql_query($sql) or die(mysql_error());
?>

[paulmo]

ok, made those modifications. city/region still not posting to db.

[rhodesa]

but when you submit it...does nothing happen? you should get an alert box with some info in it

[paulmo]

i'm getting no alert box, on web page after form's processed, or anywhere in mysql (guessing alert would be on page though).

 

the city/region is working on form, just not posting to db table; id/name/theme/date are all working, and on same line.

[premiso]

i'm getting no alert box, on web page after form's processed, or anywhere in mysql (guessing alert would be on page though).

 

the city/region is working on form, just not posting to db table; id/name/theme/date are all working, and on same line.

 

AJAX is a pain in the butt to debug. What I usually do, with post data anyways, is create a form that is just straight html to post to that ajax page, so when you submit it you see the feedback.

 

Chances are you have an error in your code and the script is throwing the error and ajax does not know how to handle that well so it just does nothing.

[paulmo]

success! was missing the comma here:

geoip_region_name: geoip_region_name(),

 

i've put // marks around the alert function and $_mysql print to remember this.

 

thanks!!

[premiso]

success! was missing the comma here:

geoip_region_name: geoip_region_name(),

 

now to get rid of ie alert box?:

UPDATE interactive SET geoip_city = '$geoip_city', geoip_region_name = '$geoip_region_name' WHERE id = '20'

 

thanks!

 

JS Syntax error, should have known. That is what a good JS Debug Console is for (Firefox has one built in) and I always check that first if my AJAX does not work =)

 

Good luck.

[paulmo]

thanks premiso, will check out debug console. as mentioned above, made notes around alert function for future. no more alert box. learned a lot here—thanks.