explode string then insert into database

[Twitch]

Hello all, been trying for over a day to get something to work and after searching and searching I could not find what I was looking for.  I found people looking to do something similar, but the answers seemed more complicated than I would think they need to be.  First off, I'm still horrible at arrays and loops so try not to laugh too hard if my code is waaaayyyy off...haha

 

I am essentially trying to tie products and quantities to a reservation.  The user has the ability to add items to the form.  I'm using jquery .clone to create each new item product and quantity form fields.  When the user submits the form jquery inputs the values of the cloned fields into 2 hidden text fields called "productsField" and "quantitiesField"  The values of these fields look something like value="1,2,2,1,3" depending on how many products were added.

 

What I want to do is explode both and then enter them into the database.  Seems simple enough, but apparently I am not getting it.

 

I thought something like this would work, but it is not.

 

  if (isset($_POST["reserve_button"])) {
   $theReservationID = $_SESSION['createdReservationID'];
  $items = explode (",",$_POST['productsField']); 
$quantities = explode (",",$_POST['quantitiesField']); 

// loop through array 
$number = count($items); 
for ($i=0; $i<=$number; $i++) 
{ 
    // store a single item number and quantity in local variables 
    $itno = $items[$i]; 
    $quant = $quantities[$i]; 
     
    if ($items[$i] <> '') { 
    mysql_query('INSERT INTO reservation_items (reservationID,productID,productQuantity) VALUES($theReservationID,$itno,$quant)'); 
    } 
} 
}

 

Any help would be greatly appreciated.

 

Thanks in advance,

Twitch

[PFMaBiSmAd]

Your logic looks correct.

 

The problem is that php variables are not replaced with their value when used inside of a single-quoted string. Your query statement starts and ends with single-quotes, so the three php variables are just the variable names as strings of characters making up each variable name.

 

You should almost ALWAYS use double-quotes around a query statement.

[robert_gsfame]

So it should be

 

mysql_query("INSERT INTO reservation_items(reservationID,productID,productQuantity) VALUES('$theReservationID','$itno','$quant')");

[Twitch]

Much obliged for the reply, PFMaBiSmAd.

 

It's almost working now.  Putting the double quotes made the insert work.  Thank you.  Unfortunately it seems to only insert the first product and quantity.  If the productsField value is "1,2" and the quantitiesField is "4,3" only one row for the reservation gets inserted using the first values.  So if the reservationID is "30" then I see

 

reservation ID | productID | productQuantity

30                  |  1            |  4

 

in the database instead of

reservation ID | productID | productQuantity

30                  |  1            |  4

30                  |  2            |  3

 

Like you said, my code (now that you informed me about the single and double quotes) should work so I'm baffled again.  Which isn't that uncommon...haha

 

-Twitch

 

[Twitch]

So it should be

 

mysql_query("INSERT INTO reservation_items(reservationID,productID,productQuantity) VALUES('$theReservationID','$itno','$quant')");

 

Yes I put the double quotes around the query and at least I got the insert to work.  Unfortunately the loop isn't.    Trying to figure that out now.

[PFMaBiSmAd]

Any chance that the reservationID column is defined as a key in your table so that duplicates cause a query error?

 

In addition to using double-quotes around the overall query string, I also recommend forming the query string in a php variable, such as $query. This would allow you to echo $query inside of the loop so that you can see what it actually is. You would then use the $query variable inside the msyql_query($query) statement.

 

You can also echo mysql_error(); on the next line after the mysql_query() line to see if the query is failing and producing an error.

[Twitch]

PFMaBiSmAd, you're amazing and I'm an idiot...haha  You nailed the problem.  I had set up the reservation_items table a long time before I actually needed it and I had set the reservationID not only as the primary key, but to auto_increment!  Clearly it doesn't need either in a one to many relationship.

 

I never think to look at the database when a problem occurs, I always focus on the code of the page.

 

Thank you thank you thank you!

 

I am bookmarking this page so I can reference your other suggestions.

 

-Twitch