[PHP/SQL] Not quite sure what the problem is.

So im building a php/SQL website for my RuneScape clan. IT includes a rank system, as well as clan-points and missions.

Im working on the mission system at the moment, but seem to have ran into an unusual problem. I'm sure I'm going about it in the wrong way, and I think someone here can help me.

The way im doing it now, the member starts on the missions.php page, here is how it works:

The player chooses one of the runescape skills from a dropdown box, as well as their level in said skill in an adjacent box.
When the player clicks search for a mission, the page runs a PHP script searching the MISSIONS_DB table in the SQL database for any missions for that selected skill, and level.

Missions.php

PHP Code:

---

<?php

//DATABASE DETAILS TAKEN OUT//

$db_handle = mysql_connect($server, $user_name, $password);
if (isset($_GET['skill'])) {

$skill = $_GET['skill'];
$level = $_GET['level'];


mysql_select_db($database, $db_handle);

$SQL = "SELECT * FROM missions_db WHERE skill = '$skill' AND level = '$level'";

$result = mysql_query($SQL);



while ($db_field = mysql_fetch_assoc($result)) {

print "<div style='border-bottom: 1px solid white;'>";

print "<b>Mission:</b> " . $db_field['desc'] . "<br />";

print "<b>Select:</b> <a href='tools/insert-mission.php?select=" . $db_field['id'] . "'>Select This Mission</a><br />";

print "</div>";



}
}
?>

---

So when the user has selected the mission, by clicking the link to that mission in the LOOP above ^^, it takes them to insert-mission.php.

The problem is in the insert-mission page. I need it to connect to two different tables, the MISSION_DB table, and the MISSIONS table. It needs to grab the description, and amount of points from MISSIONS_DB. It then needs to connect to missions and insert that information, as well as the date and another date set a week ahead of "today".

The way ive done it so far, is dfinitely wrong. but here it is:

Insert-mission.php:

PHP Code:

---

<?php
if($_GET['select'] != "") {
$select = $_GET['select'];

//db info taken out// 

$conn = mysql_connect($dbhost, $dbuser, $dbpass); 

mysql_select_db($dbname, $conn); 

$query1 = "SELECT * from missions_db WHERE id='$select';"; 

$result1 = mysql_query($query1); 
while ($db_field = mysql_fetch_assoc($result1)) {
    $desc = $db_field['desc'];
    $desc = $db_field['skill'];
    $points = $db_field['level'];
}

$today = date("m/d/Y");
$one_week = date("m/d/Y");

$dynamite = explode("/", $one_week);

$month = $dynamite[0];
$next_week = $dynamite[1] + 7;
$year = $dynamite[2];

$dbdate = $month . "/" . $next_week . "/" . $year;

echo "<META HTTP-EQUIV='Refresh' Content='0; URL=insert-mission2.php?user=" . $username . "&points=" . $points . "&desc=" . $desc . "&today=" . $today . "&exp=" . $dbdate . "'>"; 

mysql_close();

}
else
{
    header('Location: ../missions.php');
}
?>

---

insert-mission2.php:

PHP Code:

---

<?php

//DB INFO TAKEN OUT//

$username = $_GET['user'];
$points = $_GET['points'];
$desc = $_GET['desc'];
$today = $_GET['today'];
$dbdate = $_GET['exp'];

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass); 

mysql_select_db($dbname, $conn1); 

$query = "INSERT INTO missions ( user , points , desc , date , stoned , exp_date )         VALUES ( '$username' , '$points' , '$desc' , '$today', , 'blitzed' , '$dbdate' );"; 

mysql_query($query);
mysql_close();
?>

---

Everything works fine until it reaches the part to insert the mission into the MISSIONS table, i echo'd all the information going into the missions table, and they all have values. yet it still wont insert them into the database.

Any help would be greatly appreciated.

Change insert-mission2.php to:

PHP Code:

---

<?php 

//DB INFO TAKEN OUT// 

