[RESOLVED] PHP, MYSQL updating a value

[Jazz00006]

I'm extreamly new to php and mysql but i've manadged to get the basics of retriving a value from a mysql table.

but now i would like to know how i can write to the same table/data/value

what ive got so far is

<?php
$Ref = $_GET['ID']; 
$host = 'localhost';
$db = 'sf_ref'; 
$user = '*'; //Beeped out for foums ;) 
$pass = '*'; //Beeped out for foums ;)
$sql = mysql_connect($host, $user, $pass);
$dbHandle = mysql_select_db($db);
$result = mysql_query("SELECT * FROM `name` WHERE `Ref_Id` = '".$_GET['ID']."';", $sql);
$data = mysql_fetch_assoc($result);

$Name = $data['Name'];
$Number = $data['Ref_Num'];
$Number2 = $Number;
$Number2++;

$sql = "UPDATE name SET Ref_Num='$Number2' WHERE Name = '$Name'" ;
$result = mysql_query($sql) or die("SQL Update failed");
?>

but it doesnt work, can anyone tell me how to make it work or fix my code,
cheers Jazz

[penagate]

I see you've used the "or die" method, which is pretty much stock standard in bad PHP tutorials worldwide. Get rid of it. If you have a problem with SQL always use the mysql_error() function to see exactly what it is.

$sql = "UPDATE name SET Ref_Num='$Number2' WHERE Name = '$Name'" ;
$result = mysql_query($sql);

if (!result)
  echo mysql_error(); 

Also your query could use a bit of tidying up. Don't use quotes around numbers.

$sql = 'update `name` set `Ref_Num` = ' . $Number2 . 'where `Name` = \''. $Name .'\''; 

Try that.


Edit: And:

$Number2 = $Number;
$Number2++; 

Why not just:

$Number2 = $Number + 1; 

[penagate]

You could also do the increment in one query.

$sql = 'update `name` set `Ref_Num` = `Ref_Num` + 1 where `Name` = \''.$Name.'\''; 

Or you could set the auto_increment flag on that field.

Also, your table/field names are a bit unconventional. Table name is usually a plural of entities, like "employees". Your "Name" field in the "name" table makes things unnecessarily confusing.

[Jazz00006]

lol, i told u im not the best @ php coding, and i dont know much about syntax, so i use what i know =S

cheers, it now works

[penagate]

Well now you know

Pop open the "Thread Tools" menu and click "Mark Thread Resolved" if you're happy.

[Jazz00006]

actually, i was just preparing to ask another thing,

how do i do a quick stats thing eg

if($Ref == "Stats") {
// DO a loop through the Ref ID numbers and get the names and the ref_num


echo "User: ".$Name." has referd ".$Number." people."
}

[penagate]

Something like this?

$rows = mysql_query('select * from `name`');
if (is_resource($rows)) {
  while ($row = mysql_fetch_assoc($rows)) {
    echo 'User: ' . $row['Name'] . ' has referred ' . $row['Ref_Num'] . ' people.';
  }
}
else {
  echo mysql_error();
} 

[Jazz00006]

cheers

[Jazz00006]

OK so i tried this script for a while, it worked fine, but then i put in another part and it seemed to stop working, have i done anything wrong or is it my server

you can see it doesnt work @ http://sf.greyfyre.info/ref.php?id=X
X can be either 1 , 2 or 3 or stats (Lowercase)

<?php
//
//Get your ID number
$Ref = $_GET['id']; 
$Total = 0;
$BestName = "N/A";
$BestNum = 0;
if($Ref == "stats") {
$host = 'localhost';
$db = 'sf_ref'; 
$user = '*'; 
$pass = '*';
$sql = mysql_connect($host, $user, $pass);
$dbHandle = mysql_select_db($db);
$result = mysql_query("SELECT * FROM `name` WHERE `Ref_Id` = '".$_GET['ID']."';", $sql);
$data = mysql_fetch_assoc($result);
$rows = mysql_query('select * from `name`');
if (is_resource($rows)) {
  while ($row = mysql_fetch_assoc($rows)) {
    echo 'User:  ' . $row['Name'] . ' has referred  ' . $row['Ref_Num'] . ' people.<BR>';
    $Total = $Total + $row['Ref_Num'];
    if( $row['Ref_Num'] > $BestNum ) {
    $BestName =  $row['Name'];
    $BestNum = $row['Ref_Num'];
    }
  }
  
   echo '<BR>Total people refered  ' . $Total . '.<BR>';
   echo '<BR>Person with the most referd people is   ' . $BestName . ' ';
   echo "<!----";
}
else {
  echo mysql_error();
} 
} else {



$host = 'localhost';
$db = 'sf_ref'; 
$user = '*'; 
$pass = '*';
$sql = mysql_connect($host, $user, $pass);
$dbHandle = mysql_select_db($db);
$result = mysql_query("SELECT * FROM `name` WHERE `Ref_Id` = '".$_GET['ID']."';", $sql);
$data = mysql_fetch_assoc($result);

$Name = $data['Name'];
$Number = $data['Ref_Num'];
$Number2 = $Number + 1; 

$sql = "UPDATE name SET Ref_Num='$Number2' WHERE Name = '$Name'" ;
$result = mysql_query($sql);

if (!result)
  echo mysql_error();  
    


if(isset($_GET['page'])) {
if(stristr($_GET['page'], 'sf') == FALSE) {
   echo '<meta http-equiv="Refresh" content="0;URL=http://sf.greyfyre.info" /> <!----';
  }
echo '<meta http-equiv="Refresh" content="0;URL=' . $_GET['page'] . '" />';
} else {
echo '<meta http-equiv="Refresh" content="0;URL=http://sf.greyfyre.info" />';
}
}
echo "---!>";

?>

thanx for any help (again =S)

[penagate]

I don't understand, where is the issue?

[Jazz00006]

it fails to update the values, is it because the server recently updated its mysql version? or something in the code

[Ecniv]

Make sure it doesn't do the meta refresh and get it to output the sql statement it uses.
Copy that and open your MySql admin page. Paste the Sql into the sql window and run it. Check the table to see if values have updated.

Probably its just not finding or passing the value for the record (key value) to update.

[Jazz00006]

well, im suprised that no-one spotted it, i didn't even see it untill i looked realy hard, it says @ top get id and in the code say ID (dam case sensetive)

heh heh heh now it works