help with sql/javascript interaction

[aqhulcus]

hi, i'm really new to programming in general, and i think i've figured out how to insert and select data from mysql databases, however, i can't seem to get values from a table and work on it

here, i'm trying to

1) grab a number from my table
2) generate a random number in javascript, which i add onto the number i grabbed
3) update the table value to this new number

<?php
$con = mysql_connect("","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $con);

$result = mysql_query("SELECT num FROM number WHERE key=1");


while($row = mysql_fetch_array($result))
  {
  echo ("var num=$row['num']"); 
  }





<script>

var ran=Math.floor(Math.random()*11)

number1=num+ran

document.write(number1);

</script>





mysql_select_db("databasename", $con);

mysql_query("UPDATE number SET num=number1, 
WHERE key=1");

mysql_close($con);
?>

the table has 2 rows, "key" so that i know which number i'm using ( in this case key=1) and num, which is the number that gets worked on

i guess i'm having issues with passing values back and forth from different languages.

thanks in advance!

[pritaeas]

You cannot combine javascript and php like this. First, the javascript is not outputted correctly. Second, even if it was, javascript is not executed on the server (like php) but on the client. These languages have no knowledge of each other.

[aqhulcus]

after a bit of looking around i now understand how javascript and php need to be seperated, and all is well,except i just can't get it to update my table:

<?php
$con = mysql_connect("","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $con);

$result = mysql_query("SELECT * FROM number");


while($row = mysql_fetch_array($result))
  {
  echo $row['num'] . $row['key']. " " . $row['key'];
  echo "<br />";
  }

echo "<br/>";



?>

<script language = 'javascript'>

num= "<?= $row['num'] ?>";

var ran=Math.floor(Math.random()*11)

number1=num+ran

document.write(number1);
</script>

<?php
$con = mysql_connect("","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $con);

mysql_query("UPDATE number SET num = 'number1' 
WHERE key = '1'");

mysql_close($con);
?>

[pritaeas]

It does not work this way. Although you now correctly output your script, there is still the problem that there is no interaction possible between javascript and php. So the outputted value of number1 cannot be read by php. If you want this to work you'll have to do it completely in php.

[aqhulcus]

i'm afraid i am a bit lost sometimes, in regards to programming, learnt it off the internet myself since the beginning.

so skip the javascript altogether since php can handle some arithmetic on its own?

[pritaeas]

http://nl2.php.net/rand

[aqhulcus]

so, replacing the javascript part with php all the way

<?php

$con = mysql_connect("","username","password");

if (!$con)

{
#
die('Could not connect: ' . mysql_error());
#
}

 

mysql_select_db("databasename", $con);

 

$result = mysql_query("SELECT * FROM number");


while($row = mysql_fetch_array($result))

{

echo $row['num'] . $row['key']. " " . $row['key'];

echo "<br />";

} 

 $num=$row['num']



$r = rand( 1, 100 );

$number1 = $num + $r

mysql_select_db("databasename", $con);

mysql_query("UPDATE number SET num = 'number1' 
WHERE key = '1'");

mysql_close($con);
?>

but it's still not working as i'd hoped

[pritaeas]

mysql_query("UPDATE number SET num = '$number1' WHERE key = '1'");

Not sure what you want, but if you want to update every record in the table then the update query should be inside the loop, not outside.

[aqhulcus]

<?php
$con = mysql_connect("","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("databasename", $con);

$result = mysql_query("SELECT * FROM number WHERE key=1");


$row = mysql_fetch_array($result)
  
  echo $row['num'] . $row['key'];
 
  
  
  $num=$row['num']



$r = rand( 1, 100 );

$number1 = $num + $r



mysql_select_db("databasename", $con);

mysql_query("UPDATE number SET num = '$number1' 
WHERE key = '1'");

mysql_close($con);
?>

i take it the loop is the "while" bit? my programming vocab's not great

[aqhulcus]

so i've removed the loop

the key=1 is just a reference point so my code is operating on the correct row of my table, "number"

now i get this feeling that the query is a loop.. is it??

i guess i should say it.. i'm a noob.

[aqhulcus]

anyway, thanks for your patience. seriously, i never realised i had so much wrong till now.

and feel free to redirect me to an online tutorial. haven't come across too many good ones that have examples on how to pass variables from sql to php and back.

[pritaeas]

The code as you now posted will execute the query and show the first record in the result set. It will then update the number to a new one.

Side note: the mysql_select_db on line 29 is not necessary, since it was already selected on line 8.

Have you checked out w3schools yet ?

[aqhulcus]

so the last version of code should work? it seems to have issues with the

$result = mysql_query("SELECT * FROM number WHERE key='1'");

once i remove the "where", things are fine. with it, nothing displays

and then the while part is an issue- without it, nothing displays

while($row = mysql_fetch_array($result))

works, but

$row = mysql_fetch_array($result)

doesn't for me

and till i figure that out i can't really tell if the rest works or not

i hear there are "debugging" programs out there that might be able to help me?

p.s i have checked out w3 schools, alot of the above was copy and pasted from their examples, which i then modified. and thanks again for the help

[aqhulcus]

ahh.. i didn't put semicolons at the end of some rows...

still, it seems i have the wrong syntax for the "where" issue according to the server response

[aqhulcus]

other than this issue

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Key='1'' at line 1

it's working fine. thanks pritaeas

[aqhulcus]

turns out the "where" was part of a reserved words issue

http://www.daniweb.com/forums/thread285461.html

so, with ` ` around my column names, it works. yay!