$username = $_GET['user']; 
$points = $_GET['points']; 
$desc = $_GET['desc']; 
$today = $_GET['today']; 
$dbdate = $_GET['exp']; 

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass);  

mysql_select_db($dbname, $conn1);  

$query = "INSERT INTO missions ( user , points , desc , date , stoned , exp_date )  VALUES ( '$username' , '$points' , '$desc' , '$today', , 'blitzed' , '$dbdate' );";  

mysql_query($query) or die(mysql_error()); 
mysql_close();
?>

---

It should return a MySQL error, with that error try to fix it or just post the error here.

whats your website

EDIT:
your method of doing something simple and doing it in 3 pages is VERY BAD practice. learn to simplify and combine. you can easily do this is 1 page.

A couple things.

1) Your site is easily injectable. Fix that. Google php anti mysql injection, or just look up PHP's mysqli library.

2) As holthelper said, what you are doing is not good practice. You can easily achieve similar results on one PHP script using the $_GET variable and an if checking if the GET variable is set correctly or not.

Condensed all your code into one script. I didn't test it, I did it real quick, so don't count on it working perfectly (if it all :ott1:). You still need to protect it against sql injections by using stuff like:

PHP Code:

---

if(!ctype_alnum($skill)) 


---

or

PHP Code:

---

$skill = preg_replace("/[^0-9]/","", $skill); 


---

PHP Code:

---

<?php
//DATABASE DETAILS TAKEN OUT//

$db_handle = mysql_connect($server, $user_name, $password);
if (isset($_GET['skill'])) {

$skill = $_GET['skill'];
$level = $_GET['level'];

mysql_select_db($database, $db_handle);

$SQL = "SELECT * FROM missions_db WHERE skill = ".$skill." AND level = ".$level;
$result = mysql_query($SQL);

while ($db_field = mysql_fetch_assoc($result)) {
print "<div style='border-bottom: 1px solid white;'>";
print "<b>Mission:</b> " . $db_field['desc'] . "<br />";
print "<b>Select:</b> <a href='mission.php?select=" . $db_field['id'] . "'>Select This Mission</a><br />";
print "</div>";


}
}
elseif($_GET['select'] != "") {
$select = $_GET['select'];

//db info taken out//

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

mysql_select_db($dbname, $conn);

$query1 = "SELECT * from missions_db WHERE id='".$select."'";

$result1 = mysql_query($query1);
while ($db_field = mysql_fetch_assoc($result1)) {
    $desc = $db_field['desc'];
    $desc = $db_field['skill'];
    $points = $db_field['level'];
}

$today = date("m/d/Y");
$one_week = date("m/d/Y");

$dynamite = explode("/", $one_week);

$month = $dynamite[0];
$next_week = $dynamite[1] + 7;
$year = $dynamite[2];

$dbdate = $month . "/" . $next_week . "/" . $year;

echo "<META HTTP-EQUIV='Refresh' Content='0; URL=mission.php?user=" . $username . "&points=" . $points . "&desc=" . $desc . "&today=" . $today . "&exp=" . $dbdate . "'>";

mysql_close();

}
elseif($_GET['select'] !="")
{

//DB INFO TAKEN OUT//
$username = $_GET['user'];
$points = $_GET['points'];
$desc = $_GET['desc'];
$today = $_GET['today'];
$dbdate = $_GET['exp'];

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass);

mysql_select_db($dbname, $conn1);

$query = "INSERT INTO missions ( user , points , desc , date , stoned , exp_date ) VALUES ('".$username."' , '".$points."' , '".$desc."' , '".$today."', , '".blitzed."' , '".$dbdate."')";

mysql_query($query);
mysql_close();
}
else
{
    header('Location: mission.php');
}
?>

---

zkemppel:
Use isset($_GET['select']) instead of $_GET['select'] != "".

$skill = $_GET['skill'];
$level = $_GET['level'];

I see a hacking waiting to happen. CLEAN your variables.

Quote:

---

Originally Posted by EliteGM

