MySQL insert not working...any ideas?

[kevon]

<?php
error_reporting(E_ALL^E_NOTICE);
$connect = mysqli_connect("")//connect details removed;
$doc = $_GET["doctor"];
$username = $_GET["username"];
$sql = "SELECT fname, lname from newpatient where username = '$username'";
$result = mysqli_query($connect, $sql);
$value = mysqli_fetch_row($result);
$fname = $value[0];
$lname = $value[1];
$totalcost = $_GET["totalcost"];
$reason1 = $_GET["reason1"];
$reason2 = $_GET["reason2"];
$reason3 = $_GET["reason3"];
$reason4 = $_GET["reason4"];
$reason5 = $_GET["reason5"];
$reason6 = $_GET["reason6"];
$reason7 = $_GET["reason7"];
$reason8 = $_GET["reason8"];
$date = $_GET["date"];

$reasons = array($reason1,$reason2,$reason3,$reason4,$reason5 ,$reason6,$reason7,$reason8);
rsort($reasons);
$reason1 = $reasons[0];
$reason2 = $reasons[1];
$reason3 = $reasons[2];
$reason4 = $reasons[3];

if(isset($_REQUEST["yes"]))
{
$sql1 = "SELECT * FROM appointments where doctor_name = '$doc' and time = '$time'";
$result1 = mysqli_query($connect, $sql1);
$num_rows = mysqli_num_rows($result1);
if($num_rows > 0)
{
echo "Appointment Time already chosen. Select another time.";
echo "<script language = 'javascript'>document.location.href='make_appointm ent.php?doc=$doc&username=$username'</script>";
}
else
{
$sql2 = "INSERT INTO appointments (username, time, doctor_name, cost, reason1_for_visit, reason2_for_visit,reason3_for_visit,reason4_for_vi sit, fname, lname) values ('$username','$date','$doc',$totalcost,'$reason1', '$reason2','$reason3','$reason4','$fname','$lname' )";
$result2 = mysqli_query($connect, $sql2);
if($result2)
echo "This worked.";
else
echo "Insert did not work.";
//echo "<script language = 'javascript'>document.location.href='registered_lo gin_page.php?username=$username'</script>";
}
}
mysqli_close($connect);
?>
<form>
Doctor:<br> <input type = "text" readonly value ="<?php echo $doc;?>">
<br>
Reason for Visit:<br> <input type = "text" readonly value ="<?php echo $reason1;?>">
<br>
<?if($reason2 != "")
echo "Reason for Visit:<br> <input type = 'text' readonly value ='$reason2'>";?>
<br>
<?if($reason3 != "")
echo "Reason for Visit:<br> <input type = 'text' readonly value ='$reason3'>";?>
<br>
<?if($reason4 != "")
echo "Reason for Visit:<br> <input type = 'text' readonly value ='$reason4'>";?>
<br>
Time:<br> <input type = "text" readonly value ="<?php echo $date;?>">
<br>
Cost:<br> <input type = "text" readonly value ="$<?php echo $totalcost;?>">
<br><br>
Do you want to confirm this appointment <?php echo $fname." ".$lname;?>?<br>
<input type = "submit" name = "yes" value = "YES"><input type = "submit" name = "no" value = "NO">

[ramesh]

I'm not that well versed with SQL queries, but you do have a space in reason4_for_vi sit that I don't think should be there.

[Arch_Angel]

I'm not that well versed with SQL queries, but you do have a space in reason4_for_vi sit that I don't think should be there.

I believe the forum did that to his post. If you click to edit his post, you will see it doesn't have a space there.

@Kevon, 1. what error do you get, if any?
2. Could you provide us with the data types of each table you are trying to insert into?
3. You could also output the sql2 query to your browser and see what values are being inserted into the database.

[techie2000]

Break it up some.. try something like this:

$sql2 = "INSERT INTO appointments
(username, time, doctor_name, cost, reason1_for_visit, reason2_for_visit,reason3_for_visit,reason4_for_vi sit, fname, lname)
values ('" . $username . "' ,'" . $date. "','" . $doc . "'," . $totalcost . ",'" . $reason1. "', '". $reason2 . "','" . $reason3 ."','" . $reason4 . "','" . $fname. "','" . $lname . "' )";

Basically use the PHP string concatenation... unless you are getting a more specific error output...

Also i notice that you are trusting user input. NEVER do that.

Original:

$totalcost = $_GET["totalcost"];
$reason1 = $_GET["reason1"];
$reason2 = $_GET["reason2"];
$reason3 = $_GET["reason3"];
$reason4 = $_GET["reason4"];
$reason5 = $_GET["reason5"];
$reason6 = $_GET["reason6"];
$reason7 = $_GET["reason7"];
$reason8 = $_GET["reason8"];

New:

$reason1  = empty($_GET["reason1"]) ? null : addslashes($_GET["reason1"]);

// or something simpler:

$reason1 = addslashes($_GET["reason1"]);

Basically, If a value is not entered is sets it to null and if a value is present is adds slashes. You should protect your self against SQL injections.

also, you could have saved yourself some time by changing:

$totalcost = $_GET["totalcost"];
$reason1 = $_GET["reason1"];
$reason2 = $_GET["reason2"];
$reason3 = $_GET["reason3"];
$reason4 = $_GET["reason4"];
$reason5 = $_GET["reason5"];
$reason6 = $_GET["reason6"];
$reason7 = $_GET["reason7"];
$reason8 = $_GET["reason8"];
$date = $_GET["date"];

$reasons = array($reason1,$reason2,$reason3,$reason4,$reason5 ,$reason6,$reason7,$reason8);
rsort($reasons);
$reason1 = $reasons[0];
$reason2 = $reasons[1];
$reason3 = $reasons[2];
$reason4 = $reasons[3];

to:

$reasons = Array (1 => addslashes($_GET["reason1"]),
                         2 => addslashes($_GET["reason2"]),
                         3 => addslashes($_GET["reason3"]),
                         4 => addslashes($_GET["reason4"]),
                         5 => addslashes($_GET["reason5"]),
                         6 => addslashes($_GET["reason6"]),
                         7 => addslashes($_GET["reason7"]),
                         8 => addslashes($_GET["reason8"])
                        );

In your SQL you can reference the array directly : $reasons[1] etc...

Try adding some debugging if your SQL still doesnt work:

$result2 = mysqli_query($connect, $sql2);

if (!$result2) { 
   echo "Insert did not work.";
   echo mysql_error();
   //echo "<script language = 'javascript'>document.location.href='registered_lo gin_page.php?username=$username'</script>"; 

  // or terminate execution of script
  die(mysql_error());
}

One more thing, I don't believe anyone should every use inline php. HTML and PHP should always be kept separate because it makes code more maintainable and easier to read. When in doubt php.net is a pretty good documentation and example wise. If all else fails use Google.

[owen]

The problem is usually explained in the ERROR MESSAGE. But I can take a wild guess that your totalcost variable is not a proper number. You should read up on ways to avoid sql injection in php as well. Do it. Do it today.