Query not working.

tLittlemore

Hi,

The 2nd piece of code below is really confusing me!

It's querying a DB to get the value of F_Type_Id for a certain F_Desc.

The values of $F_Desc[] come from checkboxes on a form. the code creating the forms looks like the following:

$i = 0;
	while( $query_data = mysql_fetch_row( $result ) ) {
		// first convert the string to an HTML-friendly format
		$query_data[0] = str_replace( " ", "&nbsp;", $query_data[0] );
		print "<br />" . $query_data[0] . " <input name='F_Desc[". $i ."]' type='Checkbox' value=" . $query_data[0] . ">";
		$i++;
		print "Quantity <input name='Qty' type='Text' value='0'>";
	}

The checkboxes work fine and I've echoed the $F_Desc[] array; it gets filled with the relevant checked items.

The code below should get the relevant F_Type_Id based on the F_Desc. But it doesn't! 🙁

If I run the queries directly in MySQL, typing in the relevant F_Desc, I get the correct values returned for the F_Type_Id.

So the problem seems to lie somewhere in passing the value of $F_Desc[$ctr] into the query string. I've tried all sorts of concatenation methods using all manner of quote (single and double) combinations, but I've got nowhere!!

At the moment there are only two F_Descs in the DB so the loop runs through twice.

while( $ctr < count( $F_Desc ) ) {
		// replace the HTML special char &nbsp; with a space
		$F_Desc[$ctr] = str_replace( "&nbsp;", " ", $F_Desc[$ctr] );

	// get the F_Type_Id from each feature
	$query = "SELECT F_Type_Id FROM Feature_Types "
	. "WHERE F_Desc='" . $F_Desc[$ctr] . "'";

	$result = mysql_query( $query, $link_id ) or die( "Query failed at F_Type_Id retrieval stage." );
	$row = mysql_fetch_row( $result );
	$F_Type_Id = $row[0];

	if( $F_Desc[$ctr] != "" ) {	
		// now that F_Type_Id has been retrieved, insert the Feature info
		$query1 = "INSERT INTO Features "
		. "( Feature_Id, Room_Id, F_Type_Id, Qty ) "
		. "VALUES ( NULL, '$Room_Id', '$F_Type_Id', '$Qty' )";

		$result1 = mysql_query( $query1 ) or die( "Query failed at Feature insertion stage." );

	}
	$ctr++;
}

I've done some testing of variables by echoing their values and got the following:

$result = 'resource id#5' and 'resource id#6'
$F_Desc[$ctr] = 'Power Points' and 'TV Points'

The F_Descs are correct and the query works fine when i manually put those values in a MySQL query but the $row[0] variable returned from the mysql_fetch_row function is always a blank string. So I end up inserting all the data into the database but without a F_Type_Id value.

This has got me really stumped and I've spent over half a day staring at it!

If anyone can offer any help, it would be greatly appreciated.

MrAlaska

My eyes are crossed right now, so I can't see the problem either. What does it return when you echo your sql statement back? You mentioned building a sql statement and running a query with the values entered, but what happens when you echo #query1, then copy and past the actual query you tried into MySql?

tLittlemore

Cheers for the reply.

That was a good idea. I echoed both $query and $query1 and they gave the correct string that's needed. I then copied and pasted the output into a direct SQL query and got the expected results returned.

$query echoes: SELECT F_Type_Id FROM Feature_Types WHERE F_Desc='Power Points'

$query1 echoes: INSERT INTO Features ( Feature_Id, Room_Id, F_Type_Id, Qty ) VALUES ( NULL, '23', '', '1' )

So the Features table is getting all the info inserted into it but with a blank F_Type_Id.

It seems to be a problem returning the result of the query from the mysql_fetch_row function.

Thanks

MrAlaska

Well, at least it's getting narrowed down. Now you need to see if the first query is a valid query by pasting it directly into MySql and see if it returns anything. You might just be failing to match the data properly.

tLittlemore

Yeah, I did try the first query aswell and that returns the expected F_Type_Id number.

Just to add to the problem...
I've noticed that the Qty in the DB doesn't take the value of the $Qty variable either...

I've no idea whats going on as I already have several different PHP pages doing similar things.

Cheers

Tim