Results 1 to 5 of 5

Thread: MySQL insert not working...any ideas?

  1. #1
    Join Date
    Dec 2007
    Posts
    151
    Rep Power
    0

    Default MySQL insert not working...any ideas?

    <?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">
    CCNA looking for an opportunity

  2. #2
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    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.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  3. #3
    Join Date
    Apr 2003
    Posts
    13,269
    Rep Power
    34

    Default

    Quote Originally Posted by ramesh View Post
    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.
    Last edited by Arch_Angel; Mar 10, 2012 at 01:47 PM.
    "The best software is the one that fits your needs." - A_A

    Virus free since: date unknown
    Anti-virus free since: August 2008

  4. #4
    Join Date
    Oct 2009
    Posts
    785
    Rep Power
    0

    Default

    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:
    Code:
    $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:
    Code:
    $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:

    Code:
    $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:
    Code:
    $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:
    Code:
    $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.

  5. #5
    Join Date
    Feb 2003
    Posts
    3,184
    Rep Power
    0

    Default

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •