SQL insert not working... any ideas

[kevonini]

<?php

error_reporting(E_ALL^E_NOTICE);

    $connect = mysqli_connect("");//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_appointment.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_visit, 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_login_page.php?username=$username'</script>";

}

}

mysqli_close($connect);

?>

 

[Mahngiel]

http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing

[Pikachu2000]

Define "not working". Errors? Output? White screen of death?

[kevonini]

Not seeing how string parsing is applicable.......The data is not being sent to the database and the column names are correct and the data types are compatible....

[cpd]

Have you tried to echo the mysql_error?

[Mahngiel]

Not seeing how string parsing is applicable.......The data is not being sent to the database and the column names are correct and the data types are compatible....

 

String parsing is relevant because you're attempting to send strings contained within variables to mysql.  Double quoting php variables ensures the literal parsing.

[Pikachu2000]

There's nothing wrong with the way the query string is quoted. Echoing it and making sure it contains the expected values would be helpful, though.

[kevonini]

what wud be the mysqli value to send to the mysqli_error function?

[PFMaBiSmAd]

mysqli_error requires the connection link as a parameter - echo mysqli_error($connect);

[kevonini]

Got this error message - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''','','','','','')' at line 1

This would mean that empty strings are being passed to the database... but the values for these can be null so what am i missing?

[Pikachu2000]

$totalcost, being a presumably numeric value is unquoted in the query string. If there is no value, you'll need to explicitly assign it 0 or NULL. You also should be developing with the following directives in your php.ini file: error_reporting = -1 and display_errors = On.

[kevonini]

There is a value for $totalcost

[Pikachu2000]

It would appear that in reality, there is not. Have you echoed the query string yet?

[kevonini]

It seems u r right.. $totalcost is known until it reaches that section of the code. Going to try to pull it from the database inside the statement block.

[redsmurph]

My take on SQL is to abstract it whenever possible, as you always have to escape your values and the SQL syntax is generally easy to get wrong, without any static syntax control what-so-ever.

 

sqlinsert below takes $values as an associative array of column values.

 

sqlquery is just mysql_query with error reporting via e-mail, so you can directly replace it with mysql_query.

 

You can thank me later.

 

Cheers,

Anders

 

function esc($text)

{

    return mysql_real_escape_string($text);

}

 

function sqlinsert($table, $values)

{

    $len = sizeof($values);

    if ($len > 0)

    {

        $query = "INSERT INTO $table ( ";

        $first = true;

        foreach ($values as $column => $value)

        {

            $query .=!$first ? ', ' : '';

            $first = false;

            $query .= "`$column`";

        }

 

        $query .= ' ) VALUES ( ';

 

        $first = true;

        foreach ($values as $column => $value)

        {

            $query .=!$first ? ', ' : '';

            $first = false;

            $query .= "'" . esc($value) . "'";

        }

 

        $query .= ' )';

        sqlquery($query);

        return mysql_insert_id();

    }

 

    return false;

}

 

 

[kevonini]

I am a newbie and your abstraction has me more confused than before.

[redsmurph]

Replace:

sqlquery --> mysqli_query

mysql_insert_id --> mysqli_insert_id

 

Then run the function like this:

 

$id_appointments = sqlinsert('appointments', array('username' => $username, ...));

 

or create the array first and put it in the call:

 

$values = array();

$values['username'] = $username;

...

$id_appointments = sqlinsert('appointments', $values);

 

If you want to test on the result:

 

if ($id_appointments !== false)

{

    // It worked !

}

else

{

    // It failed !

}

 

Cheers,

Anders

[kevonini]

A few issues....not sure what parameters to use now for the mysqli_query - normally i use the ($link, $query) now it is picking up the link as null...also not sure what to use for mysqli_insert_id...thought it wud be the link but that is showing as null as well. A number of warnings for the escape sequence part as well.

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in C:\xampp\htdocs\confirm_appointment.php on line 57

 

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\confirm_appointment.php on line 82

 

Warning: mysqli_insert_id() expects parameter 1 to be mysqli, null given in C:\xampp\htdocs\confirm_appointment.php on line 83

 

[kevonini]

figured out the last two warnings...but the first one is still an issue and the insert still did not work.

[redsmurph]

I never use mysqli, so I wasn't aware of differences, but this seems to explain it:

 

link: A link identifier returned by mysqli_connect() or mysqli_init()

 

So you need to add $connect to both esc and sqlinsert and add $connect to the call of esc.

 

function esc($connect, $text)

{

    return mysql_real_escape_string($connect, $text);

}

 

I hope you can "connect the dots".

[kevonini]

Warnings figured out and after the abstraction it comes back to the same issue....something wrong with the syntax of my query...

[redsmurph]

Echo mysqli_error($connect) if it still fails.

[kevonini]

I did that already that's how I know it's still the sql syntax.

[redsmurph]

Please state the complete query string (the completed $query in sqlinsert).

 

I suspect the table somehow doesn't match what you try to insert.

[kevonini]

Think i'm going to call this one a loss and change the requirements for the query... this is a small issue and it's taking up too much time....Thanks much for all the assistance....I learnt quite a lot!