Variable from url, using it in mysql...

Oni

Hey,

I seem to have a little problem with the beginning of my code.

There is a page where the user gives user id in variable and I want to be able to use that variable in mysql statements. At first, I check to see if the variable is a number and then comes the mysql part... It seems as though the variable loses it's value after I use it once. I dunno how to have it keep it?

<?php
If ( $user > '0' ) {
mysql_connect("localhost", "surfall", "psw") or die(mysql_error());
mysql_select_db("surfall_surf") or die(mysql_error());

//Update surfers stats
$result = mysql_query("SELECT id, credits, earned FROM users WHERE id='$user'") or die(mysql_error()); 
$row = mysql_fetch_array( $result );
$id = $row['id'];
$credits = $row['credits'];
$earned = $row['earned'];

$credits = ($credits + '0.7');
$earned = ($earned + '0.7');

$result = mysql_query("UPDATE users SET credits='$credits' AND earned='$earned' WHERE id='$user'")
or die(mysql_error()); 

//etc...
?>

Any ideas?

thorpe

Because $user is not defined anywhere. You need to use the $GET[] superglobal.

if ($_GET['user'] > 0) {

Also note that intergers should not be surrounded in quotes.

Oni

Thanks for clearing up the number quotes thing... I wasn't too sure about them 😉

When it comes to the rest of the code, I tried what you suggested and the code still doesn't work? As in, just a blank page.

Below is all of the code on the page:

<?php
$user = $_GET['user'];
if ($user > 0) { 
mysql_connect("localhost", "surfall", "psw") or die(mysql_error());
mysql_select_db("surfall_surf") or die(mysql_error());

//Update surfers stats
$result = mysql_query("SELECT id, credits, earned FROM users WHERE id='$user'") or die(mysql_error()); 
$row = mysql_fetch_array( $result );
$id = $row['id'];
$credits = $row['credits'];
$earned = $row['earned'];

$credits = ($credits + '0.7');
$earned = ($earned + '0.7');

$result = mysql_query("UPDATE users SET credits='$credits' AND earned='$earned' WHERE id='$user'")
or die(mysql_error()); 






//Get site to show
$result = mysql_query("SELECT doono, credits, ref FROM users WHERE credits >= 1
ORDER BY RAND()
LIMIT 1") or die(mysql_error()); 

$row = mysql_fetch_array( $result );

$credits = $row['credits'];
$doono = $row['doono'];
$credit = $row['credits'];
$ref = $row['ref'];
$credits--;

$result = mysql_query("UPDATE users SET credits='$credits' WHERE doono='$doono'")
or die(mysql_error()); 




header("Location: http://doono.com/?id=$doono");
}
else {
print "That user can't be found!";


?>

bradgrafelman

For one thing, you're missing a closing } so you either need to add one to close the final else statement, or just remove the opening brace since the else statement contains a single operation, i.e.

if($blah == TRUE) {
     // do stuff..
     // do stuff..
} else
     // do one thing.

Also, if 'id' is a numeric type in your MySQL table (I'm betting it is... at least, it should be) then don't use single quotes around the number in your MySQL queries

Lastly, if the whole 'if ($user > 0) {' bit is to check if they entered a number (and not text), then you're better off using [man]is_numeric/man to check this, because PHP will parse the string "1; DROP DATABASE test;" and see the 1 in the beginning and then stop when it comes to a non-numeric character, giving that string a numeric value of greater than 0.

Oni

Thanks for the help 😉

The code works almost perfectly now...

But, when I try to update the "credits" column with new data, for some reason it always records it as "0"

As for the "earned" column, it doesn't change the value?

<?php
$user = $_GET['user'];
if (is_numeric($user)) { 
mysql_connect("localhost", "surfall", "psw") or die(mysql_error());
mysql_select_db("surfall_surf") or die(mysql_error());

//Update surfers stats
$result = mysql_query("SELECT id, credits, earned FROM users WHERE id=$user") or die(mysql_error()); 
$row = mysql_fetch_array( $result );
$id = $row['id'];
//$credits = $row['credits'];
//$earned = $row['earned'];

$credits = ($row['credits'] + 0.7);
$earned = ($row['earned'] + 0.7);

$result = mysql_query("UPDATE users SET credits=$credits AND earned=$earned WHERE id=$user")
or die(mysql_error()); 






//Get site to show
$result = mysql_query("SELECT doono, credits, ref FROM users WHERE credits >= 1
ORDER BY RAND()
LIMIT 1") or die(mysql_error()); 

$row = mysql_fetch_array( $result );

$credits = $row['credits'];
$doono = $row['doono'];
$credit = $row['credits'];
$ref = $row['ref'];
$credits--;

$result = mysql_query("UPDATE users SET credits=$credits WHERE doono=$doono")
or die(mysql_error()); 




header("Location: http://doono.com/?id=$doono");
}
else {
print "That user can't be found!";
}

?>

bradgrafelman

After this:

$credits = ($row['credits'] + 0.7); 
$earned = ($row['earned'] + 0.7);

Try echo'ing out the variables so you can see what their values are. If the values look right, then try printing your query.

The way I normally do MySQL queries is this:

$query = 'SELECT doono, credits, ref FROM users WHERE credits >= 1 ORDER BY RAND() LIMIT 1';
$result = mysql_query($query) or die(mysql_error());

This way, if the query isn't behaving as expected, you can simply throw in an "echo $query;" before the mysql_query() and visually inspect it.

Oni

I tried the mysql thing you suggested. The query looks okay to me?

UPDATE users SET credits=0.7 AND earned=1.7 WHERE id=1

However, same problem... It doesn't update "earned" and it updates "credits" as a value of 0...

bradgrafelman

What type are the fields "credits" and "earned" ?

You can try adding single quotes around them... though I just tried inserting numeric values into my server (numeric meaning without quotes) for a column type of FLOAT and it worked fine...

Oni

They're "varchar"

They will contain decimal numbers. (ex: 17.4)

bradgrafelman

Well that would explain it! If they're numbers... use a numeric type! Make them floats.

Oni

Nope,

Still doesn't work?

And I also tried to change the "extra" part of my id column to make it auto_increment and float but I got an error?

bradgrafelman

Auto_increment means automatically make the column in each row increase by one. It's commonly used for an "ID" field to identify each row - a unique number to index each row.

If you changed it to float, and it still doesn't work... try adding single quotes around the values in your query.

Also, what version of MySQL server are you running?

Oni

I'm using "4.1.18-standard-log"

I'll try to erase all entries and then set the id column as auto_increment...

Oni

I still get:

 SQL query:

ALTER TABLE `users` CHANGE `id` `id` INT( 5 ) NOT NULL AUTO_INCREMENT

MySQL said: Documentation
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key