$skill = $_GET['skill'];
$level = $_GET['level'];

I see a hacking waiting to happen. CLEAN your variables.

---

While recommended, if you bind parameters using PHP's mysqli library, than you don't need to clean the variables - although it is still recommended, for validation and error reporting purposes, as well as, if you want to, to log possible hacking attempts.

Quote:

---

Originally Posted by timebomb

zkemppel:
Use isset($_GET['select']) instead of $_GET['select'] != "".

---

Read my post again, all I did was combine his script and change his mysql queries, I didn't touch any of the other code.

Quote:

---

Originally Posted by timebomb

While recommended, if you bind parameters using PHP's mysqli library, than you don't need to clean the variables - although it is still recommended, for validation and error reporting purposes, as well as, if you want to, to log possible hacking attempts.

---

of course, but this guy here isn't using mysqli ;)

also just now i'm reading your post about injection, didn't catch that before so sorry for repeating what has already been said!

Quote:

---

Originally Posted by EliteGM

of course, but this guy here isn't using mysqli ;)

---

Yes, that's the problem. Solution = start using MySQLi.

I noticed your meta redirect was unnecessary, all you need to do is insert the data right then and there.

PHP Code:

---

<?php
// connect to mysql database
$db = new mysqli($server, $user_name, $password, $database);

// if ?skill=..
if (isset($_GET['skill']))
{
    // grab desc and id from missions_db. ? indicates dynamic data (see bind_param below)
    $SQL = "SELECT `desc`, `id` FROM `missions_db` WHERE `skill`=? AND `level`=?";

    // prepare the query above
    if ($stmt = $db->prepare($SQL))
    {
        // bind skill and level- which are integers (i); 'ii'
        $stmt->bind_param('ii', $_GET['skill'], $_GET['level']);

        // execute the query above
        $stmt->execute();

        // bind data from query above
        $stmt->bind_result($desc, $id);

        // fetch the data from result directly above
        while($stmt->fetch())
        {
            // print a link for each mission for the user to click
            print "<div style='border-bottom: 1px solid white;'>
            <b>Mission:</b> {$desc} <br />
            <b>Select:</b> <a href='mission.php?select={$id}'>Select This Mission</a><br />
            </div>";
            
        }

        // done with this query, close MySQLi statement
        $stmt->close();
    } else
    {
        // uncomment below to show error if query is unsuccessful.
        #die($db->error); //comment out for DB variable security
        
    }

} elseif (isset($_GET['select']))
{
    $SQL = "SELECT `desc`,`skill`,`level` from `missions_db` WHERE id=?";
    
    if($stmt = $db->prepare($SQL))
    {
        $stmt->bind_param('i', $_GET['select']);
        $stmt->execute();

        $stmt->bind_result($desc, $skill, $level);

        $stmt->fetch();
        $stmt->close();

        $today = date("m/d/Y");
        $one_week = date("m/d/Y");

        $dynamite = explode("/", $one_week);

        $month = $dynamite[0];
        $next_week = $dynamite[1] + 7;
        $year = $dynamite[2];

        $dbdate = $month . "/" . $next_week . "/" . $year;

        $SQL = "INSERT INTO missions ( user , points , desc , date , stoned , exp_date ) VALUES (?,?,?,?,?,?)";

        if($stmt = $db->prepare($SQL))
        {
            // 's' indicates a "string". Correct me if I'm wrong, I can only guess based on your variable names
            $stmt->bind_param('sissss', $username, $points, $desc, $today, $blitzed, $dbdate);
            $stmt->execute();
            $stmt->close();
    
        } else
        {
            #die($db->error);
        }
        
    } else
    {
        #die($db->error);
    }

} else
{
    header('Location: mission.php');
}

---

I commented the parts you probably don't understand yet.

I encourage you to use the MySQL improved (mysqli) extension which supports much more modern features for the MySQL database.

Note: Not tested due to lack of database dependencies.

Thanks for all the help guys, I'm definitely going to change to MYSQLi as well as look up sql injections.