Incorrect integer value: '' for column 'compid'

PHP
1

Hi All, i’ve run into an issue and i cant see the wood through the trees.

The tables in question are the contacts & company table. The contact form is what i’m currently working on where staff can add new customers and assign them to a company. The company id (compid) is a foreign key that links the contacts table to the companies table. The odd thing is, its pulling the data from the companies table but wont submit the form as it’s giving me an incorrect integer value. However in the value box, i have echo’d the id of the company.

Please can someone tell me what i’m missing. Thanks in advance.

<th class="text-nowrap" scope="row">Company Name</th>
                            <td><div class="form-group <?php echo (!empty($compid_err)) ? 'has-error' : ''; ?>">
                    <select type="text" name="compid" class="form-control" value="<?php echo $compid; ?>">
                    <option hidden>Select Company</option>



                    <?php
                    $get_comp_list_SQL = "SELECT * FROM `companies` ORDER BY `compname` ASC";

                    $result_get_comp_list = mysqli_query($con,$get_comp_list_SQL);
                    // while loop
                    while($row_get_comp_list = mysqli_fetch_array($result_get_comp_list)) {
                    $comp_list_ID =             $row_comp_list['id'];
                    $comp_list_name =             $row_get_comp_list['compname']; 
                    ?>
                        <option value="<?php echo $comp_list_ID; ?>"><?php echo $comp_list_name; ?></option>
                    <?php
                    }
                    ?>
                    </select>
                        </div></td>
                            </tr>
2

We can’t troubleshoot a query you don’t show us.

3

Does everything look OK in the resulting HTML?
Are the values in the options all integers as expected?
I’m not sure about having a value on the select though.

1 Like
4

Hi M_hutley

What do you need me to post? i thought i might just be missing a trick with the code above?

5

Well you tell us that your insert query is throwing the error in the topic.

Your code above doesnt contain an insert query :stuck_out_tongue:

1 Like
6

You are talking about a problem when submitting the form, but we don’t see what happens to the submitted data, only the form creation.

7

The sumbitted data doesn’t go anywhere. I get an error 500 and this…

PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect integer value: ‘’ for column ‘compid’ at row 1 in …/z_createcontact.php:76
Stack trace:
#0 …/z_createcontact.php(76): mysqli_stmt_execute(Object(mysqli_stmt))
#1 {main}
thrown in …/z_createcontact.php on line 76

8

So what’s in z_createcontact.php?

9

Thats the name of the page that creates a contact and is what the above extract is from. The thing that i don’t understand is that its pulling the ID & company name from the company table, so in theory having inside the option it should post the id related to the company to the contacts table?

10

I don’t see that anywhere in the code you posted.

11

Its not. This is what i have in my statement…

/ Check input errors before inserting in database
    if(empty($fname_err) && empty($lname_err) && empty($email_err) && empty($contactno_err)){
        // Prepare an insert statement
        $sql = "INSERT INTO contacts (compid, fname, lname, title, email, contactno, mobileno, role, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
         
        if($stmt = mysqli_prepare($con, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "sssssssss", $param_compid, $param_fname, $param_lname, $param_title, $param_email, $param_contactno, $param_mobileno, $param_role, $param_status);
// Set parameters
            $param_compid = $compid;
			$param_fname = $fname;
			$param_lname = $lname;
            $param_title = $title;
            $param_email = $email;
			$param_contactno = $contactno;
            $param_mobileno = $mobileno;
			$param_role = $role;
			$param_status = $status;
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                echo "Record created successfully"; //Redirect to landing page
                header("location: contacts.php");
                exit();
            } else{
                echo "Something went wrong. Please try again later.";
            }
        }
         
        // Close statement
        mysqli_stmt_close($stmt);
    }
    
    // Close connection
    mysqli_close($con);
}
?>
12

Which is what we’ve been asking you to post :stuck_out_tongue:
Your bindparams say $param_compid is a string. Mysql is expecting it to be an integer. So it’s failing.
mysqli_stmt_bind_param($stmt, “sssssssss”, $param_compid, $param_fname, $param_lname, $param_title, $param_email, $param_contactno, $param_mobileno, $param_role, $param_status);

2 Likes
14

Doh! Sorry gents.

That is a great spot m_hutley. I just need to change this to an i (in theory) for it to work right? I knew i was missing something :man_facepalming:

1 Like
15

I… think so. I’m not 100% sure if the binding will take care of the String → Integer conversion for you or if you need to explicitly convert it when you say $param_compid = $compid.

Try it. If it fails, try explicitly converting your string to an integer, and try again.

1 Like
16

Ok, i think you may be onto something there. Instead of submitting the compid to the contacts table its throwing in a 0.

How do i explicitly convert to to an integer?

I tried this but this is making it post a 0 and dropping the email from the form submit.

if($stmt = mysqli_prepare($con, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "issssssss", $param_compid, $param_fname, $param_lname, $param_title, $param_email, $param_contactno, $param_mobileno, $param_role, $param_status);
17

To be honest if there is a better way of doing this i am all ears :slight_smile:

18

What is the value of $compid when it goes into the processor?

As Sam pointed out, your <select> should not have a value. The ‘value’ of the select is provided by the chosen<option> within it.

19

The value should be that of the company id from which it came from (if that makes sense).
Apologies Sam i must have missed your comment.
Interesting point though. I’ll drop out the value to see if that stops throwing it off.

20

PDO :stuck_out_tongue:

1 Like
21

Well i know what it should be, I asked what it IS :wink:

echo the value out so you can see what the script thinks it’s getting. That way you can figure out if it’s a problem in your conversion to integer, or the form’s just not sending the right value.

1 Like