www.webdeveloper.com
Results 1 to 9 of 9

Thread: Total stumped on INSERT statement

  1. #1
    Join Date
    Dec 2008
    Posts
    33

    Total stumped on INSERT statement

    So I am trying to use php to insert data into a mysql table. I have done many Insert scripts but this 1 in particular has got me stumped hardcore.

    The code is:

    PHP Code:
    $feature $_POST['f_featureids'];
    $c count($feature);

    for (
    $i=0$i $c$i++) {
    $sql[$i] = "INSERT INTO Listing_Features SET fk_listingID = '".$id."', fk_featureID = '".$feature[$i]."'";
    $res[$i] = mysql_query($sql[$i]);
    if (!
    $res[$i]) {
        echo 
    '<p style="font-family:Georgia">'.mysql_errno().':'.mysql_error().'</p>';
        }
    sleep(1);

    this is pulling values from a multiple-select form field for features. I've tested this script first by echoing what the output would be and it looks good, but when I actually run it it fails. The error message I keep getting no matter how many insert statement there are is:

    1064: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 'I' at line 1

    I've read up on the 1064 error which means a misuse of a mysql keyword, but the only upercase I's i have in the Insert statement are Insert and Into. Both $id and $feature[$i] are numbers. Any suggestions?

    P.s.
    I have four different Insert commands proceeding this one and they all work just fine, code:

    PHP Code:
    $mls $_POST['f_mlsnumber'];
    $status $_POST['f_statusid'];
    $housetype $_POST['f_housetypeid'];
    $price $_POST['f_askingprice'];
    $featured $_POST['f_featuredlisting'];

    $sql 'INSERT INTO Listing_Data SET mlsnumber = "'.$mls.'", fk_statusID = "'.$status.'", fk_housetypeID = "'.$housetype.'", askingprice = "'.$price.'", featuredlisting = "'.$featured.'"';
    $r mysql_query($sql);

    if (!
    $r) {
        echo 
    mysql_error().'r1<br />';
        }
    sleep(1);

    $get "SELECT listingID FROM Listing_Data WHERE mlsnumber = '".$mls."'";
    $quer mysql_query($get);

    if (!
    $quer) {
        echo 
    mysql_error().'quer<br />';
        }
        
    $array mysql_fetch_array($quer);
    $id $array['listingID'];
    sleep(1);

    $lotsize $_POST['f_lotsize'];
    $sqfoot $_POST['f_sqfoot'];
    $bedrooms $_POST['f_bedrooms'];
    $bathrooms $_POST['f_bathrooms'];
    $floors $_POST['f_floors'];
    $rooms $_POST['f_rooms'];
    $yrbuilt $_POST['f_yrbuilt'];
    $parkingID $_POST['f_parkingid'];
    $vehicles $_POST['f_vehicles'];
    $desc $_POST['f_description'];
    $fees $_POST['f_assocfees'];

    $sqlA 'INSERT INTO House_Info SET fk_listingID = "'.$id.'", lotsize = "'.$lotsize.'", sqfoot = "'.$sqfoot.'", bedrooms = "'.$bedrooms.'", bathrooms = "'.$bathrooms.'", floors = "'.$floors.'", rooms = "'.$rooms.'", yrbuilt = "'.$yrbuilt.'", fk_parkingID = "'.$parkingID.'", vehicles = "'.$vehicles.'", description = "'.$desc.'", assocfees = "'.$fees.'"';
    $rA mysql_query($sqlA);

    if (!
    $rA) {
        echo 
    mysql_error().'rA<br />';
        }
    sleep(1);

    $address $_POST['f_address'];
    $city $_POST['f_city'];
    $state $_POST['f_state'];
    $zip $_POST['f_zip'];
    $countyID $_POST['f_countyid'];
    $schoolID $_POST['f_schooldistrictid'];
    $neighborhood $_POST['f_neighborhood'];

    $sqlB 'INSERT INTO Location SET fk_listingID = "'.$id.'", address = "'.$address.'", city = "'.$city.'", fk_countyID = "'.$countyID.'", state = "'.$state.'", zip = "'.$zip.'", fk_schooldistrictID = "'.$schoolID.'", neighborhood = "'.$neighborhood.'"';
    $rB mysql_query($sqlB);

    if (!
    $rB) {
        echo 
    mysql_error().'rB<br />';
        }
    sleep(1); 

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,227
    Not sure what the problem might be, but I find it helps to break up the query into as many lines as practical to help narrow down exactly where the problem is. For current debuggin purposes you might also want to output the actual query string if it fails.
    PHP Code:
    $sql[$i] = "INSERT
    INTO Listing_Features 
    SET
    fk_listingID = '"
    .$id."', 
    fk_featureID = '"
    .$feature[$i]."'";
    $res[$i] = mysql_query($sql[$i]);
    if (!
    $res[$i]) {
        echo 
    '<p style="font-family:Georgia">'.mysql_errno().':'.mysql_error().'</p><pre>'.$sql[$i].'</pre>';

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2003
    Location
    here
    Posts
    4,551
    Try just editing the query like this:
    PHP Code:
    $sql[$i] = "INSERT
      INTO Listing_Features
      SET `fk_listingID`='
    {$id}',
        `fk_featureID` = '
    {$feature[$i]}'"
    And see if that works or the line number offers better help.

    EDIT: doh, beaten to it.
    If you are using PHP please use the [PHP] and [/PHP] forum tags for highlighting...
    The same applies to HTML and the forums [HTML][/HTML] tags.

  4. #4
    Join Date
    Dec 2008
    Posts
    33
    Ok so I put but the whole statement into a variable called $test and then echoed $test within the loop.

    Code:

    PHP Code:
    $feature $_POST['f_featureids'];
    $c count($feature);

    for (
    $i=0$i $c$i++) {
    $test[$i] = '$sql['.$i.'] = INSERT INTO Listing_Features SET fk_listingID = '.$id.', fk_featureID = '.$feature[$i].'
    $res['
    .$i.'] = mysql_query($sql['.$i.'])';
    //if (!$res[$i]) {
    //    echo '<p style="font-family:Georgia">'.mysql_errno().':'.mysql_error().'</p><pre>'.$sql[$i].'</pre>';
    //    }
    sleep(1);
    echo 
    $test[$i].'<br />';

    echoed results:

    $sql[0] = INSERT INTO Listing_Features SET fk_listingID = 154, fk_featureID = 8 $res[0] = mysql_query($sql[0])
    $sql[1] = INSERT INTO Listing_Features SET fk_listingID = 154, fk_featureID = 9 $res[1] = mysql_query($sql[1])

    As you can see I selected 2 items from the multi-select form hence the 2 sql statements.

    I tried putting $id and $feature[$i] into {} and got the same error and I also tried the <pre>$sql[$i]</pre> and the result was:

    1064: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 'I' at line 1

    "I"

    1064: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 'I' at line 1

    "I"

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,227
    But that doesn't show us exactly, 100&#37;-for-sure what was sent to MySQL, as compared to echoing the actual query string variable that was used in the mysql_query() function, (as was suggested in my preceding reply).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Dec 2008
    Posts
    33
    So I set my script up like you said in your first post:

    PHP Code:
    for ($i=0$i $c$i++) {
    $sql[$i] = "INSERT
    INTO Listing_Features
    SET
    fk_listingID = "
    .$id.",
    fk_featureID = "
    .$feature[$i]."";

    $res[$i] = mysql_query($sql[$i]);
    if (!
    $res[$i]) {
        echo 
    '<p style="font-family:Georgia">'.mysql_errno().':'.mysql_error().'</p><pre>'.$sql[$i].'</pre>';
        }
    sleep(1);

    And the output is:

    1064: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 'I' at line 1

    I
    1064: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 'I' at line 1

    I

    So I'm guess only I from INSERT is being put into $sql[$i].

  7. #7
    Join Date
    Dec 2008
    Posts
    33
    So did a couple more testing and found that taking the [$i] off all the $sql's in the loop made the code work flawlessly. Thanks again NogDog and scragar for the insight.

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,227
    Doh!

    I think the problem is that $sql is a string, not an array. So you are only using the first character of the query string since $sql[0] indicates the first character of the string. You might want to declare it as an empty array right before you start the loop:
    PHP Code:
    $sql = array();
    for(...) { 
    // etc.... 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Dec 2008
    Posts
    33
    Yep i had that Doh! moment too as I posted the <pre></pre> results. Sat there and as I was tring something different the answer to my problem hit me like a ton of bricks!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles