Can't find the problem with this INSERT

Square1

Hi all:

Spent about 3 hours trying to find the problem with no luck.

I am running an INSERT inside a loop. It isn't inserting. When I run the INSERT out the loop it is fine

Here is the code with the loop:

	if ($stmt = mysqli_prepare($link, "
		SELECT
			promoter,
			shipping,
			eventDate,
			dueBy,
			returnBy,
			note,
			item,
			price,
			itemNote,
			moAS,
			whose
		FROM
			TicketPrices
		WHERE
			evID = ? 
		")) {

 	mysqli_stmt_bind_param($stmt, "s", $pEvId);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_bind_result($stmt,
		$rPromoter,
		$rShipping,
		$rEventDate,
		$rDueBy,
		$rReturnBy,
		$rNote,
		$rItem,
		$rPrice,
		$rItemNote,
		$rMoAS,
		$rWhose
	);
	while (mysqli_stmt_fetch($stmt)) {

		$stmtInsert = mysqli_prepare($link, "INSERT INTO TicketPrices (promoter) VALUES (?)");
		mysqli_stmt_bind_param($stmtInsert, 's', $rPromoter);				
		mysqli_stmt_execute($stmtInsert);
		mysqli_stmt_close($stmtInsert);	

	}
	mysqli_stmt_close($stmt);
	}

I have also run an echo $rPromoter to test the loop. It is working for the echo.

No error messages. it just doesn't enter the row into the table.

Thanks!

Derokorian

2 questions: First if you only need one column why select so many? Second, if you are selecting to insert you can just do that with SQL: INSERT...SELECT

$sql = "INSERT INTO TicketPrices (promoter) SELECT promoter FROM TicketPrices WHERE evID = ?"
if( $stmt = mysqli_prepare($link,$sql) ) {
   mysqli_stmt_bind_param($stmt, "s", $pEvId);
   mysqli_stmt_execute($stmt);
   mysqli_stmt_close($stmt);
}

Also another query: Why are you selecting the promoter from this table, just to insert them back into the same table table as new rows? There may be a better way to do what you are trying to accomplish.

Square1

It is actually quite a few columns being inserted. This was watered down for troubleshooting. A number of variables are coming from different rows of the same table, some variables are coming from elsewhere. Here is the actual INSERT:

$stmtInsert = mysqli_prepare($link, "
				INSERT INTO TicketPrices(
					player,
					promoter,
					location,
					shipping,
					eventDate,
					dueBy,
					returnBy,
					note,
					item,
					price,
					itemNote,
					whose,
					moAS,
					evID
				)
				VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
				");

		mysqli_stmt_bind_param($stmtInsert, 'ssssssssssssss',
			$rPlayer,
			$rPromoter,
			$rLocation,
			$rShipping,
			$rEventDate,
			$rDueBy,
			$rReturnBy,
			$rNote,		
			$rItem,
			$rPrice,
			$rItemNote,
			$rWhose,
			$rMoAS,
			$pDupeEvID
		);

Square1

With regards to:

$sql = "INSERT INTO TicketPrices (promoter) SELECT promoter FROM TicketPrices WHERE evID = ?"

Some of my variables are coming from elsewhere so I don't think this would work. But if I did bring all variables in from a single source, would this enter multiple records at once like a loop?

Square1

mysqli_stmt_store_result($stmt); fixed the problem, but I would like to know more about a better way to do this. If this can be down w/o a loop that is nice!

bradgrafelman

Square1;10997139 wrote:
Some of my variables are coming from elsewhere so I don't think this would work.

Are those values constant for all rows you're INSERT'ing? If so, you can of course add those into the columns that you're SELECT'ing:

SELECT col1, 'some literal string value', 1234, 'some other literal string', col2, ...

Square1;10997139 wrote:
But if I did bring all variables in from a single source, would this enter multiple records at once like a loop?

It would INSERT however many rows were returned by the SELECT statement (ignoring any errors/duplicates, of course).

Square1

bradgrafelman;10997154 wrote:

SELECT col1, 'some literal string value', 1234, 'some other literal string', col2, ...

So for example, is the following correct?

$sql = "INSERT INTO TicketPrices (promoter), MailOrder ('Constant1'), Player (player), Discount ('Constant2'), Location (location) SELECT promoter, player, location FROM TicketPrices WHERE evID = ?"
if( $stmt = mysqli_prepare($link,$sql) ) {
   mysqli_stmt_bind_param($stmt, "s", $pEvId);
   mysqli_stmt_execute($stmt);
   mysqli_stmt_close($stmt);
}

And to take it a step further, it just so happens that the receiving table and the providing table are both 'TicketPrices' in this example. I assume both tables do not need to be the same.

Derokorian

It would be more like:

INSERT INTO TicketPrices
(promoter,MailOrder,Player,Discount,Location)
SELECT
promoter,'Constant1',player,'Constant2',location
FROM TicketPrices
WHERE evID = ?

It's still an insert statement followed by a SELECT instead of a values () part. INSERT INTO table (column list) SELECT columns FROM table. The select is a regular SELECT so you can even use joins.

Square1

Very cool!!