[FIXED] PHP blank space error in SQL query statement

Issue

my first post so go easy please :) I've searched the site for help with this but can't find anything very useful for this situation.

I've a simple PHP/HTML web application using MVC Framework and CodeIgniter that allows a user to select a company from a html select multiple box, and then add information, e.g. rate card information, to an sql database using 2 tables with 1 matching foreign key.

It all works fine if the company name is only one word, but gives database error 1452 when there is spaces in any part of the company name. I've used str_replace() to replace ' ' with '&nbsp' when getting data from the database and vice versa. I've copied error message and code below. Apologies if it's a bit of a mess.

Any help is greatly appreciated, been looking at this for too long :(

Error Message

A Database Error Occurred

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (`custDB`.`RateCard`, CONSTRAINT `RateCard_ibfk_1` FOREIGN KEY (`id_comp`) REFERENCES `Company` (`id_comp`))

CALL add_rate('test agreement', '123456', '');

Filename: /home/user/NetBeansProjects/CCP/models/custDB/custdbmodel.php

Line Number: 112

Line 112 is the SQL query string in the add_rate() function below

HTML page view

<b>
<div>
    <label for="Aggrement Link">Agreement Link</label>
    <input type="input" name="createRateAggreement" /><br />
    <label for="Intial Fee">Initial Fee</label>
    <input type="number" name="createRateIntialFee" /><br />
    <?php
    echo "<select multiple=\"multiple\" name=\"createRateComp\">";
    foreach($companies->result() as $row)
    {
        $tempCompName=$row->name_comp;
        $tempCompName=str_replace(' ','&nbsp;',$tempCompName);
        echo "<option value=$tempCompName>$tempCompName</option>";
    }?></select><br><input type="submit" name="ubmit" value="Create New Rate Card" />
</div>
<?b>

Model Functions

public function add_rate()
{               
    $rateAgreementIN=$this->input->post('createRateAggreement');
    $rateIntialFeeIN=$this->input->post('createRateIntialFee');
    $compNameIN=$this->input->post('createRateComp');              

    $compNameIN=  str_replace(" ", " ", $compNameIN);
    $temp=$this->custDBModel->getCompanyIDBYname_comp($compNameIN)->row();
    $compIDIN=$temp->id_comp;

    $query = $this->db->query('CALL add_rate(\''.$rateAgreementIN.'\', \''.$rateIntialFeeIN.'\', \''.$compIDIN.'\');');
    $query->free_result();
}


public function getCompanyIDBYname_comp($compID)
{
 $query=$this->db->query('CALL getCompanyIDBYname_comp(\''.$compID.'\')');

 $output=$query;

while(mysqli_next_result($this->db->conn_id)) 
{
    if($query = mysqli_store_result($this->db->conn_id))
        {
                mysqli_free_result($query);
        }
}                                  
    return $output;     
}

database stored procedure add_rate

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_rate`(IN AGREEin varchar(255), IN INTIALFEEin double, IN IDCOMPin INT(11))

BEGIN

INSERT INTO RateCard (agreementLink_rate, date_rate, intialFee_rate, id_comp) VALUES (AGREEin, CURDATE(), INTIALFEEin, IDCOMPin);
END

@alex here's the getCompanyIDBYname_comp stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `getCompanyIDBYname_comp`(IN NAMEin varchar(55))
BEGIN
SELECT id_comp
FROM custDB.Company
WHERE name_comp=NAMEin;
END

Solution

Was able to fix this problem... I used the preg_replace("/[^A-Za-z0-9]/", '', $string) method on the HTML select multiple box choice to remove all non alphanumeric spaces from the selection chosen by the user, then edited the SQL stored procedure to remove any white spaces from the db table's name_comp column for the search. It's a bit of a workaround but the application is able to handle company names with spaces in them as it should, so everyone's happy here. Thanks for the help :)

Here's the changes I made to the model function add_rate()

$compNameIN=  str_replace(" ", " ", $compNameIN);

->

$compNameIN=  preg_replace('/[^A-Za-z0-9 ]/','', $compNameIN);

and the getCompanyBYIDcomp_name stored procedure

FROM custDB.Company
WHERE name_comp=NAMEin;
END

->

FROM custDB.Company comp
WHERE replace(comp.name_comp, ' ', '' )= NAMEin
END

Answered By - MollyMoo